problem with renewing variables during a loop?

[emrys404]

Hello. Okay here the deal. I have been creating a forum engine (for fun) from scratch and have run into a problem. I'll tell you now that i'm new to php and mysql and have been using this as a learning excercise. The problem is the code below produces this output:

Bob
Registered: June 03
Location: Earth
Posts: 1

Ralph
Registered:
Location:
Posts:

The loop only runs twice in this example but the problem is obvious. Only the first time through the loop do the details get printed. I have an inkling the problem is with reusing the variables and as such i have tried to mysql_free() and unset() them (as shown by the commented out code) but to no avail.

<?php

$link = mysql_connect("localhost", "**", "**") or die("Could not connect to database");
 mysql_select_db("ma", $link) or die("Could not select database");


 $idt = $_SERVER['QUERY_STRING'];

 $resultposts = mysql_query("SELECT * FROM posts WHERE thread_id = '$idt'") or die (mysql_error());

 $i = 0; 
 $num = mysql_num_rows($resultposts);

 while($i < $num)
 {

printf("%s\n", mysql_result($resultposts,$i, "author_name"));

$userid = mysql_result($resultposts,$i, "author_id"); 
$resultdetails = mysql_query("SELECT * FROM members WHERE id = '$userid'") or die (mysql_error());
$member = mysql_fetch_row($resultdetails);

?><br>	

Registered: 	<?php echo $member[5]; ?><br>
Location: 	<?php echo $member[8]; ?><br>
Posts: 		<?php echo $member[7]; ?><br><br>



<?php
	/*
         mysql_free_result($resultdetails);
        unset($member);
	unset($userid);
	*/

 $i++;
 }// end while

 mysql_free_result($resultposts);
 mysql_close();


?>

Thanks for all help in advance
-Emrys

[Mordecai]

I suggest using:

while ($member = mysql_fetch_array($resultposts,MYSQL_NUM)) {
  // user data
}

This will work better because it forces PHP to reuse the same variable (user specified, $member in this example).

[emrys404]

Hmm, i can see how that would force the reusability of the member variables, however i can only get it to print out the first entry when i use it. Can you explain this further, offer another solution or provide a more indepth example?

Sorry
-emrys

[ayzee]

Correct me if I'm wrong but it seems that you are searching for all member posts then dragging data out of another table relating to that member?

If this is true then there is a much more efficient way of doing this.

Just send a dump of the structure of both tables and i'll see if I can help.

[toddmarx]

just a stab in the dark here but maybe try changing:

$idt = $_SERVER['QUERY_STRING'];

to...

$id = $HTTP_GET_VARS["QUERY_STRING"];

Like I said, just a stab in the dark.

[ereptur]

I always use this type of loop when iterating through MySQL results.

$query = "SELECT userid,author,registered,location FROM posts WHERE thread_id = '$idt'";
$result = mysql_query($query) or die(mysql_error());
while(list($userid,$author,$registered,$location) = mysql_fetch_array($result)) {
   $posts_query = "SELECT COUNT(*) FROM posts WHERE userid='$userid'";
   $posts_result = mysql_query($query) or die(mysql_error());
   list($num_posts) = mysql_fetch_array($result);
   echo <<<END
   <br />
   $author<br />
   Registered: $registered<br />
   Location: $location<br />
   Posts: $num_posts<br /><br />
   END;
   mysql_free_result($posts_result);
   unset($posts_query);
   unset($posts_result);
}

[emrys404]

ayzee> What is happening there is the initial loop is pulling info (posts) out of the posts table, and inside that loop,
the code is finding the userid of the poster and pulling info about the poster out of the members database.
Dump for the posts and members tables:

CREATE TABLE posts (
pid bigint(20) NOT NULL auto_increment,
author_id int(10) default NULL,
author_name varchar(32) default NULL,
post_date int(10) default NULL,
post text,
thread_id bigint(20) default NULL,
PRIMARY KEY (pid)
) TYPE=MyISAM;

CREATE TABLE members (
id int(10) NOT NULL auto_increment,
username varchar(32) default NULL,
password varchar(32) default NULL,
mgroup tinyint(2) default NULL,
email varchar(60) default NULL,
joined varchar(15) default NULL,
avatar varchar(128) default NULL,
num_posts mediumint(7) default NULL,
location varchar(128) default NULL,
signature text,
title varchar(64) default NULL,
new_pass varchar(32) default NULL,
PRIMARY KEY (id),
UNIQUE KEY index_name (username)
) TYPE=MyISAM;

There is also another table called threads that gets used.

