Combining 2 mysql expressions to fit into one while statements

junior84

Hi Guys

I need some help with mysql/php if anyone has the time..

This is my code, it gets the records from the database and loops untill they are all printed down the page.

			$data = mysql_query("SELECT * FROM buyers_bookmarks WHERE clientsid = '$userID1'")
				or die(mysql_error());
				$info = mysql_fetch_array( $data ); 

			$bookmarkP = $info[propertyid];

			$query="select * from listed_properties WHERE propertyID = 'devon'";  // query string stored in a variable
			$rt=mysql_query($query);          // query executed 
			echo mysql_error();              // if any error is there that will be printed to the screen

			while($nt=mysql_fetch_array($rt)){

			echo "<a href='property_page.php?pid=$nt[propertyID]'><h3>$nt[titleOfListing]</h3>";
			echo "<table>";
			echo "<tr><td>";
			echo "<img src='images/$nt[eventPicture].jpg' height='100px'><br>";
			echo "</td><td valign='top'>";

I need to get $bookmarkP into the while statement here while($nt=mysql_fetch_array($rt)){

a link to a decent document would be amazing 🙂

junior84

I fixed it myself

$data = mysql_query("SELECT * FROM buyers_bookmarks WHERE clientsid = '$userID1'")
				or die(mysql_error());

			while($info = mysql_fetch_array($data )){

			$bookmarkP = $info[propertyid];

			$query="select * from listed_properties WHERE propertyID = '$bookmarkP'";  // query string stored in a variable
			$rt=mysql_query($query);          // query executed 
			echo mysql_error();        
			// if any error is there that will be printed to the screen

			while($nt=mysql_fetch_array($rt)){

			echo "<a href='property_page.php?pid=$nt[propertyID]'><h3>$nt[titleOfListing]</h3>";
			echo "<table>";
			echo "<tr><td>";
			echo "<img src='images/$nt[eventPicture].jpg' height='100px'><br>";
			echo "</td><td valign='top'>";
			echo"$nt[propertyType]<br>$nt[price]<br>$nt[secondline]<br>$nt[bedrooms]<br>$nt[bathrooms]<br></td></tr></table> More Info</a><br><br> ";

			     // name class and mark will be printed with one line break at the end
				}
				}

laserlight

You can actually accomplish this by running just one query, e.g.,

$sql = sprintf("SELECT listed_properties.*
    FROM buyers_bookmarks, listed_properties
    WHERE buyers_bookmarks.propertyid=listed_properties.propertyID
        AND buyers_bookmarks.clientsid=%d
    ORDER BY listed_properties.propertyID",
    $userID1);
$result = mysql_query($sql);

while ($row = mysql_fetch_assoc($result)) {
    echo "<a href='property_page.php?pid=$row[propertyID]'><h3>$row[titleOfListing]</h3>";
    echo "<table>";
    echo "<tr><td>";
    echo "<img src='images/$row[eventPicture].jpg' height='100px'><br>";
    echo "</td><td valign='top'>";
    echo "$row[propertyType]<br>$row[price]<br>$row[secondline]<br>$row[bedrooms]<br>$row[bathrooms]<br></td></tr></table> More Info</a><br><br> ";

// name class and mark will be printed with one line break at the end
}

Remember to use htmlspecialchars() or htmlentities() when printing those variables if it is undesirable that they contain HTML code or clientside scripting.