Displaying data from two tables.

Question

Ok. Here is what I'm trying to accomplish. I am saving the changes to a forms textarea so that all the changes that have been made to that textarea can be viewed. Like a string of comments. I've gotten the data into the two tables. I need help getting it back out.

I have data in two tables "worklog" and "worklognotes".

worklog is a series of work orders from a form that has a textarea is called $spec

worklognotes is a running list of changes that are made to the textarea $spec

In worklognotes I have the id from worklog saved as worklog_id so as changes are made the data will be matched to the right work order.

I can display the information from worklog just fine. But I can't see the info from worklognotes at all. My goal is to display the data from worklognotes and to display the records in worklognotes WHERE worklognotes.worklog_id = worklog.id

I hope this makes sense. Writing it out like this has helped.

Here is the query statements I'm using. Thanks for your help.

$connection=mysql_connect ("localhost", "foo", "bar") or die ("I cannot connect to the database.");
$db=mysql_select_db ("database", $connection) or die (mysql_error());
$query = "SELECT * FROM worklog WHERE id=$id LIMIT 0,1";
$sql_result = mysql_query($query, $connection) or die (mysql_error());

$connection2=mysql_connect ("localhost", "foo", "bar") or die ("I cannot connect to the database.");
$db=mysql_select_db ("database", $connection2) or die (mysql_error());
$query2 = "SELECT * FROM worklognotes,worklog WHERE worklognotes.worklog_id=worklog.id ORDER BY worklog_id DESC";
$sql_result2 = mysql_query($query2, $connection2) or die (mysql_error());

-- cont -- 09-13-10

ok here is the modified query

$connection = mysql_connect ("localhost", "adsites_seth", "callie") or die ("I cannot connect to the database."); $db = mysql_select_db ("adsites_cerebra", $connection) or die (mysql_error());

$query = "SELECT * FROM worklog "; $query .= "LEFT JOIN worklognotes ON worklog_id = worklognotes.worklog_id "; $query .= "WHERE worklog.id=worklognotes.worklog_id ORDER BY worklognotes.worklog_id ASC";

$sql_result = mysql_query($query, $connection) or die (mysql_error());

I have four entries in the worklognotes table and all four are displaying on one page. I only need the one entry that belong to the worklog I'm viewing and if the is no entry. Display nothing.

Answer 1

As I understand (correct me if I'm wrong), you have a 'one to many' relationship between worklog and worklongnotes.

If this is indeed the case, then you want a query that will give you rows 'joined' by worklog.id.

Untested, the following example should be of use:

<?php
$connection = mysql_connect ("localhost", "foo", "bar") or die ("I cannot connect to the database.");
$db = mysql_select_db ("database", $connection) or die (mysql_error());

$query = "SELECT * FROM worklog ";
$query .= "LEFT JOIN worklognotes ON worklog.id = worklognotes.worklog_id ";
$query .= "WHERE worklog.id=$id ORDER BY worklognotes.worklog_id ASC";

$sql_result = mysql_query($query, $connection) or die (mysql_error());
?>

Which could give something like:

SELECT * FROM worklog LEFT JOIN worklognotes ON worklog.id = worklognotes.worklog_id 
WHERE worklog.id=1 ORDER BY worklognotes.worknotes_id ASC;

+-------------+----------------+----------------------------------------+
| id          |  worknotes_id  | log                                    |
+-------------+----------------+----------------------------------------+
| 1           | 1              | Houses atre grate                      | 
| 1           | 2              | Houses are grate                       | 
| 1           | 3              | Houses are great                       | 
| 2           | 4              | Shop5 are smelly                       | 
| 2           | 5              | Shops are smelly                       | 
| 2           | 6              | Shopes aren't smelly                   | 
+-------------+---------------------------------------------------------+

To get all worklognotes for a given worklog.id:

SELECT * FROM worklog LEFT JOIN worklognotes ON worklog.id = worklognotes.worklog_id 
WHERE worklog.id=1 AND worklog.id = 1 ORDER BY worklognotes.worknotes_id ASC;

+-------------+----------------+----------------------------------------+
| id          |  worknotes_id  | log                                    |
+-------------+----------------+----------------------------------------+
| 1           | 1              | Houses atre grate                      | 
| 1           | 2              | Houses are grate                       | 
| 1           | 3              | Houses are great                       | 
+-------------+---------------------------------------------------------+