Help with making a Product Database

[downfall]

Hi,

I'm trying to make a product database for my music clothing website, and intergrate it with PHP for a dynamic online store, with no hardcoding.

I've got no problem with entering the data, very easy with phpmyadmin :) I've created a Table called "products" in my database, and at the moment have these fields for the "products" table: id, category, band, product_name and price. I will need to add more fields such as stock, size, etc.

But this is what I want to be able to do, but having trouble trying to understand how to pull it off.

I want to be able to pull all the category names from the "products" table into my navigation menu on my website, so it would list "t-shirts", "Hoodies", "hats" etc... AND so they are all linked and go to the appropriate category page when clicked.

In my little PHP book, to list and loop things like the "category" field I want to be displayed on my left navigation, I would need to something like this it looks like:

while($row= mysql_fetch_array($rs)  )
[

  $list .= .Srow["category"].;

echo($list);

But, how can I have it so everything that gets listed, gets linked to it's own category page, that lists all product names along with its prices for that category?

[alpine]

one example:
[code]
<?php

while($row=mysql_fetch_array($rs))
{
$id = $row["id"];
$cat = $row["category"];

$list .= "<a href='details.php?id=$id'>$cat</a>\n";
}

echo <<<_HTML

$list

_HTML;

?>
[/code]

[speedy33417]

One solution would be to make a second table called categories, with fields: id, category_name (add more for your taste)

Pull all category names from this table to create your links. Change the product table so you would have a category_id instead of category.

Hope this helps.

[downfall]

Thank you both for your help!

Alpine - So simply putting that code on my left menu would link the appropriate category to its own category page? Is their anything I would need to change in the code to make it run properly, apart from changing details.php to productlistings.php (my listings page).

I'm a bit of a newbie to this..sorry if the question seems stupid!

[alpine]

I assume you run $rs = mysql_query("select whatever..") before this AND that field id is your primary key,
then it creates valid links that you can use.

- and in productlistings.php you do a new mysql_query using $_GET['id'] as WHERE clause.

Test and play with what you've got, then post code again if any problem appears  :)

[downfall]

Thanks, I'll have a go with it and see what happens. Any problems, I'll be back!

[downfall]

I have now used your code alpine, and it now lists all the categoies on my left menu with all the categoires linked :).

So I'm getting T-Shirts, Posters, Hats etc.. all linked down my left menu, exactly what I wanted.

BUT, [b]all[/b] of the category links all link to: /details.php?id=

Shouldn't the above, after "id=" actually have something after it, such as the category name or something so all the links go to the right category?

At the moment they all go to the same place.

Also, I'm wondering why "id=" is there, as I don't have any id to seperate categorys created in my table/database, such as category_id,.. I only have a "category" field to sort categories.

Admittedly, I haven't created any php for the details.php page yet, but this shoudn't matter yet, as this page is only going to output what the links are told to do.

Can anyone help me?

Instead of all my category links saying this  (if i were hoving over the hats category link):

www.mysite.com/details.php?id=

shouldn't it say this (if I were hoving over the hats category link:)

www.mysite.com/details.php?category=hats

and if I were hoving over the t-shirts category link:

www.mysite.com/details.php?category=t-shirts

Can anyone tell me how to achieve this?

[HuggieBear]

sure,

Make your link look like this:

[code=php:0]<href="details.php?id={$row['category']}">{$row['category']}</a>
[/code]

Obviously change this value for the one you're using

Regards
Huggie

[downfall]

Thanks huggie, but doesn't like that, I'm getting a parse error (unexpectd t_string)

This is what I get with the link inputted in the code I had:

while($row=mysql_fetch_array($rs))
{
$id = $row["id"];
$cat = $row["category"];

$list .= "<a href='details.php?id={$row['category']}">{$row['category']}</a>
}

echo <<<_HTML

$list

_HTML;




Before it was working with this, without the link change:

while($row=mysql_fetch_array($rs))
{
$id = $row["id"];
$cat = $row["category"];

$list .= "<a href='details.php?id=$id'>$cat</a>\n";
}

echo <<<_HTML

$list

