PHP MySQL WHERE column-value is in $_POST

Question

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?

wrap it in quotes then. checking for errors on the query would have told you about it — Funk Forty Niner, Mar 24 '17 at 13:12
You're treating Strings $lat as Integers in your query. That might be the problem. — matfax, Mar 24 '17 at 13:12
*"EDIT: Instead of downvoting my question, please eloborate what's wrong with it."* - wasn't mine but your question stands to get closed based on string literals not quoted. Edit: and it was. Btw; that should have been a comment, rather than in the answer. — Funk Forty Niner, Mar 24 '17 at 13:13
your thinking goes in the wrong direction us it like `" ... LIKE '%".$sports."%'"` — edi, Mar 24 '17 at 13:15
@Fred-ii- Alright, but I've changed that and it still isn't working, I'll edit my question. — Guido, Mar 24 '17 at 13:25
don't overwrite your original post though; place it as an edit and remove this *"EDIT: Instead of downvoting my question, please eloborate what's wrong with it."* while you're at it. — Funk Forty Niner, Mar 24 '17 at 13:25
you're not using this `$gym` anywhere. You may have meant to use `$id` and error reporting would have helped you here. — Funk Forty Niner, Mar 24 '17 at 13:35
@Fred-ii- See my 2nd edit, it makes clear that there is a problem with multiple values in the LIKE clause. I don't know how to solve it though. — Guido, Mar 24 '17 at 13:37
use `OR`'s then rather than `AND` - echo your query out and you'll see what's going in (or not). and my comment above there about `$gym`. — Funk Forty Niner, Mar 24 '17 at 13:38
I'm running the code from "Edit 2" in my phpMyAdmin environment. So there's no PHP anymore. The problem is something in the LIKE statement. — Guido, Mar 24 '17 at 13:39

score 2 · Answer 1 · edited May 23 '17 at 12:32

2

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/

edited May 23 '17 at 12:32

Community

1
1

answered Mar 24 '17 at 13:12

Vixed

3,429
5
37
68

Test it using only: `Select * FROM sport WHERE name LIKE '%".$sports."%'";` If it works, must be some other parameter in your query that fails. – Vixed Mar 24 '17 at 13:27
1

You're asking something new now :/ – Vixed Mar 24 '17 at 14:15
1

**I gave you the operator to work**, if you need someone that finish the job for you, I think that you can call some freelance ;) – Vixed Mar 24 '17 at 14:25
Except for the fact that your answer did not work. I am still solving this right now. No need to make such comments to be honest. – Guido Mar 24 '17 at 14:41

score 2 · Accepted Answer · answered Mar 24 '17 at 14:00

2

I think you want to use the IN 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.

answered Mar 24 '17 at 14:00

Luud van Keulen

1,204
12
38

I managed to solve it like this. I added the parameters by looping through them and adding them to the SQL statement. After that I executed the SQL and got the rows back which I wanted! Thank you! – Guido Mar 24 '17 at 15:23

score 0 · Answer 3 · answered Mar 24 '17 at 13:14

0

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%."'";

answered Mar 24 '17 at 13:14

Saibal Roy

413
2
5

I'm receiving the following error: br />Parse error: syntax error, unexpected '.' in PHPFILE on line 19
– Guido Mar 24 '17 at 13:17
Sorry use this $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."%'"; – Saibal Roy Mar 24 '17 at 13:19
0 rows returned. – Guido Mar 24 '17 at 13:24
this answer needs to be edited in order to reflect the right syntax. As it stands, `LIKE '%".$sports%."'` isn't correct. The right syntax was only mentioned in a comment. – Funk Forty Niner Mar 24 '17 at 13:39

PHP MySQL WHERE column-value is in $_POST

3 Answers3