[RESOLVED] How to do this with php&mysql

[HenryGuy]

I have two tables,
one contains: (category database)
catid (auto increment on insert)
catname (name of category)
subid (if subcategory then is not 0)

the other contains: (items database)
id (auto increment)
name
catid (links category and item database)

I have something that I am having trouble visualizing a solution for. I want to run two queries, saving the data in two seperate arrays. I want to be able to only run two queries to the database, one containing all the info from category database and one containing all the items database information

Currently there is a while loop that renders all the items (unless sorted by another element) on a scrollable, sortable table.

I suppose the question I'm asking is, how do I query the category database once, then run the while loop for items and inside the while loop compare the catid (contained within the items database) to display the appropriate catname (contained with the category database).

The reason I feel I should do this is to avoid unnecessary queries on each loop through the while statement just to find the category name. I also do not want to store the name inside the items database to avoid future appendages to the entire database, in case the end-user decides to alter the name of a category, nothing would be affected except the categories database since the category id will never change but the name the user see is what actually matters.

I have a good background in php and mysql and learned to program when I was 12 but have since been out. Now 10 years later I have hopped back on the scene and my knowledge has increased vastly but I still need help occasionally. If anyone has an answer I'd appreciate it! Or if my question was not specific enough or, perhaps, too verbose please let me know and I'll attempt to restate it.

[dalecosp]

You create an associative array of categories, with the catid as the key and the catname as the value.

It's then fairly easy to look up that data during each iteration of your while() without querying the RDBMS.

HTH,

[HenryGuy]

I have spent the past few hours reading up examples and text on everything array related and I still cannot wrap my head around how to take a query, even using mysql_fetch_assoc and search through it.

I just cannot understand the logic surrounding foreach, list, in_array, array_key_exists etc.

Could someone give me a basic example, I'm not asking for my problem to be solved as I'd rather learn how to do it then be given an answer but I just can't seem to grasp it with the information available.

I'm not sure how to explain the problems I am coming across as it is really late at night, who knows I might sleep on it and dream up the answer. But if someone could give me an example using a MySQL query with 3 fields being turned into an associative array and then being "searched" and ONLY the appropriate value that matches a variables value, being echoed, it'd help a lot! I feel this is a breaking point between 2D programming and 3D programming and I hope in a few months to begin learning C++. If I can't grasp this now, I doubt I can make the game I want to.

Again thanks so much! An example isn't necessary if a little more description and/or steps could be given. Obviously I'm willing to put the effort forth to teach myself but I am just having a difficult time with this subject.

[johanafm]

You pretty much

$cats = query_db($cat_query);
$items = query_db($item_query);
$categories = array();

# set up your array
while ($c = nextrow($cats))
{
	$categories[$c['catid']]['catname'] = $c['catname'];
}
while ($i = nextrow($items))
{
	$categories[$i['catid']]['items'][] = $i;
}

# use it
foreach ($categories as $cat)
{
	foreach ($cat['items'] as $item)
	{
		echo $item['name'] . '(' . $cat['name'] . ')<br />';
	}
}

But you are better off doing it all using one query

SELECT name, catname
FROM items
INNER JOIN category c ON c.catid = items.catid

And process that result set once, since it will contain all the info you need.

[HenryGuy]

Thank you ever so much for that! The inner join works perfectly and I now quite a bit more about manipulating database information.

I am using the inner join in place of the foreach statements but I am still interested in learning this method.

I am confused as to how the foreach is working in this example though.
I've always struggled with understanding arrays and every example and explanation shows me what you are showing me now. Could you maybe elaborate a little further?

Perhaps tell me what is going on in these portions of the code.

I understand the while statement is feeding the rows information into $c and $i respectively. Where it says categories[$c[catid][catname] is that saying categories array = row from SQL query with catid and catname? I'm confused as to what is happening to this data here. This is possibly why I can't understand how the foreach is working. I feel like they are similar to SQL queries but with different syntax but am having trouble relating the procedures.

Are the foreach statements doing this:
foreach $categories part of the array label it as $cat
and then foreach $cat[items] part of the array label it as $items?
What exactly happens in this foreach statement, is it reassigning variable names, I only half understand the needle and haystack jargon.
Is it searching the $categories array where $cat, inside the for statement, is equal to $items where $cat[items] = the catid located inside $categories??

Thanks again for all your help!

[bradgrafelman]

HenryGuy wrote:

What exactly happens in this foreach statement, is it reassigning variable names, I only half understand the needle and haystack jargon.

[man]foreach[/man]($array as $item) loops through each item in the array $array one-by-one, assigning each value (in turn) to the variable $item.

[HenryGuy]

Thank you! Still befuddled by the logic of the statement but I'm going to run some tests with echos to simulate what the outputs would be in different situations!

Thanks for the explanation everyone, hopefully I can continue to contribute to these forums as I intend to make software engineer my profession!

This has been resolved 😃