[RESOLVED] easier/cleaner way todo this?

scrupul0us

			<?php
				require ("$incdir/dbconnect.php");
				$query =  'SELECT * FROM comments WHERE pid=1';
				$result = mysql_query($query, $connection);

			//If we have a result
			if (mysql_num_rows($result) > 0)
			{
					while ($row = mysql_fetch_assoc($result))
					{
					$query2 =  'SELECT * FROM users WHERE uid="'.$row["uid"].'"';
					$result2 = mysql_query($query2, $connection);
					$row2 = mysql_fetch_assoc($result2);  

					$mydate = date("m/d/Y @ H:i", strtotime($row['cdate']));


					?>
					<fieldset><legend><b><?=$mydate?> By: <?=$row2["ufname"]." ".$row2["ulname"]?></b></legend>
					<?=$row["cnote"]?><br/>
					<?php
					}						
					mysql_free_result($result);	
					mysql_free_result($result2);							
			}
			//DB IS DOWN
			else
			{
			echo "error";
			}

			//Clean up memory
			mysql_close($connection);
			mysql_free_result($result);
			mysql_free_result($result2);
		?>

seems like having 2 queries,results, rows is a bit much... i have the uid in the comments table "internally related" to the uid in my users table if that makes any diff

Roger_Ramjet

Use a join

$query =  'SELECT * FROM comments INNER JOIN users USING(uid) WHERE pid=1';

scrupul0us

and as a result i'd be able to use:

$row["uid"] and $row["cdate"]; (variables from both tables)

within the same $row variable?

Roger_Ramjet

Exactly. With the * you will get every column from all the joined tables, cos you can join up to 64 tables I believe. You can also list the columns by name if you only want some of them, and yopu can use columns from both tables in your WHERE clause.

scrupul0us

sweet!... ive known "about" the different joins, but never had a use so I never relly learned them... thanks for the enlightenment!

~Brian