creating html table from data pulled from database using PHP

Question

I am working on a project to pull data from a sql database via PHP and create an HTML table from it. It is part of a form I am creating and the table will be a listing of what I am selling, costs, weights, etc. Anyway, I need to do this with PHP. So far I have the following in this section of my code to make this table:

<?php
$db=mysqli_connect(null,null,null,'weblab')
   or die("Can't connect to DB: " .
   mysqli_connect_error());

$query="SELECT fruit_item_no, fruit_name, fruit_price, fruit_weight FROM fruit_$

$result=mysqli_query($db, $query);
if (!$result) {
print "Error - The query could not be executed!" .
      mysqli_error();
}
    print "<table class = 'main'><caption> <h2> Fruit Purchasing Form </h2> </c$
    print "<tr align = 'center'>";

    $num_rows=mysqli_num_rows($result);

    if ($num_rows > 0) {
        $row=mysqli_fetch_assoc($result);
        $num_fields=mysqli_num_fields($result);

        $keys=array_keys($row);
        for ($index = 0; $index < $num_fields; $index++)
            print "<th class='a'>" . $keys[$index] . "</th>";
        print "</tr>";

        for ($row_num = 0; $row_num < $num_rows; $row_num++) {
            print "<tr>";
            $values = array_values($row);
            for ($index = 0; $index < $num_fields; $row_num++) {
                $value=htmlspecialchars($values[$index]);
                print "<td class = 'b'>" . $value . "</td>";
            }
        print "</tr>";
        $row=mysqli_fetch_assoc($result);
        }
    }
    else {
        print "There were no such rows in the table <br />";
    }
    print "</table>";
?>

The problem I am running into is when I attempt the view the entire page in Chrome, all I see is:

I think the error is somewhere around this section of code:

for ($row_num = 0; $row_num < $num_rows; $row_num++) {
            print "<tr>";
            $values = array_values($row);
            for ($index = 0; $index < $num_fields; $row_num++) {
                $value=htmlspecialchars($values[$index]);
                print "<td class = 'b'>" . $value . "</td>";

If anyone can give me a little direction on what I might be missing, I would greatly appreciate it. I have been researching a while now and no clear fix has come up that i can see.

UPDATE: Thought it would be best to just update my post instead of new question or comment somewhere. Thanks to all your suggestions I was able to correct my code to properly display my table and worked out all validation errors. I apologize for the code reading nightmare... I am still fairly new at this and I need to work more at it.

I did run into one issue I still cannot resolve though. I am trying to add an input box on the end of each row in the table I created. Problem is no matter what I try the boxes stay in a line above the table (pretty much at the top of the browser screen.

I placed this line in the sql query block to try to get the input box:

echo ("<input name='item[]' type='text' .=''/>");

and I also tried:

echo ("<input type='text' name ='item'/>");

Both did the same thing in giving me the line of boxes along the top. Any direction you could provide in getting these to be at the end of each row? I do not need it to put data back in the database or add to a table there. I just am trying to get input fields that will allow input that will submit with my form.

$query="SELECT fruit_item_no, fruit_name, fruit_price, fruit_weight FROM fruit_$ is unfinished as is print "<table class = 'main'><caption> <h2> Fruit Purchasing Form </h2> </c$ — Funk Forty Niner
– Funk Forty Niner, Commented Nov 19, 2015 at 23:15
in your second for loop "for ($index = 0; $index < $num_fields; $row_num++)" there should be $index++ instead of $row_num++ — rishal
– rishal, Commented Nov 19, 2015 at 23:26
The most amazing thing you could ever learn right now is how to separate your view from your code. This thing is a nightmare to read. — skrilled
– skrilled, Commented Nov 19, 2015 at 23:26

Tristan · Accepted Answer · 2015-11-19 23:32:11Z

You can simplify the code by running and while loop on the result and iterating through the rows. And then using foreach loops since each row is an array of key value pairs. On the first iteration you could print the header.

$num_rows=mysqli_num_rows($result);
if ($num_rows > 0) {
    $i = 0;
    // loop through each row in the result
    while ($row=mysqli_fetch_assoc($result)) {
        // if first then print the header
        if ($i == 0) {
            print "<tr>";
            foreach ($row as $field => $value) {
                print "<th class='a'>".$field."</th>";
            }
            print "</tr>";
        }
        // print the row
        print "<tr>";
        foreach ($row as $value) {
            print "<td class='b'>".$value."</td>";
        }
        print "</tr>";
        $i++;
    }
}
else {
    print "<tr></td>There were no such rows in the table</td></tr>";
}

As suggested in the comments it would be a good idea to separate your view from your code.

Ok... going to risk getting smacked for not fully thinking this one, but maybe its been me working too long on this project... can you give a brief explanation on separate view from code? Or direct me to a place to learn better on doing that. That works too.

Supreme Dolphin · Accepted Answer · 2015-11-19 23:30:45Z

0

In your second for loop, you have this:

for($index=0; $index<$num_fields; $row_num++)

Well, the value of $index will remain 0 throughput your execution. Try changing it to

for($index=0; $index<$num_fields; $index++)
{
  $row_num++;
 //rest of code
}

Hope it helps.

answered Nov 19, 2015 at 23:30

Supreme Dolphin

2,6941 gold badge17 silver badges26 bronze badges

Collectives™ on Stack Overflow

creating html table from data pulled from database using PHP

2 Answers 2

3 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Related