SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Addict
    Join Date
    Jun 2005
    Posts
    269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Learning php - how to query another set of results?

    Hi guys,

    I am practicing some PHP on a gaming script and my knowledge is limited but I'd like to think I am learning quite quickly.

    I've managed to join 2 database tables in a query and display the results with a wildcard "LIKE" statement.
    This seems to work absolutely fine (to my amazement).

    What I would like to do is now COUNT the results which I produced in my first query.

    1st query:
    Code PHP:
    $result1 = mysql_query("SELECT badc_mis_prog.red_tgt, badc_mis_prog.misnum, badc_pilot_mis.misnum, badc_pilot_mis.hlname ".
     "FROM badc_mis_prog, badc_pilot_mis ".
    	"WHERE badc_mis_prog.misnum = badc_pilot_mis.misnum AND badc_mis_prog.red_tgt LIKE 'SUM-%'");

    This produces a table like this:
    Number | Results with the word "SUM" | Member name

    How do I produce results which counts the number of times a member name appears in the above results?

    e.g

    Number | Results with the word "SUM" | Member name
    1 SUM 1 John
    2 SUM 2 Paul
    3 SUM 3 Harry
    4 SUM 4 Harry
    5 SUM 5 Paul
    6 SUM 6 Paul

    The results I want to make would show:
    John = 1
    Paul = 3
    Harry = 2

    I came up with a query like this, but it reads from the whole database again and not from my $results1 query.

    Code PHP:
    $result2 = mysql_query("SELECT badc_mis_prog.red_tgt, COUNT(badc_pilot_mis.hlname) FROM badc_pilot_mis, badc_mis_prog WHERE red_tgt LIKE 'SUM-%' GROUP BY badc_mis_prog.red_tgt");

    Sorry this probably sounds really newbie, I just need to get the $results2 to query the $results1 query without reading the whole database again.

    Any pointers would be great!

  2. #2
    SitePoint Addict MrBaseball34's Avatar
    Join Date
    Sep 2002
    Location
    Round Rock, Texas
    Posts
    277
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    See the GROUP BY clause in the mySQL manual.
    MrBaseball34
    Hook'Em Horns!

  3. #3
    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)
    to have a second query which produces counts from the results of another query, just put the other query as a subquery --
    Code:
    SELECT q.hlname
         , COUNT(*) AS times
      FROM ( SELECT badc_mis_prog.red_tgt
                  , badc_mis_prog.misnum
                  , badc_pilot_mis.misnum
                  , badc_pilot_mis.hlname
               FROM badc_mis_prog
             INNER
               JOIN badc_pilot_mis
                 ON badc_pilot_mis.misnum = badc_mis_prog.misnum 
              WHERE badc_mis_prog.red_tgt LIKE 'SUM-%'
           ) AS q
    GROUP
        BY q.hlname
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Addict
    Join Date
    Jun 2005
    Posts
    269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah I think I understand thanks guys.
    One more quick question as these subqueries are new to me, how to I print these results on the page as I'm unsure which variable to pick up now?

  5. #5
    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)
    from my last query, you would print out the values of hlname and times
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Addict
    Join Date
    Jun 2005
    Posts
    269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you r937 I really appreciate your help.
    For some reason I am just receiving a blank page when trying to print the results for those variables you've outlined. Any ideas?

  7. #7
    SitePoint Addict
    Join Date
    Jun 2005
    Posts
    269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I managed to make a little more progress on this.
    I am receiving the mysql error:
    Code MySQL:
    Duplicate column name 'misnum'

    This is correct as both the tables:
    badc_mis_prog & badc_pilot_mis which I am trying to join have a column "misnum" - this is what I am using as the relationship column to create my results. Before introducing the subquery this seemed to work which is strange.

    I have tried exchanging
    Code PHP:
    SELECT q.hlname
         , COUNT(*) AS times

    for

    Code PHP:
    SELECT q.hlname
         , COUNT(badc_pilot_mis.hlname) AS times

    Only to receive the same result...

    Thanks for your continued help fellas, sorry if this is really newbie stuff, trying to get my head around database joining.

  8. #8
    SitePoint Addict
    Join Date
    Jun 2005
    Posts
    269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well after reading a few more different things, it would appear assigning a table alias would be my solution - got it all working. Thanks so much guys!

  9. #9
    SitePoint Addict
    Join Date
    Jun 2005
    Posts
    269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I thought perhaps that was the hard stuff out the way!
    With r937's help I got the statement to work. To put this into action I then wanted to combine the statement with an existing query within my system.

    I'm hitting my head against a brick wall trying to merge these select statements.
    I have ended up using the UNION command (not sure if this is correct or not).
    However the latest error is:

    The used SELECT statements have a different number of columns
    From what I've read people are saying add "NULL" as the missing column names, well I have no idea how many columns I am actually missing or whether this even works with the subquery which R937 helped me implement today.

    Any ideas fellas? Thank you in advance!

    Query:
    Code PHP:
       $query="(SELECT hlname
    ,missions
    ,kia_mia
    ,akills
    ,gkills
    ,ak_x_mis
    ,gk_x_mis
    ,ak_x_kia
    ,gk_x_kia
    ,friend_ak
    ,friend_gk
    ,chutes
    ,smoke
    ,lights
    ,rescues
    ,sqd_army
    ,points
    ,rank
    ,mis_steak_max
    ,a_steak_max
    ,g_steak_max
    ,experience from badc_pilot_file where in_sqd_name='$sqd' and sqd_accepted='1'$minmis_cmd order by $key $order) 
    UNION
    (SELECT  q.hlname
         , COUNT(*) AS times
      FROM ( SELECT badc_mis_prog.red_tgt
                  , badc_mis_prog.misnum
                  , badc_pilot_mis.misnum AS mis
                  , badc_pilot_mis.hlname
               FROM badc_mis_prog
             INNER
               JOIN badc_pilot_mis
                 ON badc_pilot_mis.misnum = badc_mis_prog.misnum 
              WHERE badc_mis_prog.red_tgt LIKE 'SUM-%'
           ) AS q GROUP BY q.hlname)";

  10. #10
    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)
    Quote Originally Posted by jasper1106 View Post
    ... I have no idea how many columns I am actually missing
    allow me to help you

    this --
    Code:
    SELECT hlname
    ,missions
    ,kia_mia
    ,akills
    ,gkills
    ,ak_x_mis
    ,gk_x_mis
    ,ak_x_kia
    ,gk_x_kia
    ,friend_ak
    ,friend_gk
    ,chutes
    ,smoke
    ,lights
    ,rescues
    ,sqd_army
    ,points
    ,rank
    ,mis_steak_max
    ,a_steak_max
    ,g_steak_max
    ,experience 
    FROM ...
    selects 22 columns (i counted 'em for ya)

    this query --
    Code:
    SELECT  q.hlname
         , COUNT(*) AS times
      FROM ...
    selects only 2 (i counted these for ya as well)

    so the second query is missing 20 columns in order to take part in the UNION

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

  11. #11
    SitePoint Addict
    Join Date
    Jun 2005
    Posts
    269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey R937, I greatly appreciate your additional help.
    I did count the columns yesterday and came to the same tally as yourself.
    However I just couldn't get rid of the error when adding a further 20 NULLs.

    Could you advise where in the script I would add these NULLs to ensure I have them in the right place. I was adding them after
    "AS q GROUP BY q.hlname"
    After all the joining and matching had happened, is this incorrect?

  12. #12
    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)
    in my opinion you should not be trying to UNION those two queries, because they are unrelated and access different tables, and forcing them through the UNION meatgrinder merely adds code bloat to your app

    just run them separately
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Addict
    Join Date
    Jun 2005
    Posts
    269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi R937, unfortunately I am forced to join them due to the rest of the scripting (it's pretty messy). Any tips on where to place the NULLs would be great. If things work out, I'll try to invest some time to rewrite the whole script as it's pretty poorly written to start with as you rightly pointed out meatgrinder style!

  14. #14
    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)
    Code:
    SELECT q.hlname
         , COUNT(*) AS times
         , NULL
         , NULL
         , ...
      FROM ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Addict
    Join Date
    Jun 2005
    Posts
    269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks a lot!


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
  •