SQL question

SQL ----

$unique_model = $db->Execute("SELECT DISTINCT products_model FROM products");

Display ----

while (!$unique_model->EOF) {
echo $unique_model->fields['products_model'];
$unique_model->MoveNext();
}

This will echo out the names as long as your query is bringing back the distinct values. You can also add code to it such as links. To setup the correct reference you may have to build a more complex query.

Example:

echo '<a href="link code" alt="alt name" />'.$unique_model->fields['products_model'].'</a>';

hitmike could you help build that for me, as that is exactly what i am looking to do.

Have the generated list become clickable and result in each item searching the shop... you follow?

yay! Thank you all

<?php $unique_model = $db->Execute("SELECT DISTINCT products_model_number FROM `zen_products_description`ORDER BY products_model_number ASC"); while (!$unique_model->EOF) { echo '<a href="http://website-here.com/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><br />'; $unique_model->MoveNext(); } ?>

Ok one last question, how would I get the above code to only show results from one manufactuers_id? something using left join?

manufactuers_id is in zen_products and products_model_number is in zen_products_description

Done it

<?php $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 = '2' ORDER BY pd.products_model_number ASC"); while (!$unique_model->EOF) { echo '<a href="http://webdomain.com/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><br />'; $unique_model->MoveNext(); } ?>

Deleted post...

Is it possible to go through each manufacturers_id one by one and generate a different list each time without me having to have separate php statements for each manufacturers_id?

<?php $manufacturers = $db->Execute("SELECT manufacturers_name FROM zen_manufacturers WHERE manufacturers_id = '2'"); $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 = '2' ORDER BY pd.products_model_number ASC"); echo $manufacturers->fields['manufacturers_name']; while (!$unique_model->EOF) { echo '<a href="http://webdomain.com/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><br />'; $unique_model->MoveNext(); } ?>

The generated list is quite long even when split into the different manufacturers, what I want to know is it possible to make the generated list split into different div columns? Is it possible to get the outputted list to spread itself across 4 div columns?

I'm using the following code

<?php
$manufacturers1 = $db->Execute("SELECT manufacturers_name FROM zen_manufacturers WHERE manufacturers_id = '1'");
$unique_model1 = $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 = '1' ORDER BY pd.products_model_number ASC");
?>

<?php echo '<h3>'.$manufacturers1->fields['manufacturers_name'].' Some text here:</h3>'; ?> 
<ul id="list" class="model-list">

<?php
echo "<br /><br />";
while (!$unique_model1->EOF) {
echo '<li class="list-item"><a href="http://webdomain.co.uk/index.php?main_page=advanced_search_result&search_in_description=1&keyword='.$unique_model1->fields['products_model_number'].'" alt="alt name" /><font color="#0099cc">'.$unique_model1->fields['products_model_number'].'</font></a></li>' . "\n";
$unique_model1->MoveNext();
}
?>

</ul>

any ideas?

So to recap my last two requests are to have the php script generate separate lists for each of the manufacturers_id, i.e. start at "1" and end at the last number

Other request is to have the outputted list spread itself across four columns automatically. Just need it to after "x" results place a </div> and then start a new <div> for a new column.

Also would be nice if the first request could then be sorted alphabetically by the manufacturers name "ORDER BY manufacturers_name ASC"

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

<?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.

Here is a preliminary code for the model list.```php

<?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.

Testing it now, will report back with news :)

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.

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.

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

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

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.

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

<?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;} ```