heather-allen
asked on
sql query list doesn't display foreign key correctly
Hi Experts
This is my second question - am trying to learn and am having some success but stuck again! Please can anyone help?
I have 2 tables in my database; projects and clients.
projects has 6 columns of data one of which is a foreign key called client_id linked to the clients table. clients is simply a 2 column table with client_id (primary key) and client_name.
When I query the projects table and list the results I would like the foreign key (client_id) to display the corresponding client_name. I have got this working to a fashion but the client name displayed is always client_id 1 and not the one associated with the project. I'm new to sql and am not sure how to use one query to get info from both tables and list the results as I need them.
At present i'm using 2 queries:
$conn = dbConnect('admin');
$result = mysql_query("SELECT project_id, project_name, client_id, thumbnail, live, project_order FROM projects ORDER BY project_order");
$query_listClients = "SELECT clients.client_name, projects.project_id FROM clients, projects WHERE projects.client_id = clients.client_id";
$listClients = mysql_query($query_listCli ents, $conn) or die(mysql_error());
$row_listClients = mysql_fetch_assoc($listCli ents);
$totalRows_listClients = mysql_num_rows($listClient s);
$numRows = mysql_num_rows($result);
Please don't laugh at my inexperience!
When listing the results i'm using:
while ($row = mysql_fetch_assoc($result) ) {
if($row['live']=="y") {$live="checked=\"checked\ "";} else {$live="";}
echo '<li>
<input type="hidden" name="form_order[]" value="'. $row['project_id'] .'" />
<table width="900" border="0" id="projectform">
<tr>
<td width="80" align="center">'. $row['project_order'] .'</td>
<td width="200">'. $row['project_name'] .'</td>
<td width="200">'. $row_listClients['client_n ame'].'</t d>
<td width="120" align="center"><input type="checkbox" name="live_check[]" id="live_check"'. $live.'value="' . $row['project_id'] .'" /></td>
<td width="100" align="center"><img src="../images/thumb/'. $row['thumbnail'] .'" alt="'. $row['project_name'] .'" width="82" height="50" /></td>
<td width="100" align="center"><a href="project_update.php?p roject_id= '. $row['project_id'] .'">EDIT</a></td>
<td width="100" align="center"><a href="project_delete.php?p roject_id= '. $row['project_id'] .'">DELETE</a></td>
</tr>
</table>
</li>';
}
?>
I think this is probably straight forward but I can't work it out. I have set the tables to innoDB and indexed the primary key in clients and set up the relationship correctly as far as I can see.
Can anyone help?
thanks
Heather
This is my second question - am trying to learn and am having some success but stuck again! Please can anyone help?
I have 2 tables in my database; projects and clients.
projects has 6 columns of data one of which is a foreign key called client_id linked to the clients table. clients is simply a 2 column table with client_id (primary key) and client_name.
When I query the projects table and list the results I would like the foreign key (client_id) to display the corresponding client_name. I have got this working to a fashion but the client name displayed is always client_id 1 and not the one associated with the project. I'm new to sql and am not sure how to use one query to get info from both tables and list the results as I need them.
At present i'm using 2 queries:
$conn = dbConnect('admin');
$result = mysql_query("SELECT project_id, project_name, client_id, thumbnail, live, project_order FROM projects ORDER BY project_order");
$query_listClients = "SELECT clients.client_name, projects.project_id FROM clients, projects WHERE projects.client_id = clients.client_id";
$listClients = mysql_query($query_listCli
$row_listClients = mysql_fetch_assoc($listCli
$totalRows_listClients = mysql_num_rows($listClient
$numRows = mysql_num_rows($result);
Please don't laugh at my inexperience!
When listing the results i'm using:
while ($row = mysql_fetch_assoc($result)
if($row['live']=="y") {$live="checked=\"checked\
echo '<li>
<input type="hidden" name="form_order[]" value="'. $row['project_id'] .'" />
<table width="900" border="0" id="projectform">
<tr>
<td width="80" align="center">'. $row['project_order'] .'</td>
<td width="200">'. $row['project_name'] .'</td>
<td width="200">'. $row_listClients['client_n
<td width="120" align="center"><input type="checkbox" name="live_check[]" id="live_check"'. $live.'value="' . $row['project_id'] .'" /></td>
<td width="100" align="center"><img src="../images/thumb/'. $row['thumbnail'] .'" alt="'. $row['project_name'] .'" width="82" height="50" /></td>
<td width="100" align="center"><a href="project_update.php?p
<td width="100" align="center"><a href="project_delete.php?p
</tr>
</table>
</li>';
}
?>
I think this is probably straight forward but I can't work it out. I have set the tables to innoDB and indexed the primary key in clients and set up the relationship correctly as far as I can see.
Can anyone help?
thanks
Heather
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks spprivate
how will this work with the other query
$result = mysql_query("SELECT project_id, project_name, client_id, thumbnail, live, project_order FROM projects ORDER BY project_order");
does that still need to be separate or can I do it all in one query?
how will this work with the other query
$result = mysql_query("SELECT project_id, project_name, client_id, thumbnail, live, project_order FROM projects ORDER BY project_order");
does that still need to be separate or can I do it all in one query?
ASKER
thanks john
Tried that - changed p.client_name to c.client_name and it works!
Thank you so much!!!! I have learned something new and very chuffed!
Tried that - changed p.client_name to c.client_name and it works!
Thank you so much!!!! I have learned something new and very chuffed!
clients.client_name,
projects.project_id FROM PROJECTS
INNER JOIN CLIENTS ON clients.client_id =projects.client_id