Solved

sql query list doesn't display foreign key correctly

Posted on 2009-04-11
4
422 Views
Last Modified: 2013-12-12
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_listClients, $conn) or die(mysql_error());
$row_listClients = mysql_fetch_assoc($listClients);
$totalRows_listClients = mysql_num_rows($listClients);
$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_name'].'</td>
    <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?project_id='. $row['project_id'] .'">EDIT</a></td>
    <td width="100" align="center"><a href="project_delete.php?project_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
0
Comment
Question by:heather-allen
  • 2
4 Comments
 
LVL 15

Expert Comment

by:spprivate
ID: 24122158
SELECT
clients.client_name,
projects.project_id FROM PROJECTS
INNER JOIN CLIENTS ON  clients.client_id =projects.client_id

0
 
LVL 7

Accepted Solution

by:
johnnash1180 earned 250 total points
ID: 24122175
I think that you are not iterating the row_listClients list properly.


However, you can do this in single query itself. Use the following query:

SELECT p.project_id, p.project_name, p.client_id, p.client_name, p.thumbnail, p.live, p.project_order FROM projects p, clients c where c.client_id=p.client_id ORDER BY project_order;

Using the above query, you can get the client name in a single query and need not to store the client id client name in a separate list.

Hope it helps.

John Nash



0
 
LVL 1

Author Comment

by:heather-allen
ID: 24122183
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?
0
 
LVL 1

Author Comment

by:heather-allen
ID: 24122218
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!
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Undefined variable problem 5 23
MYSQL responding very slow 3 27
IIF in access query 19 28
Update one table with results from another table in SQL 6 29
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to count occurrences of each item in an array.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question