Mysql Join, PHP

Question

Just before i put the line of code in let me explain what i am trying to achieve.

Id like to query a table where a specific page has a specific parent_id. Once i have all the results i would then like to take the id of the page which has this specific parent_id and fetch all its relative page parts from another table.

Then i simply want to have this information in an array ready to print...

Any help would be greatly appreciated...

$q = $CONN->query("SELECT cms_page.id, cms_page.title, cms_page.slug, cms_page_part.* FROM cms_page LEFT JOIN cms_page_part ON cms_page_part.page_id=cms_page.id  WHERE cms_page.parent_id='8'");

Answer 1

Can you post the results of your query? As far as I can see your select statement should do the trick repeating the cms_page.id etc fields for each matching part.

See also http://www.w3schools.com/Sql/sql%5Fjoin%5Fleft.asp

This (I hope) should solve your $row['page_part'] question:

$rows = array();
$result = mysql_query($sql);
$row = mysql_fetch_assoc($result);
while ($row) {
  $rows[$row['title']][] = $row; // or select only the fields that you need
  $row = mysql_fetch_assoc($result);
}

foreach($rows as $portfolio => $parts) {
  // adjust for actual html rendering
  echo '<pre>';
  print_r($portfolio);
  echo "\n";
  $count = 0;
  foreach($parts as $part) {
    echo ++$count . ' - ' . $part['cms_page_part_field1'] . ' - ' . $part['cms_page_part_field2'] . "\n";
  }
  echo '</pre>';
}

Answer 2

There should be no problems with retrieving multiple rows given your current query:

$sql = "SELECT cms_page.id, cms_page.title, cms_page.slug, cms_page_part.* 
FROM cms_page 
LEFT JOIN cms_page_part ON (cms_page_part.page_id = cms_page.id)
WHERE cms_page.parent_id = '8'";

You would want to loop over a resultset from mysql_query like so:

$result = mysql_query($sql);
if ($result) {
    // keep track of number of portfolio items
    $i = 1;
    // iterate over rows
    while($row = mysql_fetch_assoc($result)) {
        // output data to page for each row
        echo 'Portfolio item ' . $i;
        echo 'Title: ' . $row['title'] . '<br />';
        echo 'Slub: ' . $row['slug'] . '<br /><br />';
    }
}