Searching index of table

[mitti2000]

Hi everybody

I'm new to the forum After I worked through a PHP book, I'm working on my first actual program now.

Yesterday I stumbled upon a problem, I wasn't able to solve and I couldn't find a solution through google.

I have this code:

Code: Select all

$sqlab = "select * from children";
$sqlab .= " where index = " .$kknumber;
$res = mysql_query($sqlab);
$num = mysql_num_rows($res);
if($num==0)
   {
   echo "no matching data / no hay datos coincidentes";
   }


After that I just read out the fields with mysql_fetch_assoc.
index is the primary key of the table and has auto increment.

With the code as it is I get following error:

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in ***\***\***.php on line 67
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 'index = 2' at line 1

Line 67 is "$num = mysql_num_rows($res);"

I tried to change the line:

Code: Select all

$sqlab .= " where index = " .$kknumber;


into that (witout the " ")

Code: Select all

$sqlab .= " where index = $kknumber";


without luck

Than I tired that (with index in ' ')

Code: Select all

$sqlab .= " where 'index' = $kknumber";


And I dont get an error anymore, but the program doesn't find any result.

Can anybody help me please?

Thanks,
mitti2000

[Nullsig]

Seems like your query is failing. More than likely the table you are accessing doesn't have a field named "index" when you put single quotes around "index" you changed the meaning of the query so it stopped failing but still returned no results.

I will explain why.

Your initial query:
"SELECT * FROM children WHERE index = $kknumber"

Translates to:
Select all fields in the table "children" where the field with name "index" is equal to the value of $kknumber


Your other query
"SELECT * FROM children WHERE 'index' = $kknumber";

Translates to:
Select all fields in the table children when the word 'index' is equal to the value of $kknumber

Your second query doesn't consider any of the table data in the WHERE clause. Since I assume $kknumber is an integer (or float) value it will never be equal to the string of "index" so your query resolves as false and returns 0 rows.


You need to make sure your query works. You should test it in mysql command shell or in PHPMyAdmin to see what the error is.

[mitti2000]

Thanks for your quick answer.

In PHPMyAdmin I tried the query :

Code: Select all

SELECT * FROM `children` WHERE `index` = 1


And it gave me the right result.

So how do I use that in my program?

I tried copy pasting it but that doesn't work.

mitti2000

[mitti2000]

Ok, just got it to work
The line is like following now:

Code: Select all

$sqlab = "SELECT * FROM `children` WHERE `index` = " . $kknumber;


Could someone explain why in this query the single quotes (') are necessary and in other queries I have with strings like

Code: Select all

$sqlab = "select * from children where lastname like '%" . $kklastname . "%'";


work perfectly without the quotes?

Thanks for the answers

mitti2000

[Nullsig]

It's based on the data type of the field you are comparing. In general you should always have the single quotes around the comparator.

One of the reasons for this is dealing with SQL injection. The other reason is that for most people it is easier to just put single quotes on everything than to learn what does and does not need the quotes.