second query isn't run.. can you help?

turkish · Mar 29, 2008

here is my code..

<?
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select your chosen database, try again soon");

 $bowtypeQuery = 'SELECT bowtype from scores;';
    $bowtypeResult = mysql_query($bowtypeQuery) or die('currently under development, could not do the first query, try again soon');
	while( $bowtype_row = mysql_fetch_array( $bowtypeResult ) ) {
		$bowtype = $bowtype_row['bowtype'];
		print "<b>Bowtype: ".$bowtype."</b><br />";

 $genderQuery = "SELECT gender FROM scores WHERE bowtype=$bowtype;";
   $genderResult = mysql_query($genderQuery) or die('currently under development, could not do the second query, try again soon');;
	while($gender_Row = mysql_fetch_array( $genderResult )) {
		$gender = $gender_Row['gender'];
		print  "<i>Gender: ".$gender."</i><br />";
        }

$query="
SELECT count(*) as pld, name, sum(score) as ttlscore, sum(maximum),round(sum(score)/sum(maximum),3) handicap, sum(hits) as ttlhits, sum(golds) as ttlgolds, sum(inners), sum(petticoats), sum(points) as ttlpoints 
FROM scores 
WHERE bowtype = $bowtype AND gender = $gender 
GROUP BY name 
ORDER BY ttlpoints desc, ttlscore desc, ttlhits desc, ttlgolds desc, inners desc, petticoats desc;";

$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();
?>
<center>
<table border="1" cellspacing="2" cellpadding="2" class="league">
<th width="200"><font face="Verdana">Name</font></th>
<th><font face="Verdana">pld</font></th>
<th><font face="Verdana">Score</font></th>
<th><font face="Verdana">Max</font></th>
<th><font face="Verdana">H/C</font></th>
<th><font face="Verdana">Hits</font></th>
<th><font face="Verdana">G</font></th>
<th><font face="Verdana">I</font></th>
<th><font face="Verdana">P</font></th>
<th><font face="Verdana">Pts</font></th>
</tr>
<!--------------------------------------->
<?
$i=0;

while ($i < $num) {
$bowtype=mysql_result($result,$i,"bowtype");
$gender=mysql_result($result,$i,"gender");
$pld=mysql_result($result,$i,"pld");
$name=mysql_result($result,$i,"name");
$score=mysql_result($result,$i,"ttlscore");
$maximum=mysql_result($result,$i,"sum(maximum)");
$handicap=mysql_result($result,$i,"handicap");
$hits=mysql_result($result,$i,"ttlhits");
$golds=mysql_result($result,$i,"ttlgolds");
$inners=mysql_result($result,$i,"sum(inners)");
$petticoats=mysql_result($result,$i,"sum(petticoats)");
$points=mysql_result($result,$i,"ttlpoints");

?>
<!--------------------------------------->

	<tr>
	<td width="200"><font face="Verdana"><a href=modules.php?name=Archer&uname=<? echo $name;?>><? echo $name; ?></a></font></td>
	<td><font face="Verdana"><? echo $pld; ?></font></td>
	<td><font face="Verdana"><? echo $score; ?></font></td>
	<td><font face="Verdana"><? echo $maximum; ?></font></td>
	<td><font face="Verdana"><? echo $handicap; ?></font></td>
	<td><font face="Verdana"><? echo $hits; ?></font></td>
	<td><font face="Verdana"><? echo $golds; ?></font></td>
	<td><font face="Verdana"><? echo $inners; ?></font></td>
	<td><font face="Verdana"><? echo $petticoats; ?></font></td>
	<td><font face="Verdana"><? echo $points; ?></font></td>
	</tr>
<?

$i++;
} 
  } 
echo "</table></center></p>";
?>
?>

hopefully you can help... many thanks

my output is:

currently under development, could not do the second query, try again soon

so it fails the second query.. the first query works fine...

dougal85 · Mar 30, 2008

change your or die to display the mysql_error.

mysql_error http://uk3.php.net/mysql_error

turkish · Mar 30, 2008

<?
$bowtypeQuery = 'SELECT bowtype from scores;';
    $bowtypeResult = mysql_query($bowtypeQuery)   or die('Query 1 failed. ' . mysql_error());
	while( $bowtype_row = mysql_fetch_array( $bowtypeResult ) ) $bowtype = $bowtype_row['bowtype'];print "<b>Bowtype: ".$bowtype."</b><br />";

 $genderQuery = "SELECT gender FROM scores WHERE bowtype=$bowtype;";
   $genderResult = mysql_query($genderQuery)  or die('Query 2 failed. ' . mysql_error());
	while($gender_Row = mysql_fetch_array( $genderResult )) $gender = $gender_Row['gender']; print  "<i>Gender: ".$gender."</i><br />";
