PHP/SQL query

sto

New Member
Im having issues trying to make a product catalog. Im using mySQL for the database using php to query. My database structure is terrible but being in a hurry i structured it into 6 separate tables. One per parent product.

Carpet
Hardwood
Tile
ect..

Im using a form input and a option field to search the database. The option field has 7 options(All products, carpet, hardwood, tile, laminate, ect). I need alot of work on this portion too and sugestions would be appreciated. My codes a mess and incomplete but ill post what i have at the bottom.

The second question and the main reason im posting is i cant find a good way to query all tables. I realize that a proper structure would solve this but iv had little luck in finding a solution with my amount of experience. Iv settled on this for not knowing of a better option.

Code:
 select * FROM carpet, hardwood, tile where match(name, color, manufacturer, brand, fibre, store_stock, specials, room_type, pattern_repeat, size, samples_avalible, product_style, product_usage, reviews, p_info, price, warranty) against ('*$find*' IN BOOLEAN MODE);"

Im in way over my head and any help would be very appreciated. Ill link to my php code and the live page.


tl;dr Need to query 6 tables for lack of a better option. Suggestions on my code are appreciated because its work in progress and mess.

http://www.deerfootcarpet.com/search/search.php

Thanks,
Nathen
 
Last edited:

leroy30

New Member
I use MSSQL so I don't know enough MYSQL syntax but as yourself and DHDdirect pointed out this should really be a single table.

Each product type would be a value set in a field in the table. For example this could be your table structure...

Product_Table
>> GUID As UniqueIdentifier
>> Type As VarChar(16) (Int is more efficient but string will be more readable to you)
>> Name As VarChar(128)
etc.. etc..

Then in your query for search you would have something like...

SELECT * FROM Product_Table WHERE Type=@SearchParamater

Now you could have a seperate table with definitions of product types but probably best you keep it simple for your first one and hard-code it in.
 

sto

New Member
OK, iv restructured my data according to the mysql tech resources, unfortunately it was taken off the internet right after i printed it. Scanned copy here It still left me a question about the products themselves.

higherdb.jpg


1. Do all the products go in one table? If so how do i have separate columns for each. For carpet i dont need the width of a hardwood board but i need pattern repeat.

2. Do i have a separate table for each that holds the carpet info i need?

3. Do the products have numbers in the nested_category table?

Thanks,

Sto
 
Last edited:

jonmark

New Member
Let's try to do a query of our database for people who have cats for a pet. We will do this by adding a WHERE clause to set pet equal to Cat.

<?php
// Connects to your Database
mysql_connect("your.hostaddress.com", "username", "password") or die(mysql_error());
mysql_select_db("Database_Name") or die(mysql_error());
$data = mysql_query("SELECT * FROM friends WHERE pet='Cat'")
or die(mysql_error());
Print "<table border cellpadding=3>";
while($info = mysql_fetch_array( $data ))
{
Print "<tr>";
Print "<th>Name:</th> <td>".$info['name'] . "</td> ";
Print "<th>Color:</th> <td>".$info['fav_color'] . "</td> ";
Print "<th>Food:</th> <td>".$info['fav_food'] . "</td> ";
Print "<th>Pet:</th> <td>".$info['pet'] . " </td></tr>";
}
Print "</table>";
?>
 

nafirici

New Member
OK, iv restructured my data according to the mysql tech resources, unfortunately it was taken off the internet right after i printed it. Scanned copy here It still left me a question about the products themselves.

higherdb.jpg


1. Do all the products go in one table? If so how do i have separate columns for each. For carpet i dont need the width of a hardwood board but i need pattern repeat.

I usually create two tables, one that is the adjacency version of the table and then the nested_set version which is the tree structure

You can add an atttributes table to separate out stuff like width.

2. Do i have a separate table for each that holds the carpet info i need?

3. Do the products have numbers in the nested_category table?

I would, but they would join to the Product table if you do it how I do.

Thanks,

Sto

My replies are inline
 
Top