displaying data from two tables

[rajchahal]

hi

I have two tables (table a - which contains the master record and table b - which contains the publish dates) - table b contains a foreign key to table a.

 

I would like to display all of the contents in table a (as a list) and on the same line I would need to know from table b if the publish date has expired.

 

I've tried left join, this lists all of table a but in addition it also replicates data from table b.

 

Could someone explain how this can be done, I don't think I need to join the table..I think I'm having problems displaying the data.

 

Help kindly appreciated.

[cooldude832]

well can u show us what u tried first?

[rajchahal]

Here is the code, a bit of a mash up as I decided to normailise the data, instead of one table I'm now using 2 tables.

I need to display the word 'lapsed' if todays date is greater than all occurances (unpub)

 

the code below is still based on the single table so obviously won't work.

 

table 2 has the following fields:

id (primary key), publish(date),unpublish(date), showtime(varchar),id_article (foreign key) 

 

 

 

 

$result = mysql_query("SELECT * FROM articles");

// the line below prints out all of table a (articles) and any joining occurances in table b (article_occur)
//$result =  mysql_query("SELECT * FROM articles  LEFT JOIN article_occur ON article_occur.id_article=articles.id_article ");

<table width="800" border="1">
<tr>
<td bgcolor="#FFFFCC">Ref</td> 
<td bgcolor="#FFFFCC">Category</td>
<td bgcolor="#FFFFCC">Title</td> 
<td bgcolor="#FFFFCC">Edit</td> 
<td bgcolor="#FFFFCC">Status</td>
  <td width="17%" bgcolor="#FFFFCC">Enable</td>
  <td width="13%" bgcolor="#FFFFCC">Delete</td>
</tr>
<? while($row = mysql_fetch_array($result)) 
{
?>
  <tr>
    <td width="4%"><?=$row['id_article'] ?></td>
    <td width="4%"><?=$row['category'] ?></td>
    <td width="24%"><?=$row['title'] ?></td>
    <td width="11%"><a href="update_article.php?id=<?=$row['id_article']?>">edit</a></td>
    <td width="13%">
   <?
    $dateMake = mktime(0,0,0,date("m"),date("d"),date("y"));
$dateVal = date("Y-m-d", $dateMake);

   if (($dateVal > $row['unpub1']) and ($dateVal > $row['unpub2']) and ($dateVal > $row['unpub3'])and ($dateVal > $row['unpub3']) )
   
   echo '<font color = red>Lapsed </font>';
   else
   echo 'Active';
   
   
   ?>   </td>
    <td>
    <?php if ($row['enable'] ==1) 
echo "yes";
else

echo '<font color = red>no</font>';

?>    </td>
    <td><a href="delete_article.php?id=<?=$row['id_article']?>&category=all" target="_self">delete </a></td>
  </tr>
  
  <? } ?>
</table>

[cooldude832]

SELECT articles.*, article_occur.*
FROM `articles` 
LEFT JOIN article_occur USING(id_article)
GROUP BY articles.id_article

 

Your join selects nothing from the seconday table making it useless

[rajchahal]

hi thanks for your reply, I have tried something similiar see line 3 of previous code.

The problem is that all of table a with it's associated rows in table b are printed.

I want to print ONLY 'table a' rows, and then manipulate the accociated entry in table b.

 

I don't think it's a sql query problem but the way in which I am looping and prinding the data from the two tables,

At the moment I'm going through the full array, need to print the records where only entries exist in 'table a'

 

 

[fenway]

hi thanks for your reply, I have tried something similiar see line 3 of previous code.

The problem is that all of table a with it's associated rows in table b are printed.

I want to print ONLY 'table a' rows, and then manipulate the accociated entry in table b.

 

I don't think it's a sql query problem but the way in which I am looping and prinding the data from the two tables,

At the moment I'm going through the full array, need to print the records where only entries exist in 'table a'

 

 

What's a & b? use table aliases.

[cooldude832]

LEFT JOIN "b" on what ever criteria makes it what you want