[ereptur]

ok then, here goes:

$posts_query = "SELECT author_id,author_name FROM posts";
$posts_result = mysql_query($posts_query) or die(mysql_error());
if(mysql_num_rows($posts_result) > 0) {
   while(list($author_id,$author_name) = mysql_fetch_array($posts_result)) {
      $user_query = "SELECT username,joined,location FROM members WHERE userid='$author_id'";
      $user_result = mysql_query($user_query) or die(mysql_error());
      list($username,$joined,$location) = mysql_fetch_array($user_result);
      $user_posts_query = "SELECT COUNT(*) FROM posts WHERE author_id='$author_id'";
      $user_posts_result = mysql_query($user_posts_query) or die(mysql_error());
      list($num_posts) = mysql_fetch_array($user_post_result);
      echo <<<END
      <br />
      $username<br />
      Registered: $joined<br />
      Location: $location<br />
      Posts: $num_posts<br />
      <br />
      END;
   }
}

[emrys404]

ugh, okay i guess i'm just stupid but i can follow this logically but i cant get it to work:

<?php
$link = mysql_connect("localhost", "emrys", "yeomen") or die("Could not connect to database");
 mysql_select_db("ma", $link) or die("Could not select database");


 $idt = $_SERVER['QUERY_STRING'];


$posts_query = "SELECT author_id,author_name FROM posts WHERE thread_id = '$idt'";
$posts_result = mysql_query($posts_query) or die(mysql_error());
if(mysql_num_rows($posts_result) > 0) {
   while(list($author_id,$author_name) = mysql_fetch_array($posts_result)) {
      $user_query = "SELECT username,joined,location FROM members WHERE id='$author_id'";
      $user_result = mysql_query($user_query) or die(mysql_error());
      list($username,$joined,$location) = mysql_fetch_array($user_result);
      $user_posts_query = "SELECT COUNT(*) FROM posts WHERE author_id='$author_id'";
      $user_posts_result = mysql_query($user_posts_query) or die(mysql_error());
      list($num_posts) = mysql_fetch_array($user_post_result);
      ?>
      <br />
      <?php $username ?><br />
      Registered: <?php $joined ?> <br />
      Location: <?php $location ?><br />
      Posts: <?php $num_posts ?> <br />
      <br />
      <?php
   }
}

 mysql_close();
?>

I had to change the echo <<<END END; because it wouldnt work and kept giving me an error. um, now it says

list($num_posts) = mysql_fetch_array($user_post_result);

is saying supplied argument is not valid....

Thanks for all the help.

[ereptur]

Sorry, change the $user_post_result to read $user_posts_result

[emrys404]

ahhaha! i'm dumb. the problem with my original way of doing it was on the thread reply page i wasnt assigning author_id to the reply post.. hahah.
Anyway thank you guys. I learned alot of more efficient ways of doing things from this thread. Thanks alot for all your help.

-emrys

[ayzee]

No problems emrys.

I was about to suggest that you use a JOIN within your SQL to further reduce load on your mySQL server.

If for example you had 50 members who had posted, at the moment you would be making 51 seperate calls to the database on each page view.

Whilst such a small number wouldn't really matter, I'm sure you can imagine what a nightmare it would be if that users number ever reached the hundreds or thousands mark?

Using a JOIN in this case would cut your database load to just one query (at initial page load).

Hope this helps,

Cheers.

[emrys404]

Hum, i can see how having 50+ people browsing, posting etc. at one would will mess with my current design. As i said i'm am pretty much a novice to sql/php. I did some reading on JOIN and found it will join tables together, but how will using it only require one query at page load?

Thanks alot.

-emrys

-ps. about how many querys can mysql handle before it starts to crash or slow significantly?

[ayzee]

Hi emrys,

I've attached a php file which "hopefully" should work first time
and provide you with an effective example of using JOINS.

I've just wrote it, haven't tested it and it may contain bugs that I've missed.

Hope this helps, please let me know if this is what you were after.

As for database size and connections, honestly I'm not entirely
sure. However I do know that from the benchmarks on
mySQL.com it is very fast.

Cheers.

[ayzee]

Sheesh!

Just read my last posting and have realised that I am still very much hungover.

A 10 year old english student could have done better :p

Cheers.

[emrys404]

Great! Thank you, that was exactly what i needed. I am going to do some redesigning of some other pages with that method in mind. Again thank you very much.

-emrys

[ayzee]

You're more than welcome emrys.

Glad to be of help.

If you need any more advice feel free to contact me.

Cheers.