[SOLVED] Mysql php loop

[illumi]

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

[kickstart]

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

[mpharo]

$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];

     }

}

[illumi]

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

[JJ2K]

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 />";
}
}


?>

[Andy-H]

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

[illumi]

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.

[Andy-H]

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.

[JJ2K]

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?

[Andy-H]

When a field is made auto increment it is also automatically made unique.

[JJ2K]

When a field is made auto increment it is also automatically made unique.

 

Thanks for the information Andy!

[illumi]

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?

[Andy-H]

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.

[illumi]

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.

[Andy-H]

$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];
}

[illumi]

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.

[Andy-H]

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.

[illumi]

Thanks, you've been very helpful.

 

Cheers!

[Andy-H]

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