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>';
}
}
?>
You’re using $r and $row in the second process so therefore you will lose your first process’ results. No?
Hi,
Nope, I’ve used different variables in my actual code.
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.
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.
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