[RESOLVED] Creating a Menu from database results

[shauny17]

Hi Guys,

I am trying to create a menu from a database of data, my database contains 2000+ results, layout below:

| price | product_code | stock_available | category_tier1 | category_tier2 | category_tier3 | short_description | long_description | web_link | weight |

I am trying to make a menu from cat tier 2 and 3 (Tier 1 doesnt contain alot of data),
however they contain duplicate results on some rows eg

Cameras
Cameras
Hard Drives
Optical Media

Is there a way in PHP wer i can create a menu eg
Cameras (2)
Hard Drives (1)
Optical Media (1)

which will then go to a sub-menu?

Kindest Regards,

Shaun :glare:

[bradgrafelman]

Sounds like a job for SQL to handle... i.e. using "GROUP BY category_tier2" or whatever as well as selecting a COUNT(*) to get the number of rows in that group.

[shauny17]

bradgrafelman wrote:

Sounds like a job for SQL to handle... i.e. using "GROUP BY category_tier2" or whatever as well as selecting a COUNT(*) to get the number of rows in that group.

so my query would be:

SELECT * FROM 'stock' GROUP BY 'category_tier2' DESC

how would i then have a menu for each different category description?

eg
Cameras
Hard Drives
Optical Drives

etc etc?

[bradgrafelman]

Any chance you can provide us with a few sample rows? I still don't understand your structure very well...

Also, note the use of backticks ( ` ) is not the same as single quotes ( ' ).
Finally, using 'SELECT *' is never a good idea - even if you are 100% sure you're going to need data from every column in the table, it's still best to name the columns you're using if nothing else but for scalability reasons. Otherwise, you're more than likely just wasting resources.

[shauny17]

I Have exported the table to a html file, can be viewed here: http://shaunrigby87.myby.co.uk/sql/index.HTML

NOTE: short_description and long_description have been removed because they are 5000+ chars in length

[bradgrafelman]

Well... I'm trying to figure out the best way to do this... the best I can come up with is passing the category_tier2 name in the query string. Something like this:

$query = 'SELECT `category_tier2`, COUNT(category_tier2) AS numItems FROM `stock` GROUP BY `category_tier2` ORDER BY `category_tier2`';
$exec = mysql_query($query);

while($result = mysql_fetch_assoc($exec)) {
    echo '<a href="myscript.php?tier2=' . urlencode($result['category_tier2']) . '">' . $result['category_tier2'] . ' (' . $result['numItems'] . ')</a><br/>';
}

[shauny17]

THANKS BRAD!

now to go to tier 3 would i do

SELECT category_tier3 WHERE category_tier2 = 'cameras', COUNT(category_tier2) AS numItems FROM stock GROUP BY category_tier3 ORDER BY category_tier3

????

[bradgrafelman]

I would change the tier2 to tier3 in your COUNT() statement, but yes, that's what you'd do.

And of course, if you're pulling data from the $_GET variable to use in your query's WHERE clause, you'll need to sanitize the data with functions like [man]mysql_real_escape_string/man or, at the very least, [man]addslashes/man.

[shauny17]

what would the addslashs() function do?

[bradgrafelman]

Both (one or the other - not combined!) help prevent SQL injection attacks. A quick Google or search on these boards should inundate you with information about these attacks if you're not familiar with them.

[shauny17]

I tried the followed statement:

SELECT category_tier3, COUNT(category_tier3) AS numItems FROM stock GROUP BY category_tier3 ORDER BY category_tier3, WHERE category_tier2 = Cameras

as a test but it says:

ght syntax to use near 'WHERE category_tier2 = 'Cameras'' at line 1

[bradgrafelman]

I'm not sure if it matters, but I've always seen the WHERE statements follow the FROm statement (before the GROUP/ORDER statements).

Also, I'm not sure which you're using, but in your example query, you have backticks surrounding the word Cameras. Since you're passing SQL a string value, those backticks should be single quotes instead.

[shauny17]

Its ok , i just sorted it, the statement should read:

SELECT category_tier3 , COUNT(category_tier3) AS numItems FROM stock WHERE category_tier2 = 'Cameras' GROUP BY category_tier3 ORDER BY category_tier3

i was using cameras as an example.

Brad thanks for all your help, you have been great! :p