php variable in mysql statement...how do I do it

Question

 <?php
 mysql_connect("localhost", "root", "") or die(mysql_error()); 
 mysql_select_db("names") or die(mysql_error()); 

 $green = "rose";    

 $data = mysql_query("SELECT * FROM freinds2 WHERE name = '"$green"'); 
 or die(mysql_error()); 

 Print "<table border cellpadding=3>"; 
 while($info = mysql_fetch_array( $data )) 
 { 
     Print "<tr>"; 
     Print "<th>Name:</th> <td>".$info['name'] . "</td> "; 
     Print "<th>Pet:</th> <td>".$info['pet'] . " </td></tr>"; 
     Print "<th>Color:</th> <td>".$info['fav_color'] . "</td> "; 
     Print "<th>Food:</th> <td>".$info['fav_food'] . " </td></tr>"; 
 } 
 Print "</table>"; 
 ?>

The above code works fine until I try and replace the name= in the SELECT statement with the variable I created above $green = 'rose'.It wont take the variable the way I put it in the SELECT statement.Is there a special way of placing variables in a select statement.? Many thanks jim

You should really get away from mysql, use PDO or MySQLi as I put in my answer below. You should also learn to use prepared queries to prevent SQL injections for user inputted variables like POST/GET etc. — Devon Bessemer
– Devon Bessemer, Commented Apr 9, 2014 at 16:41

morissette · Accepted Answer · 2014-04-09 16:34:30Z

3

You need to remove the additional quotes:

 $data = mysql_query("SELECT * FROM freinds2 WHERE name = '"$green"');

becomes

 $data = mysql_query("SELECT * FROM freinds2 WHERE name = '$green'");

or

 $data = mysql_query("SELECT * FROM freinds2 WHERE name = '" . $green . "'");

It is also appropriate to mention that mysql_query is deprecated as of PHP 5.5.0 and you should look to update your code to use MySQLi or PDO_MySQL

edited Apr 9, 2014 at 16:34

answered Apr 9, 2014 at 16:32

morissette

1,1092 gold badges8 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

morissette Over a year ago

Additionally, you can also concat if you want to keep your quotes

Jack M. Over a year ago

Please mention that mysql_query is deprecated

morissette Over a year ago

Added deprecation data

Tabby · Accepted Answer · 2014-04-09 16:36:40Z

1

Try this->

$data = mysql_query("SELECT * FROM freinds2 WHERE name = '$green'");

answered Apr 9, 2014 at 16:36

Tabby

3881 silver badge11 bronze badges

1 Comment

SuperDJ Over a year ago

This should be the one you need

Devon Bessemer · Accepted Answer · 2014-04-09 16:45:21Z

0

You should get away from mysql as the mysql API is deprecated.

You can use PDO or MySQLi, I prefer PDO since it supports more than just MySQL.

$db = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8', 'username', 'password');

To use a standard query, you can do:

$stmt = $db->query("SELECT * FROM freinds2 WHERE name = '".$green."'");

But for most variables, especially user inputed fields, you should use prepared queries.

$stmt = $db->prepare("SELECT * FROM freinds2 WHERE name = ?");
$stmt->execute(array($green));

This query is protected from SQL injections.

To obtain multiple results you could run:

while ($info = $stmt->fetch()) {

     Print "<tr>"; 
     Print "<th>Name:</th> <td>".$info['name'] . "</td> "; 
     Print "<th>Pet:</th> <td>".$info['pet'] . " </td></tr>"; 
     Print "<th>Color:</th> <td>".$info['fav_color'] . "</td> "; 
     Print "<th>Food:</th> <td>".$info['fav_food'] . " </td></tr>"; 

}

Here is a nice tutorial for PDO: http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers

edited Apr 9, 2014 at 16:45

answered Apr 9, 2014 at 16:40

Devon Bessemer

35.6k10 gold badges77 silver badges102 bronze badges

1 Comment

user3480507 Over a year ago

many thanks for this I am working my way through the tutorial

Collectives™ on Stack Overflow

php variable in mysql statement...how do I do it

3 Answers 3

3 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related