Check if a PHP variable exists in a MySQL table

Question

I'm trying to check if the content of the variable $host exists in the ID column of my database. Afterwards I want it to assign the corresponding value to $exists, if the entry is in the database or not.

For some reason this part of my script is always returning the same result, regardless if $host is contained in the database or not. Please help :)

$query = mysqli_query($conn, "SELECT 'ID' FROM 'servers' WHERE 'ID' = '$host'");
if (empty($query)) {
        $exists = "false";
}
else {
        $exists = "true";
}

**A:** You're using the wrong identifiers for your table and column. Remove the quotes. Plus, that's not the way to check if a record exists, you're just checking if the query returns true or not. — Funk Forty Niner, Oct 26 '14 at 12:42
important --- http://code.tutsplus.com/tutorials/why-you-should-be-using-phps-pdo-for-database-access--net-12059 — CS GO, Oct 26 '14 at 12:43
http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — CS GO, Oct 26 '14 at 12:45
Well the sad part is that it returns the same result when I set $host to a value that's contained in the database, and when I set it to a different value. I am definitely going to check on the links that you sent. I'm writing the script for a bash alias, but it would definitely be a good idea to learn how to write securely. Thank you very much. — Neekoy, Oct 26 '14 at 13:27

score 3 · Accepted Answer · edited Oct 26 '14 at 13:03

3

This line

$query = mysqli_query($conn, "SELECT 'ID' FROM 'servers' WHERE 'ID' = '$host'");

needs to be like this:

$query = mysqli_query($conn, "SELECT `ID` FROM `servers` WHERE `ID` = '$host'");

Right now, you are selecting ID as a string, so you need to put table and column names in `` and you put strings (or variables containing strings in ' ' )

and then do

$count = $conn -> num_rows($query);
if ($count < 1 ) {
$exists = "false";
}
else
{
$exists = "true";
}

to actually check the number of rows containing $host 's value

Also, you should at least use

$host = mysqli_real_escape_string($conn, $host);

before using a variable in a query to avoid mysql injection, but better use prepared statements. There are some links in the comments to your question which will help you with that.

Sidenote:

Having used or die(mysqli_error($conn)) to mysqli_query() would have signaled the error.

edited Oct 26 '14 at 13:03

Funk Forty Niner

74,450
15
68
141

answered Oct 26 '14 at 12:46

baao

71,625
17
143
203

Needs to be like this; why? Plus, OP stands at coming back saying "it doesn't work". – Funk Forty Niner Oct 26 '14 at 12:46
I understand. Now, OP's question is *"I'm trying to check if the content of the variable $host exists"* - Whether OP truly wants to check if it returns TRUE or FALSE, is fine, but it doesn't really check if a row containing the variable being passed actually exists. Wait for OP to respond. – Funk Forty Niner Oct 26 '14 at 12:51
Was just at writing the second part, if it's still wrong I don't see something right, @Fred-ii- – baao Oct 26 '14 at 12:54
What you suggested is definitely working. There seems to be another issue somewhere around the code, however now it's correctly determining if $host is contained in the database or not. Thank you very much <3 – Neekoy Oct 26 '14 at 13:43
You're welcome @Neekoy , Please mark the answer as correct if it has helped you – baao Oct 26 '14 at 13:47
@Neekoy if you have another question, just ask it, I'm sure someone can help you out – baao Oct 26 '14 at 13:48
I marked it, but I can't upvote you because I don't have enough reputation - I'm pretty new. Will get back here and do it once I have 15. Also, I will make sure to ask if I can't figure it out, just don't want to ask really stupid questions. – Neekoy Oct 26 '14 at 14:01
Thank you! Don't worry, I'm sure there is a question more stupid than yours already online here... But it's the right way you're going – baao Oct 26 '14 at 14:03

score 1 · Answer 2 · 2014-10-26T12:51:50.337

1

$query = mysqli_query($conn, "SELECT ID FROM servers WHERE ID = '".$host."'");
if (empty($query)) {
        $exists = "false";
}
else {
        $exists = "true";
}

edited Oct 26 '14 at 12:51

answered Oct 26 '14 at 12:46

I love "drop-in code" with no explanations. Plus, it will fail. `WHERE ID = $host` – Funk Forty Niner Oct 26 '14 at 12:48
@Fred-ii- What is unclear ? (and sorry for the quotes, i reedited the answer) – Oct 26 '14 at 12:52
When an answer is given with no explanation as to why OP's code is failing, OP would have not learned the reason why it is failing and stands at doing the same mistake again. – Funk Forty Niner Oct 26 '14 at 12:56
@Fred-ii- I agree with you, but for this specific question, nothing much to explain, it is just a syntaxic error. – Oct 26 '14 at 13:02

Check if a PHP variable exists in a MySQL table

2 Answers2