Loop within Loop - PDO

barney0o0 · February 7, 2018

Hello...another post in as many days

I wish to show all records in a query. 'Within' this loop i need to echo out data from another query that is dependent on the the first query..........so

<?php 
$mm = $pdo->query("SELECT n_cat1.cat1id, n_cat1.c1_tit_it FROM n_cat1 ORDER BY c1_tit_it ASC");
$mm->execute();

while ( $m = $mm->fetchl(PDO::FETCH_ASSOC)) { 
$sublistid = $m['cat1id'];
		 
	  ?>


<html>
<title><?php echo $m['c1_tit_it']; ?></title>
<subtitle><?php echo $s['c2_tit_it']; ?></subtitle>
</html>



<?php   }
 
?>

For the second query (that would need to populate the <subtitle> (could be 2 - 10 records)) i would have this:

$ss = $pdo->query("SELECT * FROM n_cat2 WHERE cat1link ='$sublistid' ORDER BY c2_tit_it ASC");
$ss->execute();
while ( $s = $ss->fetch(PDO::FETCH_ASSOC)) {

How would i do this?....before in mysql it was fine to loop within loop, however PDO is a little different and my knowledge is limited

To put it in context, the 'n_cat1' is the main menu, whereas the 'n_cat2' is the submenu/s under each main menu,

Thanks in advance...hopefully i can leave you all in peace for a while after this

Barand · February 7, 2018

First,

Don't run queries within loops, it's inefficient. Instead, use a JOIN to get all your data in a single query.

Don't use "SELECT * ". Specify the columns you need.

Don't put variable directly into query strings. Use prepared statements.


SELECT n1.cat1id
     , n1.c1_tit_it
     , n2.whateverA
     , n2.whateverB
FROM n_cat1 n1
     JOIN n_cat2 n2 ON n1.cat1id = n2.cat1link
ORDER BY c1.tit_it, c2_tit_it

barney0o0 · February 7, 2018

Hi Barand, i don't believe a join would work for what i need...unless im misinterpreting your suggestion

The output structure would be something like this:

$m['c1_tit_it'];  (with cat1id of 1)

$s['c2_tit_it'];  (with sublistid of 1)

$s['c2_tit_it'];  (with sublistid of 1)

$s['c2_tit_it'];  (with sublistid of 1)

$s['c2_tit_it'];  (with sublistid of 1)



$m['c1_tit_it'];  (with cat1id of 2)

$s['c2_tit_it'];  (with sublistid of 2)

$s['c2_tit_it'];  (with sublistid of 2)



$m['c1_tit_it'];  (with cat1id of 3)

$s['c2_tit_it'];  (with sublistid of 3)

$s['c2_tit_it'];  (with sublistid of 3)

$s['c2_tit_it'];  (with sublistid of 3)

etc etc

I get your point about select*...its a 3 column table and im being lazy

Re. prepared statements - yes, i always do..its just for this example

thanks

B

Barand · February 7, 2018

Just check for changes in the c1_tit_value.

$sql = "SELECT 
       n1.cat1id
     , n1.c1_tit_it
     , n2.c2_tit_it
     FROM n_cat1 n1
        JOIN n_cat2 n2 ON n1.cat1id = n2.cat1link
     ORDER BY c1.tit_it, c2_tit_it";
$res = $pdo->query($sql);

$current = '';
foreach ($res as $row) {
    if ($row['c1_tit_it'] != $current) {
        echo $row['c1_tit_it'] . '<br>';         // when value changes, output new value
        $current = $row['c1_tit_it'];            // reset the current value
    }
    echo ' - ' . $row['c2_tit_it'] . '<br>';
}

barney0o0 · February 8, 2018

Thanks Barand..it works like a dream (i didn't think it would )...nice approach that i'd definitely use in the future

Many thanks again

barney0o0 · February 8, 2018

Oops spoke too soon I need a tweak on how the record its echo'd

The structure of the menu within the repeat would be:

<li class="has-dropdown">
<a href="#">'.$c1.'</a>

<ul>

<li><a href="#">'.$c2.'</a></li> // repeat all C2 records
<li><a href="#">'.$c2.'</a></li> // etc etc


</ul>


</li> // closed li 'has-dropdown', repeat all loop depending in C1 no. of records

However the closing ul li is repeating with the no. of records from C2, rather than that of C1...what i have so far is:

<?php 
$sql ="SELECT 
       n1.cat1id
     , n1.c1_tit_it
     , n2.c2_tit_it
     FROM n_cat1 n1
        JOIN n_cat2 n2 ON n1.cat1id = n2.cat1link
     ORDER BY c1_tit_it, c2_tit_it";
$res = $pdo->query($sql);

	  
	  $current = '';
foreach ($res as $row) {
	
	$c1 = str_replace(" ", "-", $row['c1_tit_it']);
	$c2 = str_replace(" ", "-", $row['c2_tit_it']);
    if ($row['c1_tit_it'] != $current) {
		
		
		
		    echo '<li class="has-dropdown">
			<a href="#">'.$c1.'</a>
			<ul>';

        $current = $row['c1_tit_it'];  
		
		
		

    }
    echo '<li><a href="#">'.$c2.'</a></li>';
	
	
	?> </ul> </li><?php
				
								
}
	  ?>

Barand · February 8, 2018

There is a repeating pattern there

start <ul>

while (items)

    if change of c1
        if there was a previous list
            close previous </ul>
            close previous </li>
        endif

        output new <li><a ... >c1</a> 
        open new <ul>
    endif

    output <li><a...>c2</a></li>

endwhile

close last </ul> and </li>

close final </ul>

barney0o0 · February 8, 2018

Sorry, i dont understand..i think you need to spell it out more literally to me


Start repeat C1
<li>

<ul>
Start repeat C2
<li></li>
End repeat C2
</ul>

<li>
End repeat C1

would you have a mo to tweak what i sent before...my head hurts

Barand · February 8, 2018

All you have to do is translate the pseudocode I gave you to php code, line by line. At least give it a try and come back if you get stuck. Most of the php has been provided already.

In future (especially if you are expecting code) I recommend you tell us what you want from the outset, not a simplified version then, following the solution, the actual problem. It's annoying and time wasting for us to go through the process twice (and makes my head hurt). The requirement in your reply #6 is quite different from that in reply #3.

mac_gyver · February 8, 2018

^^^ that's called sneaking up on a problem, and programming is a sneaky person detector.

because the requirements changed, a clearer solution for this would be to pre-process the data from the query, storing it as an array of arrays, using the cat1id as the main array index. to produce the output from this array would then just take two nested foreach() loops. this would also eliminate repeating the code to close the final </ul> and </li> section.

the pseudo code to produce the output would be -

start <ul>

foreach(items as sub_items)
{
    output new <li><a ... >c1</a>
    open new <ul>

    foreach(sub_items as item)
    {
        output <li><a...>c2</a></li>
    }

    close </ul> and </li>
}

close final </ul>

Sign In

Loop within Loop - PDO

Recommended Posts

barney0o0

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

barney0o0

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

barney0o0

Link to comment

Share on other sites

barney0o0

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

barney0o0

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

mac_gyver

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information