CODEWITHSUNDEEP
phpmysql
GuerillaRadio
GuerillaRadio

Reputation: 1297

Selecting certain row information from MySql after joining tables

I am building a photo uploading website and I have joined two tables together in order to display the username of the person who uploaded the image. This works but it is printing every name in the database rather than just the image that is selected.

The selected image is called using the following query... 

$data = mysql_query("SELECT * FROM photos WHERE id=$imageID");

The following code then attempts to display the username, but I need it to target only the row of the $imageID. How would I do this?

<?php
        $query = mysql_query("SELECT username, user_id ".
        "FROM users, photos ".
        "WHERE (users.id = photos.user_id)");
        while($uploader = mysql_fetch_array( $query ))
        {   
        echo $uploader['username']; }
?>

Thanks in advance.

Upvotes: 0

Views: 157

Answers (2)

joshuahealy
joshuahealy

Reputation: 3569

<?php
    $query = mysql_query("SELECT username, user_id ".
    "FROM users, photos ".
    "WHERE (users.id = photos.user_id and photos.id = $imageID)");
    while($uploader = mysql_fetch_array( $query ))
    {   
    echo $uploader['username']; }
?>

But this is a little dodgy as you're just substituting a variable straight into your sql and it could be subject to injection attacks. The same goes for your other query.

You should probably look in to using parameterised queries.

Upvotes: 1

Perception
Perception

Reputation: 80633

You didn't actually narrow down the search result by the ID of the photo in question. I would imagine you want a query more like this:

$data = mysql_query("SELECT u.username, p.user_id FROM users u, photos p WHERE u.id = p.user_id AND p.id = $imageID");

Upvotes: 1

Related Questions