PHP MySQL WHERE column-value is in $_POST

336

I'm trying to get all the rows which contain a particular text. However, when I execute the query, no rows are returned. I'm retrieving the text from a post request which looks like this "Krachttraining,Spinning" (= 2 values). I think my code fails on the following part (if I leave this out, the query returns some rows):AND CONCAT('%', sport.name, '%') LIKE $sports.

FYI. I know you can perform SQL injection on this, this will be fixed later.

<?php
$servername = "SECRET";
$username = "SECRET";
$dbpassword = "SECRET";
$dbname = "SECRET";

$lat = $_POST['lat'];
$lng = $_POST['lng'];
$sports = $_POST['sports'];

echo $sports; //Echo's: Krachttraining,Spinning.

// Create connection.
$conn = new mysqli($servername, $username, $dbpassword, $dbname);

// Check connection.
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT gym.id FROM gym, sport, gym_sport WHERE lat BETWEEN '$lat'-1 AND '$lat'+1 AND lng BETWEEN '$lng'-1 AND '$lng'+1 AND gym.id = gym_sport.gym_id AND sport.id = gym_sport.sport_id AND CONCAT('%', sport.name, '%') LIKE $sports";
$result = $conn->query($sql);
$output = array();

if ($result->num_rows > 0) {
    // output data of each row.
    while($row = $result->fetch_assoc()) {
        $id = $row["id"];
        array_push($output, $gym);
    }

    //Brackets because of GSON's parser.
    echo "[" . json_encode($output) . "]";

}

$conn->close();
?>

EDIT: Changed SQL statement to:

$sql = "SELECT * FROM gym, sport, gym_sport WHERE lat BETWEEN '$lat'-1 AND '$lat'+1 AND lng BETWEEN '$lng'-1 AND '$lng'+1 AND gym.id = gym_sport.gym_id AND sport.id = gym_sport.sport_id AND sport.name LIKE '%".$sports."%'";

Still getting 0 rows returned.

EDIT 2: I ran the following code in my phpMyAdmin environment, and it returned 0 rows.

Select * FROM sport WHERE name LIKE '%Krachttraining,Spinning%';

However when I'm running the following code, it returns 1 row:

Select * FROM sport WHERE name LIKE '%Krachttraining%';

I don't really get it what I'm doing wrong, any thoughts?

32

Answer

Solution:

I think you want to use theIN statement. This will check if any word in the array matches.
For instance:Select * FROM sport WHERE name IN ('Spinning', 'Krachttraining');
Will return every row which has the name Spinning or Krachttraining.

905

Answer

Solution:

Just use:

SELECT .FROM...WHERE AND sport.name LIKE '%".$sports."%'";

After question editing

After you changed the question, I suggest to take a look at this answer to better understand what you should to do: https://stackoverflow.com/a/3902567/1076753

Anyway I think that you have to learn a bit about the like command: http://www.mysqltutorial.org/mysql-like/

920

Answer

Solution:

Change the sql to :

$sql = "SELECT gym.id FROM gym, sport, gym_sport WHERE lat BETWEEN '$lat'-1 AND '$lat'+1 AND lng BETWEEN '$lng'-1 AND '$lng'+1 AND gym.id = gym_sport.gym_id AND sport.id = gym_sport.sport_id AND sport.name LIKE '%".$sports%."'";

People are also looking for solutions to the problem: php - How to access all the incoming emails for all different accounts on a mail server?

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.