SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    May 2006
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Soooo sloooow...

    Is there a more elegant solution to this without using two selects?

    I'm listing car dealers and the number of vehicles they have in stock for a pull-down menu - unfortunately the selects take ages as for each dealer in the while loop - there has to be a sub-select to find out how many cars there are in the database.

    Help!

    (Quite new to PHP and MySQL)


    <?php
    $sql=mysql_query("SELECT id, name FROM directory WHERE category='Dealer' ORDER BY name");
    while ($dealer=mysql_fetch_array($sql)) {
    $deal=$dealer['id'];
    $numofcars=mysql_query("SELECT COUNT(id) AS number FROM cars WHERE dealerid=$deal");
    $numberofcars=mysql_fetch_array($numofcars);
    echo '<option value="'.$dealer['id'].'">'.$dealer['name'].' ('.$numberofcars['number'].')</option>';
    }
    ?>

  2. #2
    SitePoint Wizard HarryR's Avatar
    Join Date
    Dec 2004
    Location
    London, UK
    Posts
    1,376
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You could simplify this by just doing a join, for example:

    Code:
    SELECT d.id, d.name, COUNT(c.id) AS car_count
      FROM directory d
      LEFT JOIN cars c ON (c.dealerid = d.id)
      WHERE d.category = 'Dealer' 
      ORDER BY d.name;

  3. #3
    SitePoint Evangelist ClickHeRe's Avatar
    Join Date
    Mar 2005
    Location
    Ottawa, Canada
    Posts
    580
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you need some learning in MySQL query my friend =]

    The query goes along something like this (not tested):

    Code:
    SELECT t1.id, t1.name, count(*) AS num FROM directory AS t1, cars AS t2 WHERE t1.category='Dealer' AND t1.id=t2.dealerid GROUP BY t1.id ORDER BY t1.name;
    David

  4. #4
    SitePoint Member
    Join Date
    May 2006
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ClickHeRe View Post
    you need some learning in MySQL query my friend =]

    The query goes along something like this (not tested):

    Code:
    SELECT t1.id, t1.name, count(*) AS num FROM directory AS t1, cars AS t2 WHERE t1.category='Dealer' AND t1.id=t2.dealerid GROUP BY t1.id ORDER BY t1.name;

    Wow. No need to test it ... I've just tried it out and it works like a dream!

    Not like my clunky interpretation. I guess I should read more on joins ...

    Many thanks for your help.

  5. #5
    SitePoint Evangelist ClickHeRe's Avatar
    Join Date
    Mar 2005
    Location
    Ottawa, Canada
    Posts
    580
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    no problem, now you know this exists =]
    David


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •