SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    o_O O_o BlueFire2k5's Avatar
    Join Date
    Mar 2003
    Location
    Sioux Falls, SD
    Posts
    475
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Argh! Query problem...

    Can someone please tell me what is wrong with this query...

    PHP Code:
    $db_num_stuff mysql_query("SELECT COUNT(t.*) AS num_topics, COUNT(s.*) AS num_msg FROM t.topics,s.messeges WHERE bid="$t['id'] ."") or print mysql_error(); 
    It gives me this error...

    Code:
    You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '*) AS num_topics, COUNT(s.*) AS num_msg FROM t.topics,s.messege
    Any clues?
    Thanks.
    "Sa souvraya niende misain ye."
    - Robert Jordan, The Wheel of Time

  2. #2
    o_O O_o BlueFire2k5's Avatar
    Join Date
    Mar 2003
    Location
    Sioux Falls, SD
    Posts
    475
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Whoops, sorry about putting that in the wrong place.
    "Sa souvraya niende misain ye."
    - Robert Jordan, The Wheel of Time

  3. #3
    Mlle. Ledoyen silver trophy seanf's Avatar
    Join Date
    Jan 2001
    Location
    UK
    Posts
    7,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No problem. Can I also remind you of our signature guidelines

    Sean
    Harry Potter

    -- You lived inside my world so softly
    -- Protected only by the kindness of your nature

  4. #4
    o_O O_o BlueFire2k5's Avatar
    Join Date
    Mar 2003
    Location
    Sioux Falls, SD
    Posts
    475
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by seanf
    No problem. Can I also remind you of our signature guidelines

    Sean
    Ahh, didn't know about that. *Changed*

  5. #5
    Mlle. Ledoyen silver trophy seanf's Avatar
    Join Date
    Jan 2001
    Location
    UK
    Posts
    7,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you

    Sean
    Harry Potter

    -- You lived inside my world so softly
    -- Protected only by the kindness of your nature

  6. #6
    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)
    regarding your query --
    Code:
    SELECT COUNT(t.*) AS num_topics
       ,   COUNT(s.*) AS num_msg 
      FROM t.topics
       ,   s.messeges 
     WHERE bid=value
    this will not give you what you seem to want (counts for both tables)

    which table is bid in, and can you please explain exactly what you want?


    rudy

  7. #7
    o_O O_o BlueFire2k5's Avatar
    Join Date
    Mar 2003
    Location
    Sioux Falls, SD
    Posts
    475
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    regarding your query --
    Code:
    SELECT COUNT(t.*) AS num_topics
       ,   COUNT(s.*) AS num_msg 
      FROM t.topics
       ,   s.messeges 
     WHERE bid=value
    this will not give you what you seem to want (counts for both tables)

    which table is bid in, and can you please explain exactly what you want?


    rudy
    bid is in both tables (board id). This is a query to count total messeges and total topics in a board.

    I know I can do with this in two querys and using mysql_num_rows, but I was wondering if I could cut it down to one...

  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)
    yes, you can do it in one query, with a UNION, i.e. if you're on version 4

    otherwise, no

    well, i mean, i think there's a way, using a cross join, but you really don't want to do that, the performance will stink to high heaven...


    rudy

  9. #9
    o_O O_o BlueFire2k5's Avatar
    Join Date
    Mar 2003
    Location
    Sioux Falls, SD
    Posts
    475
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    yes, you can do it in one query, with a UNION, i.e. if you're on version 4

    otherwise, no

    well, i mean, i think there's a way, using a cross join, but you really don't want to do that, the performance will stink to high heaven...


    rudy
    In fact, we are on version 4.0.12. So if you could help me out with the UNION thing, I would be most appreciative (never used UNION before >_>).

  10. #10
    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)
    Code:
    select 'topics' as what
       ,   count(*) as howmany
    from topics
    where bid=value
    union all
    select 'messages'
       ,   count(*) 
    from messeges 
    where bid=value

  11. #11
    o_O O_o BlueFire2k5's Avatar
    Join Date
    Mar 2003
    Location
    Sioux Falls, SD
    Posts
    475
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ahh, thank you.

  12. #12
    o_O O_o BlueFire2k5's Avatar
    Join Date
    Mar 2003
    Location
    Sioux Falls, SD
    Posts
    475
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So, err, this doesn't seem to work? o_O
    PHP Code:
                    $db_num_stuff mysql_query("SELECT 'topics', COUNT(id) AS num_topics FROM topics WHERE bid="$t['id'] ." UNION ALL SELECT 'messages', COUNT(id) AS num_msg FROM messeges WHERE bid="$t['id'] ."") or print mysql_error();
                    while(
    $s=mysql_fetch_array($db_num_stuff))
                    {
                        
    $num_topics $s['num_topics'];
                        
    $num_msg $s['num_msg'];
                    } 

  13. #13
    o_O O_o BlueFire2k5's Avatar
    Join Date
    Mar 2003
    Location
    Sioux Falls, SD
    Posts
    475
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ahh, did some research on the way UNION works and figured this code out...

    PHP Code:
                    $db_num_stuff mysql_query("(SELECT 'topics', COUNT(id) AS howmany FROM topics WHERE bid="$t['id'] .") UNION ALL (SELECT 'messages', COUNT(id) FROM messeges WHERE bid="$t['id'] .")") or print mysql_error();
                    
    $i 0;
                    while(
    $s=mysql_fetch_array($db_num_stuff))
                    {
                        if(
    $i == 0)
                        {    
    $num_topics $s['howmany'];        }
                        else
                        {    
    $num_msg $s['howmany'];    }
                        
    $i++;
                    } 
    ...which seems to work fine.
    Last edited by BlueFire2k5; Apr 27, 2003 at 17:48.


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
  •