Two SELECT's in one $query.

[beaux1]

Alright, let's say I have:

$query = SELECT SUM(something)....

 

How do I put in another SELECT before it? I want to SELECT `row1`, `row2` before it starts SELECT SUM.

 

Thanks.

[Jessica]

so put them in before the SUM

 

SELECT row1, row2, SUM(x) AS y

[beaux1]

See, I think I'm doing something wrong with the SQL.

I keep getting:

Warning: mysql_result(): supplied argument is not a valid MySQL result resource in C:\wamp\www\display2.php on line 31

Warning: mysql_result(): supplied argument is not a valid MySQL result resource in C:\wamp\www\display2.php on line 32

Warning: mysql_result(): supplied argument is not a valid MySQL result resource in C:\wamp\www\display2.php on line 33

Warning: mysql_result(): supplied argument is not a valid MySQL result resource in C:\wamp\www\display2.php on line 34

Warning: mysql_result(): supplied argument is not a valid MySQL result resource in C:\wamp\www\display2.php on line 35

Warning: mysql_result(): supplied argument is not a valid MySQL result resource in C:\wamp\www\display2.php on line 36

Warning: mysql_result(): supplied argument is not a valid MySQL result resource in C:\wamp\www\display2.php on line 37

Warning: mysql_result(): supplied argument is not a valid MySQL result resource in C:\wamp\www\display2.php on line 38

Warning: mysql_result(): supplied argument is not a valid MySQL result resource in C:\wamp\www\display2.php on line 39

Warning: mysql_result(): supplied argument is not a valid MySQL result resource in C:\wamp\www\display2.php on line 40

Warning: mysql_result(): supplied argument is not a valid MySQL result resource in C:\wamp\www\display2.php on line 41
Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

 

Here's my code:

<?php
include 'config.php';
include 'opendb.php';

$query = "SELECT `number`, `first`, `section`, `district`, SUM(leathersuit + leathersuit2 + leathersuit3 + leathersuit4 + leathersuit5 + leathersuit6 + leathersuit7 +leathersuit8 + leathersuit9 + leathersuit10) AS leathersuit_count, SUM(helmet + helmet2 + helmet3 + helmet4 + helmet5 + helmet6 + helmet7 + helmet8 + helmet9 + helmet10) AS helmet_count, SUM(rainsuit + rainsuit2 + rainsuit3 + rainsuit4 + rainsuit5 + rainsuit6 + rainsuit7 + rainsuit8 + rainsuit9 + rainsuit10) 
AS rainsuit_count, SUM(polotop + polotop2 + polotop3 + polotop4 + polotop5 + polotop6 + polotop7 + polotop8 + polotop9 + polotop10) AS polotop_count, SUM(poloneck + poloneck2 +  poloneck3 + poloneck4 + poloneck5 + poloneck6 + poloneck7 + poloneck8 + poloneck9 + poloneck10) AS poloneck_count, SUM(gloves + gloves2 + gloves3 + gloves4 + gloves5 + gloves6 + gloves7 + gloves8 + gloves9 + gloves10) 
AS gloves_count, SUM(boots + boots2 + boots3 + boots4 + boots5 + boots6 + boots7 + boots8 + boots9 + boots10) AS boots_count, 
SUM(underwear + underwear2 + underwear3 + underwear4 + underwear5 + underwear6 + underwear7 + underwear8 + underwear9 + underwear10) AS underwear_count, SUM(eyewear + eyewear2 + eyewear3 + eyewear4 + eyewear5 + eyewear6 + eyewear7 + eyewear8 + eyewear9 + eyewear10) AS eyewear_count, SUM(earplugs + earplugs2 + earplugs3 + earplugs4 + earplugs5 + earplugs6 + earplugs7 + earplugs8 + earplugs9 + earplugs10) AS earplugs_count, 
SUM(belt + belt2 + belt3 + belt4 + belt5 + belt6 + belt7 + belt8 + belt9 + belt10) AS belt_count, SUM(pinlock + pinlock2 + pinlock3 + pinlock4 + pinlock5 + pinlock6 + pinlock7 + pinlock8 + pinlock9 + pinlock10) AS pinlock_count FROM `information`";
$leathersuit_count = mysql_result($result, 0, "leathersuit_count");
$helmet_count = mysql_result($result, 0, "helmet_count");
$rainsuit_count = mysql_result($result, 0, "rainsuit_count");
$polotop_count = mysql_result($result, 0, "polotop_count");
$poloneck_count = mysql_result($result, 0, "poloneck_count");
$gloves_count = mysql_result($result, 0, "gloves_count");
$underwear_count = mysql_result($result, 0, "underwear_count");
$eyewear_count = mysql_result($result, 0, "eyewear_count");
$earplugs_count = mysql_result($result, 0, "earplugs_count");
$belt_count = mysql_result($result, 0, "belt_count");
$pinlock_count = mysql_result($result, 0, "pinlock_count");
$result = mysql_query($query) or die(mysql_error()); 
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
echo "stuff echoing values will go here";
}


