Page 1 of 1

Getting started with join (simple example) [solved]

Posted: Tue Mar 17, 2009 7:34 am
by papa
Hi,

I'm currently reading about database design and am not very good at joins etc.

I have a pretty simple example and was hoping someone could point me in the right direction, having trouble understanding the mysql.org examples.

I have a pretty basic select statement, and I want to get userId's name from my user table:

Code: Select all

 
 
<table class="event" width="400">
<?php
 
if($link = $db->dbConnect()) {
    $sql = "SELECT eventId, name, url, descr, event_date, userId, create_date 
        FROM   ufc_event
    WHERE event_date > CURDATE()
    ORDER BY event_date ASC";
 
    $result = mysql_query($sql);
 
    while ($row = mysql_fetch_assoc($result)) {
        echo "<tr>\n";
        echo "<th><a href=\"".$row["url"]."\" target=\"self\">".$row["name"]."</a> - ".$row["event_date"]."</th>\n";
        echo "</tr>\n";
        echo "<tr>\n";
        echo "<td id=\"details\"><b>Added by:</b> ".$row["userId"]." [".$row["create_date"]."]</td\n";
        echo "</tr>\n";
    
        echo "<tr>\n";
        echo "<td>".nl2br($row["descr"])."</td>\n";
        echo "</tr>\n";
    }
 
    mysql_free_result($result);
}
?>
</table>
 
What I've done until now is to add a query in the loop and fetch the user name where userId = $row["userId"]...

Re: Getting started with join (simple example)

Posted: Tue Mar 17, 2009 9:43 am
by papa
$sql = "SELECT ue.eventId, ue.name, ue.url, ue.descr, ue.event_date, ue.userId, ue.create_date, ud.name
FROM ufc_event AS ue JOIN user_details AS ud
ON ue.userId = ud.user_id";
This query works closey to what I want to acheive. However the ud.name replaces my ue.name. I want to replace ue.userId with ud.name. Any suggestions?



edit:
Got it!