Two queries conflicting

Stu

Hi everyone.

I am in the process of creating my own forum with a mate but am stuck on trying to get two queries to work together. One code queries the database to get the ID, name and order of a category and the other queries the database to get the last post date and who made it. I managed to get the first query to work alone just fine but when introducing the second query, it just does not work. Depending on where in the script I insert the second query, errors are either generated or the second query just doesn't work.

Hope that was explained enough so you understand what is happening.

Here is the code without the second query in:

<html>

<head>
<title>Happy House Forum - Home</title>
<style type="text/css">
.tester {display: show; border-width: 0px;}
BODY {font-family: Verdana,Arial;}
A:link { text-decoration: none; color: 000099;}
A:visited { text-decoration: none; color: 000099;}
A:hover { text-decoration: underline; color: 000099;}
</style>

</head>

<body link="#000099" vlink="#000099" alink="#000099" bgcolor="#6699FF">

<?

  require("./headers.htm");
  require("./database.php");

  mysql_connect($dbhost, $dbusername, $dbpassword);
  mysql_select_db($database);

  echo "<center>";
  echo '<table cellpadding="1" cellspacing="1" border="1" width="92%" style="border-style: outset;">';
  echo "<tr>";
  echo '<td class="titlebg" bgcolor="333366" colspan="2" width="73%"><font size="2" color="FFFFFF"><b>Board name</b></font></td>';
  echo '<td class="titlebg" bgcolor="333366" align="center" width="1%"><font size="2" color="FFFFFF"><b>Topics</b></font></td>';
  echo '<td class="titlebg" bgcolor="333366" align="center" width="1%"><font size="2" color="FFFFFF"><b>Posts</b></font></td>';
  echo '<td class="titlebg" bgcolor="333366" align="center" width="24%"><font size="2" color="FFFFFF"><b>Last Post</b></font></td>';
  echo "</tr>";

  $how_many_cats = "SELECT count(*) FROM categories";
  $result_how_many = mysql_query($how_many_cats);

  if($result_how_many == 0)
  {
     echo "No categories found!";
  }
  elseif($result_how_many == 1)
  {
  $query = "SELECT id, name, catOrder FROM categories WHERE catOrder=1";
  $result = mysql_query($query);

  while ($query_data = mysql_fetch_row($result))
  {

  $catname = $query_data[1];

  echo "<tr>";
  echo "<td colspan=\"5\" class=\"catbg\" bgcolor=\"666699\"><font size=\"2\" color=\"FFFFFF\"><b>$catname<b></font></td>";
  echo "</tr>";

  }

  $query = "SELECT id, name, description, category, moderators, boardOrder, numTopics, numPosts FROM boards WHERE category=1";
  $result = mysql_query($query);

  while ($query_data = mysql_fetch_row($result))
  {

  $id = $query_data[0];
  $name = $query_data[1];
  $description = $query_data[2];
  $moderators = $query_data[4];
  $boardOrder = $query_data[5];
  $numTopics = $query_data[6];
  $numPosts = $query_data[7];

  echo "<tr>";
  echo '<td class="windowbg" bgcolor="FFFFFF" width="7%" align="center">';
  echo "<font size=\"1\">New/old post icon</font>";
  echo "</td>";
  echo '<td class="windowbg2" bgcolor="FFFFFF" align="left" width="66%">';
  echo "<font size=\"2\"><a href=viewboard.php?id=$id><b>$name</b></a><br><font size=\"1\">$description<br></font><i>Moderators: $moderators</font></i>";
  echo "</td>";
  echo "<td class=\"windowbg\" bgcolor=\"FFFFFF\" valign=\"middle\" align=\"center\" width=\"1%\"><font size=\"2\">$numTopics</font></td>";
  echo "<td class=\"windowbg\" bgcolor=\"FFFFFF\" valign=\"middle\" align=\"center\" width=\"1%\"><font size=\"2\">$numPosts</font></td>";
  echo "<td class=\"windowbg2\" bgcolor=\"FFFFFF\" valign=\"middle\" width=\"24%\"><font size=\"2\">$last_post_date<br>by $last_post_by</font></td>";
  echo "</tr>";

  }
  }
  elseif($result_how_many > 1)
  {    

  $id = 1;

  $query = "SELECT id, name, catOrder FROM categories WHERE catOrder=$id";
  $result = mysql_query($query);

  while ($query_data = mysql_fetch_row($result))
  {

  $catname = $query_data[1];

  echo "<tr>";
  echo "<td colspan=\"5\" class=\"catbg\" bgcolor=\"666699\"><font size=\"2\" color=\"FFFFFF\"><b>$catname<b></font></td>";
  echo "</tr>";

  }

  $query = "SELECT id, name, description, category, moderators, boardOrder, numTopics, numPosts FROM boards WHERE category=$id";
  $result = mysql_query($query);

  while ($query_data = mysql_fetch_row($result))
  {

  $id = $query_data[0];
  $name = $query_data[1];
  $description = $query_data[2];
  $moderators = $query_data[4];
  $boardOrder = $query_data[5];
  $numTopics = $query_data[6];
  $numPosts = $query_data[7];

  echo "<tr>";
  echo '<td class="windowbg" bgcolor="FFFFFF" width="7%" align="center">';
  echo "<font size=\"1\">New/old post icon</font>";
  echo "</td>";
  echo '<td class="windowbg2" bgcolor="FFFFFF" align="left" width="66%">';
  echo "<font size=\"2\"><a href=\"viewboard.php?id=$id\"><b>$name</b></a></font><br><font size=\"1\">$description<br><i>Moderators: $moderators</font></i>";
  echo "</td>";
  echo "<td class=\"windowbg\" bgcolor=\"FFFFFF\" valign=\"middle\" align=\"center\" width=\"1%\"><font size=\"2\">$numTopics</font></td>";
  echo "<td class=\"windowbg\" bgcolor=\"FFFFFF\" valign=\"middle\" align=\"center\" width=\"1%\"><font size=\"2\">$numPosts</font></td>";
  echo "<td class=\"windowbg2\" bgcolor=\"FFFFFF\" valign=\"middle\" width=\"24%\"><font size=\"2\">$last_post_date<br>by $last_post_by</font></td>";
  echo "</tr>";

  }

  $id = $id++;

  $query = "SELECT id, name, catOrder FROM categories WHERE catOrder=$id";
  $result = mysql_query($query);

  while ($query_data = mysql_fetch_row($result))
  {

  $catname = $query_data[1];

  echo "<tr>";
  echo "<td colspan=\"5\" class=\"catbg\" bgcolor=\"666699\"><font size=\"2\" color=\"FFFFFF\"><b>$catname<b></font></td>";
  echo "</tr>";

  }

  $query = "SELECT id, name, description, category, moderators, boardOrder, numTopics, numPosts FROM boards WHERE category=$id";
  $result = mysql_query($query);

  while ($query_data = mysql_fetch_row($result))
  {

  $id = $query_data[0];
  $name = $query_data[1];
  $description = $query_data[2];
  $moderators = $query_data[4];
  $boardOrder = $query_data[5];
  $numTopics = $query_data[6];
  $numPosts = $query_data[7];

  echo "<tr>";
  echo '<td class="windowbg" bgcolor="FFFFFF" width="7%" align="center">';
  echo "<font size=\"1\">New/old post icon</font>";
  echo "</td>";
  echo '<td class="windowbg2" bgcolor="FFFFFF" align="left" width="66%">';
  echo "<font size=\"2\"><a href=\"viewboard.php?id=$id\"><b>$name</b></a></font><br><font size=\"1\">$description<br><i>Moderators: $moderators</font></i>";
  echo "</td>";
  echo "<td class=\"windowbg\" bgcolor=\"FFFFFF\" valign=\"middle\" align=\"center\" width=\"1%\"><font size=\"2\">$numTopics</font></td>";
  echo "<td class=\"windowbg\" bgcolor=\"FFFFFF\" valign=\"middle\" align=\"center\" width=\"1%\"><font size=\"2\">$numPosts</font></td>";
  echo "<td class=\"windowbg2\" bgcolor=\"FFFFFF\" valign=\"middle\" width=\"24%\"><font size=\"2\">$last_post_date<br>by $last_post_by</font></td>";
  echo "</tr>";

  }

}


  echo "</table>";
  echo "</td>";
  echo "</tr>";
  echo "</table>";

  ?>
