formatting database output

[generic]

I'm trying to get the output from my database to format into a specific pattern for a nested jquery accordion menu without having a million ugly queries and I'm hoping someone here can show me an easier way to accomplish it.

Ideally, the output would look like this:

[jordanrodriguez.com...]

... where Walt Disney World would be a manufacturer with associated product lines, and then associated products under that.

If anyone can help me with this, I'd be seriously appreciative. I suck at joins in the worst way and I'm just having trouble wrapping my head around an efficient way to write the queries(s).

// mysql structure

table: manufacturers
man_id
man_name
sort // order to be displayed among other manufacturers (INT)

table: series
series_id
series_name
series_man // associated man_id (INT)
sort // order to be displayed among other series (INT)

table: products
prod_id
prod_name
prod_series // associated series_id (INT)
sort // order to be displayed among other products (INT)

// end result for each manufacturer and related series, products. This block would replicate for each manufacturer

<h2>man_name</h2>
<div>
 <h3>series_name</h3>
 <div>
 <ol>
  <li>prod_name</li>
  <li>prod_name</li>
 </ol>
 </div>

 <h3>series_name</h3>
 <div>
 <ol>
  <li>prod_name</li>
  <li>prod_name</li>
 </ol>
 </div>

</div>

Thanks in advance for any help!

[generic]

Nevermind, I figured it out. I just went with the million ugly queries option for the time being lol Thanks anyway folks.

[topr8]

what i do is use lots of ugly queries - but then store the result in a special table in the database and actually do a simple select from that table.


every day i run mainentaince cron jobs which do 'lots of ugly queries' to populate this database table - it's a form of caching, and it works well.

[swa66]

I suck at joins in the worst way

Essentially joins are not hard at all. And they are at the core of using a relational database.

You start with a table (your manufacturers), and you tell in your case your database to add next to it, the columns of another table (your series) in such a fashion that the id's match. And then you do that once more for your products.

Now you need to choose what happens if an entry in e.g. your manufacturers has no match in the series: include NULLs in the rows for the the columns that are added or not ?
-> this is the type of join that you need.

I'll presume that all producs you're interested in have series that exist and all series have manufactures as well. If not, you might want to change the type of join.

untested, but something like this should work:

SELECT m.man_name, s.series_name, p.prod_name
FROM products AS p
INNER JOIN series AS s ON s.series_id = p.prod_series 
INNER JOIN manufacturers AS m ON m.man_id = s.series_man
ORDER BY m.order, s.order, p.order ;

What I'd do:
read up on joins over at wikipedia (it's really against my nature to recommend anything over there, but in this case ... )
[en.wikipedia.org...]
They have a link there near the bottom to "A Visual Explanation of SQL Joins" -> it might help.

Next read up on the actual documentation of mysql (choose the right version to get the subtilities right)
[dev.mysql.com...]

And then : play a bit with it.
do some selects interactively on the database and see the effect of it all.

The really nifty stuff is that you can add columns to a list of manufactures with e.g. the number of products they have in your database, but don;t go there on your first tries, take it a step at a time.

[generic]

Thanks for the input guys. I tried my hand at it and frankly, I'm surprised I got anything to work as intended. I have a carousel that displays manufacturer, series and product information and I stared it down logically (pffft) and came up with this, which works:

$result = mysql_query(" 
SELECT 
products.prod_id, 
products.prod_name, 
products.prod_image, 
products.prod_series, 
series.series_id, 
series.series_name, 
series.series_man, 
manufacturers.man_id, 
manufacturers.man_name 
"." 
FROM 
products, 
series, 
manufacturers 
"." 
WHERE 
products.prod_image IS NOT NULL 
"."
AND 
products.prod_series = series.series_id 
"." 
AND 
series.series_man = manufacturers.man_id
") or die(mysql_error());

How ultra-inefficient is that? haha

[swa66]

That's actually pretty inefficient as you use the cartesian product (it's a kind on implicit join you use by listing multiple tables) : it matches every row of every table with every row of all the other tables creating a MONSTER intermediate table; and then it runs your where clauses to thin it out.

Using inner joins, you essentially move parts of your where clause to the join and make a much smaller intermediate table.

SELECT
products.prod_id,
products.prod_name,
products.prod_image,
products.prod_series,
series.series_id,
series.series_name,
series.series_man,
manufacturers.man_id,
manufacturers.man_name
FROM
products
INNER JOIN series ON products.prod_series = series.series_id
INNER JOIN manufacturers ON series.series_man = manufacturers.man_id
WHERE
products.prod_image IS NOT NULL

is all you need

The AS stuff I showed above is just a shorthand notation not to have to write the full table names all the time.

Use "explain" on your selects to see what it does ... you might be surprised by the number of records you had going there.

[generic]

hahaha and there ya go. I think I'll stick with piles of queries for the time being. And here I thought I was moving on up LOL If this was CSS or javascript, I'd eat it for breakfast... I'll get it eventually though. Thanks for the input everyone!

[generic]

Since I've got you here and we're talking about inefficiency, how would you write a query to fix this mess? It works, but I'm sure it's a total POS on the server side. Not trying to get you to do my work - this does work - but I'm curious to see how it would be simplified. I think that would really help me wrap my head around the different joins. (this is the code that is currently running my nested accordion I mentioned in the OP). And please, be kind, I'm *obviously* a front end designer, not a programmer.

// grab manufacturer info
$manresult = mysql_query("SELECT man_id, man_name FROM manufacturers") or die(mysql_error());
while($manrow = mysql_fetch_array( $manresult )) {

// start building menu with mans
echo '<h2>'.$manrow['man_name'].'</h2>
<div>'.PHP_EOL;

// grab series info
$seriesresult = mysql_query("SELECT series_id, series_name, series_man FROM series WHERE series_man = '".$manrow['man_id']."' ORDER BY sort ASC") or die(mysql_error());
while($seriesrow = mysql_fetch_array ( $seriesresult )) {

echo '<h3>'.$seriesrow['series_name'].'</h3>'.PHP_EOL;

echo '<div>
<ol>'.PHP_EOL;

// grab product info
$prodresult = mysql_query("SELECT prod_id, prod_name, prod_series FROM products WHERE prod_series = '".$seriesrow['series_id']."' ORDER BY sort ASC") or die(mysql_error());
while($prodrow = mysql_fetch_array ( $prodresult )) {

echo '<li><a href="products.php?id='.$prodrow['prod_id'].'">'.$prodrow['prod_name'].'</a></li>'.PHP_EOL;
}

echo '</ol>
</div>

</div>'.PHP_EOL;
}
}

[swa66]

I'd use the one I wrote earlier:

SELECT m.man_name, s.series_name, p.prod_name
FROM products AS p
INNER JOIN series AS s ON s.series_id = p.prod_series 
INNER JOIN manufacturers AS m ON m.man_id = s.series_man
ORDER BY m.order, s.order, p.order ;

it will give you all products and add in the related series and manufacturer data.
(You can add more columns to the select as you need them, they're all available of course).

Make sure to set the sorting so that manufacturers and series stay together. I presumed that they all had different order values, if that's not the case: you need more sorting to make sure they stay together.

Then as you iterate over the results, keep track of the previous manufacturer and the previous series (start out with a nonexistent value such as an empty string). Whenever they change: you start a new series or manufacturer as needed.