Where statement help please?

[deki]

Hey guys,

 

I am just learning php with MySQL and all i want to do is edit some users i have in the database, to do so, i want to click on a persons ID, then display it on the next page with the persons details and edit it.

 

I\'m a little stuck, hopefully you guys will help me, This is what i have, just simply brings up all my entries. The problem is when i add in \"WHERE id=$id\" i get no entries come up at all! Stuffs me why, when i take that part out of the statement, it works! Here is my code, tell me what you guys think.

 

<?php

$username="";

$password="";

$database="test";



mysql_connect(localhost,$username,$password);

@mysql_select_db($database) or die( "Unable to select database");

$query="SELECT * FROM contacts WHERE id=$id";

$result=mysql_query($query);



$num=mysql_numrows($result);



mysql_close();

?>

<table border="0" cellspacing="2" cellpadding="2">

<tr>

<th><font face="Arial, Helvetica, sans-serif">Name</font></th>

<th><font face="Arial, Helvetica, sans-serif">Phone</font></th>

<th><font face="Arial, Helvetica, sans-serif">Mobile</font></th>

<th><font face="Arial, Helvetica, sans-serif">Fax</font></th>

<th><font face="Arial, Helvetica, sans-serif">E-mail</font></th>

<th><font face="Arial, Helvetica, sans-serif">Website</font></th>

</tr>

<?

$i=0;

while ($i < $num) {

$first=mysql_result($result,$i,"first");

$last=mysql_result($result,$i,"last");

$phone=mysql_result($result,$i,"phone");

$mobile=mysql_result($result,$i,"mobile");

$fax=mysql_result($result,$i,"fax");

$email=mysql_result($result,$i,"email");

$web=mysql_result($result,$i,"web");

?>



<tr>

<td><font face="Arial, Helvetica, sans-serif"><? echo "$first $last"; ?></font></td>

<td><font face="Arial, Helvetica, sans-serif"><? echo "$phone"; ?></font></td>

<td><font face="Arial, Helvetica, sans-serif"><? echo "$mobile"; ?></font></td>

<td><font face="Arial, Helvetica, sans-serif"><? echo "$fax"; ?></font></td>

<td><font face="Arial, Helvetica, sans-serif"><a href="mailto:<? echo "$email"; ?>">E-mail</a></font></td>

<td><font face="Arial, Helvetica, sans-serif"><a href="<? echo "$web"; ?>">Website</a></font></td>

</tr>



<?

++$i;

}





echo "</table>";

 

Thanks guys.

Cheers

[Napper]

Yo dude,

 

okay I am not too sure if I can help you since I am at the very, very beginning of my journey to master php/mysql but I happened to have had the same problem like you today. Situation was that I was passing on some variable from one site to the other and wanted to base the selection of database entries on these variables. After 4 hours of trial and error I finally found something that worked for me. Essentially I all you need to do (I hope) is adding some of them funny little \' signs (me foreigner. me not know word for \') around your variable, sorta in this fassion:

 

$query=\"SELECT * FROM contacts WHERE id=\'$id\'\";

 

Did the trick for me. Oh, well. Hope it helps.

 

Reg.

 

Napper

[deki]

Hey napper,

 

Thanks for taking your time to try and help, this is buggering me becuase i tried what you said before, and just now again, and still no good. I dont know whyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy

 

[Napper]

Well, as far as I understand it you naturally need to define a value for $id first, which should happen on the page before the one you want the user details to be on. (The idex page that lists all the users, or whatever). I did that by defining passing on the variable in the link in this manner:

 

<a href=\"user_details.php?user_id=<? echo \"$id\";?>\">

 

which would look like this in HTML (given user 345 was clicked):

 

<a href=\"user_details.php?user_id=345\">

 

Following this link would lead you to the website user_detail.php and while passing on the value 345 for your user_id. This in turn should then be sufficient to filter the MySQL database for the detail-information on user 345.

 

Uh, one other difference I found (but as I said, I do not know much about this either) is that I am mentioning the database again in my \"where\" statement (mainly because I draw information out of different tables). So that looks then like this:

 