<br><br>

<div align="center">
      <center>
      <table width="92%" border="1" style="border-style: outset;" cellspacing="1" cellpadding="1">
        <tr>
        <td width="100%" align="center" bgcolor="333366" colspan="2"><font size="2" color="FFFFFF"><b>Forum Statistics</b></font></td>
        </tr>
        <tr>
        <td width="100%" bgcolor="666699" colspan="2"><font size="2" color="FFFFFF">
        <b>Members</b></font></td>
        </tr>
        <tr>
	    <td width="7%" bgcolor="FFFFFF" align="center">
        <img src="images/info.gif" width="20" height="20"></td>
        <td width="93%" bgcolor="FFFFFF"><font size="1">The Happy House members have made X 
        threads and X posts since START DATE.<br>The newest member is NEWEST 
        MEMBER and there are a total of TOTAL MEMBERS.</font</td>
        </tr>
        <tr>
	    <td width="100%" bgcolor="666699" colspan="2">
        <font size="2" color="FFFFFF"><b>Who's online</b></font></td>
        </tr>
        <tr>
	    <td width="7%" bgcolor="FFFFFF" align="center">
        <img src="images/online.gif" width="20" height="20"></td>
        <td width="93%" bgcolor="FFFFFF"><font size="1">There are X people on Happy House:<br>
        X Members and X Guests<br>
        List of members</font</td>
        </tr>
        </table>
        </center>
    </div>

