Retrieve content from database - nested query problem

PHP
1

Hi all,

I’m retrieving some records from a MySQL database using a query (a while loop) in another query. Everything works fine except that the while loop prints out the same row twice, instead of two separate rows (I’ve only got 2 rows in that particular table at the moment for testing purposes).

Could there be some conflict between the first

$row = mysqli_fetch_array($r, MYSQLI_ASSOC); {

and the nested

while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) ?

For my second query, I’ve changed some variables in my actual code so that they don’t conflict ($row, $num etc).

The following code is just some sample code I created for demonstration purposes, so my query code isn’t accurate but the focus is on how the second query (with while loop) is nested.

Could someone please have a look at this?

Thank you in advance.



<?php

$q = "SELECT name, address, books_read,  FROM table1 INNER JOIN table2 WHERE some_id = 5";		

$r = @mysqli_query ($dbc, $q); 	
$num = mysqli_num_rows($r);
if ($num > 0) {
$row = mysqli_fetch_array($r, MYSQLI_ASSOC); {

Print '<div>
<p class="name">'.$row['name'] . ' </p>
<p class="address">'.$row['address'].'</p>
</div>';


$q = "SELECT books_read FROM table1 INNER JOIN table2 WHERE some_id = 5";		

$r = @mysqli_query ($dbc, $q); 	
$num = mysqli_num_rows($r);
if ($num > 0) {
while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {

Print '<div><p class="books">'.$row['books_read'] . ' </p></div>';
}
}

Print '<div>
<p>other content</p>
</div>';

 }
}
?>
2

You’re using $r and $row in the second process so therefore you will lose your first process’ results. No?

3

Hi,

Nope, I’ve used different variables in my actual code.

4

Since I was guessing at what your code looked like, I took my best guess. If you want more help you’ll have to show us the relevant part that is mis-behaving.

5

Can I assume that “books_read” from table2 probably has two records? Let’s do a little test. Comment out that second query.

<?php

$q = "SELECT name, address, books_read,  FROM table1 INNER JOIN table2 WHERE some_id = 5";

$r = @mysqli_query ($dbc, $q);
$num = mysqli_num_rows($r);
if ($num > 0) {
$row = mysqli_fetch_array($r, MYSQLI_ASSOC); {

Print '<div>
<p class="name">'.$row['name'] . ' </p>
<p class="address">'.$row['address'].'</p>
</div>';

/*
$q = "SELECT books_read FROM table1 INNER JOIN table2 WHERE some_id = 5";

$r = @mysqli_query ($dbc, $q);
$num = mysqli_num_rows($r);
if ($num > 0) {
while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {

Print '<div><p class="books">'.$row['books_read'] . ' </p></div>';
}
}
*/
Print '<div>
<p>other content</p>
</div>';

 }
}
?>

Does the name loop twice?

And what about if you did two separate queries for table1 and table2.

<?php

$q = "SELECT name, address FROM table1 WHERE some_id = 5";

$r = @mysqli_query ($dbc, $q);
$num = mysqli_num_rows($r);
if ($num > 0) {
$row = mysqli_fetch_array($r, MYSQLI_ASSOC); {

Print '<div>
<p class="name">'.$row['name'] . ' </p>
<p class="address">'.$row['address'].'</p>
</div>';


$q = "SELECT books_read FROM table2 WHERE some_id = 5";

$r = @mysqli_query ($dbc, $q);
$num = mysqli_num_rows($r);
if ($num > 0) {
while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {

Print '<div><p class="books">'.$row['books_read'] . ' </p></div>';
}
}

Print '<div>
<p>other content</p>
</div>';

 }
}
?>

Assuming some_id is a unique identifier in both tables.

Then you can simply do one query and check if heading (name etc) has been displayed and only show it once.

<?php

$q = "SELECT name, address FROM table1 INNER JOIN table2 WHERE some_id = 5";

$r = @mysqli_query ($dbc, $q);
$num = mysqli_num_rows($r);
if ($num > 0) {
	$heading = 0;
	while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
		if($heading == 0){
			Print '<div>
			<p class="name">'.$row['name'] . ' </p>
			<p class="address">'.$row['address'].'</p>
			</div>';
		}
	 	$heading++;
		Print '<div><p class="books">'.$row['books_read'] . ' </p></div>';
	}
	Print '<div>
	<p>other content</p>
	</div>';
}
?>

Rough draft anyway.

6

Hi Drummin and others,

thank you for your help. Everything is working fine. For my second query I did change the $row variable in

$row = mysqli_fetch_array($r, MYSQLI_ASSOC); {

but somehow forgot to change it in

<p class="address">'.$row['address'].'</p>

Thanks

Ciao