SQL question

[gjh42]

This is an interesting question... I'm working on it now:)

[hitmike]

<?php
$manufacturers = $db->Execute("SELECT manufacturers_name, manufacturers_id  FROM zen_manufacturers ORDER BY manufacturers_id");

// loop through manufacturers
while (!$manufacturers->EOF) {

// query for products based on manufacturer ID
$unique_model = $db->Execute("SELECT DISTINCT pd.products_model_number, p.manufacturers_id
                              FROM " . TABLE_PRODUCTS_DESCRIPTION . " pd
                              LEFT JOIN " . TABLE_PRODUCTS . " p
                              ON pd.products_id = p.products_id
                              WHERE p.manufacturers_id = '".$manufacturers->fields['manufacturers_id']."'
                              ORDER BY pd.products_model_number ASC");

// manufacturer header
echo '<h1>'.$manufacturers->fields['manufacturers_name'].'</h1>';

// loop through products found based on manufacturer ID
echo '<ul>';
  while (!$unique_model->EOF) {
    echo '<li class="list-item">
            <a href="index.php?main_page=advanced_search_result&search_in_description=1&keyword='.$unique_model->fields['products_model_number'].'" alt="alt name" />
              <font color="#0099cc">'.$unique_model->fields['products_model_number'].'</font>
            </a>
          </li>' . "\n";
    $unique_model->MoveNext();
  } // end of loop
echo '</ul>';

  $manufacturers->MoveNext();
} // end of loop
?>

This is made so that it will grab the manufacturers that you have and then loop through them. On each loops it looks through the products table to match models that may be assigned to a product that has the same manufacturer.

After that it is a matter of spitting all that information out where applicable.

I did alter your code a little to my liking, so you may just need to adjust some it back to how you would like it to show.

[gjh42]

Here is a preliminary code for the model list.

<?php
//test by gjh42 2010-11-16
// model_list.php

$content = '<div id="modelList">' . "\n" . '<div class="modelListColumn">' . "\n";
$col_count = 0;
$col_max_mfr = 20;//set as desired
$col_max_item = 21;
$manufacturers = $db->Execute("SELECT manufacturers_name, manufacturers_id FROM " . TABLE_MANUFACTURERS . " ORDER BY manufacturers_name ASC");

while (!$manufacturers->EOF) {
  $unique_model = $db->Execute("SELECT DISTINCT pd.products_model_number, p.manufacturers_id FROM " . TABLE_PRODUCTS_DESCRIPTION . " pd LEFT JOIN " . TABLE_PRODUCTS . " p ON pd.products_id = p.products_id WHERE p.manufacturers_id = '" . $manufacturers->fields['manufacturers_id'] . "' ORDER BY pd.products_model_number ASC");
  if($col_count >= $col_max_mfr){//don't start list at bottom of col
    $col_count = 0;
    $content .= '</div>' . "\n" . '<div class="modelListColumn">' . "\n";
  }
  $content .= '<h3>'.$manufacturers->fields['manufacturers_name'].' Some text here:</h3>'; 
  $content .= '<ul>';
  $um_count = 0;
  while (!$unique_model->EOF) {
    if(($col_count >= $col_max_item)and($um_count >= 2)and($um_count < (sizeof($unique_model)-1))){//avoid widows & orphans
      $col_count = 0;
      $content .= '</ul></div>' . "\n" . '<div class="modelListColumn"><ul>' . "\n";
    }
    $content .= '<li><a href="index.php?main_page=advanced_search_result&search_in_description=1 keyword=' .$unique_model->fields['products_model_number']. '" alt="alt name" />' .$unique_model1->fields['products_model_number']. '</a></li>' . "\n";
    $col_count ++;
    $um_count ++;
    $unique_model1->MoveNext();
  }//uniq
  $content .= '</ul>' . "\n";
  $manufacturers->MoveNext();
}//man
$content .= '</div><br class="clearBoth">' . "\n" . '</div>' . "\n";
echo $content;
//EOF

Some style rules to adapt for appearance:

/*stylsheet rules*/ 
#modelList { border-right: 1px solid #aabbcc; margin: 0.5em;}
.modelListColumn {float: left; width: 24.5%; border-left: 1px solid #aabbcc;}
#modelList h3 {}
#modelList ul {}
#modelList li {}
#modelList a {color: #0099cc;}

I haven't installed this in a test site yet, so there may well be bugs. You can insert it in tpl_header.php, or require or include the model_list.php file from tpl_header or another file. Let me know what happens; seeing it live will make it easier to debug.

[DigitalShadow]

Testing it now, will report back with news :)

[DigitalShadow]

First chunk worked great, changed the ORDER BY to manufacturers_name

The second chunk of code didn't work at all, and didn't throw up any mysql errors, it just broke the page, I will look into that one see if I can spot anything wrong with it.

[gjh42]

If I can see the page, I may be able to tell where it is breaking. It would also help if you post the latest debug log from your /cache/ folder.

[DigitalShadow]

fixed it

two instances of unique_model1

the 1 isn't needed

But can I now make one last request? :)