?>

i get the error:

Query 2 failed. Unknown column 'LB' in 'where clause'

where LB is one of the entries in the bowtype field.. which is what i would expect?!

any ideas?

i think it's not taking the value of bowtype from the first query into the second query right?!

thanks

dagon · Mar 30, 2008

WHERE bowtype='$bowtype'

turkish · Mar 30, 2008

Excellent.. such a simple thing eh!

thank you very much..

my next problem is... it is only showing the first bowtype and gender.. and not showing all the data...

<?
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select your chosen database, try again soon");

 $bowtypeQuery = 'SELECT DISTINCT bowtype from scores;';
    $bowtypeResult = mysql_query($bowtypeQuery)   or die('Query 1 failed. ' . mysql_error());
	while( $bowtype_row = mysql_fetch_array( $bowtypeResult ) ) $bowtype = $bowtype_row['bowtype'];print "<b>Bowtype: ".$bowtype."</b><br />";


 $genderQuery = "SELECT DISTINCT gender FROM scores WHERE bowtype='$bowtype';";
   $genderResult = mysql_query($genderQuery)  or die('Query 2 failed. ' . mysql_error());
	while($gender_Row = mysql_fetch_array( $genderResult )) $gender = $gender_Row['gender']; print  "<i>Gender: ".$gender."</i><br />";

$query="SELECT count(*) as pld, name, sum(score) as ttlscore, sum(maximum),round(sum(score)/sum(maximum),3) handicap, sum(hits) as ttlhits, sum(golds) as ttlgolds, sum(inners), sum(petticoats), sum(points) as ttlpoints FROM scores WHERE gender='$gender' AND bowtype='$bowtype' GROUP BY name ORDER BY ttlpoints desc, ttlscore desc, ttlhits desc, ttlgolds desc, inners desc, petticoats desc;";


$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();
?>
<center>
<table border="1" cellspacing="2" cellpadding="2" class="league">
<th width="200"><font face="Verdana">Name</font></th>
<th><font face="Verdana">pld</font></th>
<th><font face="Verdana">Score</font></th>
<th><font face="Verdana">Max</font></th>
<th><font face="Verdana">H/C</font></th>
<th><font face="Verdana">Hits</font></th>
<th><font face="Verdana">Golds</font></th>
<th><font face="Verdana">Inners</font></th>
<th><font face="Verdana">P</font></th>
<th><font face="Verdana">Ttl Pts</font></th>
</tr>
<?
$i=0;
while ($i < $num) {
$bowtype=mysql_result($result,$i,"bowtype");
$gender=mysql_result($result,$i,"gender");
$pld=mysql_result($result,$i,"pld");
$name=mysql_result($result,$i,"name");
$score=mysql_result($result,$i,"ttlscore");
$maximum=mysql_result($result,$i,"sum(maximum)");
$handicap=mysql_result($result,$i,"handicap");
$hits=mysql_result($result,$i,"ttlhits");
$golds=mysql_result($result,$i,"ttlgolds");
$inners=mysql_result($result,$i,"sum(inners)");
$petticoats=mysql_result($result,$i,"sum(petticoats)");
$points=mysql_result($result,$i,"ttlpoints");
?>
		<tr>
		<td width="200"><font face="Verdana"><a href=modules.php?name=Archer&uname=<? echo $name;?>><? echo $name; ?></a></font></td>
		<td><font face="Verdana"><? echo $pld; ?></font></td>
		<td><font face="Verdana"><? echo $score; ?></font></td>
		<td><font face="Verdana"><? echo $maximum; ?></font></td>
		<td><font face="Verdana"><? echo $handicap; ?></font></td>
		<td><font face="Verdana"><? echo $hits; ?></font></td>
		<td><font face="Verdana"><? echo $golds; ?></font></td>
		<td><font face="Verdana"><? echo $inners; ?></font></td>
		<td><font face="Verdana"><? echo $petticoats; ?></font></td>
		<td><font face="Verdana"><? echo $points; ?></font></td>
		</tr>
<?
$i++;
} 
echo "</table></center></p>";
?>

turkish · Mar 30, 2008

thanks

turkish · Mar 31, 2008

i guess i need to put the whole thing in a loop? can anyone point me in the right direction?

thanks

second query isn't run.. can you help?

Tturkish

Ddougal85

Tturkish

Ddagon

Tturkish

Tturkish

Tturkish