[SOLVED] Maximum Commands Per Page. Is 52 Too Many?

[Fluoresce]

I have a page on my site which presents an A-to-Z list of automotive makes.  The page looks like this:

 

Acura

Alfa Romeo

Aston Martin

Audi

 

Bentley

Bitter

BMW

 

Etc.

 

The following code is used to select the makes from the database:

 

$query = "SELECT make, url FROM tmake WHERE make LIKE 'A%'";
$result = mysql_query($query, $conn) or trigger_error("SQL", E_USER_ERROR);
if(mysql_num_rows($result) >= 1) {
while($row = mysql_fetch_assoc($result)) {
echo "<a href=\"" . $row['url'] . "\">" . $row['make'] . "</a><br />";
} // end while
} // end if

else {
echo "None";
}

 

All I modify is the end of the top line to select makes that begin with a different letter.  For example, if I change 'A%' to 'B%', the code will select all makes beginning with the letter B instead of A.

 

Question 1) Is it OK to use the same code 26 times on the same page, once for each letter?

 

Another, related, question . . .

 

I want the page to also present how many models there are for each make in the database, like this:

 

Acura (78)

Alfa Romeo (20)

Aston Martin (10)

Audi (77)

 

Bentley (7)

Bitter (4)

BMW (99)

 

The problem is, the models are in another table of the database.

 

Question 2) If I add a SELECT COUNT(*) command to every letter, there will be 52 commands on one page!  Will that be OK?

[trq]

1: There is absolutely no need to do that. Just get all the results ordered alphabetically and display them that way.

 

2: Why would you need that? Your already selecting all the records, simply use mysql_num_rows to count them.

[Fluoresce]

Thanks for the response, Thorpe!

 

"1: There is absolutely no need to do that. Just get all the results ordered alphabetically and display them that way."

 

I was trying to keep the As away from the Bs, etc. They must be separate, because, at the top of the page, I have an alphabet. When visitors hit A, they are dropped down to the As, which sit under a huge A headline. It looks like this:

 

A | B | C | D

 

A

 

Acura

Audi

 

B

 

Bentley

BMW

 

Can I still do it your way?

 

"2: Why would you need that? Your already selecting all the records, simply use mysql_num_rows() to count them."

 

The first one is a SELECT command; the second one is a COUNT command. The COUNT has to count rows which occur in a completely different table - hence my confusion.

 

How might I be able to do this? A little push in the right direction would be appreciated.

[killah]

$a_o_l = array
(
1 => 'A',
2 => 'B',
3 => 'C',
4 => 'D',
5 => 'E',
);
if( empty($a_o_l) )
{
echo 'None.';
}
else
{
for($i = 0; $i < count($a_o_l); $i++)
{
	$query = mysql_query("SELECT make, url FROM tmake WHERE make LIKE '".$a_o_l[$i]."%'");
	if( mysql_num_rows($query) >= 1 )
	{
		while($soc = mysql_fetch_assoc($query))
		{
			$numb = mysql_num_rows(mysql_query("SELECT ID FROM table WHERE make = '".$soc['make']."'");
			echo '<a href="'.$soc['url'].'">'.$soc['make'].'</a> ('.$numb.')<br />';
		}
	}
}
}

 

UNTested!

[Fluoresce]

Hi, Killah.

 

I appreciate the effort, man. Thank you very much. But I'm a newbie, dude. I don't understand any of that code. I tried it but it didn't work - even after I changed everything to suit my database.

 

In one table I have the car makes; in another I have the models. I need the makes to be selected from their table, and the models to be counted in their table. Is that possible?

[premiso]

<?php
$query = "SELECT make, url FROM tmake ORDER BY make";
$result = mysql_query($query, $conn) or trigger_error("SQL", E_USER_ERROR);

if(mysql_num_rows($result) > 0) {
$last_start = "A";
while($row = mysql_fetch_assoc($result)) {
	$start = substr($row['make'], 0);
	if ($start != $last_start) {
		$last_start = $start;
		echo "<br /><b>" . $start . "</b><br />";
	}

	$tResult = mysql_query("SELECT count(model) FROM models WHERE make = '{$row['make']}'");
	$total = mysql_result($tResult, 0);
	echo "<a href=\"{$row['url']}\">{$row['make']}</a> ({$total})<br />";
} // end while
}// end if
else {
echo "None";
}
?>

 

That should get you what you want. The inner query can probably be compressed into 1 query if I wanted to take the time to do it. But this will be better than your original code by far.

 

Note that the tResult query will need to be modified to fit your models table structure.

 

EDIT:

Added the "A" "B" to the top etc.

[premiso]

One other minor fix (edit time was up)

 

      $start = substr($row['make'], 0);

 

Should be

      $start = substr($row['make'], 0, 1);

 

Sorry about that.

[Psycho]

No offense Permiso, but doing queries within loops is bad form in my opinion. It can lead to severe performance issues.

 

Note: you will need to modify the table and field names in the JOIN part of the query as appropriate for your database.

//Query ALL the records ordered by make
$query = "SELECT tmake.make, tmake.url, COUNT(models.model) as model_count
          FROM tmake
          JOIN models ON models.makeid = tmake.makeid
          ORDER BY tmake.make
          GROUP BY models.model";

$result = mysql_query($query, $conn) or trigger_error("SQL", E_USER_ERROR);

if(mysql_num_rows($result) >= 1)
{
    //There were results, let's display them
    $current_letter = false;
    while($row = mysql_fetch_assoc($result))
    {
        //Detected a new letter. Insert header anchor
        if($current_letter != substr($row['make'], 0, 1))
        {
            $current_letter != substr($row['make'], 0, 1)
            echo "<br /><h1><a name=\"$current_letter\">$current_letter</a></h1><br />";
        }
        //Display the current make with count
        echo "<a href=\"{$row['url']}\">{$row['make']} ({$row['model_count']})</a><br />";
    } // end while

} // end if
else
{
    //There were no results
    echo "None";
}

[premiso]

No offense Permiso, but doing queries within loops is bad form in my opinion. It can lead to severe performance issues.

 

None taken. I know it is bad, but my brain is not working right for SQLs today, so yea. Thanks for posting a correct way to do that.

 

lol and that is why I said:

The inner query can probably be compressed into 1 query if I wanted to take the time to do it

[Fluoresce]

Premiso and MjDamato, thank you both very much! I will try your suggestions immediately.

[Fluoresce]

Thanks for the help, guys! I really appreciate it.

 

What I want to do has become much more complicated. I must go away and think about it. As such, this thread will now be marked 'Solved'.

 

Once again, thanks for everything.