MySQL results not displaying in table?

Question

I am teaching myself PHP and MySQL, and I am trying to retrieve some information from my database and put it into a table.

So far only the table column headers are showing up, and no information is showing up for each column. It is also taking ages for the PHP file to display.

Please can you point out the problem with my code.

<?php
mysql_connect("localhost",$username,$password);
mysql_select_db($dbname) or die("Unable to select Database");
$query = "SELECT * FROM table_1";
$result = mysql_query($query);
$numcount = mysql_num_rows($result);
echo "<h2>$numcount rows in table_1.</h2>";
mysql_close();
?>

<table border="0" cellspacing="4" cellpadding="2">
<tr>
<th><font face="Futura">Type |</font></th>
<th><font face="Futura">Name |</font></th>
<th><font face="Futura">Street |</font></th>
<th><font face="Futura">Address1 |</font></th>
<th><font face="Futura">Address2 |</font></th>
<th><font face="Futura">Town |</font></th>
<th><font face="Futura">County |</font></th>
<th><font face="Futura">Postcode |</font></th>
<th><font face="Futura">Number |</font></th>
<th><font face="Futura">Latitude,Longitude</font></th>
</tr>

<?php
$i=0;
while ($i < 843) {
$type = mysql_result($result,$i,"type");
$name = mysql_result($result,$i,"name");
$street = mysql_result($result,$i,"street");
$addr1 = mysql_result($result,$im,"address1");
$addr2 = mysql_result($result,$im,"address2");
$town = mysql_result($result,$im,"town");
$county = mysql_result($result,$im,"county");
$postcode = mysql_result($result,$im,"postcode");
$number = mysql_result($result,$im,"number");
$latlong = mysql_result($result,$im,"latlong");
}
?>

<tr>
<td><font face="Futura"><?php echo $type;?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $name;?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $street;?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $addr1;?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $addr2;?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $town;?></font></td>
</tr>
<?php
$i++;
?>
<?
echo "</table>"; 
?>
</body>
</html>

GG. · Answer 1 · 2011-03-14T01:04:58.343

2

mysql_result() works when you do mysql_close() before ?
Why not use mysql_fetch_row() here ?

EDIT WITH MORE INFOS for @XcodeDev :

You can use mysql_result() when you need a single result, for example :

$query = mysql_query("SELECT COUNT(id) FROM users");

Then

$count = mysql_result($query, 0);

When you expect a single line of result with several data, used

$result = mysql_fetch_row($query); => $result[0], $result[1], $result[2] etc

Or

$result = mysql_fetch_assoc($query); => $result['type'], $result['name'] etc

When you expect several lines of results with several data, used

while ($result = mysql_fetch_row($query)) {
    => $result[0], $result[1], $result[2] etc
}

Or

while ($result = mysql_fetch_assoc($query)) {
    => $result['type'], $result['name'] etc
}

edited Mar 14 '11 at 01:04

answered Mar 13 '11 at 23:04

GG.

21,083
14
84
130

1

@XcodeDev you should get used to this usage. mysql_fetch_row or my choice is mysql_fetch_array is always better. There is no need to define or count a $i. – aiternal Mar 13 '11 at 23:08
@Ahmet Kemal I am completely new to PHP/MySQL I was just following a tutorial. I will look into it now. – max_ Mar 13 '11 at 23:12

Rasika · Accepted Answer · 2011-03-13T23:08:54.690

0

For a starter, you are having an infinite loop as $i is not being incremented inside the loop. If you increment $i, it should fix the problem. What is $im?

The code should look like:

<?php
$i=0;
while ($i < 843) {
    $type = mysql_result($result,$i,"type");
    $name = mysql_result($result,$i,"name");
    $street = mysql_result($result,$i,"street");
    $addr1 = mysql_result($result,$i,"address1");
    $addr2 = mysql_result($result,$i,"address2");
    $town = mysql_result($result,$i,"town");
    $county = mysql_result($result,$i,"county");
    $postcode = mysql_result($result,$i,"postcode");
    $number = mysql_result($result,$i,"number");
    $latlong = mysql_result($result,$i,"latlong");

?>

<tr>
<td><font face="Futura"><?php echo $type;?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $name;?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $street;?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $addr1;?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $addr2;?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $town;?></font></td>
</tr>
<?php
    $i++;
} // this is where the loop is ending.
?>
<?
mysql_close(); // close mysql connection after reading data
echo "</table>"; 
?>

edited Mar 13 '11 at 23:08

answered Mar 13 '11 at 22:59

Rasika

1,980
13
19

$im has just been realised as a spelling mistake, and $i is being incremented near the bottom of the code snippet I provided. – max_ Mar 13 '11 at 23:01
But $i is not being incremented within the while loop – Mark Baker Mar 13 '11 at 23:03
Note that you are incrementing $i outside the loop. Use the code above to fix it. – Rasika Mar 13 '11 at 23:04
Not a practical way and $number = mysql_result($result,$i,"number"); usage gives error sometimes. – aiternal Mar 13 '11 at 23:11
I personally use while($row = mysql_fetch_row($result)) as this is a clean way to get the loop incremented. The above snippet was based on the posted code. – Rasika Mar 13 '11 at 23:13
@Ahmet Kemal "number" is a string not an integer – max_ Mar 13 '11 at 23:13
@XcodeDev it is not important. You could do everything using mysql_fetch_array that you do with mysql_result. Plus, it is a better usage and also better performance. @Rasika great minds think alike ;) – aiternal Mar 13 '11 at 23:18

MySQL results not displaying in table?

2 Answers2