_HTML;





Can you see anything wrong with the first one, and why the page doesn't work at all now?

[HuggieBear]

OK, that's be cause you put single quotes around it, and you didn't replace it for yor variable, which was the important part.

Change it to this:

[code]
while($row=mysql_fetch_array($rs))
{
$id = $row["id"];
$cat = $row["category"];

$list .= "<a href='details.php?id=$cat'>$cat[/url]\n";
}

echo <<<_HTML

$list

_HTML;
[/code]

Regards
Huggie

[downfall]

Hi,

thanks for the quick reply.

ive now gone out and using a diff computer, so I'll i'll give it a go when i get in 2-3 hours.

So as I understand, with the code you given me along with alpine, when I hover over a category link, it will show and ultimately link me towards the appropriate category page. ie details.php?category=t-shirts or something like that

and all i would need to do is on details.php page just $name, $price, etc, and what ever category is clicked on the page before shows the names and prices of the selected category?

Is that how it should work?

[HuggieBear]

Correct, your details page would look something like this I guess.  You'd just need to get the category from the URL and use it in the SQL query.

[code]
<?php
include('connect.php') // Initiate your database connection

$cat = $_GET['id'];

$sql = "SELECT name, image, description FROM products WHERE category = '$cat'";
$result = mysql_query($sql);
while ($product = $mysql_fetch_array($result, MYSQL_ASSOC)){
  echo <<<HTML
  <table>
    <tr>
    <td><img src="{$product['image']}" alt="{$product['name']}"></td>
    </tr>
    <tr>
    <td>{$product['name']}</td>
    </tr>
    <tr>
    <td>{$product['description']}</td>
    </tr>
  </table>
  <br><br>
HTML;
}
?>
[/code]

Regards
Huggie

[downfall]

HuggieBear (or anyone!!)

This was what you said previously:

$list .= "<a href='details.php?id=$cat'>$cat[/url]\n";

I've now changed it to this:

$list .= "<a href='details.php?id=$cat'>$cat</a>\n";

I'm now getting a page that works again! I just changed "[/url]" to "</a>" and now seems to be working. Is that how it should be?


I'm now getting /details.php?id=t-shirts when hoving over the T-Shirt link :)

[downfall]

Right, going onto the details.php page, I have put the code in what huggiebear said in details.php:

[code]$cat = $_GET['id'];

$sql = "SELECT name, image, description FROM products WHERE category = '$cat'";
$result = mysql_query($sql);
while ($product = $mysql_fetch_array($result, MYSQL_ASSOC)){
  echo <<<HTML
  <table>
    <tr>
    <td><img src="{$product['image']}" alt="{$product['name']}"></td>
    </tr>
    <tr>
    <td>{$product['name']}</td>
    </tr>
    <tr>
    <td>{$product['description']}</td>
    </tr>
  </table>
  <br><br>
HTML;
}
[/code]

Above the code I obviously put the connect to the right database etc.

Anyway, it doesn't seem to work!!

After clicking on the category link, the details.php page loads with this error:

Fatal error: Call to undefined function: () in /home/rockrag/public_html/details.php on line 26

^^ the above Error, line 26 refers to this line of the code:

while ($product = $mysql_fetch_array($result, MYSQL_ASSOC)){

Can anyone see whats wrong with it? I'm so close now thanks to everyones help here, just need to get this sorted!!

I'd appreciate anyone who can help me!

[HuggieBear]

Sorry, typo!

Remove the $ from the front of [b][color=red]$[/color][/b][color=green]mysql_fetch_array(...[/color]

Regards
Huggie

[downfall]

YES! It works! Exactly what I wanted! :)  :) Thanks huggie

But believe it or not, I've found another problem. :(

Before, I had only entered 2 products into the database, and under the category field, 1 of them was entered as "T-Shirts", and the other one entered as "Posters".

But now I have filled in a new row (in phpmyadmin) to enter a new product, and also called it "T-shirts" under the category field, this is the first time I've tried entering the same category name for a different product, and now I'm getting 2 "T-Shirt" links on my left menu!! The good news is the T-shirt link does show both the T-shirt products added, but obviously I don't want to add a new Category link on my left navigation every time I add a product that already is under an existing category!!