include 'closedb.php';
?>

[bouwob]

If your looking for a subquery it would go something like this

 

select * from table1 where table1.something in (select something from table2)

 

You could also use joins but I like the setup of subqueries.

[artacus]

You're way off. Try:

$query = "SELECT ... ";
$result = mysql_query($query) or die(mysql_error());
while($row=mysql_fetch_assoc($result)) {
   echo "pinlock count: $row[pinlock_count]";
}

[bouwob]

or better yet add

 

group by MIN(),MAX(),COUNT(),...)

[beaux1]

Alright, here's my code now:

<?php
include 'config.php';
include 'opendb.php';
$query = "SELECT `number`, `first`, `section`, `district`, SUM(leathersuit + leathersuit2 + leathersuit3 + leathersuit4 + leathersuit5 + leathersuit6 + leathersuit7 +leathersuit8 + leathersuit9 + leathersuit10) AS leathersuit_count, SUM(helmet + helmet2 + helmet3 + helmet4 + helmet5 + helmet6 + helmet7 + helmet8 + helmet9 + helmet10) AS helmet_count, SUM(rainsuit + rainsuit2 + rainsuit3 + rainsuit4 + rainsuit5 + rainsuit6 + rainsuit7 + rainsuit8 + rainsuit9 + rainsuit10) 
AS rainsuit_count, SUM(polotop + polotop2 + polotop3 + polotop4 + polotop5 + polotop6 + polotop7 + polotop8 + polotop9 + polotop10) AS polotop_count, SUM(poloneck + poloneck2 +  poloneck3 + poloneck4 + poloneck5 + poloneck6 + poloneck7 + poloneck8 + poloneck9 + poloneck10) AS poloneck_count, SUM(gloves + gloves2 + gloves3 + gloves4 + gloves5 + gloves6 + gloves7 + gloves8 + gloves9 + gloves10) 
AS gloves_count, SUM(boots + boots2 + boots3 + boots4 + boots5 + boots6 + boots7 + boots8 + boots9 + boots10) AS boots_count, 
SUM(underwear + underwear2 + underwear3 + underwear4 + underwear5 + underwear6 + underwear7 + underwear8 + underwear9 + underwear10) AS underwear_count, SUM(eyewear + eyewear2 + eyewear3 + eyewear4 + eyewear5 + eyewear6 + eyewear7 + eyewear8 + eyewear9 + eyewear10) AS eyewear_count, SUM(earplugs + earplugs2 + earplugs3 + earplugs4 + earplugs5 + earplugs6 + earplugs7 + earplugs8 + earplugs9 + earplugs10) AS earplugs_count, 
SUM(belt + belt2 + belt3 + belt4 + belt5 + belt6 + belt7 + belt8 + belt9 + belt10) AS belt_count, SUM(pinlock + pinlock2 + pinlock3 + pinlock4 + pinlock5 + pinlock6 + pinlock7 + pinlock8 + pinlock9 + pinlock10) AS pinlock_count FROM `information`";
$result = mysql_query($query) or die(mysql_error()); 
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
echo $row['pinlock_count'];
}


include 'closedb.php';
?>

 

And I get:

Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

 

What's that mean?

[bouwob]

http://dev.mysql.com/doc/refman/5.0/en/counting-rows.html