select / while loop with increment

[komp]

Hi gurus,

I have a little problem below and I just cannot work it out.

The following code works fine and produces the results I want in the format I want *but* I cannot for the life of me work out how to increment both :-

while($transrow = mysql_fetch_array($result))
while($row = mysql_fetch_array($timeresult))

to populate the table. I just get repeated information.

Did I make sense in my explanation, if so please do pass on your ideas.

I look forward to learning from you.

Regards

John

==============================================
= code base starts here =
==============================================

<?php
include 'config.php';
include 'opendb.php';
mysql_select_db("dvbtime");
$timeresult = mysql_query("SELECT *, from_unixtime(time_utc, '%M %e, %Y, %l:%i%p') as Converted FROM result");
$result = mysql_query("select * from transponder, result where transponder.id like result.num and result.diff > 3 or result.diff < -7");
if (!$result) {
die("Query to show tables failed");
}
if (!$timeresult) {
die("Query to show tables failed");
}
echo "<h1>Joined Table based on time difference to match transponder: {$table}</h1>";
echo "<table border='1'>
<tr>
<th>ID</th>
<th>NUM</th>
<th>FREQ</th>
<th>POL</th>
<th>SR</th>
<th>VPID</th>
<th>APID</th>
<th>SID</th>
<th>TRANSPONDER</th>
<th>CHANNELS</th>
<th>DIFFERENCE</th>
<th>TIME_RECV</th>
<th>TIME_UTC</th>
<th>UTC-CONVERTED</th>
</tr>";
while($transrow = mysql_fetch_array($result))
while($row = mysql_fetch_array($timeresult))
{
echo "<tr>";
echo "<td><center>" . $transrow['id'] . "</center></td>";
echo "<td><center>" . $transrow['num'] . "</center></td>";
echo "<td><center>" . $transrow['freq'] . "</center></td>";
echo "<td><center>" . $transrow['pol'] . "</center></td>";
echo "<td><center>" . $transrow['sr'] . "</center></td>";
echo "<td><center>" . $transrow['vpid'] . "</center></td>";
echo "<td><center>" . $transrow['apid'] . "</center></td>";
echo "<td><center>" . $transrow['sid'] . "</center></td>";
echo "<td><center>" . $transrow['comment'] . "</center></td>";
echo "<td><center>" . $transrow['channel'] . "</center></td>";
echo "<td><center>" . $transrow['diff'] . "</center></td>";
echo "<td><center>" . $row['time_recv'] . "</center></td>";
echo "<td><center>" . $row['time_utc'] . "</center></td>";
echo "<td><center>" . $row['Converted'] . "</center></td>";
echo "</tr>";
}
echo "</table>";
?>

[eelixduppy]

Remove one of the while loops. Try changing the following:

while($transrow = mysql_fetch_array($result)) 
while($row = mysql_fetch_array($timeresult)) 
{

To this:

while($transrow = mysql_fetch_array($result)) {
$row = mysql_fetch_array($timeresult);

This is assuming that both queries are going to return the same amount of rows EVERY time.

And Welcome to WebmasterWorld! :)

[cameraman]

If you know they'll always have the same number of records returned, you can do:
while($transrow = mysql_fetch_array($result)){
$row = mysql_fetch_array($timeresult);
.
.

If not, then what I'd do is read them into arrays first:
while($transrow = mysql_fetch_array($result))
$transrows[] = $transrow;
while($row = mysql_fetch_array($timeresult))
$rows[] = $row;
foreach($transrows as $idx => $transrow) {
echo transrow tds;
if(isset($rows[$idx])) {
// as a side note, you'd refer to it as $rows[$idx]['time_recv'] etc.
echo row tds;
}
}

With two recordsets of different lengths you could still do it by reading the larger one with while and the smaller one inside, but you'd need to make concessions for when the smaller one 'ran out'. I would choose the array method because I think that makes it a little easier.

oof, took too long composing my answer...

[komp]

oddly neither of these seem to work. It just seems to kill off the browser.

I think i need to readjust things.. so i have this code now to create the page yet I still dont have the time_utc being converted and placed in the column proper_time.

=========================
code start
=========================

<?php
include 'config.php';
include 'opendb.php';
mysql_select_db("dvbtime");

$result = mysql_query("select * from transponder, result where transponder.id li
ke result.num and result.diff > 4 or result.diff < -4 order by result.diff ASC")
;
if (!$result) {
die("Query to show tables failed");
}

$fields_num = mysql_num_fields($result);

echo "<h1>Joined Table based on time difference to match transponder: {$table}</
h1>";
echo "<table border='1'><tr>";
for($i=0; $i<$fields_num; $i++)
{
$field = mysql_fetch_field($result);
echo "<td>{$field->name}</td>";
}
echo "</tr>\n";
while($row = mysql_fetch_row($result))
{
echo "<tr>";

foreach($row as $cell)
echo "<td>$cell</td>";
echo "</tr>\n";
}
mysql_free_result($result);
?>

=============================
end code
=============================

so i need to refer to the table "result" in the column "time_utc" as well and place that result in order into the php table column "proper_time"

The time_utc is in unix time "1216199437" and i need to get GMT in proper_time column.... :/

Again do I make any sense hehe

Thanks for the welcome and the responses so far.

[komp]

here is a select 1 record from the DB's

mysql> select * from result where id = 225443;
+--------+-----+------------+------------+-------------+------+
¦ id ¦ num ¦ time_recv ¦ time_utc ¦ proper_time ¦ diff ¦
+--------+-----+------------+------------+-------------+------+
¦ 225443 ¦ 1 ¦ 1216198816 ¦ 1216198815 ¦ NULL ¦ -1 ¦
+--------+-----+------------+------------+-------------+------+
1 row in set (0.00 sec)

mysql> select * from transponder where id = 1;
+----+-----+------+-------+-----+-------+------+------+------+--------------------------+----------------------------------------------------------------+
¦ id ¦ num ¦ os ¦ freq ¦ pol ¦ sr ¦ vpid ¦ apid ¦ sid ¦ comment ¦ channels ¦
+----+-----+------+-------+-----+-------+------+------+------+--------------------------+----------------------------------------------------------------+
¦ 1 ¦ 2 ¦ NULL ¦ 11739 ¦ v ¦ 27500 ¦ 2332 ¦ 2334 ¦ 6004 ¦ Astra 2A - Transponder 2 ¦ Living, Living+1, Living 2, Challenge, Trouble, Bravo, Bravo+1 ¦
+----+-----+------+-------+-----+-------+------+------+------+--------------------------+----------------------------------------------------------------+
1 row in set (0.00 sec)

ID in transponder = NUM in result

mysql> select count(*) from transponder;
+----------+
¦ count(*) ¦
+----------+
¦ 96 ¦
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from result;
+----------+
¦ count(*) ¦
+----------+
¦ 1869 ¦
+----------+
1 row in set (0.00 sec)