CODEWITHSUNDEEP
phpmysql
poxin
poxin

Reputation: 13

PHP query single line from database

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?

Upvotes: 0

Views: 3196

Answers (4)

Galen
Galen

Reputation: 30170

$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

Upvotes: -1

outis
outis

Reputation: 77420

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.

Upvotes: 2

Doug Neiner
Doug Neiner

Reputation: 66201

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.

Upvotes: 0

ChronoFish
ChronoFish

Reputation: 3707

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.

Upvotes: 2

Related Questions