PHP query single line from database

Question

I'm having a problem echoing a single line from a sql query. I'm still pretty new at this but I can't figure it out at all.

I have a page titled "listing.php?id=7"

Inside the page is this script:

<?php
mysql_connect("localhost","user","pass"); 
mysql_select_db("table"); 

$query = "SELECT * FROM vehicles WHERE id='$id'";
$result = mysql_query($query);

while($r=mysql_fetch_array($result))
{   

$year=$r["year"];
$make=$r["make"];
$model=$r["model"];
$miles=$r["miles"];
$pricepay=$r["pricepay"];
$pricecash=$r["pricecash"];
$transmission=$r["transmission"];
$color=$r["color"];
$vin=$r["vin"];

echo"$year $make $model $miles $pricepay $pricecash $transmission $color $vin<br />";

}
?>

The problem lies within "WHERE id='$id'". When I use a var, it displays nothing, but if I manually make it my ID number, example 7, it works fine. What's am I doing wrong?

Answer 1

if

SELECT * FROM vehicles WHERE id=7

works but

SELECT * FROM vehicles WHERE id='$id'

doesn't work then get ride of the quotes around $id

So

SELECT * FROM vehicles WHERE id=$id

The quotes are turing $id into a string comparison - which won't work if the column type is integer.

Answer 2

$query = sprintf("SELECT * FROM vehicles WHERE id=%d", mysql_real_escape_string($_GET['id']));
$result = mysql_query($query);

1. I hope you don't have register globals on which means you should use $_GET['id'];
2. You can't put quotes around the id field if it's an int in your table
3. Use mysql_real_escape_string to prevent sql injection

Answer 3

Even better, use PDO. Create the connection:

$db = new PDO("mysql:host=localhost;dbname=someDBname", 'user', 'password');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

but do it in just one script, preferably in a singleton or somesuch. This has many advantages, including placing all database passwords in one file (which is easier to secure) and reducing the possibility of typos in the hostname, database name, username or password causing the connection to fail. Use it as:

try {
    $query = $db->prepare(
        "SELECT year, make, model, miles, pricepay,
                pricecash, transmission, color, vin 
           FROM vehicles WHERE id=?"
    );
    $query->execute(array($_REQUEST['id']));
    while ($row = $query->fetch(PDO::FETCH_NUM)) {
        echo implode(', ', $row);
    }
} catch (PDOException $exc) {
    echo "Query failed.";
}

This uses a prepared query, which is not vulnerable to SQL injection. It also does away with "or die".

In case you haven't seen singletons, here's an example:

class DB {
    private static $db;
    static function open() {
        if (! isset(self::$db) ) {
            self::$db = new PDO('mysql:host=hostName,dbname=dbName', 'user', 'password');
            $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        }
        return self::$db;
    }
}

Then, whenever you need a connection, just call DB::open(). If you need to connect to multiple hosts, store PDOs in an associative array within DB, rather than DB::$db. In this case, you could put the connection information in the DB script, or put it in a separate configuration file that DB parses.

Answer 4

Take your original code and add this line before the query:

$id = (int)$_GET['id']; // Sanitize Integer Input

And change your query as others suggested to remove the quotes:

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

I am assuming your id is a normal mysql auto_increment which starts at 1. That means if `$_GET['id'] is anything but a number, it will come back as 0 and thus not match anything in the database.