Arrays or not to use arrays-that is the question

chaosdivine

Hello, this is a great place to learn and I have enjoyed browsing others threads -now however I need some desperate help to solve a problem I am having (I appreciate anyone's efforts in advance to help me out). Here I start my ramble:::

I am building a domain trading website. On the index page is a "marquee domains" DHTML scrolling layer. Here, data is shown according to MySQL queries that access data from two different tables categories and domains.

I have created an administration section that an admin can create/add/delete categories and domains and therefore the category headings can be variable(not a fixed number) and same with the number of domains that are under a category heading can vary as well.

Please see the attached JPEG that will better show what I am trying to accomplish.

What I have done is get a category heading and its corresponding domains but only after individual and multiple select statements and queries to the database. This is not the proper way to do things and I know it but I don't know PHP or MySQL that well to get my info properly formatted.

What I am asking is-is there a better way to get a variable amount of category headings and their corresponding variable amount of domains from the MySQL database and what PHP control structures should I use to get the appropriate data.

I have thought about using arrays and joins...I am even thinking of subselects (which wont be supported until MySQL 4.2 or something lilke that). To be honest I have no clue what to do...

here are my mysql tables:
lookup: (used for joins)
+-----+-----+
| DID | CID |

+-----+-----+
| 8 | 15 |
| 9 | 12 |
| 10 | 15 |
+-----+-----+

domains:
+----+---------------+------------+-------+------+
| ID | domains | domainDate | price | AID |
+----+---------------+------------+-------+------+
| 8 | sss.bb | 0000-00-00 | 100 | 6 |
| 9 | golfsucks.com | 0000-00-00 | 1500 | 6 |
| 10 | testing.com | 0000-00-00 | 123 | 6 |
+----+---------------+------------+-------+------+

categories:
+----+------------------+
| ID | Name |
+----+------------------+
| 1 | Business |
| 2 | Consumer Goods |
| 3 | Finance |
| 4 | Food and Leisure |
| 5 | Gaming |
| 6 | Health |
| 7 | Memorabilia |
| 8 | Pets |
| 9 | Real Estate |
| 10 | Short Names |
| 11 | Showcase Names |
| 12 | Sports |
| 13 | Technology |
| 14 | Travel |
| 15 | Uncategorized |
| 16 | XXX |
+----+------------------+

Here is a MySQL query that I wrote that is getting me somewhere but still not doing what I want... I don't know if this helps but...

select categories.*, domains.domains, domains.price, lookup.DID, lookup.CID from categories,domains,lookup where domains.ID=lookup.DID and categories.ID=lookup.CID order by categories.name;

Gives me:
+----+----------------+---------------------+-------+-----+-----+
| ID | Name | domains | price | DID | CID |
+----+----------------+---------------------+-------+-----+-----+
| 1 | Business | sss.bb | 100 | 8 | 1 |
| 1 | Business | golfsucks.com | 1500 | 9 | 1 |
| 2 | Consumer Goods | ssssssss.com | 1237 | 13 | 2 |
| 3 | Finance | nocash.com | 88 | 14 | 3 |
| 5 | Gaming | ilike2lose.com | 3456 | 15 | 5 |
| 6 | Health | scratchnsniff.ca | 23 | 16 | 6 |
| 7 | Memorabilia | lostnfound.biz | 45 | 17 | 7 |
| 8 | Pets | happydog.com | 678 | 18 | 8 |
| 10 | Short Names | sdefv.ca | 1 | 20 | 10 |
| 11 | Showcase Names | feffffc.ca | 33 | 21 | 11 |
| 12 | Sports | golfsucks.com | 1500 | 9 | 12 |
| 13 | Technology | fcwfv.ca | 6 | 22 | 13 |
| 14 | Travel | ilikesheep.ca | 89778 | 23 | 14 |
| 15 | Uncategorized | testing.com | 123 | 10 | 15 |
| 15 | Uncategorized | shitonthepillow.com | 234 | 24 | 15 |
| 16 | XXX | tesboysdomain | 34592 | 12 | 16 |
+----+----------------+---------------------+-------+-----+-----+
16 rows in set (0.07 sec)

barand

Looks like you are well on the way. You only need to read the results of your query, outputting a category header when the category changes and listing the domains. Store the price in an array as you go if you need to look it up later.

$prices[$cid][$did] = $price

If a domain can only belong to one category, I'd drop the lookup table and store the category in the domain table then you only need the one join.

chaosdivine

Thanks Barand I will have to try out your suggestion. I did not even think to store the price in an array.

I am still kind of lost on your:

You only need to read the results of your query, outputting a category header when the category changes and listing the domains.

however.

How would I go about doing this since the category headers are variable and same with the domains?

I must be missing something that is staring me right in the face :rolleyes:

barand

So long as your database is up to date, your page will reflect the latest contents with something like this :-

	$sql = "select categories.*, domains.domains, domains.price, ".
	"lookup.DID, lookup.CID from categories,domains,lookup ".
	"where domains.ID=lookup.DID and categories.ID=lookup.CID ".
	"order by categories.name";
	$results = mysql_query($sql);
	lastcat="";
	while (list($cid, $cat, $dom,$price,$did)=mysql_fetch_row($result)) {
		if ($cid!=$lastcat) echo "<strong>$cat</strong><br>";
		$lastcat=$cid;
		echo "$dom<br>";
		$prices[$cid][$did]=$price;
	}

chaosdivine

Thank you for your help Barand!

Your code is nice and compact. I have not tried it out yet-will do tomorrow.

I apprecaite you taking the time to help me out!!!! It was driving me nuts...Someday I hope to be able to grasp the language like you can.

🙂

chaosdivine

Thanks again for your help, it works just wonderfully! I am thrilled!

I never would have thought to code the arrays in the way you did. I learned alot!😃