Jump to content


Photo

Help with making a Product Database


  • Please log in to reply
81 replies to this topic

#1 downfall

downfall
  • Members
  • PipPipPip
  • Advanced Member
  • 61 posts

Posted 28 September 2006 - 02:14 PM

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?

#2 alpine

alpine
  • Members
  • PipPipPip
  • Advanced Member
  • 756 posts
  • LocationNorway

Posted 28 September 2006 - 02:22 PM

one example:
<?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;

?>


#3 speedy33417

speedy33417
  • Members
  • PipPipPip
  • Advanced Member
  • 80 posts

Posted 28 September 2006 - 02:26 PM

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.

#4 downfall

downfall
  • Members
  • PipPipPip
  • Advanced Member
  • 61 posts

Posted 28 September 2006 - 03:04 PM

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!

#5 alpine

alpine
  • Members
  • PipPipPip
  • Advanced Member
  • 756 posts
  • LocationNorway

Posted 28 September 2006 - 03:09 PM

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  :)

#6 downfall

downfall
  • Members
  • PipPipPip
  • Advanced Member
  • 61 posts

Posted 28 September 2006 - 03:30 PM

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

#7 downfall

downfall
  • Members
  • PipPipPip
  • Advanced Member
  • 61 posts

Posted 29 September 2006 - 12:19 PM

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, all 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?


#8 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 29 September 2006 - 12:32 PM

sure,

Make your link look like this:

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

Obviously change this value for the one you're using

Regards
Huggie
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#9 downfall

downfall
  • Members
  • PipPipPip
  • Advanced Member
  • 61 posts

Posted 29 September 2006 - 12:51 PM

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?



#10 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 29 September 2006 - 01:08 PM

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:

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;

Regards
Huggie
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#11 downfall

downfall
  • Members
  • PipPipPip
  • Advanced Member
  • 61 posts

Posted 29 September 2006 - 01:31 PM

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?

#12 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 29 September 2006 - 01:47 PM

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.

<?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;
}
?>

Regards
Huggie
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#13 downfall

downfall
  • Members
  • PipPipPip
  • Advanced Member
  • 61 posts

Posted 29 September 2006 - 04:39 PM

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 :)


#14 downfall

downfall
  • Members
  • PipPipPip
  • Advanced Member
  • 61 posts

Posted 29 September 2006 - 05:27 PM

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

$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;
}

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!

#15 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 29 September 2006 - 06:07 PM

Sorry, typo!

Remove the $ from the front of $mysql_fetch_array(...

Regards
Huggie
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#16 downfall

downfall
  • Members
  • PipPipPip
  • Advanced Member
  • 61 posts

Posted 29 September 2006 - 06:30 PM

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!! ;)

#17 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 29 September 2006 - 06:43 PM

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

You probably have something like:
$sql = "SELECT category FROM products";

Change to:
$sql = "SELECT DISTINCT category FROM products";

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
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#18 alpine

alpine
  • Members
  • PipPipPip
  • Advanced Member
  • 756 posts
  • LocationNorway

Posted 29 September 2006 - 06:51 PM

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.

#19 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 29 September 2006 - 06:56 PM

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


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

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


Regards
Huggie
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#20 alpine

alpine
  • Members
  • PipPipPip
  • Advanced Member
  • 756 posts
  • LocationNorway

Posted 29 September 2006 - 07:01 PM

my misreading - thought it was about the categories table




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users