illumi Posted November 5, 2009 Share Posted November 5, 2009 Been trying to solve this for hours without result. It's probably really simple, and just some stuff i've missed. Lets say my mysql table 1 looks lite this: NUMBER HEADLINE 1 headline 1 2 headline 2 And table 2: NUMBER CONTENT 1 content 1a 1 content 1b 1 content 1c 2 content 2a 2 content 2b 2 content 2c The thing I wanna do when looping this content, is to first get in the following structure Headline 1 content 1a content 1b content 1c Headline 2 content 2a content 2b content 2c Whenever I try to do it, I get the following structure: Headline 1 content 1a headline 1 content 2b headline 1 content 2c etc... Mostly been using while loop, maybe not the right way of do it? Anyway, can someone help me with this problem? Thx Link to comment https://forums.phpfreaks.com/topic/180437-solved-mysql-php-loop/ Share on other sites More sharing options...
kickstart Posted November 5, 2009 Share Posted November 5, 2009 Hi Presume you are using a JOIN. Store the headline in each loop round, but only output it if it is different to the last stored value. All the best Keith Link to comment https://forums.phpfreaks.com/topic/180437-solved-mysql-php-loop/#findComment-951910 Share on other sites More sharing options...
mpharo Posted November 5, 2009 Share Posted November 5, 2009 $select = mysql_query("SELECT * FROM table1"); while ($sql=mysql_fetch_array($select2)) { $select2 = mysql_query("SELECT * FROM table2 WHERE NUMBER=$sql[NUMBER]"); while($sql2=mysql_fetch_array($select2)) { echo $sql[HEADLINE] . " " . $sql2[CONTENT]; } } Link to comment https://forums.phpfreaks.com/topic/180437-solved-mysql-php-loop/#findComment-951911 Share on other sites More sharing options...
illumi Posted November 5, 2009 Author Share Posted November 5, 2009 kickstart: Don't really follow you there, I'm not really that skilled in php, more detalied explanation please? mpharo: Your suggestion shows the result exactly like the way I don't want it to be displayed (i.e. same as before) Further help needed Link to comment https://forums.phpfreaks.com/topic/180437-solved-mysql-php-loop/#findComment-952152 Share on other sites More sharing options...
JJ2K Posted November 5, 2009 Share Posted November 5, 2009 Probably more complicated than it needs to be, but i'm new to PHP also so this is how i'd do it: <? $query = "SELECT * FROM table1"; $res = mysql_query($query) or die(mysql_error()); while(mysql_fetch_array($res) = $row){ $num = $row['number']; $hline = $row['headline']; $query2 = "SELECT * FROM table2 WHERE number=$num"; $res2 = mysql_query($query2) or die(mysql_error()); echo "<h1>$hline</h1>"; while(mysql_fetch_array($res2) = $row2){ echo $row2['content']."<br />"; } } ?> Link to comment https://forums.phpfreaks.com/topic/180437-solved-mysql-php-loop/#findComment-952161 Share on other sites More sharing options...
Andy-H Posted November 5, 2009 Share Posted November 5, 2009 Can you not re-design your database to have an order column in table 2, this way you can do - $query = "SELECT t1.HEADLINE , t2.CONTENT FROM t1 INNER JOIN t2 ON t1.NUMBER = t2.NUMBER ORDER BY t2.ORDER ASC"; $result = mysql_query($query)or trigger_error("Error with query on line " . __LINE__, E_USER_ERROR); while ($row = mysql_fetch_row($result)) { // $row[0] = headline // $row[1] = content } ############### ###Table Data## ############### NUMBER HEADLINE 1 headline 1 2 headline 2 And table 2: NUMBER CONTENT ORDER 1 content 1a 1 1 content 1b 2 1 content 1c 3 2 content 2a 1 2 content 2b 2 2 content 2c 3 Link to comment https://forums.phpfreaks.com/topic/180437-solved-mysql-php-loop/#findComment-952165 Share on other sites More sharing options...
illumi Posted November 6, 2009 Author Share Posted November 6, 2009 I can absolutely re-design the mysql structure and add an order field. While JJ2K's solution worked (Thanks!) your's seems smoother. Gonna try it out to see if i can get it to work. Link to comment https://forums.phpfreaks.com/topic/180437-solved-mysql-php-loop/#findComment-952173 Share on other sites More sharing options...
Andy-H Posted November 6, 2009 Share Posted November 6, 2009 I would also make the order field auto_incremental so if you wish to create a form to insert new content, you can ensure that the new order id will be higher than the others. Link to comment https://forums.phpfreaks.com/topic/180437-solved-mysql-php-loop/#findComment-952176 Share on other sites More sharing options...
JJ2K Posted November 6, 2009 Share Posted November 6, 2009 Nice work, I need to learn on table joins! Would it not be better to convert the ORDER field just to a unique auto_increment field? That way it covers unique and ordering? Link to comment https://forums.phpfreaks.com/topic/180437-solved-mysql-php-loop/#findComment-952179 Share on other sites More sharing options...
Andy-H Posted November 6, 2009 Share Posted November 6, 2009 When a field is made auto increment it is also automatically made unique. Link to comment https://forums.phpfreaks.com/topic/180437-solved-mysql-php-loop/#findComment-952185 Share on other sites More sharing options...
JJ2K Posted November 6, 2009 Share Posted November 6, 2009 When a field is made auto increment it is also automatically made unique. Thanks for the information Andy! Link to comment https://forums.phpfreaks.com/topic/180437-solved-mysql-php-loop/#findComment-952187 Share on other sites More sharing options...
illumi Posted November 6, 2009 Author Share Posted November 6, 2009 Thanks for your great work guys. I however encountered a problem. Can't seem to output any data via your solution andy. Maybe it's just me that's a total noob right now, but any advice on how to best execute the output? Link to comment https://forums.phpfreaks.com/topic/180437-solved-mysql-php-loop/#findComment-952192 Share on other sites More sharing options...
Andy-H Posted November 6, 2009 Share Posted November 6, 2009 Can you show me your code and output so far please. I also forgot headline only needs to be outputted once each time, will implement this for you later. Link to comment https://forums.phpfreaks.com/topic/180437-solved-mysql-php-loop/#findComment-952194 Share on other sites More sharing options...
illumi Posted November 6, 2009 Author Share Posted November 6, 2009 Below is my real code right now. What I'm trying to achieve with this is basically to pull polls from a forum to a portal frontpage. $query = "SELECT forum_topics.poll_title, forum_poll_options.poll_option_text FROM forum_topics INNER JOIN forum_poll_options ON forum_topics.topic_id = forum_poll_options.topic_id ORDER BY forum_poll_options.poll_option_id ASC"; $result = mysql_query($query)or trigger_error("Error with query on line " . __LINE__, E_USER_ERROR); while ($row = mysql_fetch_row($result)) { $row[0] = 'poll_title'; $row[1] = 'poll_options_text'; } I've never used the syntax of: $row[0] = 'poll_title'; $row[1] = 'poll_options_text'; and that is where my problem lies. Don't really know what I'm supposed to do with it. Tried to echo the rows, but only got errors. Link to comment https://forums.phpfreaks.com/topic/180437-solved-mysql-php-loop/#findComment-952199 Share on other sites More sharing options...
Andy-H Posted November 6, 2009 Share Posted November 6, 2009 $query = "SELECT forum_topics.topic_id, forum_topics.poll_title, forum_poll_options.poll_option_text FROM forum_topics INNER JOIN forum_poll_options ON forum_topics.topic_id = forum_poll_options.topic_id ORDER BY forum_poll_options.poll_option_id ASC"; $result = mysql_query($query)or trigger_error("Error with query on line " . __LINE__, E_USER_ERROR); $currID = NULL; while ($row = mysql_fetch_row($result)) { if ($row[0] != $currID) echo '<h1>' . $row[1] . '</h1>'; echo '<p>' . $row[2] . '</p>'; $currID = $row[0]; } Link to comment https://forums.phpfreaks.com/topic/180437-solved-mysql-php-loop/#findComment-952210 Share on other sites More sharing options...
illumi Posted November 6, 2009 Author Share Posted November 6, 2009 Got it working now, but one final question, just for the learning perspective: <? $query = "SELECT * FROM table1"; $res = mysql_query($query) or die(mysql_error()); while(mysql_fetch_array($res) = $row){ $num = $row['number']; $hline = $row['headline']; $query2 = "SELECT * FROM table2 WHERE number=$num"; $res2 = mysql_query($query2) or die(mysql_error()); echo "<h1>$hline</h1>"; while(mysql_fetch_array($res2) = $row2){ echo $row2['content']."<br />"; } } ?> In this code by JJ2k: Why isn't the second echo enclosed by "" marks? Played a little bit with it, and it seems to be correct. So just curious about this behaviour, trying to develop as a coder. Link to comment https://forums.phpfreaks.com/topic/180437-solved-mysql-php-loop/#findComment-952218 Share on other sites More sharing options...
Andy-H Posted November 6, 2009 Share Posted November 6, 2009 Thats because he is echoing a variable and then concatenating it with the fillstop/period '.' to add a line break in a string. I found this link which explains what I mean. Link to comment https://forums.phpfreaks.com/topic/180437-solved-mysql-php-loop/#findComment-952223 Share on other sites More sharing options...
illumi Posted November 6, 2009 Author Share Posted November 6, 2009 Thanks, you've been very helpful. Cheers! Link to comment https://forums.phpfreaks.com/topic/180437-solved-mysql-php-loop/#findComment-952224 Share on other sites More sharing options...
Andy-H Posted November 6, 2009 Share Posted November 6, 2009 No problem @JJ2K PHPfreaks has a tutorial on sql joins btw if you wish to learn about them. http://www.phpfreaks.com/tutorial/data-joins-unions Link to comment https://forums.phpfreaks.com/topic/180437-solved-mysql-php-loop/#findComment-952226 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.