JOIN query

[g1ng3r_girl]

Separate, but also beginner question. When doing a join query like this, how do I pull the ID I want into my html table when the columns in both database tables are called “id”?

Example:

$query = "SELECT * FROM users u JOIN role r ON u.role_id = r.id LIMIT $start, $display";
    $result = @mysqli_query ($dbc, $query);
    echo "<table class='printTable'><tr>

    <th>Role</th><th>First Name</th><th>Last Name</th><th>Email</th><th>Address</th><th>City</th><th>State</th><th>Zip</th><th>Phone</th><th>*</th><th>*</th></tr>"; 

    while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
        echo "<tr>
        <td>".$row['role']."</td>
        <td>".$row['first_name']."</td>
        <td>".$row['last_name']."</td>
        <td>".$row['email']."</td>
        <td>".$row['home_address']."</td>
        <td>".$row['city']."</td>
        <td>".$row['state']."</td>
        <td>".$row['zip']."</td>
        <td>".$row['phone']."</td>
        <td><a href=deleteconfirm.php?id=".$row['id'].">Delete</a></td>
        <td><a href=updateform.php?id=".$row['id'].">Update</a></td>
        </tr>";

The last part:

<td><a href=deleteconfirm.php?id=".$row['id'].">Delete</a></td>

is pulling the ID for the role table, but I want to pull the ID for the users table.

I’ve tried $row[‘id’] , $row[‘u.id’], $row[‘u’.‘id’] but it either doesn’t work, does nothing, or gives an error.

Subsequent question, why does it default to pulling r.id? I am not sure how it decides that. You’ve been very helpful so far and I really appreciate it! I did go back and update a lot of my code throughout the project… and while I haven’t fixed my problem (yet!) I fixed a lot of other problems

[phdr]

I split this into its own thread.

The way to do this is to list out the columns you are SELECTing (which is one of the good things you were doing in the display cart thread.) You can then list the table.column you want, and if you do happen to need both of the ‘id’ columns, you can assign an alias name for it/them to allow each one to be referenced in the fetched data.

why does it default to pulling r.id?

This is due to how php populates associative arrays. If you fetched the row of data as a numerically indexed array (don’t do it this way for real), you will see that all the columns are present. Php produces the associative array by using the column name as the key and the value as the value. When there are multiple columns with the same name, any later same name column in the data overwrites the earlier value, i.e. the last column by any name ‘wins’. So, list out the columns you are selecting and only select the columns you need.

[g1ng3r_girl]

This makes sense. I was being a bit lazy about listing out all the columns I needed and was hoping there might be a shortcut, but this worked and I understand what is happening better as well.

[ErnieAlex]

You can also just pull what you want loosely something like this:
SELECT *, u.id AS display_id FROM etc…
This would just create a special display id from the correct table’s id.