0

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?

Improve this question
1
  • If you indent text with at least four spaces, it will be formatted as code. Edit your question now and try it. Commented Nov 20, 2009 at 20:17

4 Answers 4

Reset to default
2

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.

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

4 Comments

It gives me the error: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/sallyaut/public_html/temporary/listing.php on line 101. Line 101 is: while($r=mysql_fetch_array($result))
Also move away from doing SELECT * FROM, it is inefficient, and creates problems if DB schemas are changed, define your requested elements (year, make, model, etc;). Also ALWAYS sanitize your data when making ANY SQL queries (wether select or insert). Or you open yourself up to SQL injection attacks.
Noted, I understand the first part, but like I said, I am pretty new to this. What do you mean by sanitize my data?
MySQL will cast the string to an integer and carry on. The quotes are perfectly fine.
2

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.

Improve this answer

2 Comments

Parse error: syntax error, unexpected '}' in /home/sallyaut/public_html/temporary/listing.php on line 110
There was a typo in the exception handling block, but it's fixed now. You'll want to be able to catch typos like the one I made, so compare the before and after and make sure you understand what the error was about. Also make sure you read up on the PDO classes and prepared queries.
0

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.

Improve this answer

Comments

-1 
$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
Improve this answer

2 Comments

$id in my table is an int. When I tried your script, it gave me an error of: Parse error: syntax error, unexpected ',' in /home/sallyaut/public_html/temporary/listing.php on line 98
@Galen, your code has multiple errors. It is missing the string formatting option, and your myqsl_real_escape_string is missing parens, etc.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.