Using PHP data in MySQL

Question

I want to search for data in a database using data from PHP

for example:

<?php
$con = mysql_connect("localhost", "root");
if (!$con){
    die('Could not connect: ' . mysql_error());
}
mysql_select_db("UsersDB", $con);
$name = "Jack"
$result = mysql_query("SELECT Username
    FROM Users
    WHERE Username = $name", $con);
echo $result;
mysql_close($con);
?>

But that doesn't work.

Is it possible to use $name in the select statement?

"That doesn't work" is hardly an analytical problem description. — Lightness Races in Orbit, Jan 07 '12 at 10:28
I sense a lack of understanding of how PHP works with MySQL, check out http://www.tizag.com/mysqlTutorial/mysqlquery.php and other pages there for a simple breakdown of how to query, insert, sort and even set up MySQL databases. — George Reith, Jan 07 '12 at 10:33
You should be aware that inserting $name directly into your query opens up your database to SQL injection attacks, if $name can be specified by the user through a form or what have you. You should construct the query using parameters like this http://stackoverflow.com/questions/60174/best-way-to-stop-sql-injection-in-php — Levi Botelho, Jan 07 '12 at 10:37

Lightness Races in Orbit · Answer 1 · 2012-01-07T10:34:24.163

2

You forgot a semicolon after your definition of $name (turn on error reporting to see parse errors).
You didn't delimit the name inside your SQL query.
echo $result doesn't do anything useful.
Time to read a peer-reviewed PHP book!

edited Jan 07 '12 at 10:34

answered Jan 07 '12 at 10:28

Lightness Races in Orbit

358,771
68
593
989

score 1 · Answer 2 · answered Jan 07 '12 at 10:33

Username is a VARCHAR/string, so the username value that you're searching for needs to be quoted:

$result = mysql_query("SELECT Username 
               FROM Users 
               WHERE Username = '$name'", $con);

others have pointed out the other errors in your code

kv-prajapati · Answer 3 · 2012-01-07T11:35:46.293

0

You have to call mysql_fetch_row() or mysql_fetch_assoc() method to retrieve the result.

$name = "Jack";
$result = mysql_query("SELECT Username
               FROM Users
               WHERE Username = '$name'", $con);
if($result)
{
   $row=mysql_fetch_assoc($result);
   if($row)
   {
   }
}

edited Jan 07 '12 at 11:35

answered Jan 07 '12 at 10:29

kv-prajapati

90,019
18
141
178

score 0 · Answer 4 · edited Jan 07 '12 at 10:35

Echoing the result resource is probably not what you intended to do. You need to use the provided function mysql_result() to read the data from the resultset. Look more closely at the example from http://php.net/manual/en/function.mysql-result.php:

$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
if (!mysql_select_db('database_name')) {
    die('Could not select database: ' . mysql_error());
}
$result = mysql_query('SELECT name FROM work.employee');
if (!$result) {
    die('Could not query:' . mysql_error());
}
echo mysql_result($result, 2); // outputs third employee's name

mysql_close($link);

See also mysql_fetch_row() and mysql_fetch_array().

score 0 · Answer 5 · answered Jan 07 '12 at 11:17

Your main problem is you're echoing $result which is a MySQL Result, not data.

Please read this

You should be doing this:

$sql = "Select * from `table`";

// If you will have many results
if ($result = mysql_query($sql)) {
  while ($record = mysql_fetch_array($result)) {
    // Do stuff for each record
    }
  }

// If you will have ONE result
if ($result = mysql_query($sql)) {
  if ($record = mysql_fetch_array($result)) {
    // Do stuff for this record
    }
  }

Note

It is very bad practice to just dump $name straight into the query, ever heard of SQL injection?

The proper way to do it is, especially if you don't know the value of $name (i.e. it is from a form input):

$sql = "SELECT Username 
        FROM Users 
        WHERE Username = '".mysql_real_escape_string($name)."'";

Using PHP data in MySQL

5 Answers5