52 Commands On One Page . . . Is That Too Many?

Discussion in 'PHP' started by Masterful, Feb 17, 2009.

0
  1. #1
    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";
}
    PHP:
    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 (8)
    • Bitter (4)
    • BMW (99)
    The problem is, the models are in another table.

    Question 2) If I add a SELECT COUNT(*) command to every letter, there will be 52 commands on one page! Is that too many?
     
    Masterful, Feb 17, 2009 IP
  2. tihan

    tihan Active Member

    Messages:
    64
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    93
    #2
    1. you don't have to copy-paste the code 26 times, you can make function for it (http://hu.php.net/manual/en/functions.user-defined.php).
    2. if you are afraid that it's too much, you can save the result to .txt file and display the .txt instead of making new sql commands every time. You can regenerate .txt in every hour or when you needed.
     
    tihan, Feb 18, 2009 IP
  3. crivion

    crivion Notable Member

    Messages:
    1,669
    Likes Received:
    45
    Best Answers:
    0
    Trophy Points:
    210
    Digital Goods:
    3
    #3
    that will harm your sql server

    why dont you do a "select distinct(make) ......" etc and it'll be easier
     
    crivion, Feb 18, 2009 IP
  4. e_hippie

    e_hippie Guest

    Messages:
    32
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #4
    Good suggestion. But to add to this, and complete the request for displaying a total count of makes in the DB, the OP should do a join with the 2nd table.
     
    e_hippie, Feb 21, 2009 IP
  5. mentalic

    mentalic Peon

    Messages:
    37
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #5
    for question 2)
    Let's assume that your structure is like:
    tmake: id, make
    tmodel: id, modelname, tmake_id
    You can:
    select make, count(*)
    from tmake, tmodel
    where tmake.id = tmodel.tmake_id
    group by make
    And there you go, you have all count(*) with one select. Another option so that you can avoid group by functions would be to add a field to tmake (eg total_models) that would be updated whenever a model is added to your db.
     
    mentalic, Feb 22, 2009 IP
  6. NFreak

    NFreak Peon

    Messages:
    38
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #6
    No way will 52 commands harm your server. I probably have over 200 queries on some pages of my site, and it still loads quickly. In fact, I have a cron job which runs every 30 minutes on my site doing over 2,000 queries in a second or two.
     
    NFreak, Feb 22, 2009 IP