barney0o0 Posted February 7, 2018 Share Posted February 7, 2018 (edited) 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 Edited February 7, 2018 by requinix fixing messed up bbcode Quote Link to comment Share on other sites More sharing options...
Barand Posted February 7, 2018 Share Posted 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 1 Quote Link to comment Share on other sites More sharing options...
barney0o0 Posted February 7, 2018 Author Share Posted 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted February 7, 2018 Share Posted 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>'; } 1 Quote Link to comment Share on other sites More sharing options...
barney0o0 Posted February 8, 2018 Author Share Posted 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 Quote Link to comment Share on other sites More sharing options...
barney0o0 Posted February 8, 2018 Author Share Posted 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 } ?> Quote Link to comment Share on other sites More sharing options...
Barand Posted February 8, 2018 Share Posted 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> Quote Link to comment Share on other sites More sharing options...
barney0o0 Posted February 8, 2018 Author Share Posted 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted February 8, 2018 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted February 8, 2018 Share Posted February 8, 2018 (edited) ^^^ 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> Edited February 8, 2018 by mac_gyver Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.