MySql

[Anon]

I am going to redo my whole site with a database, and it seems MySql is the thing to use.

What if i wanted different catagorys, as it looks like MySql works on a single table structure.

would i have to have a variable with in that structure that would hold the type of cataogry that the item you are adding to the database is is associated with that field, that you would later recall to show all the data in that catagory.

Is this the right way of doing it or is there a easier way.

I have experience with C programming so i know how things are working.

-alicomb

[[deleted]]

A single table structure??

[stephanie]

Alicomb,

I would create at least two tables where the category_id would be the field that relates the two tables. The single table structure will sure works, but would be less efficient when you want to expand/update/query the categories ...

CREATE TABLE product (
ID int(11) DEFAULT '0' NOT NULL auto_increment,
category_id int(3) DEFAULT '0' NOT NULL,
date date DEFAULT '0000-00-00' NOT NULL,
name varchar(100) NOT NULL,
description varchar(250) NOT NULL,
etc ...
PRIMARY KEY (ID)
);

CREATE TABLE category (
category_id int(3) DEFAULT '0' NOT NULL,
cat_name varchar(25) NOT NULL,
ect...
PRIMARY KEY (category_id)
);

ex to list 10 products maximum for each category (in php) according to the tables above :

---

<?
mysql_connect($hostname, $username, $password);

$result_category = mysql_query("SELECT * FROM category order by cat_name");
$nb_of_category = mysql_numrows($result_category);

//for each category

for ($i=0; $i<$nb_of_category; $i++){

//print category name
$category_id = mysql_result($result_category,$i,"category_id");
$cat_name = mysql_result($result_category,$i,"cat_name");
print "<p>$cat_name";

$result_data =  mysql_query("select * from product where category_id='$category_id' order by ID desc LIMIT 0,10 ");  
$nb_of_product = mysql_numrows($result_data);

// print data
for ($j=0;$j<$nb_of_product; $j++){
	$ID = mysql_result($result_data,$j,"ID");
	$name = mysql_result($result_data,$j,"name");
	//ect...
	print "<br>ID - $name";
}

}

?>

Hope that helps.