Selecting a Single MySQL Database record.

tycragg

Hi there. I'm trying to select a single record from a table in a MySQL database, and having very frustrating problems. The address bar looks like this:
http://www.website.com/test.php?id=1

The PHP code is:

20  $qstr = "SELECT * from users WHERE id=$id";
21  $result = mysql_query($qstr);
22  if (mysql_num_rows($result))
23  {
24  $email = mysql_result($result,0, "email");
25  }

I get the error message:
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in *** on line 22

Any ideas?

leatherback

try echoeing out the error that you get:

  $id = $_GET['id'];
  $qstr = "SELECT * from users WHERE id=$id";
  $result = mysql_query($qstr) or die(mysql_error());
  if (mysql_num_rows($result)>0)
    {
    $email = mysql_result($result,0, "email");
    } 
  else
    {
    $email = '';
    }

Oh eh, yeah: I assume you do make sure you know what you get in right? I mean, you need to 'clean' incoming data

tycragg

leatherback wrote:
try echoeing out the error that you get:
  $id = $_GET['id'];
  $qstr = "SELECT * from users WHERE id=$id";
  $result = mysql_query($qstr) or die(mysql_error());
  if (mysql_num_rows($result)>0)
    {
    $email = mysql_result($result,0, "email");
    } 
  else
    {
    $email = '';
    }
Oh eh, yeah: I assume you do make sure you know what you get in right? I mean, you need to 'clean' incoming data

I get the error:
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

What does this mean? How exactly do you 'clean' the data?

leatherback

It means that your SQL is incorrect. You send something to the database that the database does not understand. Echo out the query itself, and check whether is is exactly like what you were expecting.

echo $qstr;

You need to pass the data through something like [man]mysql_escape_real_string[/man]. There are a lot of threads about this topic. Have a browse/search

tycragg

I get the result:
SELECT * from users WHERE id=

Meaning that the "id" variable isn't being sent. Any idea why?

leatherback

No idea, as you do not show how you get the $id value.

How do you retrieve the ID from your URL? in the code in post #2 I gave you the simplest way of getting it in, but in order to use it in a query you need to pass it through e.g., mysql_escape_real_string or something.

tycragg

I have no way of retrieving it at the moment. Could you point me in a direction?

Thanks man.

leatherback

$id = mysql_real_escape_string($_GET['id']);

tycragg

Well, it was working, but it's giving me problems once again. Here's my code:

$user = mysql_real_escape_string($_GET['user']);
$qstr = "SELECT * from users WHERE user=$user";
echo ($qstr);
$result = mysql_query($qstr) or die(mysql_error());
if (mysql_num_rows($result)>0)
    {
    $email = mysql_result($result,0, "email");
    }
  else
    {
    $email = '';
    }

If I echo $qstr I get the error:
SELECT * from users WHERE user=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

tycragg

Well, it was working, but it's giving me problems once again. Here's my code:

$user = mysql_real_escape_string($_GET['user']);
$qstr = "SELECT * from users WHERE user=$user";
echo ($qstr);
$result = mysql_query($qstr) or die(mysql_error());
if (mysql_num_rows($result)>0)
    {
    $email = mysql_result($result,0, "email");
    }
  else
    {
    $email = '';
    }