MySQL + PHP Question.

[Big-E]

So basically, I am going to have a database which contains items of various categories, and within the categories are various types and then for each type there will be various sizes.

The tables is set up like this:

Category Type Size Quantity Price etc..


Basically, the rest of the table is pretty arbitrary pertaining to my question.

My question consists of this..
What would be an easy way to extract information from the database so I can create lists for each category dynamically so I only have to create one function such as getCategories() and it lists all the various categories only once (even if its listed multiple times) and then for each category I can call getTypes($categoryName) and it will list the various types for that category, and then I can call another function getSizes($type) and it will list all the various sizes.

Basically, I am trying to create a fully functional dynamic catalog for all the items in my database so that I don't have to edit much code later on down the road.

[Swan]

well effective use of primary keys would be a good start in ensuring maximum data effiency. Out of curiosity have you normalised your data yet, and if so, in what notational form have you reached so far?

[Big-E]

Well, I don't think you are exactly understanding my question - nor do I understand yours. I may know what you mean, but in the terms and context of which you used, I am unfamiliar to what you are speaking of.

I can start by explaining my question:

What is the easiest way to do the follow:

If I have a database of the following:

Hex Nut 1/2"
Wing Nut 1/4"
Wing Nut 1/2"
Hex Bolt 1/2"
Hex Bolt 3/4"


I would like to create a function so that I can list my products in a catalog form in the following manner.

Nuts > Wing > 1/2"
1/4"
Hex > 1/2
Bolts > Hex > 1/2"
3/4"

Of course, these are arbitrary values but it gives you an idea. Basically, I want to FIND and EXTRACT SIZE values for each TYPE in various CATEGORIES only once - I can not devise a way, with the knowledge I have right now and I am wondering what an easy way to do this, would be?

[ayu]

well, something like...

getCategories() {

/*Assuming that you already have an included/global file like config.php*/

$connect = @mysql_connect($host, $user, $pass)
or die (could not connect to database);
$database = mysql_select_db($db)
or die(could not find database);
$sql = "SELECT * FROM categories";
$query = mysql_query($sql);

/*assuming that you have a table named categories, with a name column*/
while($row = mysql_fetch_array($query)) { echo $row['name']; }

mysql_close($connect);
}


Ok, so this is a basic code since i just woke up, and i'm pretty hungry at this very moment. But it gives you the basic idea at least, it grabs all the posts in the specified tables, and then looks them out and puts them in an array and then prints the name of the post out. Would not look very nice on the screen of course, but as i said, quick example ^^

[Big-E]

See that is the problem, I know how to do that much - but for prod_type (which in this instance would be the name variable, you used) would be recurring multiple times.

So it would go.

Bolts > Hex
Hex
Hex
Hex
Leg
Leg
..etc


It will repeat because the are listed multiple times in the table, I want to devise a way to pull out ALL the DIFFERENT PROD_TYPES but only ONCE.

[Big-E]

This is actually the code I have right now.

Code: Select all

function listProduct($item){

    $query = mysql_query("SELECT * FROM product WHERE prod_category = '$item'") or die("Unable to obtain list of products.");
    if($list = mysql_fetch_array($query)){
	echo '<a href="/AHF/' . $list['prod_type'] . '.php">' . list['prod_type'] . '</a>'; 
    }
}

This requires a LOT of manually editing, especially once I get adding 1000+ items. I am looking to devise a much easier method to automatically extract the values out of the DB.

[ayu]

simply add "LIMIT 1" at the end of the SQL query

[Big-E]

There we go. That was quite painless.