Select from table, where column is foreign key in PHP

Question

Even though there is an entry in the database, with this query, I always get 0 entries back

$sql = "SELECT * FROM saved_food WHERE user_id = ? AND favorite_food LIKE ?";

$stmt = $conn->prepare($sql);
$stmt->bind_param("is", $me['id'], $favFood);
$stmt->execute();
var_dump($stmt->num_rows);

the dump is 0 The user_id colum is a foreign key, and shows to the id of the table "user". I can't see the error here. Is there a special method for foreignkey values?

seems you need `%` wildcard before and after question mark following `like` keyword. — Barbaros Özhan, Nov 18 '19 at 18:12
Forget about `num_rows`. You don't need it. It will always show you zero rows. — Dharman, Nov 18 '19 at 18:13
@BarbarosÖzhan no, I even tried it with favorite_food = ?. The output of $favFood is correct. It works on phpmyadmin, with the same query — seref, Nov 18 '19 at 18:17
@Dharman it works in other files though. And I need to check if there's an entry in the Database — seref, Nov 18 '19 at 18:18
Are you sure the query works at all? It's worth trying in a SQL client directly to compare. Your `favorite_food` column could have things like leading or trailing spaces which messes up the query. — tadman, Nov 18 '19 at 18:25
@tadman I'm 100% sure. I dumped the favorite_food, and it has no spaces at leading nor trailing. I copied the values of the outcome in phpmyadmin, and it found me a row. — seref, Nov 18 '19 at 18:27
just remove that pointless line, `var_dump($stmt->num_rows);` — Your Common Sense, Nov 18 '19 at 18:31
you don't need it "to check". To check if there's an entry in the Database simply fetch the selected data — Your Common Sense, Nov 18 '19 at 18:34
SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1 — , Nov 18 '19 at 18:36
@YourCommonSense this is a MySQL response to his query. There is no such think like a question mark. But I do not understand why you ask me this because I wrote it relative clearly. — , Nov 18 '19 at 18:50
@YourCommonSense No it is not MySQL. It is probably working wit PHP but it is deffinitivly no MySQL synthax. The Query would be using a concaternation and would be for your information **"SELECT * FROM \`saved_food\` WHERE \`user_id\` = '".$me['id']."' AND \`favorite_food\` LIKE '".$favFood."' ;"** Have a nice evening. — , Nov 18 '19 at 19:10
@HenryStack please read [here](/questions/60174/how-can-i-prevent-sql-injection-in-php) — Your Common Sense, Nov 18 '19 at 19:19
@HenryStack do you *really* think strip_tags() or strval() has *anything* to do with SQL? That's a rhetorical question. Have a nice day — Your Common Sense, Nov 18 '19 at 19:38
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/202592/discussion-between-henry-stack-and-your-common-sense). — , Nov 18 '19 at 19:42
@HenryStack Please **do not** advocate using SQL injection as a solution. This is extremely reckless and builds very, very bad habits. — tadman, Nov 18 '19 at 20:36

score 0 · Answer 1 · answered Nov 18 '19 at 18:31

0

I got the error... facepalm I forgot to call ->get_result();

$sql = "SELECT * FROM saved_food WHERE user_id = ? AND favorite_food LIKE ?";

$stmt = $conn->prepare($sql);
$stmt->bind_param("is", $me['id'], $favFood);
$stmt->execute();
$result = $stmt->get_result();
var_dump($result->num_rows);

answered Nov 18 '19 at 18:31

seref

543
2
6
19

SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1 – Nov 18 '19 at 18:37
If prepared properly this should work. – tadman Nov 18 '19 at 20:36

Select from table, where column is foreign key in PHP

1 Answers1