Can it be so that the manufacturers_name spans the whole page width and just the outputted products_model_number is split into four columns?



manufacturers_name

products_model_number | products_model_number | products_model_number | products_model_number
products_model_number | products_model_number | products_model_number | products_model_number
products_model_number | products_model_number | products_model_number | products_model_number

<hr />

manufacturers_name

products_model_number | products_model_number | products_model_number | products_model_number
products_model_number | products_model_number | products_model_number | products_model_number
products_model_number | products_model_number | products_model_number | products_model_number

<hr />

etc etc

[DigitalShadow]

at the moment it is like this

manufacturers_name1 | manufacturers_name2 | manufacturers_name3 | manufacturers_name4
products_model_number1 | products_model_number2 | products_model_number3 | products_model_number4
products_model_number1 | products_model_number2 | products_model_number3 | products_model_number4
products_model_number1 | products_model_number2 | products_model_number3 | products_model_number4

i would prefer it like this

manufacturers_name1

products_model_number1 | products_model_number1 | products_model_number1 | products_model_number1
products_model_number1 | products_model_number1 | products_model_number1 | products_model_number1
products_model_number1 | products_model_number1 | products_model_number1 | products_model_number1

<hr />

manufacturers_name2

products_model_number2 | products_model_number2 | products_model_number2 | products_model_number2
products_model_number2 | products_model_number2 | products_model_number2 | products_model_number2
products_model_number2 | products_model_number2 | products_model_number2 | products_model_number2

<hr />

etc

[gjh42]

Oh yes...

$unique_model1->MoveNext(); 

The l and 1 are so similar that it's easy for them to hide:)

That is quite a different layout, easier in a way because it is simpler, but will require redoing much of the div/list coding.

[gjh42]

I think this is done - my eyes are too fuzzy to look at it any more.

<?php
//test by gjh42 2010-11-16     second layout version
//good for mfr lists that are all large
// model_list2.php

$content = '<div id="modelList">' . "\n";
$manufacturers = $db->Execute("SELECT manufacturers_name, manufacturers_id FROM " . TABLE_MANUFACTURERS . " ORDER BY manufacturers_name ASC");

while (!$manufacturers->EOF) {
  $unique_model = $db->Execute("SELECT DISTINCT pd.products_model_number, p.manufacturers_id 
                                FROM " . TABLE_PRODUCTS_DESCRIPTION . " pd LEFT JOIN " . TABLE_PRODUCTS . " p 
                                ON pd.products_id = p.products_id 
                                WHERE p.manufacturers_id = '" . $manufacturers->fields['manufacturers_id'] . "' 
                                ORDER BY pd.products_model_number ASC");
  $col_count = 0;
  $content .= '<div class="mfrList">' . "\n";
  $content .= '<h3>' .$manufacturers->fields['manufacturers_name']. '</h3>' . "\n";
  $col_max_item = ceil(sizeof($unique_model)/4);
  $content .= '<ul class="first">' . "\n";
  while (!$unique_model->EOF) {
    if($col_count > $col_max_item){
      $col_count = 0;
      $content .= '</ul>' . "\n" . '<ul>' . "\n";
    }
    $content .= '  <li><a href="index.php?main_page=advanced_search_result&search_in_description=1 keyword=' .$unique_model->fields['products_model_number']. '" alt="alt name" />' .$unique_model->fields['products_model_number']. '</a></li>' . "\n";
    $col_count ++;
    $unique_model->MoveNext();
  }//uniq
  $content .= '</ul>' . "\n" . '</div>' . "\n";
  $manufacturers->MoveNext();
}//man
$content .= '</div><br class="clearBoth">' . "\n" . '</div>' . "\n";
echo $content;
//EOF

/*stylesheet rules*/
#modelList {}
.mfrList {margin: 0.5em; padding: 0.5em; border-bottom: 1px solid #aabbcc;}
#modelList h3 {}
#modelList ul {float: left; width: 24.5%; border-left: 1px solid #aabbcc;}
#modelList ul.first {border: none;}
#modelList li {}
#modelList a {color: #0099cc;}