SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Addict
    Join Date
    Apr 2002
    Location
    Miami
    Posts
    214
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Optimize this query

    I want to do this with one query if possible


    PHP Code:
    <?php
        $result1 
    = @mysql_query("SELECT COUNT(*) AS data FROM unitop WHERE deptid= '1'");
        
    $result2 = @mysql_query("SELECT COUNT(*) AS ff FROM unitop WHERE deptid= '2'");
        
    $result3 = @mysql_query("SELECT COUNT(*) AS bc FROM unitop WHERE deptid= '3'");
        
        
    $row1 mysql_fetch_array($result1);
        
    $count1$row1["data"];
        
        
    $row2 mysql_fetch_array($result2);
        
    $count2$row2["ff"];
        
        
    $row3 mysql_fetch_array($result3);
        
    $count3$row3["bc"];    

    ?>

  2. #2
    SitePoint Evangelist compwizard's Avatar
    Join Date
    May 2002
    Location
    United States
    Posts
    457
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My sql knowledge is a little rusty, but I believe you can do something like this:

    SELECT COUNT(*) FROM unitop GROUP BY deptid
    Compwizard
    "There are 10 kinds of people in this world -- those who know binary, and those who don't."

  3. #3
    SitePoint Addict
    Join Date
    Apr 2002
    Location
    Miami
    Posts
    214
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks,

    Once you have this result...how do you break it up into the individual counts??

  4. #4
    SitePoint Addict
    Join Date
    Apr 2002
    Location
    Miami
    Posts
    214
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Here is what I am trying to do

    First check out this link. It contains a php generated form.
    http://www.mantaproductions.com/arro.../testtable.php

    The fields are arrays but I am doing something wrong. If you fill out rows consecutively they all print out but if you skip a row and fill out further down then that comes up empty.

    I know I am doing something wrong with my loop but I can not figure it out. Here is the code I am using to print out
    PHP Code:
    <?php
        $uid
    $_POST['uo'];
        
    $svol$_POST['savol'];
        
    $scom$_POST['sacom'];
        
    $ucst$_POST['unitcst'];
        
    $ovol$_POST['opvol'];
        
    $ocom$_POST['opcom'];
        
        
    $num_rowscount($uid);
        
        for(
    $i 0$i $num_rows; ++$i) {
        echo(
    "<tr><td>$uid[$i]$svol[$i]$scom[$i]$ucst[$i]$ovol[$i]$ocom[$i]</td></tr>") ;
        }
    ?>

  5. #5
    SitePoint Evangelist compwizard's Avatar
    Join Date
    May 2002
    Location
    United States
    Posts
    457
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SELECT deptid, COUNT(*) AS count FROM unitop GROUP BY deptid

    This will return a table like this:
    deptid count
    ------ -----
    1 10
    2 13
    3 25
    4 9


    so to access it, use a loop like this:
    PHP Code:
    $result mysql_query("SELECT deptid, COUNT(*) AS count FROM unitop GROUP BY deptid" );

    print 
    "<table><tr><td>Dept. ID </td><td> Count </td> </tr>";
    while (
    $row mysql_fetch_array($resultMYSQL_ASSOC))
    {
    print 
    "<tr>";
    print 
    "<td>" $row["deptid"] . " </td> <td> " $row["count"] . "</td>";
    print 
    "</tr>";
    }
    print 
    "</table>"
    Compwizard
    "There are 10 kinds of people in this world -- those who know binary, and those who don't."

  6. #6
    SitePoint Addict
    Join Date
    Apr 2002
    Location
    Miami
    Posts
    214
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for that but if I wanted to refer to them as variables to use in a further calculation not echo them out.

    What would be the name of the variable?
    deptid1
    deptid2
    I have been trying this and my page comes up blank?

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    it may not be as efficient as the GROUP BY solution, but you can also get the counts you want in one row like this (and i doubt you'll notice the difference in performance time):

    select
    sum( case when deptid= '1' then 1 else 0 end ) as data
    , sum( case when deptid= '2' then 1 else 0 end ) as ff
    , sum( case when deptid= '3' then 1 else 0 end ) as bc
    from unitop
    where deptid in ('1','2','3')

    rudy
    http://rudy.ca/

  8. #8
    SitePoint Addict
    Join Date
    Apr 2002
    Location
    Miami
    Posts
    214
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, this is really getting too complicated. I couldn not even find the definition for case in my mysql book.

    I got it working using the group approach and it will have to do for now until I get more experience. Otherwise I will go insane before I finish this one stupid form

  9. #9
    SitePoint Evangelist compwizard's Avatar
    Join Date
    May 2002
    Location
    United States
    Posts
    457
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry I didn't get back to you sooner...as long as you know the order in which the departments are in the db, you can do it like this:

    PHP Code:

    $result 
    mysql_query("SELECT deptid, COUNT(*) AS count FROM unitop GROUP BY deptid" );
    $row mysql_fetch_array($resultMYSQL_ASSOC);
    $count1$row["count"];
    $row mysql_fetch_array($resultMYSQL_ASSOC);
    $count2$row["count"];
    $row mysql_fetch_array($resultMYSQL_ASSOC);
    $count3=$row["count"]; 
    Also, if you need to, tack on an ORDER BY statment at the end of the query to ensure the correct ordering
    ie, ORDER BY deptid ASC
    Compwizard
    "There are 10 kinds of people in this world -- those who know binary, and those who don't."

  10. #10
    SitePoint Addict
    Join Date
    Apr 2002
    Location
    Miami
    Posts
    214
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks compwizard.

    Yes I do know the order. I got it to work using that approach... but a little different

    while ($row[] = mysql_fetch_array($result, MYSQL_ASSOC)) {
    $counta= $row[0]["count"];
    $countb= $row[1]["count"];
    $countc= $row[2]["count"];
    $countd= $row[3]["count"];
    }

    doesn't look like it should work after looking at your code but it seems to be working so on to the next big problem...inserting multiple arrays into mysql arrrrghhh.

  11. #11
    SitePoint Evangelist compwizard's Avatar
    Join Date
    May 2002
    Location
    United States
    Posts
    457
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well as long as you got it to work, that is great
    Compwizard
    "There are 10 kinds of people in this world -- those who know binary, and those who don't."

  12. #12
    SitePoint Zealot
    Join Date
    May 2003
    Location
    Dover, PA
    Posts
    135
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by compwizard
    My sql knowledge is a little rusty, but I believe you can do something like this:

    SELECT COUNT(*) FROM unitop GROUP BY deptid
    You're close. This will work, telling you the deptid and number of records for each:

    Code:
    SELECT deptid, COUNT(*) FROM unitop GROUP BY deptid
    http://www.statgfx.com

  13. #13
    SitePoint Addict
    Join Date
    Apr 2002
    Location
    Miami
    Posts
    214
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes that is the way it ended up being. Thanks


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
  •