php - mysql while loop

zero_ZX · February 1, 2011

Hi,

I'm in need of making some loops within each other, in order to retrieve the info which i need.

First part of my code:

<div class="hastable">

					<table id="sort-table"> 
					<thead> 
					<tr>
<?PHP
//echo '<th style="width:128px">Options</th>';
echo '	<th> Identifier </th> ';


$fieldresult = mysql_query("SELECT * FROM fields ORDER BY `order`");
while($fieldrow = mysql_fetch_array($fieldresult))


{

echo '	<th> ' . $fieldrow["name"] . ' </th> ';

So you see, here i create all the table headers required, as long as it can still fetch some data.

My problem is, that I need to execute a echo '</tr>'; when it's done fetching the data, meaning as soon as it cannot retrieve any more data, i need to close this row, as the next data coming, should be placed in a second row..

Is there any way to do this - Without exiting the loop?

cyberRobot · February 1, 2011

Unless I'm missing something you should be able to close the row after the while loop:

...
<?php
//echo '<th style="width:128px">Options</th>';
echo '	<th> Identifier </th> ';
$fieldresult = mysql_query("SELECT * FROM fields ORDER BY `order`");
while($fieldrow = mysql_fetch_array($fieldresult)) {
    echo '	<th> ' . $fieldrow["name"] . ' </th> ';
}
?>
</tr>
...

zero_ZX · February 1, 2011

Correct, but as i wrote I don't want to exit the loop, as I'm not done with the data from it.

cyberRobot · February 1, 2011

Well you could store the header in a variable and echo it out later.

...
<?php
$tableHeader = ''
//$tableHeader .= '<th style="width:128px">Options</th>';
$tableHeader .= '	<th> Identifier </th> ';
$fieldresult = mysql_query("SELECT * FROM fields ORDER BY `order`");
while($fieldrow = mysql_fetch_array($fieldresult)) {
$tableHeader .= '	<th> ' . $fieldrow["name"] . ' </th> ';
}

//DISPLAY TABLE HEADER
echo $tableHeader . '</tr>';
...
?>

If that doesn't work, it might be helpful if you show us what you're doing with the rest of the loop.

zero_ZX · February 1, 2011

This will only print the last result from the loop.

Whole loop code:

<?PHP
//echo '<th style="width:128px">Options</th>';
echo '	<th> Identifier </th> ';


$fieldresult = mysql_query("SELECT * FROM fields ORDER BY `order`");
while($fieldrow = mysql_fetch_array($fieldresult))


{


echo '	<th> ' . $fieldrow["name"] . ' </th> ';
echo '</tr>';

$identifyresult = mysql_query("SELECT * FROM identify ORDER BY `id`");
while($identifyrow = mysql_fetch_array($identifyresult))
{

echo '	<td> ' . $identifyrow["value"] . ' </td> ';

$contentresult = mysql_query("SELECT * FROM content where identify = '".$identifyrow['id']."' AND field = '".$fieldrow['id']."'");
while($contentrow = mysql_fetch_array($contentresult))
{

echo '	<td> ' . $contentrow["content"] . ' </td> ';

}
}
}
?>

As you might see here, I'm slowly building up the entire table.

First it prints the headers, but meanwhile it prints the headers, it starts to make the table rows, so i get the correct content and unique ids (identify) placed in the correct order.

This is so far the only solution which actually prints the content i need, however it mess up the tables.

cyberRobot · February 1, 2011

Could you provide a sample of what the data looks like in your MySQL tables (fields, identify, content)? Also, could you provide a sample of what you want the HTML table to look like?

zero_ZX · February 1, 2011

Identify is a unique id Binding everything together.

I'm not sure there's a need of it. It could be replaced by the regular ID column i think.

HTML table:

Identify | PC-name | Archive

KHS000 | PC-Basement |10-01-2011

KHS001 | PC-entrance | 12-12-2010

etc.. (number 2 is not in the mysql, it was just a sample to give you a better idea).

Picture: http://img191.imageshack.us/img191/4815/udklipn.png

zero_ZX · February 1, 2011

Okay, I did a little research and came to, that i could just execute the queries multiple times

So new code is:

<?PHP
//echo '<th style="width:128px">Options</th>';
echo '	<th> Identifier </th> ';


$fieldresult = mysql_query("SELECT * FROM fields ORDER BY `order`");
while($fieldrow = mysql_fetch_array($fieldresult))


{


echo '	<th> ' . $fieldrow["name"] . ' </th> ';


}

echo '</tr> </thead> ';

$fieldresult2 = mysql_query("SELECT * FROM fields ORDER BY `order`");
while($fieldrow2 = mysql_fetch_array($fieldresult2))

{

$identifyresult = mysql_query("SELECT * FROM identify ORDER BY `id`");
while($identifyrow = mysql_fetch_array($identifyresult))
{

echo '	<td> ' . $identifyrow["value"] . ' </td> ';

$contentresult = mysql_query("SELECT * FROM content where identify = '".$identifyrow['id']."' AND field = '".$fieldrow2['id']."'");
while($contentrow = mysql_fetch_array($contentresult))
{



echo '	<td> ' . $contentrow["content"] . ' </td> ';
echo ' </tr>';

}
}
}
?>

Now this produce this: http://img28.imageshack.us/img28/5186/udklip.png

it appears to duplicate the identify record :/

I also tried to move the echo identity part down in the second loop without luck.

Please let me know your suggestions.

zero_ZX · February 2, 2011

So.. yet again i moved on, and got this:

<?PHP
//echo '<th style="width:128px">Options</th>';
echo '	<th> Identifier </th> ';


$fieldresult = mysql_query("SELECT * FROM fields ORDER BY `order`");
while($fieldrow = mysql_fetch_array($fieldresult))


{


echo '	<th> ' . $fieldrow["name"] . ' </th> ';


}

echo '</tr> </thead> ';

$fieldresult2 = mysql_query("SELECT * FROM fields ORDER BY `order`");
while($fieldrow2 = mysql_fetch_array($fieldresult2))

{

$identifyresult = mysql_query("SELECT * FROM identify ORDER BY `id`");
while($identifyrow = mysql_fetch_array($identifyresult))
{

//echo '	<td> ' . $identifyrow["value"] . ' </td> ';

$contentresult = mysql_query("SELECT * FROM content where identify = '".$identifyrow['id']."' AND field = '".$fieldrow2['id']."'");
while($contentrow = mysql_fetch_array($contentresult))
{



echo ' <td> ' . $identifyrow["value"] . ' </td>	<td> ' . $contentrow["content"] . ' </td> ';


}
echo ' </tr>';
}
}
?>

Looks like this:

http://img684.imageshack.us/img684/8740/udklipk.png

I think there's something wrong when i close the table rows.. But not completely sure?

cyberRobot · February 2, 2011

You should be able to simplify things by looking into MySQL Join. You should be able to merge the last three queries with a Left Join:

http://www.tizag.com/mysqlTutorial/mysqlleftjoin.php

zero_ZX · February 2, 2011

How's that? I can't add a where clause as I dont have any data to work with yet.

jamesxg1 · February 2, 2011

How's that? I can't add a where clause as I dont have any data to work with yet.

Is this the sort of query you are looking for?

SELECT *.FLDS, *.IDNT, *.CNT FROM fields FLDS INNER JOIN identify IDNT JOIN content CNT ON CNT.identify = IDNT.id AND CNT.field = FLDS.id ORDER BY FLDS.order, IDNT.id

OR

SELECT *.FLDS, *.IDNT, *.CNT FROM fields FLDS INNER JOIN identify IDNT JOIN content CNT WHERE CNT.identify = IDNT.id AND CNT.field = FLDS.id ORDER BY FLDS.order, IDNT.id

James.

cyberRobot · February 2, 2011

You actually can, did you try the Left Join? Joining lets you pull data from multiple tables within a single query. It should make what you're trying to do a lot simpler. You'll only need two loops instead of four. Of course it takes a little work to get used to joining queries together.

zero_ZX · February 2, 2011

Sorry James, you totally lost me

Cyber, no I didn't try it, I might not understand left join, never heard of it. I see I can select multiple fields from multiple tables, but I don't see how I can put my WHERE into this.

jamesxg1 · February 2, 2011

Sorry James, you totally lost me

Cyber, no I didn't try it, I might not understand left join, never heard of it. I see I can select multiple fields from multiple tables, but I don't see how I can put my WHERE into this.

LOL!

The two queries I posted are all the queries you were using in one using JOIN, the first one is based on finding your results using ON. The second is based on using WHERE .

James.

zero_ZX · February 2, 2011

I c, then how would I print the rows? I tried with this:

$contentresult = mysql_query("SELECT *.FLDS, *.IDNT, *.CNT FROM fields FLDS INNER JOIN identify IDNT JOIN content CNT WHERE CNT.identify = IDNT.id AND CNT.field = FLDS.id ORDER BY FLDS.order, IDNT.id");
while($contentrow = mysql_fetch_array($contentresult))
{



echo ' <td> ' . $contentrow["value"] . ' </td>	<td> ' . $contentrow["content"] . ' </td> ';


}

Last question is where i should close the table rows?

cyberRobot · February 3, 2011

Does this work:

<?php
...
while($contentrow = mysql_fetch_array($contentresult)) {
    echo '<tr><td>' . $contentrow["value"] . '</td><td>' . $contentrow["content"] . '</td></tr>';
}
...
?>

Note the <tr> tag at the beginning of the echo statement and the </tr> at the end. Looking at your previous examples, it seems like the while loops that display the data are missing the open row tag.

zero_ZX · February 3, 2011

Sorry that I didn't make myself clear enough, It's a SQL error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.FLDS, *.IDNT, *.CNT FROM fields FLDS INNER JOIN identify IDNT JOIN content CNT ' at line 1

BlueSkyIS · February 3, 2011

yes, *.FLDS is invalid SQL. What are you trying to do?

Do you mean FLDS.*??

cyberRobot · February 3, 2011

yes, *.FLDS is invalid SQL. What are you trying to do?

Do you mean FLDS.*??

I'm not sure, but doesn't "...fields FLDS INNER JOIN identify IDNT JOIN content CNT ..." need to be "...fields AS FLDS INNER JOIN identify AS IDNT JOIN content AS CNT ..."?

Note that I find table name shortcuts to be more confusing the helpful. You could use the full table names in the query:

<?php
...
$contentresult = mysql_query("SELECT fields.*, identify.*, content.* FROM fields INNER JOIN identify JOIN content WHERE content.identify = identify.id AND content.field = fields.id ORDER BY fields.order, identify.id");
...
?>

zero_ZX · February 3, 2011

Thanks Cyber, now I even understand my query lol

Appears to be working, but data looks exactly like it did before when it gets ehco'ed.

The problem is, that i think i need a <td> & </td> each time it echoes a new $contentrow["content"].

zero_ZX · February 4, 2011

Still having troubles on this one

cyberRobot · February 5, 2011

Did you try adding the open table row <tr> to your loop? Note that both your header row and data rows appeared to be missing the <tr> for your original code.

If that doesn't help, it might be helpfult to post your code again since things have changed.

zero_ZX · February 5, 2011

Hi.

This is the present code:

<table id="sort-table"> 
					<thead> 
					<tr>
<?PHP
//echo '<th style="width:128px">Options</th>';
echo '	<th> Identifier </th> ';


$fieldresult = mysql_query("SELECT * FROM fields ORDER BY `order`");
while($fieldrow = mysql_fetch_array($fieldresult))


{


echo '	<th> ' . $fieldrow["name"] . ' </th> ';


}

echo '</tr> </thead> <tbody> ';

$contentresult = mysql_query("SELECT fields.*, identify.*, content.* FROM fields INNER JOIN identify JOIN content WHERE content.identify = identify.id AND content.field = fields.id ORDER BY fields.order, identify.id") or die (mysql_error());

while($contentrow = mysql_fetch_array($contentresult)) {
    echo '<tr><td>' . $contentrow["value"] . '</td><td>' . $contentrow["content"] . '</td></tr>';
}

//echo ' </tr>';
?>


					</tbody>
					</table>

It looks added to me.

cyberRobot · February 7, 2011

Sorry, I've been having a difficult time wrapping my mind around the issue is. After taking another look at the graphic you attached to Reply 8, I think I might understand.

It looks like your records are being listed in three different rows:

KHS0001|PC-basement

KHS0002|PC-entrance

KHS0001|10-01-2011

KHS0002|10-10-2010

KHS0001|MD

KHS0002|LF

Instead you want it listed as:

KHS0001|PC-basement|10-01-2011|MD

KHS0002|PC-entrance|10-10-2010|LF

Is that correct?

Sign In

php - mysql while loop

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information