How to Use $_GET in mysql_query() funcion?

[back2black]

Hi All,

Ive got a page called user.php and when I link to there I get there by sending a URL variable called id.... so basically like this...

user.php?id=6
user.php?id=3 etc etc...

Then in my main code I have a line like this:

  <?php

$result = mysql_query("SELECT * FROM sp_table WHERE id=$_GET['id']");

while($row = mysql_fetch_array($result))
  {
  echo $row['name'];
  echo "<br />";
  }

?>

For some reason this doesnt work though (i.e. it doesnt display the 'name' field for the given 'id' field).

I know that the problem part of the code is the $result line because when I replace a plain number instead of the $_GET function it displays the 'name' field ok... i.e. this...:

$result = mysql_query("SELECT * FROM sp_table WHERE id='1'");

...would display the correct 'name' field.

Any ideas what is wrong with my $GET['id'] field?

Thanks for all your help!

 

[Grizzy]

Well from what you have written,

$result = mysql_query("SELECT * FROM sp_table WHERE id=$_GET['id']");

is not the same as:

$result = mysql_query("SELECT * FROM sp_table WHERE id='1'");

If for example, $_GET['id'] = 1, then it would look like this at runtime:

$result = mysql_query("SELECT * FROM sp_table WHERE id=1");

Do you see the difference? There are no single quotes around the "1". Now I know that mysql itself does not require single-quotes, so that may not be the problem.

Have you tried to just echo $_GET['id']? If that fails then problem occurs as the variable is passed to php. If you do get an output, maybe try assigning $_GET['id'] to some variable and use that in your query instead. Try to narrow it down a bit. Hope it helps.

 

[khan0]

Instead of

$result = mysql_query("SELECT * FROM sp_table WHERE id=$_GET['id']");

Try:

$id = $_GET['id'];
$result = mysql_query("SELECT * FROM sp_table WHERE id=" . $id);

or, with single quotes:

$id = $_GET['id'];
$result = mysql_query("SELECT * FROM sp_table WHERE id='" . $id. "'");

 

[XoC--]

Also you should consider doing the following so nobody can do any SQL injections

$id = mysql_real_escape_string($_GET['id']);
$id = preg_match("/^[0-9]+$/", $id) ? $id = $id : $id = 1;

This means if id isn't a number it will default to 1

(sorry if this is off-topic)

 

[risefromdeath]

if it comes to sql injection you can use the is_number() function as well

 

[back2black]

guys - thanks very much, problem solved...

appreciated

 

[Ueland]

and to be very clear:

Do NEVER/EVER(!!) just send variables into SQL-databases without making shure that the data is valid, and secure to put it in. Or else your site WILL be taken down some day.

 

[mortenb]

Also you should consider doing the following so nobody can do any SQL injections

$id = mysql_real_escape_string($_GET['id']);
$id = preg_match("/^[0-9]+$/", $id) ? $id = $id : $id = 1;

This means if id isn't a number it will default to 1

(sorry if this is off-topic)

If you want it to be a number then this would work too:
$id = (int) $_GET['id'];

 

[fukinlee]

It depends what your mysql datatype is. Numeric types don't require quotes, but string types do. Always use mysql_real_escape_string() before making the sql query to avoid injections. Also, look up the ctype functions they are really helpful.

http://us2.php.net/manual/en/book.ctype.php

 

[Killswitch]

Also along with what these guys said, you can append or die(mysql_error()) to your query so if it errors out you can see what MySQL says. Example:

$query = mysql_query('SELECT * FROM table') or die(mysql_error());

 

[GridPanel]

Also along with what these guys said, you can append or die(mysql_error()) to your query so if it errors out you can see what MySQL says. Example:

$query = mysql_query('SELECT * FROM table') or die(mysql_error());

Never do this on a production system. The end-user should never be able to see MySQL errors, one vulnerable query and you're finished.