How to have multiple queries in the same page
Hi all i . I got a php page that i want to run multiple queries in it but one of my query does not work. The first query is working fine but not the second one. I use <? echo $result2; ?> to output the second query result but i keep getting Resource id #4 instead of number of all albums in db!!
Could any one help me fix this problem.Thanks
PHP Code:
$query = "select artist,count(distinct album),count(album)from musictest group by artist";
$result = mysql_query($query);
$query = "select count(distinct album) as albums from musictest";
$result2 = mysql_query($query);
$numbers=0;
while($row = mysql_fetch_assoc($result))
{
$numbers++; // counting number of records
?>
<tr>
<td align="middle"><? echo $numbers; ?></td>
<td><a
href="albums.php?albumname=<?=strval( $row['artist'] );?>"><font
size="2"
face="Arial, Helvetica, sans-serif"><b><?=strval( $row['artist'] );?></b></font></a><font size="2"
face="Arial, Helvetica, sans-serif"><b> </b></font></td>
<td align="middle"><?=strval( $row['count(distinct album)'] );?></td>
<td align="middle"><?=strval( $row['count(album)'] );?></td>
</tr>
<?
}
?>
<tr>
<td align="middle"> </td>
<td align="right"><b>Totals Albums</b></td>
[B] <td align="middle"><b><? echo $result2; ?></b></td> [/B]=>problem here <td align="middle"> </td>
</tr>
</TBODY> </table>
Re: How to have multiple queries in the same page
I can see you've created your second query but you don't actually execute (fetch) it. You should add something like the below. HTH :)
PHP Code:
//eg
$MyResults2 = mysql_fetch_assoc($result2))
//then....
<td align="middle"><b><? echo $MyResult2; ?></b></td>
Re: How to have multiple queries in the same page
Quote:
Originally Posted by lintz
I can see you've created your second query but you don't actually execute (fetch) it. You should add something like the below. HTH :)
PHP Code:
//eg
$MyResults2 = mysql_fetch_assoc($result2))
//then....
<td align="middle"><b><? echo $MyResult2; ?></b></td>
no output!!
Re: How to have multiple queries in the same page
PHP Code:
<td align="middle"><b><? echo $MyResult2; ?></b></td>
is ment to be
PHP Code:
<td align="middle"><b><? echo $MyResults2; ?></b></td>
you misspelt the variable name, which is why there was no output.
Re: How to have multiple queries in the same page
Isn't simple debugging a wonderful skill to have. You should try it sometime. Along with formatting your code properly.
Re: How to have multiple queries in the same page
You can't just echo a query result. The query result returned by mysql_query() is a pointer to the result set. You need to use a function such as mysql_fetch_* to get a single row.
There are two things you should be doing as a matter of habit when writing your code.
- As penagate mentioned, you need to format your code properly. It is good to see that you have started embedding the PHP inside HTML ;) :thumb:. But you also need to ensure that you are indenting your code.
This is important because it helps whoever is reading it understand the logic and flow you have employed. When using PHP inside HTML, you can use the alternate (almost VB like) tmeplating syntax with control-flow structures and loops. Again, this helps those who read your code. An example of how you might use this:
PHP Code:
?>
<table>
<tbody>
<?php while(($row = mysql_fetch_assoc($result))): ?>
<tr>
<td align="middle"><? echo $numbers; ?></td>
<td>
<a href="albums.php?albumname=<?php echo(strval( $row['artist'] ));?>">
<font size="2" face="Arial, Helvetica, sans-serif">
<b><?php echo(strval( $row['artist'] ));?></b></font>
</a>
</td>
<td align="middle"><?php echo(strval( $row['count(distinct album)']));?></td>
<td align="middle"><?php echo(strval( $row['count(album)']));?></td>
</tr>
<?php endwhile; ?>
<tr>
<td align="middle"> </td>
<td align="right"><b>Totals Albums</b></td>
<td align="middle"><b><? echo ($result_var) ?></b></td>
<td align="middle"> </td>
</tr>
</tbody>
</table>
Also, notice how all the PHP is enclosed in <?php ?> tags and not <?= ?>. This makes your code more portable as, if in future your host upgrades to a new version of PHP, they could decide to turn off the ability to use short tags.
It is of utmost importance that your code is readable, especially as you are posting it in a forum where you expect others to help you. The more readable your code the better the responses you will get.
- The second thing you need to be doing is error checking your queries. This is especially important while developing. If a query fails, you will not know until you come to use mysql_fetch_* where you will get an error saying that the variable is an invalid result resource. Error checking your queries is simple:
Code:
$query = "select artist,count(distinct album) d_album_count,count(album) album_count from musictest group by artist";
if (! ($result = mysql_query($query))) {
echo(mysql_error());
exit;
}
$query = "select count(distinct album) album_count as albums from musictest";
if (! ($result2 = mysql_query($query))) {
echo(mysql_error());
exit;
}
Now, if you have made a mistake in the query syntax, your script will print out the error and exit. Queries should never fail, so you should always exit.
Notice also how I have used column aliases, highlighted in bold. When you give the column an alias name you cna use it to refer to it in the result returned by mysql_fetch_assoc:
Code:
<td align="middle"><?php echo(strval( $row['count(distinct album)']));?></td>
// Becomes
<td align="middle"><?=strval( $row['d_album_count'] );?></td>
- Last but not least is error reporting; as demonstrated by your error above where you stated there was no output, error_reporting is not set to its maximum level. When developing, you should have error reporting set to its maximum level. This will among other things cause PHP to spit out a notice if you attempt to read a variable which you have not assigned a value to before. This will highlight possible spelling mistakes and save you a lot of time. To set error reporting to its maximum level insert the following line at the top of your script:
PHP Code:
error_reporting(E_ALL);
Re: How to have multiple queries in the same page
Many thanks to all and specially to visual ad . I am using regual note pad so indenting is hard i install notpad++ but i do not know how to move a block of code to the right!!could any one tell me how that can be then. I tried this and now my code is working. Visualad could u tell me if this type of solution is good practice or not? I find it less confusing mixing up queries.
PHP Code:
$query = "select artist,count(distinct album),count(album)from musictest group by artist";
$result = mysql_query($query);
//this if for error checking queries
if (! ($result = mysql_query($query))) {
echo(mysql_error());
exit;
}
$query2 = "select count(distinct album) from mastanamusictest";
$result2 = mysql_query($query2);
//this if for error checking queries
if (! ($result2 = mysql_query($query2))) {
echo(mysql_error());
exit;
}
while($row = mysql_fetch_assoc($result))
{
$numbers++; // counting number of records
?>
<tr>
<td align="middle"><? echo $numbers; ?></td>
<td><a
href="albums.php?albumname=<?=strval( $row['artist'] );?>"><font
size="2"
face="Arial, Helvetica, sans-serif"><b><?=strval( $row['artist'] );?></b></font></a><font size="2"
face="Arial, Helvetica, sans-serif"><b> </b></font></td>
<td align="middle"><?=strval( $row['count(distinct album)'] );?></td>
<td align="middle"><?=strval( $row['count(album)'] );?></td>
</tr>
<?
} //end of first while
while($row2 = mysql_fetch_assoc($result2))
{
?>
<tr>
<td align="middle"> </td>
<td align="right"><b>Totals Albums</b></td>
<td align="middle"><b><?=strval( $row2['count(distinct album)'] )?></b></td>
<td align="middle"> </td>
</tr>
<?
} // second while
?>
Re: How to have multiple queries in the same page
Select the lines and press Tab to indent or Shift+Tab to unindent.
Re: How to have multiple queries in the same page
Quote:
Originally Posted by tony007
Many thanks to all and specially to visual ad . I am using regual note pad so indenting is hard i install notpad++ but i do not know how to move a block of code to the right!!could any one tell me how that can be then. I tried this and now my code is working. Visualad could u tell me if this type of solution is good practice or not? I find it less confusing mixing up queries.
PHP Code:
$query = "select artist,count(distinct album),count(album)from musictest group by artist";
$result = mysql_query($query);
//this if for error checking queries
if (! ($result = mysql_query($query))) {
echo(mysql_error());
exit;
}
$query2 = "select count(distinct album) from mastanamusictest";
$result2 = mysql_query($query2);
//this if for error checking queries
if (! ($result2 = mysql_query($query2))) {
echo(mysql_error());
exit;
}
while($row = mysql_fetch_assoc($result))
{
$numbers++; // counting number of records
?>
<tr>
<td align="middle"><? echo $numbers; ?></td>
<td><a
href="albums.php?albumname=<?=strval( $row['artist'] );?>"><font
size="2"
face="Arial, Helvetica, sans-serif"><b><?=strval( $row['artist'] );?></b></font></a><font size="2"
face="Arial, Helvetica, sans-serif"><b> </b></font></td>
<td align="middle"><?=strval( $row['count(distinct album)'] );?></td>
<td align="middle"><?=strval( $row['count(album)'] );?></td>
</tr>
<?
} //end of first while
while($row2 = mysql_fetch_assoc($result2))
{
?>
<tr>
<td align="middle"> </td>
<td align="right"><b>Totals Albums</b></td>
<td align="middle"><b><?=strval( $row2['count(distinct album)'] )?></b></td>
<td align="middle"> </td>
</tr>
<?
} // second while
?>
http://www.vbforums.com/showpost.php...74&postcount=6
