Nested query issue

[1andyw]

Hi,

I am attempting to report a list of each vol with a sum of hours for each vol with the count of number of vols and total of hours under for each volsource heading, along with a grand total of all vols and a grand total of sum of hours. This is to be filtered by month.

My query is returning "Unknown column 'MHA' in 'where clause'" for $sql2.

Would appreciate an assist.

Thanks,

Andy

Query:
$sql="SELECT DISTINCT `volrecords`.`volsource`AS volsource FROM volrecords INNER JOIN hoursworked ON volrecords.id=hoursworked.vol_id WHERE `hoursworked`.`date`>='2009-03-01' AND `hoursworked`.`date` <= '2009-03-31'";
if(!$sql){echo "Unable to select" . mysql_error();}
$rs = mysql_query($sql,$conn);
if(!$rs){echo "No query" . mysql_error();}
$row=mysql_fetch_array($rs);
for($i = 0; $i < count($row); $i++){
echo "<h2> " . $row['volsource'] . "</h2>";
$sql2 = "SELECT `volrecords`.`firstname`,`volrecords`.`lastname`,SUM(`hoursworked`.`hours`)AS hours,`volrecords`.`volsource` FROM volrecords, hoursworked WHERE volrecords.id=hoursworked.vol_id AND volrecords.volsource LIKE {$row['volsource']} GROUP BY `volrecords`.`id`";
echo '<table><tr><th>Name</th><th>Hours</th></tr>';
$result=mysql_query($sql2) or die ("Query Error Please . . . " . mysql_error());
while($rows=mysql_fetch_array($result)){
echo "<tr><td>" . $rows['lastname'] . ", " . $rows['firstname'] . "</td><td>" . $rows['hours'] . "</td><td>" . $rows['volsource'] . "</td></tr>";
}
}

createtable:

$sql ="CREATE TABLE `hoursworked` (
`id` smallint(6) NOT NULL auto_increment PRIMARY KEY,
`vol_id` smallint(6) NOT NULL default '',
`date` date NOT NULL ,
`hours` decimal(5,2) NOT NULL default '',
`areaofinterest` varchar(50) NOT NULL default '',
`peers` smallint(6) NOT NULL default '',
`memo` varchar(100) NOT NULL default 'na')";

createtable:

$sql = "CREATE TABLE `volrecords` (`id` SMALLINT(6) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`firstname` VARCHAR(50) NOT NULL DEFAULT '',
`lastname` VARCHAR(50) NOT NULL DEFAULT '',
`volsource` VARCHAR(50) NOT NULL DEFAULT ''
)";

[shyam]

shouldn't your RHS be inside quotes?

$sql2 = "SELECT `volrecords`.`firstname`,`volrecords`.`lastname`,SUM(`hoursworked`.`hours`)AS hours,`volrecords`.`volsource` FROM volrecords, hoursworked WHERE volrecords.id=hoursworked.vol_id AND volrecords.volsource LIKE {$row['volsource']} GROUP BY `volrecords`.`id`";

[1andyw]

RESOLVED

Yes, you are correct. That does it.

Thank you,

Andy