Board index   FAQ   Search  
Register  Login
Board index php forum :: Database mySQL & php coding

Searching index of table

Codes here !

Moderators: macek, egami, gesf

Searching index of table

Postby mitti2000 » Wed May 30, 2012 6:12 am

Hi everybody

I'm new to the forum :D 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
mitti2000
New php-forum User
New php-forum User
 
Posts: 4
Joined: Wed May 30, 2012 4:29 am

Re: Searching index of table

Postby Nullsig » Wed May 30, 2012 7:36 am

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.
User avatar
Nullsig
php-forum Fan User
php-forum Fan User
 
Posts: 981
Joined: Thu Feb 17, 2011 6:52 am
Location: Racine, WI

Re: Searching index of table

Postby mitti2000 » Wed May 30, 2012 7:49 am

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
New php-forum User
New php-forum User
 
Posts: 4
Joined: Wed May 30, 2012 4:29 am

Re: Searching index of table

Postby mitti2000 » Wed May 30, 2012 7:53 am

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
mitti2000
New php-forum User
New php-forum User
 
Posts: 4
Joined: Wed May 30, 2012 4:29 am

Re: Searching index of table

Postby Nullsig » Wed May 30, 2012 8:09 am

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.
User avatar
Nullsig
php-forum Fan User
php-forum Fan User
 
Posts: 981
Joined: Thu Feb 17, 2011 6:52 am
Location: Racine, WI


Return to mySQL & php coding

Who is online

Users browsing this forum: No registered users and 1 guest

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.