Help Needed Simple SQL Statement Please

[fat_dog]

Hey peeps,

 

I have installed the Inventory Report Stock Level contribution, but it didn't have the ability to sort by manufacturer (a fundemental requirement in my opinion).

 

I've got the "sort by" bit working, and also how it lists by manufacturer ID. What I now need to do is turn the ID into the manufacturer name, and that's where I need your help.

 

This works:

<td class="smallText"><?php echo $products['manufacturers_id']; ?></td>

 

I need to pull manufacturers_name from the manufacturers table and display next to each product row.

 

In the code I have, they are pulling manufacturers_id from the products table hence $products, I need a way of pulling manufacturers_name from the manufacturers table (which I can just about do) but unfortunatly my knowledge of php doesn't go as far as relating the two together so it checks against the ID and displays!

 

Can anyone please help?? :blush:

[squeekit]

i don't know the Inventory Report Stock Level contribution - but this MIGHT work...

<?php echo $products['manufacturers_name']; ?>

but really - i'd need to see the php code you are updating for you might have to make other adjustments to get it working properly...

 

one example - in the php code somewhere the list of what's in the db query should have something like this in it (for the above to work):

 

-----------_query = tep_db_query(----------- m.manufacturers_name, ----------- " . TABLE_MANUFACTURERS . " m -----------

 

[ note: gray dashed "---" indicates what i don't know in the php page you're editing ]

 

notice how the manufacturers name is handled in includes/boxes/manufacturers.php:

$manufacturers_query = tep_db_query("select manufacturers_id, manufacturers_name from " . TABLE_MANUFACTURERS . " order by manufacturers_name");

because of a query like that, on that page the name is accessed with $manufacturers['manufacturers_name']

 

also note in the query i used for the above example only one table is accessed --- an example of a query that handles more than one table is in includes/boxes/manufacturers_info.php:

$manufacturer_query = tep_db_query("select m.manufacturers_id, m.manufacturers_name, m.manufacturers_image, mi.manufacturers_url from " . TABLE_MANUFACTURERS . " m left join " . TABLE_MANUFACTURERS_INFO . " mi on (m.manufacturers_id = mi.manufacturers_id and mi.languages_id = '" . (int)$languages_id . "'), " . TABLE_PRODUCTS . " p  where p.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and p.manufacturers_id = m.manufacturers_id");

because more than one table is handled in that query an indicating prefix is needed for each item > m.manufacturers_name < the "m" prefix means pull this item from manufacturers table > the "m" is set by this bit > " . TABLE_MANUFACTURERS . " m -- and again, because of a query like that, on that page the name is accessed with $manufacturers['manufacturers_name']

 

given that you are using $products['manufacturers_id'] you will have to look at the db query for $products - in it look if it is using more than one table -- it probably is using TABLE_PRODUCTS and TABLE_PRODUCTS_DESCRIPTION -- if so and you do not see " . TABLE_MANUFACTURERS . " m then add it > something like so:

 

-------- from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m --------

 

then somewhere just previous to that drop "m.manufacturers_name" into the list of what is accessed:

 

-------- p.products_id, pd.products_name, m.manufacturers_name, pd.products_description, p.products_model, --------

 

of course first check to see if such is already in the query - if they were then

<?php echo $products['manufacturers_name']; ?>

would have worked - if they were not in the query and you put them in then $products['manufacturers_name'] should work now

 

.........................................................

 

i thought about just advising you to find the support thread for that contrib, but i figured you could find the above info handy in more cases than just the one you ask about here ;)

[fat_dog]

squeekit, you're a star!! Thank you so much for solving this headache!

 

Yes everything you said makes sence and I could see what to do straight away, it's just knowing where to put the little bits of darn code :) I also understand those statements MUCH better now, so yes, that's a lesson well learnt.

 

I'll post the link to this topic in the contribution area for the stock system, as I belive this is an important thing to include in the contribution (what's the point in having a stock reorder report, if you can't sort it by supplier/manufacturer!)

 

For those using this mod, and want to include the option to display manufacturer name and also sort by manufacturer name, here is the complete query that you can just overwrite in your admin/stats_low_stock.php file around line 141.

 

  $products_query_raw = "select p.products_id, p.products_quantity, p.products_reorder, p.products_reorder_to, p.products_model, pd.products_name, p.products_price, p.products_weight, p.manufacturers_id, m.manufacturers_name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m where p.products_status = '1' and p.products_id = pd.products_id and p.manufacturers_id = m.manufacturers_id and pd.language_id = '" . (int)$languages_id . "' and p.products_quantity <= p.products_reorder group by pd.products_id order by $db_orderby $sorted";

 

You then need to add another column to the table that lists all reorder products (around line 230) to show the manufacturers name. Add this:

<td class="smallText"><?php echo $products['manufacturers_name']; ?></td>

 

And finally you'll need to add the column heading and sort-by feature.

 

Add this around line 109 (just after the code/model heading):

<td class="dataTableHeadingContent" align="left" width="20%">?<?php  if (!isset($orderby) or ($orderby == "manufacturer_id" and $sorted == "ASC"))  $to_sort = "DESC"; else $to_sort = "ASC"; echo '<a href="' . tep_href_link(FILENAME_STATS_LOW_STOCK, 'orderby=manufacturer_id&sorted=' . $to_sort) . '" class="headerLink">' . TABLE_HEADING_MANUFACTURER . '</a>';  ?></td>

 

You'll also need to modify your if ($orderby == "model") { statement to include the mfr's name:

 

Just replace with:

 if ($orderby == "model") {
 $db_orderby = "p.products_model";
  } elseif ($orderby == "name") {
 $db_orderby = "pd.products_name";
  } elseif ($orderby == "manufacturer_id") {
 $db_orderby = "p.manufacturers_id";
  } elseif ($orderby == "stock") {
 $db_orderby = "p.products_quantity";
  } else {
 $db_orderby = "p.products_model";
  }

 

Everything I have put above is simple copy and paste, the only modifications to the original code is to get it to show manufacturers name.