SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    Non-Member
    Join Date
    Oct 2008
    Posts
    372
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    total rows added

    Hey,

    I'm running a query and its returning the correct amount of rows, but what I need it to do is add all the rows and display it.

    Code MySQL:
    SELECT COUNT(check_id), check_id, check_fromid, check_message, check_date
                       FROM checks
                       WHERE check_toid = :userID
                       AND check_fromid != :userID
                       AND check_status = "Y"
                       GROUP BY check_id
                       ORDER BY check_date DESC

    Currently there are 2 rows that match this COUNT.

    PHP Code:
    echo $row['COUNT(check_id)']; 
    Output is one. It should be two.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    please dump the table -- generate the CREATE TABLE statement, and include the INSERT statements for the rows that you think should have been counted

    that way we can test it ourselves to see what might be happenin'...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Non-Member
    Join Date
    Oct 2008
    Posts
    372
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I can't dump the table, it has information I need.

    What I did though was the following:

    Code MySQL:
    SELECT COUNT(check_id)
                       FROM checks
                       WHERE check_toid = :userID
                       AND check_fromid != :userID
                       AND check_status = "Y"
                       ORDER BY check_date DESC

    It outputs correctly: 2. If I add the other columns it messes up. I think it might be due to the GROUP BY clause.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes

    think about it -- SELECT COUNT(*) returns a single value, as long as there are no other columns in the SELECT clause

    furthermore, ORDER BY doesn't make sense for a 1-row result, does it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Non-Member
    Join Date
    Oct 2008
    Posts
    372
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How would I approach using the SELECT for all the columns I need and the COUNT within one query? If possible.

  6. #6
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,510
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Select the rows, and use mysql_num_rows() to get the number of rows selected

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i'm not sure, because i don't really know what you're attempting to do

    let me ask you a question to get the discussion started -- under what circumstances do you want to get a count of some thingies (in this case, check_id), at the same time as listing the individual thingies?

    if you're gonna list them, can't you do the count in your application logic? something like mysql_num_rows in php, or something...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    aaargh, sniped again

    nice one, guido
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    Non-Member
    Join Date
    Oct 2008
    Posts
    372
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    haha

    i got it. thanks all.


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
  •