</body>

</html>

That code can be seen working here: http://www.2312.co.uk/hh/forum/

The second query I am trying to insert is below:

$query = "SELECT date_format(date_posted, '%b %D, %Y, %l:%i%p'), posted_by FROM posts WHERE board='$id' ORDER BY date_posted DESC LIMIT 0, 1";
$result = mysql_query($query) or die(mysql_error());
$last_post_date = $query_data[0];
$last_post_by = $query_data[1];

Please advise as to how to get it to work. Thanks 🙂

stolzyboy

simply name your $query, $result, $row's with

$query1
$result1
$row1

$query2
$result2
$row2

$query3
$result3
$row3

etc.......

Stu

Originally posted by stolzyboy
simply name your $query, $result, $row's with

$query1
$result1
$row1

$query2
$result2
$row2

$query3
$result3
$row3

etc.......

If I was to do that to my "second" query and name it $query2 then how would $query_data work? I tried this method before and tried $query2_data and $query_data2 but both to no avail. Was I barking up the wrong tree?

Weedpacket

Well these two lines are wrong

$how_many_cats = "SELECT count(*) FROM categories";
      $result_how_many = mysql_query($how_many_cats);

$result_how_many won't be how many results there are, it will be a result set (containing one row with one field containing the value you're really after).

$how_many_cats_query = "SELECT count(*) FROM categories";
      $how_many_cats = mysql_query($how_many_cats_query); 
$result_how_many = mysql_result($how_many_cats,0,0);

Stu

Originally posted by Weedpacket
Well these two lines are wrong
$how_many_cats = "SELECT count(*) FROM categories";
      $result_how_many = mysql_query($how_many_cats); 
$result_how_many won't be how many results there are, it will be a result set (containing one row with one field containing the value you're really after).
$how_many_cats_query = "SELECT count(*) FROM categories";
      $how_many_cats = mysql_query($how_many_cats_query); 
$result_how_many = mysql_result($how_many_cats,0,0);
[/B]

Thanks Weedpacket. I shall change that 🙂 Do you have any idea as to my problem of getting the last post displayed?

Thanks
Stu