Mysql table to html table

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Parody
Forum Contributor
Posts: 252
Joined: Fri May 06, 2005 7:06 pm
Location: Great Britain

Mysql table to html table

Post by Parody »

I have a script that reads from a MYSQL table and outputs it in a html table. There is a different row on the table for each row on the table. But on the table it misses out the first entry. I am storing the data to be printed in arrays, I think this has something to do with the ID of the array. Im not sure :(

Any ideas :)
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post by anjanesh »

first entry ? meaning first row or first field ?

Code: Select all

$sql = "SELECT * FROM `table1`";
$res = mysql_query($sql);
echo '<table>';
while ($row = mysql_fetch_assoc($res))
 {
        echo '<tr>';
        while (list($key,$value) = each($row))
         {
                echo '<td>'.$value.'</td>';
         }
        echo '</tr>';
 }
echo '</table>';
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

would help to see some code.

just loop over the array that mysql builds for you:

ex:

Code: Select all

$result = mysql_query("select * from yourtable")
  or die(mysql_error());
while($row = mysql_fetch_assoc($result)){
   echo $row['field'];
   //etc
}
Parody
Forum Contributor
Posts: 252
Joined: Fri May 06, 2005 7:06 pm
Location: Great Britain

Post by Parody »

Ok, here is the code:

Code: Select all

<?php require('Connections/HFL.php');
if (isset($_SESSION['zim'])){;} else{session_start();}
include 'config1.php';
mysql_connect($hostname_HFL, $username_HFL, $password_HFL) or die(mysql_error());
mysql_select_db("users") or die('Sorry, we are having database connection problems, please report this to an Administrator');
$query = "SELECT * FROM cars WHERE owner='$user'ORDER BY ID ASC";
$result = mysql_query($query);
$r=mysql_fetch_array($result); 
if (isset($_GET['sellid'])) {
$cartypeid = $r["cartypeid"];
$query45 = $query = "SELECT * FROM auto WHERE ID='$cartypeid'";
$result12345 = mysql_query($query45) or die(mysql_error());
$r1=mysql_fetch_array($result12345);
$query44 = $query = "SELECT * FROM stats WHERE username='$user'";
$result12342 = mysql_query($query44) or die(mysql_error());
$r0=mysql_fetch_array($result12342);
$ID = $_GET['sellid'];
$query11 = "SELECT * FROM cars WHERE ID='$ID'";
$result121 = mysql_query($query11) or die(mysql_error());
$r11=mysql_fetch_array($result121);
if ($r11["owner"] = $user){
$noscanisters = $r0["NOS"];
$car = $r1["name"];
$primeprice = $r1["price"];
$NOSID = $r["NOS"];
$turboid = $r["turbocharger"];
$exhaustid = $r["Exhaust"];
$specialid = $r["Special"];
$computerid = $r["Computer"];
$query46 = $query = "SELECT * FROM parts WHERE ID='$NOSID'";
$result123456 = mysql_query($query46) or die(mysql_error());
$r2=mysql_fetch_array($result123456);
$primeprice2 = $primeprice + $r2["price"];
$query47 = $query = "SELECT * FROM parts WHERE ID='$turboid'";
$result1234567 = mysql_query($query47) or die(mysql_error());
$r3=mysql_fetch_array($result1234567);
$primeprice3 = $primeprice2 + $r3["price"];
$query48 = $query = "SELECT * FROM parts WHERE ID='$exhaustid'";
$result12345678 = mysql_query($query48) or die(mysql_error());
$r4=mysql_fetch_array($result12345678);
$primeprice4 = $primeprice3 + $r4["price"];
$query49 = $query = "SELECT * FROM parts WHERE ID='$exhaustid'";
$result123456789 = mysql_query($query49) or die(mysql_error());
$r5=mysql_fetch_array($result123456789);
$primeprice5 = $primeprice4 + $r5["price"];
$query50 = $query = "SELECT * FROM parts WHERE ID='$specialid'";
$result1234567891 = mysql_query($query50) or die(mysql_error());
$r6=mysql_fetch_array($result1234567891);
$primeprice6 = $primeprice5 + $r6["price"];
$query51 = $query = "SELECT * FROM parts WHERE ID='$computerid'";
$result12345678911 = mysql_query($query51) or die(mysql_error());
$r7=mysql_fetch_array($result12345678911);
$primeprice7 = $primeprice6 + $r7["price"];
$primeprice8 = $primeprice7 / 100;
$price = $primeprice8 * 90;
$money = $r0["money"];
$upd8money = $money + $price;
$qry123565 = "UPDATE stats SET money='".$upd8money."' WHERE username='$user'"; 
mysql_query($qry123565)  or die(mysql_error());
$qry1235651 = "UPDATE cars SET owner='Admin' WHERE ID='$ID'"; 
mysql_query($qry1235651)  or die(mysql_error());
header("Location: garage.php");
} else {print "The car that you selected to sell is not yours";}}
?>
<link rel="stylesheet" type="text/css"
href="style.css" />
<div align="center">
<h1><?
$checkamount = "SELECT COUNT(*) as `tot` FROM `cars` WHERE owner='$user'";
$result1234 = mysql_query($checkamount) or die(mysql_error());
if (mysql_result($result1234,0,'tot') != 0) {
?><form name="form1" method="POST" action="inbox.php">
  <div align="center">
	<input type="hidden" name="del" value="1">
    <input type="submit" name="btnnew" value="Delete All Messages" id="btnnew" class="NormalButton" style="width:160px;LEFT: 8px; TOP: 8px" />
  </div>
</form><? } else {print"You do not own any cars";
die();}
?>
<link rel="stylesheet" type="text/css"
href="style.css" />
<div align="center">
<h1><table width="90%" border="1" bordercolor="#999999" bgcolor="#333333">
   <tr>
