PHP: Displaying SQL rows dynamically using $_GET['id']

Question

I am trying to display certain row of my SQL table on a page using $_GET['id'].

So I will briefly go over my setup:

I have a listing page that lists all of the vehicles I have in my SQL table (one row equals one car) and the block of code is looped to print all rows to the page.

There is an 'a' tag attached to the H1 of my listing page that lists a url id dynamically, here is an example:

<a href="carview.php?id='.$row["FullRegistration"].'">

So this is basically pulling the registration of the vehicle from my SQL table and giving the carview.php an ID based on the row that has been looped in the table.

So now to the actual page that will be displaying the dynamic content.

At the moment I am using <?php $carId = $_GET['id']; ?> to retrieve the ID that the <a href="carview.php?id='.$row["FullRegistration"].'"> code block prints.

So if I echo $carId it will print the ID out onto the page which is identical to the "FullRegistration" column of my SQL database.

The Actual Question

I am now wondering how I could query the row that has the same ID, in my case the ID is the full "FullRegistration" column of my SQL table.

How can I now print out the row that matches the ID in the URL? Let's say one of my rows have a "FullRegistration" column value of TT05QVC I then want it to display the data for that row if the url id matches.

I'm guessing I should be using a WHERE clause however after trial and error I cant seem to find a way to do this.

Any ideas how I could possibly do this?

I don't get the question. Is this really just about returning a single row from your table filtered by a single criterium? — Niels Keurentjes, Oct 09 '14 at 21:47
I'm not sure how you could get as far as knowing what a `WHERE` clause is without coming upon literally thousands of examples of how to select a row based on an ID value. Perhaps you are actually closer than you think, and if you showed us an example of your attempts, rather than just the context of the problem, we could show what you're missing? — IMSoP, Oct 09 '14 at 21:50
@JayBlanchard No, like `SELECT * FROM cars WHERE id = ?` :P http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — IMSoP, Oct 09 '14 at 21:53

score 1 · Answer 1 · answered Oct 09 '14 at 22:13

A query to retrieve the data from your table might look something like:

SELECT
    *
FROM
    your_table_name
WHERE
    `FullRegistration` = :id

This is saying "select all columns from your table where the registration is equal to a parameter called :id"

A fuller example would be:

<?php

// ...assuming $carId is set somewhere before here

// make a connection to your database
$db = new PDO('mysql:host=localhost;dbname=your_db_name;charset=utf8',
    'username', 'password');

// prepare the query on your database
$query = $db->prepare('
    SELECT
        *
    FROM
        your_table_name
    WHERE
        FullRegistration = :id
');

// bind your id to the query
$query->execute(array(':id' => $carId));

// retrieve all the cars as an array
$cars = $query->fetchAll(PDO::FETCH_ASSOC);

if (count($cars)) {
    // if we've found some cars, display the details
    foreach ($cars as $car) {
        // dump to the screen
        var_dump($car);

        // you could output your html here
        // i.e. echo '<strong>' . $car['FullRegistration'] . '</strong>';
    }
}

PHP: Displaying SQL rows dynamically using $_GET['id']

1 Answers1