Need help with joining two tables

PHP
1

Hi,

I have 2 tables, CV and CVcats. CV contains id, year, text and cvcatsid – CVcats contains catsid and title. cvcatsid refers to catsid.
What I want is to make a table listing of all content from CV with title from CVcats as a category title on top.
Category 1
-year / text
-year / text
-year / text
Category 2
-year / text
-year / text
etc.

	$result_array = array();
$counter = 0;

// View of cv content in tables
$rows = 1;

$result = mysql_query( 
"SELECT p.id
, p.year
, p.text
, p.cvcatsid
, c.catsid
, c.title 
FROM cv AS p 
LEFT JOIN cvcats AS c 
ON p.cvcatsid=c.catsid 
ORDER BY c.catsid, p.id desc" );
while( $row = mysql_fetch_array( $result ) )
	{
	$result_array[] = "<td class='cvyear'>" . $row[1] . "</td><td class='cvtext'>" . $row[2] . "</td>";
	}
	mysql_free_result( $result );	

	$result_final = "<table class='cv'><tr>\
";
	
	foreach($result_array as $cvtext)
	{
	if($counter == $rows)
	{	
	$counter = 1;
	$result_final .= "\
</tr>\
<tr>\
";
	}
	else
	$counter++;

	$result_final .= "\	<td>" . $cvtext . "</td>\
";
	}
	
	if($counter)
	{
	if($rows)
	$result_final .= "\	<td colspan='".($rows-$counter)."'></td>\
";

		$result_final .= "</tr></table>";
		}
	
	echo $result_final;

My query lists all the content without the category title. How is it possible to get the title on top of each table?
Is LEFT JOIN the correct join and do I need to use GROUP BY?
Thanks!

2

If you only want to show the categories that have some data in the cv table, then you can use an INNER JOIN.
If you want to show all categories, even if there is no data in the cv table, then you use a LEFT JOIN, but you’ll have to use the cvcats table as primary table, and left join the cv table to it.

Your script doesn’t show the category, because you don’t put it in your output.

Look at this to see the global logic of how to put a title on top of each category block: http://www.sitepoint.com/forums/showpost.php?p=4558479&postcount=3
Of course, you’ll have to modify the code to suit your specific needs.

By the way, I don’t understand why people use the numeric array keys instead of the associative ones. $row[‘id’] is so much easier to understand than $row[1]. At least, that’s my opinion :smiley:

3

Hi Giudo,

Thanks for you reply.
Regarding the join - I just want to show categories with data, so I have changed it to INNER JOIN.
I tried implementing the code in your post, and now it shows the title, but I only get 1 row of data for each category.

	
$result_array = array();
	$counter = 0;

	// View of cv content in tables
	$rows = 1;
	$saveTitle = '';
	$result = mysql_query( 
		"SELECT p.id
		, p.year
		, p.text
		, p.cvcatsid
		, c.catsid
		, c.title 
		FROM cv_ts AS p 
		INNER JOIN cvcats_ts AS c 
		ON p.cvcatsid=c.catsid 
		ORDER BY c.catsid, p.id desc" );
			while($row = mysql_fetch_assoc($result)) {
			if ($row['title'] != $saveTitle) {
			$result_array[] = "<td class='head'>" . $row['title'] . "</td><td class='cvyear'>" . $row['year'] . "</td><td class='cvtext'>" . $row['text'] . "</td>";
    		$saveTitle = $row['title'];			
			}
			
			$result_final = "<table class='cv'><tr>\
";
	
			foreach($result_array as $cvtext)
			{
				if($counter == $rows)
				{	
					$counter = 1;
					$result_final .= "\
</tr>\
<tr>\
";
				}
				else
				$counter++;

				$result_final .= "\	<td>" . $cvtext . "</td>\
";
			}
	
			if($counter)
			{
				if($rows)
			$result_final .= "\	<td colspan='".($rows-$counter)."'></td>\
";

				$result_final .= "</tr></table>";
			}
			}
	echo $result_final;

I agree on the associative array, it’s much easier.
Thanks!

4 

$result_array = array();
$counter = 0;

// View of cv content in tables
$rows = 1;
$saveTitle = '';
$result = mysql_query( 
    "SELECT p.id
        , p.year
        , p.text
        , p.cvcatsid
        , c.catsid
        , c.title 
        FROM cv_ts AS p 
        INNER JOIN cvcats_ts AS c 
        ON p.cvcatsid=c.catsid 
        ORDER BY c.catsid, p.id desc" );

while($row = mysql_fetch_assoc($result)) {
  if ($row['title'] != $saveTitle) {
    $result_array[] = "<td class='head'>" . $row['title'] . "</td><td class='cvyear'>" . $row['year'] . "</td><td class='cvtext'>" . $row['text'] . "</td>";
    $saveTitle = $row['title'];            
  } else {
    $result_array[] = "<td class='cvyear'>" . $row['year'] . "</td><td class='cvtext'>" . $row['text'] . "</td>";
  }
}
$result_final = "<table class='cv'><tr>\
";
foreach($result_array as $cvtext) {
  if($counter == $rows) {    
    $counter = 1;
    $result_final .= "\
</tr>\
<tr>\
";
  }
  else $counter++;
  $result_final .= "\	<td>" . $cvtext . "</td>\
";
}
if($counter) {
  if($rows) $result_final .= "\	<td colspan='".($rows-$counter)."'></td>\
";
  $result_final .= "</tr></table>";
}
echo $result_final;
5

WOW! Works perfectly.
Thanks a lot!