Does my database/tables need working on, if so.. any ideas? Or can it be fixed with some coding?

Again, I'll really appreciate anyones help!! ;)

[HuggieBear]

An easy fix is to change the sql code that gets the categories...

You probably have something like:
[code=php:0]$sql = "SELECT category FROM products";
[/code]

Change to:
[code=php:0]$sql = "SELECT DISTINCT category FROM products";
[/code]

In the long run you'd probably be better changing your table structure if possible, having a seperate category table with a link to the products table.

Imagine this if you will... A product table contains the 1 million products, each product fits into one of 10 categories, that's listed in the column called category in the product table.  To 'SELECT DISTINCT' categories, MySQL has to scan 1 million rows looking for distinct values and then return the 10 that it's found.  Now how much quicker would it be if MySQL went to a categories table and did a 'SELECT ALL' from that table, it only has 10 rows in it, but each of those rows is linked by a unique ID back to the product table :D

Regards
Huggie

[alpine]

well, using distinct would prevent more than one category ever to show up - thats unlightly what you want as a result.

It would be much better if you arranged your category table with a field called e.g. ID set as autoincrement primary key - and queried category links by their ID instead of their names.

[HuggieBear]

[quote]
well, using distinct would prevent more than one category ever to show up - thats unlightly what you want as a result.
[/quote]

That's exactly what he's after in that scenario!  Did you read the previous post?

[quote]
I'm getting 2 "T-Shirt" links on my left menu!! The good news is the T-shirt link does show both the T-shirt products added, but obviously I don't want to add a new Category link on my left navigation every time I add a product that already is under an existing category
[/quote]

Regards
Huggie

[alpine]

my misreading - thought it was about the categories table

[HuggieBear]

[quote author=alpine link=topic=109840.msg443897#msg443897 date=1159556474]
my misreading - thought it was about the categories table
[/quote]

:D I do it all the time.  Doesn't have a categories table yet I don't think, that's the point, so we'll see what we can do.

Huggie

[downfall]

Huggie, Alpine, you've been so helpful through all of this, thank you both!

Huggie - that works perfect , thanks (again!)

I had a feeling I'd have to restrucutre things... so before I go off deleting/adding new tables, I'll just tell you something else I'm hoping to include in my navigation that may effect my database structure...

Ok, first of all the is a music merchandise website. As you know, the category links down the left now which you've both assisted me on let the user browse through category. And your saying maybe a Category table is needed to make this run better, I understand that.

But I also plan to have a dropdown menu in the left navigation, which has all the music band names in, and when you would select one, it would display all products from that band. Essentially, this would be just like selecting a category, except in a dropdown menu, and browsing for band name.

So, my question is, would I need ANOTHER table also, for Band? At the moment, just like category, band is just another field name in my products table. So in the end would I need a products table, categories table, and a bands table?

[HuggieBear]

Yes, that sounds about right...

Regards
Huggie

[downfall]

Thanks, sorry if all the questions are getting a little tedias!

One last thing then, before I put all of this "into action"!

I create new tables, called products, categories, and bands. In categories table, the put the fields "id" and "category_name", and in the bands table, I put in the fields "id" and "band_name". In the products table, would I still need the fields "category", and "band", seeing as I have made new tables specificly for them? If I don't need them, then how can I, when putting in a new product into my database in the products table, say which category this new product belongs to and which band this product belongs to? I understand how I'd probably have to make all 3 tables have "ID" fields as the primary, but don't understand how adding a new product in the product table, how to link it with a category in the category table and a band in the band table.

How would I achieve this (probably basic) relationship?

[HuggieBear]

You still have a category field and a band field in the products table, but they only contain ID's, ID's that are linked to the primary key in both the band and categories table.  It's called normalization.

Try searcing on Google for it.  You'd do well to spend just 15 minutes reading about it or maybe taking a tutorial on it.  It seems like a boring step now, but it will save you so much time and coding in the future, and that's a promise!

Regards
Huggie