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:

$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.

$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!

See the GROUP BY clause in the mySQL manual.

to have a second query which produces counts from the results of another query, just put the other query as a subquery –

SELECT q.hlname
     , COUNT(*) AS times
  FROM ( [COLOR="Blue"]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-%'[/COLOR]
       ) AS q
GROUP
    BY q.hlname

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?

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?

from my last query, you would print out the values of hlname and times

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!

SELECT q.hlname
     , COUNT(*) AS times
     , NULL
     , NULL
     , ...
  FROM ...

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

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!

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?

allow me to help you

this –

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 –

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

:slight_smile:

Thanks a lot!

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:

   $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)";

I managed to make a little more progress on this.
I am receiving the mysql error:

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

SELECT q.hlname
     , COUNT(*) AS times

for

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.