PHP show MySQL Table in HTML

Question

i am using this code to display a full MySQL Table:

$sql="SELECT * FROM billing ";
$rs=mysql_query($sql,$conn);
$fields_num = mysql_num_fields($rs);
echo "<table border='1'><tr>";
for($i=0; $i<$fields_num; $i++) {
    $field = mysql_fetch_field($rs);
    echo "<td>{$field->name}</td>";
}
echo "</tr>\n";
while($row = mysql_fetch_row($rs)) {
    echo "<tr>";
    echo "<td>$row[0]</td>";
    echo "<td>$row[1]</td>";
    echo "</tr>\n";
}

this is showing every column heading but only 2 columns data

how can i make it show all columns without having to list each column name/array number?

if you know how many rows will be returned, you just put echo <td> in a while => while (index< numberOfRows) {echo "<td>$row[index]</td>"; index++;} — Unix von Bash
– Unix von Bash, Commented Jan 21, 2014 at 17:13

Vaisakh Parannattil Cherul · Accepted Answer · 2014-01-21 17:13:44Z

3

Use mysql_fetch_array($rs) instead of mysql_fetch_row($rs) and access each row of the resultset $rs by using the field names called above which will simplifies your query.. Try so..

answered Jan 21, 2014 at 17:13

Vaisakh Parannattil Cherul

7048 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

cardeol · Accepted Answer · 2014-01-21 17:15:31Z

1

Just iterate though all columns in each row.

$sql="SELECT * FROM billing ";
$rs=mysql_query($sql,$conn);
$fields_num = mysql_num_fields($rs);
echo "<table border='1'><tr>";
for($i=0; $i<$fields_num; $i++) {
    $field = mysql_fetch_field($rs);
    echo "<td>{$field->name}</td>";
}
echo "</tr>\n";
while($row = mysql_fetch_row($rs)) {
    echo "<tr>";
    foreach($row as $index => $data) {
       echo "<td>".$data."</td>";
    }
    echo "</tr>\n";
}

answered Jan 21, 2014 at 17:15

cardeol

2,25818 silver badges25 bronze badges

4 Comments

user2710234 Over a year ago

perfect - thanks. if i added links to the column headings, how could i do it so it would be <a href="page.php?orderby=COL_HEADING&order=ASC"><td>{$field->name}</td></a> and then if that is the current page make it change to order=DESC

cardeol Over a year ago

Use GET for the querystring parameters and then concatenate the SQL query with ORDER BY and ASC/DESC. Just string manipulation.

user2710234 Over a year ago

dont suppose you have an example please?

cardeol Over a year ago

let us continue this discussion in chat

nico · Accepted Answer · 2014-01-21 17:14:03Z

0

while($row = mysql_fetch_row($rs)) {
    echo "<tr>";
    for($i=0; $i<$fields_num; $i++) {
        echo "<td>{$row[$i]}</td>";
    }
    echo "</tr>\n";
}

answered Jan 21, 2014 at 17:14

nico

1,1389 silver badges17 bronze badges

Comments

iam-decoder · Accepted Answer · 2014-01-21 17:22:49Z

0

You could try this:

$sql="SELECT * FROM billing ";
$rs=mysql_query($sql,$conn);
$fields_num = mysql_num_fields($rs);
echo "<table border='1'><tr>";
for($i=0; $i<$fields_num; $i++) {
    $field = mysql_fetch_field($rs);
    echo "<td>{$field->name}</td>";
}
echo "</tr>\n";
$numrows = mysql_num_rows($rs);
while($row = mysql_fetch_row($rs)) {
    $i = 0;
    while($i < $numrows){
        echo "<tr>";
        echo "<td>".$row[$i]."</td>";
        echo "</tr>\n";
        $i++;
    }
}

edited Jan 21, 2014 at 17:22

answered Jan 21, 2014 at 17:17

iam-decoder

2,5641 gold badge17 silver badges30 bronze badges

3 Comments

iam-decoder Over a year ago

what? it will still pull the most recent $i when it gets to the second loop.

nico Over a year ago

il you have 3 rows with 4 fields, $i will go up to 12, so it will crash when trying to access $row[5];

nico Over a year ago

for futur reader : iamde_coder have moved the $i=0 in the first loop

Collectives™ on Stack Overflow

PHP show MySQL Table in HTML

4 Answers 4

Comments

4 Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

4 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related