Help with Better Script for Static Pages

[deezin]

Hi there. I hope that you can help me. I am extracting a bunch of static pages from a database with PHP and MYSQL. I had the script working on a smaller scale, but when it had to sort out about 4,000 listings at once, it timed out. I have listings that I want separated by city within each state. So I want the page to look something like (all the listings to be separated by city one one page like below):

LISTINGS IN NEWARK, NJ

Listing Title 1
123 Generic Street Address
Newark, NJ 08520
609-371-1223

Listing Title 2
4556 Generic Street Address
Newark, NJ 08435
609-453-4323

etc.

I have included some my code below the table information, maybe one of you can help me and take a look. Thank you!!

TABLE 1

TABLE NAME: states_cites

id (auto_incremented number)
state (State Abbreviationgs)
city (City Names)

TABLE 2

TABLE NAME: tutorials

id (auto_incremented number)
title (Listing Title)
street (street address)
city (city)
state (state abbreviation)
zip (zip code)
phone (phone number)
category (category name)
catid (category id number)
icon (image name)
sub_folder (directory folder name)
categ_exp (long explanation of category)
short_category (short category explanation)

TABLE 3

TABLE NAME: categories

id (auto_incremented number)
category (category name)
short_category (short category explanation)
description (long category description)
sub_folder (directory folder name)

You can Find the Code Posted Here in a Text File.

http://www.aimingatmoms.com/phphelp.txt
Thank You!!

[guelphdad]

Instead of pointing people to all that irrelvant code, just post your actual queries here. If you were writing a novel you wouldn't have people read the entire novel to figure out a good title for chapter 7, you'd have them read chapter 7 right?

the most likely thing that is slowing you down are the following:

1) you are using a loop to get your categories and within that you are using a separate query to get those items inside those categories. this is an expensive, and unnecessary use of resources

2) you do not have proper indexing on your tables.

4,000 rows is an insignificant number of rows. mysql or almost any other database, can easily handle data in the millions of rows without much effort.

If you have a quick look at the categories/subcategories link below that may help you.

If you are not familiar with indexing, or haven't done it on these tables, it is worth investigating in the mysql manual about how/why to implement them.