PHP MySQL Categories Tutorial

Beginners - Learning PHP
#1

Hi there.

Could anyone recomend a decent php / SQL tutorial that covers linking product categories by id ?

I have a php order form that I need to display a list of items within their specific categories.

For example:

Jeans.

 Levis
 Pepe
 Wrangler

Shirts.

 Ecco
 Nike
 Puma

ect ect

Unfortunately I am struggling with the SQL select statement. From what I have researched so far, I would need to create a database with 2 tables. One that has id, product_name, price, category_id and a second table with category_id, category_name.

At this point I am guessing that the tables will need to be linked by category id ??? (keyword guessing).

Anyhow, I am really struggling to find some info that explains what I need to do to make this work correctly

Any help or a nudge in the right direction would be greatly appreciated.

#2

You’re on the right track.

First you need to understand the concept of database relationships; have a look at these:

http://www.techrepublic.com/article/relational-databases-defining-relationships-between-database-tables/5034792
http://net.tutsplus.com/tutorials/databases/sql-for-beginners-part-3-database-relationships/
http://databases.about.com/od/specificproducts/a/Database-Relationships-An-Introduction-To-Foreign-Keys-Joins-And-E-R-Diagrams.htm

Next, you need to get to grips with SQL, joins, grouping, ordering etc: http://www.w3schools.com/sql/

There are a lot more resources out there: http://www.google.co.uk/#newwindow=1&biw=1424&bih=1084&sclient=psy-ab&q=sql+tutorial&oq=sql+&gs_l=hp.1.1.0l4.285107.285688.2.288250.4.4.0.0.0.0.165.463.1j3.4.0…0.0…1c.1.16.psy-ab.tEFGKvl7xTk&pbx=1&bav=on.2,or.r_qf.&bvm=bv.47534661,d.d2k&fp=4e7f70798aa7e636, it’s really a case of finding one that works for you.

#3

Cheers for that mate.

Some helpfull info there.

Should be enough to get me started.

#4

Hello again.

I have now created two tables.

            product                                            category                                            
                  |                                                        |

id, price, item, category_id category_id, category_name

My sql statement is as follows:

[php]$sql=“SELECT *FROM product AS p, category AS c
LEFT JOIN product
ON p.category_id=c.category_id
GROUP BY c.category_name”;
$result=mysql_query($sql);
if (false === $result) {
echo mysql_error(); [/php]

This gives me the following error:

Unknown column ‘p.category_id’ in 'on clause

I have now got to the stage where I have been trying to make this work for hours and losing the will to live.

Would someone be able to explain where I am going wrong ?? ;D

#5

Should it not be this:

[php]$sql=“SELECT * FROM product LEFT JOIN product ON product.category_id=category.category_id GROUP BY c.category_name”[/php]

#6

Hi again Xerxes.

Thanks for your help with this.

I did try something similar but got an error which advised me to give the tables an alias ?

Am just finishing at work now, so will try your solution when I get home and let you know how it goes.

Thanks again.

#7

I tried your suggestion this morning.

It gives the following error:

Not unique table/alias: ‘product’

#8

I have had a bit of a breakthrough using this:

[php]$sql=“SELECT *
FROM product AS p
INNER JOIN category AS c
ON p.category_id=c.category_id
GROUP BY c.category_name”;[/php]

However, this will only return one product per category. Or one row per column. For example, I get:

PRODUCT TITLE 1
Product

PRODUCT TITLE 2
Product

Even if I have 5 products, it will only display the first one.

I am guessing it is something to do with the group by or join but have tried many different combinations and cannot get it to function correctly.

Would anyone be able to point me in the right direction ?

#9

This may not help you in this instance, but when I create a database I avoid duplicate and ambiguous table and field names so that I don’t have to use aliases to reference fields. For example my products table might be named tbl_Products and may have the following fields:

ProductID
fManufacturerID
fCategoryID
ProductName
ProductDescription
ProductPrice
etc…

Where fCategoryID and fManufacturerID are foreign keys so in tbl_Categories I might have the fields:

CategoryID
CategoryName

I’m fairly certain it’s a LEFT JOIN you want with a GROUP BY Category Name to produce the output you’re looking for.

#10

Thanks for that Xerxes.

Thats good advice.

I have changed to left join but the problem persists unfortunately.

Will keep plugging away over the weekend and let you know if I get it rectified.

#11

Hi there everyone.

After further research and advice I am unfortunately still stuck on this one. The good news is that I believe my solution to lie within php and not an sql query statement.

The sql statement I am using at the moment is:

$sql=“SELECT *
FROM product AS p
LEFT JOIN category AS c
ON p.category_id=c.category_id
ORDER BY c.category_name”;
$result=mysql_query($sql);
if (false === $result) {
echo mysql_error();
}

When I start to loop through the rows in my tables, the output on my page looks like this:

TRAINERS
Nike
TRAINERS
Puma
TRAINERS
Addidas

SHIRTS
Polo
SHIRTS
YSL

However, my goal is to get it formatted like this:

TRAINERS
Basketball
Running
General / Casual

SHIRTS
Polo
Formal

So what I need to do in php, is eliminate all duplicate category rows.

I have put together an atrocious php attempt for you all to laugh yourselves silly over which is:

[php]<?php
// Start looping rows in mysql database.
while($rows=mysql_fetch_array($result)){
$rows[‘category_name’] = $category_name;
$category_name = array_unique($category_name);
?>

<?php echo $category_name; ?> 
<tr class="odd">
    <td class="product-title"><?php echo $rows['items']; ?></td>
        <td class="price-per-pallet" align="center">£<span><?php echo $rows['price']; ?></span></td>[/php]

Which obviously doesn’t work but the idea is to fetch the array and then use array_unique to eliminate the duplicate rows.

First of all, can someone advise me if I am even on the right lines. If I am can you advise what I would need to do to make this work.

If I am heading in completely the wrong direction (bear in mind I am fumbling around in the dark), could you advise me where I need to start looking to get this working.

Many thanks in advance from a very confused novice.

#12

From Murach’s PHP & MySQL:

Most of the time, you'll want to code an [i]inner join[/i] so that rows are only included when they key of a row in the first table is equal to (matches) the key of a row in the second table.

Your answer will likely lie in how you fetch your information from your database query. When you join a table, you get something like this:

( A really bad ASCII Table )

From ‘categories’ | From ‘products’
============+================
categoryName | productName | productID
Jean | Levis | 1
Jean | Wrangler | 2
Shirt | Fruit of Loom | 3

So if you’re printing out the list of every product from the products table, it seems nested loops would be a solution: one to process each category that’s different, and one that’s going to process all the products in a particular category.

Sponsor our Newsletter | Privacy Policy | Terms of Service