<th width="2%"><h1><u>ID</u></h1></th>
<th width="4%"><h1><u>Price</u></h1></th>
<th width="11%"><h1><u>Location</u></h1></th>
<th width="24%"><h1><u>Car</u></h1></th>
<th width="10%"><h1><u>HP</u></h1></th>
<th width="4%"><h1><u>NOS</u></h1></th>
<th width="10%"><h1><u>Turbocharger</u></h1></th>
<th width="8%"><h1><u>Special Parts</u></h1></th>
<th width="15%"><h1><u>Non-Standard Exhaust</u></h1></th>
<th width="12%"><h1><u>Computer</u></h1></th>
<th width="12%"><h1><u>Sell</u></h1></th>
<th width="12%"><h1><u>Select</u></h1></th>
</tr><?
while($r=mysql_fetch_array($result))
{	
   //the format is $variable = $r["nameofmysqlcolumn"];
   //modify these to match your mysql table columns
  
   $location=$r["location"];

   $tested=$r["tested"];

if ($tested == 'NO') {$hp = 'TEST NOW';} 
else {$hp = $r["hp"];
}
$ID=$r["ID"];
;

if ($r["NOS"]
 == '0') {$NOS = 'No';} else {$NOS = 'Yes';}
if ($r["turbocharger"]
 == '0') {$turbocharger = 'No';} else {$turbocharger = 'Yes';}
if ($r["Special"]
 == '0') {$special = 'No';} else {$special = 'Yes';}
if ($r["Exhaust"]
 == '0') {$exhaust = 'No';} else {$exhaust = 'Yes';}
if ($r["Computer"]
 == '0') {$computer = 'No';} else {$computer = 'Yes';}
$cartypeid = $r["cartypeid"];
$query45 = $query = "SELECT * FROM auto WHERE ID='$cartypeid'";
$result12345 = mysql_query($query45) or die(mysql_error());
$r1=mysql_fetch_array($result12345);
$car = $r1["name"];
$primeprice = $r1["price"];
$NOSID = $r["NOS"];
$turboid = $r["turbocharger"];
$exhaustid = $r["Exhaust"];
$specialid = $r["Special"];
$computerid = $r["Computer"];
$query46 = $query = "SELECT * FROM parts WHERE ID='$NOSID'";
$result123456 = mysql_query($query46) or die(mysql_error());
$r2=mysql_fetch_array($result123456);
$primeprice2 = $primeprice + $r2["price"];
$query47 = $query = "SELECT * FROM parts WHERE ID='$turboid'";
$result1234567 = mysql_query($query47) or die(mysql_error());
$r3=mysql_fetch_array($result1234567);
$primeprice3 = $primeprice2 + $r3["price"];
$query48 = $query = "SELECT * FROM parts WHERE ID='$exhaustid'";
$result12345678 = mysql_query($query48) or die(mysql_error());
$r4=mysql_fetch_array($result12345678);
$primeprice4 = $primeprice3 + $r4["price"];
$query49 = $query = "SELECT * FROM parts WHERE ID='$exhaustid'";
$result123456789 = mysql_query($query49) or die(mysql_error());
$r5=mysql_fetch_array($result123456789);
$primeprice5 = $primeprice4 + $r5["price"];
$query50 = $query = "SELECT * FROM parts WHERE ID='$specialid'";
$result1234567891 = mysql_query($query50) or die(mysql_error());
$r6=mysql_fetch_array($result1234567891);
$primeprice6 = $primeprice5 + $r6["price"];
$query51 = $query = "SELECT * FROM parts WHERE ID='$computerid'";
$result12345678911 = mysql_query($query51) or die(mysql_error());
$r7=mysql_fetch_array($result12345678911);
$primeprice7 = $primeprice6 + $r7["price"];
$primeprice8 = $primeprice7 / 100;
$price = $primeprice8 * 90;
   
   //display the row

?>
<tr>
      <TD BGCOLOR="#000000" align="center" valign="top"><h1><?php print "$ID"; ?></h1></td>
      <TD BGCOLOR="#000000" align="center" valign="top"><h1><?php print "$$price"; ?></h1></td>
      <TD BGCOLOR="#000000"><h1 align=center><?php print "$location"; ?></h1></td>
      <TD BGCOLOR="#000000"><h1><?php print "$car"; ?></h1></td>
      <TD BGCOLOR="#000000"><h1><?php print "$hp"; ?></h1></td>
      <TD BGCOLOR="#000000"><h1><?php print "$NOS"; ?></h1></td>
      <TD BGCOLOR="#000000"><h1><?php print "$turbocharger"; ?></h1></td>
      <TD BGCOLOR="#000000"><h1><?php print "$special"; ?></h1></td>
      <TD BGCOLOR="#000000"><h1><?php print "$exhaust"; ?></h1></td>
      <TD BGCOLOR="#000000"><h1><?php print "$computer"; ?></h1></td>
      <TD BGCOLOR="#000000"><h1><a href="garage.php?<?php print"sellid=$ID"?>">Sell</a></h1></td>
      <TD BGCOLOR="#000000"><h1><?php ?> </h1></td>
    </tr>
  <br>
  <br>
  <?    } ?>  
</h1>
Parody
Forum Contributor
Posts: 252
Joined: Fri May 06, 2005 7:06 pm
Location: Great Britain

Post by Parody »

Any ideas why it misses out the first entry in the mysql table?
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

wow! you have a lot of queries going on there. Which one/s are dying? all of them?

try grabbing the number of rows it's returning and see if that matches up to what you expect. If it does, it might be how you're looping the results, if not, then it could be something in your query.

use mysql_num_rows() to obtain the number of records it's returning.
Parody
Forum Contributor
Posts: 252
Joined: Fri May 06, 2005 7:06 pm
Location: Great Britain

Post by Parody »

I did it, because it was missing out the first entry I just created a new query and used the first result from that. The original query was ordered by DESC so I made the new one ASC and the first one was the missing one. I suppose it was a bit long winded and uses bandwidth, but hey! It works.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

Which query on what line is the start of your problems?
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Post Reply