$query=\"SELECT * FROM contacts WHERE contacts.id=\'$id\'\";

 

Either way, before to long I hope a senior member will tell you what *really* to do...

 

Reg.

 

Napper

[deki]

Yes i know. But i just have no idea why nothing at all shows up when i add that WHERE statement in. It looks fine to me. but i have no idea whats going on nor why. I\'m trying to learn php, i know coldfusion, but i want to learn php, but this is just buggering me as in why it wont work :roll:

[pallevillesen]

Try and echo your $query to the output, so you can see if the sql statement sent to the mysql server is correct.

 

99% of the times, the variable ($id) is not defined or passed correctly to the variable and subsequently to the server...

 

Also, if the statement looks allright, check that it is runnable on the mysqlserver (through mysqlshell or phpmyadmin - if you have such an access).

 

Let us know what the problem was (or is, if it persists).

 

P.

[deki]

Ok, When i echo $result, i keep getting Resource id #3 no matter what id i pick =(

 

I have no idea why! Helppppppppppppppppp!!!!!!

 

Try and echo your $query to the output, so you can see if the sql statement sent to the mysql server is correct.

 

99% of the times, the variable ($id) is not defined or passed correctly to the variable and subsequently to the server...

 

Also, if the statement looks allright, check that it is runnable on the mysqlserver (through mysqlshell or phpmyadmin - if you have such an access).

 

Let us know what the problem was (or is, if it persists).

 

P.

[pallevillesen]

Well... it\'s because your code assign the value 3 to $id, no matter what you *think* your code does....

 

So.. post the code on the referring page (i.e. on the allready posted code, try a $id = 4; on the line above the $result = SELECT.....;

 

Just to check that it does work, and the error is in the referring page.

 

And... you\'re not the first with this kind of problem...

 

P.

[deki]

Hey,

 

I have done what you asked. When i change it to id=4 or any other number, that actual id comes up only.

 

Although when i echo out my $result i still get Resource id #3

 

This isnt lookking good.

 

Does anyone have just a simple script i could look at where it has something likes this?

 

A page that shows entries, you pick which one u want so u can edit or delete? Anyone?

[pallevillesen]

Ah... so your code on page IS working....

 

Then it\'s pretty simple... Post your code of the referring page, and we\'ll get it fixed....

 

P.

[deki]

Well you see this is the problem, when i take out the \"WHERE id=$id\" the page works, although doesnt carry the variables over to the next page. When i have that where statement there i get this displayed

 

Warning: mysql_numrows(): supplied argument is not a valid MySQL result resource in c:apacheapachehtdocsdatabaseindex.php on line 11

Name Phone Mobile Fax E-mail Website

 

 

Ah... so your code on page IS working....

 

Then it\'s pretty simple... Post your code of the referring page, and we\'ll get it fixed....

 

P.

[pallevillesen]

Ok, try an

 

1. echo $query

 

BEFORE your mysql_query($query);

 

2. AND then try and run this query directly through shell or phpmyadmin or whatever... It will tell you the error.... (maybe you should have contacts.id = \'$id\' )

 

Anyway, you should always have some kind of errorhandling in your script, so you would get an error code back from the script in case of errors....

 

In this case it LOOKs like your query result is empty. Otherwise you would get an error from the mysql_query() statement....

 

But follow the 2 suggestions above... we\'ll probably get this done today... (if you\'re online).

 

P.

[deki]

Hey,

 

When i put in the statement id=contacts.id It works and brings up all the results with the correct id links i.e. ?=1 (depending on which id i click on) but then it doesnt carry it over to the next page to show just that result

 

Ok, try an

 

1. echo $query

 

BEFORE your mysql_query($query);

 

2. AND then try and run this query directly through shell or phpmyadmin or whatever... It will tell you the error.... (maybe you should have contacts.id = \'$id\' )

 

Anyway, you should always have some kind of errorhandling in your script, so you would get an error code back from the script in case of errors....  

 

In this case it LOOKs like your query result is empty. Otherwise you would get an error from the mysql_query() statement....

 

But follow the 2 suggestions above... we\'ll probably get this done today... (if you\'re online).

 

P.