Selecting certain row information from MySql after joining tables

Question

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.

score 1 · Answer 1 · answered Mar 28 '12 at 00:13

1

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");

answered Mar 28 '12 at 00:13

Perception

79,279
19
185
195

This is done on another page and works fine. $imageID is the variable I need to add into the query somewhere. – GuerillaRadio Mar 28 '12 at 00:16
Not following you there, I have image identifier in the query I posted in the answer. – Perception Mar 28 '12 at 00:17

score 1 · Accepted Answer · answered Mar 28 '12 at 00:14

1

<?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.

answered Mar 28 '12 at 00:14

joshuahealy

3,529
22
29

Thanks very much this worked perfectly. The project is only going to be used by University staff and I'm not yet expected to produce something much more complicated than this. Just out of interest though for future reference, what do you mean by parameterised queries? – GuerillaRadio Mar 28 '12 at 00:24
Haven't been taught any of this stuff... great. Thanks for the link @appclay – GuerillaRadio Mar 28 '12 at 00:37

Selecting certain row information from MySql after joining tables

2 Answers2