PHP function to loop SQL result and generate HTML tables

Question

I want to write an php function that will echo sql query results in a specific manner.

Example I have table 1 which is 10.000 rows * 43 columns:

NO NAME Prof Date of birth 1 John Teacher 1987 2 Nick Engineer 1976 3 4 5

And so on. Based on No which is an integer (1-10.000), I want to generate tables such as:

Name: John Prof: Teacher Date of birth: 1987

Name: Nick Prof: Engineer Date of birth: 1976

So far my code:

`

    $hostname = "";     
    $username = "";
    $password = "";
    $dbname = "db1";

    //connection to the database
    $con = mysqli_connect($hostname, $username, $password, $dbname)
        or die("Unable to connect to MySQL");
    mysqli_set_charset($con, 'utf8');
    /*echo "Connected to db1 database <br>";
    else
    echo "Could not connect"; */

    $query1 = "SELECT * FROM `table 1` WHERE CODE_NO = 1";
    $query2 = "SHOW COLUMNS FROM table 1";
    $result1 = mysqli_query($con, $query1);
    $result2 = mysqli_query($con, $query2);

    // Check result
    // Useful for debugging.
     if (!$result1) {
        $message  = 'Invalid query: ' . mysqli_error($con) . "\n";
        $message .= 'Whole query: ' . $query1;
        die($message);
    }

    echo "<table>"; // table tag in the HTML
        while($row = mysqli_fetch_array($result1))
        //Creates a loop to loop through results
        {
        echo

        "<tr>
            <th>CODE_NO:</th>
            <td>" . $row['CODE_NO'] . "</td>
        </tr>
        <tr>
            <th>FIRST_NAME:</th>
            <td>" . $row['FIRST_NAME'] . "</td>
        </tr>
        <tr>
            <th>SURNAME:</th>
            <td>" . $row['SURNAME'] . "</td>
        </tr>
        <tr>
            <th>Date of birth:</th>
            <td>" . $row['DOB'] . "</td>
        </tr>
        <tr>
            <th>Date of death:</th>
            <td> " . $row['DOD'] . "</td>
        </tr>";
        }
    echo "</table>"; //Close the table in HTML

    ?>`

Is it possbile to code the process once, without hard-coding anything, so it could be repeated as many times as needed?

Edit:

$x = 1; $query = "SELECT * FROMpersonsWHERE CODE_NO = '$x'"; $result = mysqli_query($con, $query);

"Is it possbile to code the process once, without hard-coding anything, so it could be repeated as many times as needed?" ... what exactly do you mean by that? — CD001
– CD001, Commented Feb 5, 2018 at 11:23
Yes. Do as you described what to do. Put the rows in an array, loop through the row-array and through the columns-array. — Jeff
– Jeff, Commented Feb 5, 2018 at 11:24
@CD001 These last lines might not be needed, as it seems they are rather confusing. You can edit them out if you like, since the problem is clearly described above. — user3725561
– user3725561, Commented Feb 5, 2018 at 11:31

Jeff · Accepted Answer · 2018-02-05 11:53:26Z

2

If I understood you correctly, this would do what you need:

function generateTableFromResult($result) {
   $html = "<table>";
   while($row = mysqli_fetch_array($result)) {
      foreach($row as $column => $value) {
        $html.="<tr><th>".$column."</th><td>".$value."</td></tr>";
      }
   }
   $html.="</table>";
   return $html;
}

// usage:
// ...
$result1 = mysqli_query($con, $query1);
echo generateTableFromResult($result1);

edited Feb 5, 2018 at 11:53

answered Feb 5, 2018 at 11:32

Jeff

6,9611 gold badge19 silver badges34 bronze badges

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

3 Comments

user3725561 Over a year ago

I am getting syntax error, unexpected 'while' (T_WHILE). If I remove it though your code seems to work fine.

user3725561 Over a year ago

0 1 CODE_NO 1 1 ΑΒΑΡΙΚΙΩΤΗΣ μΚΩΣΤΟΠΟΥΛΟΣ SURNAME ΑΒΑΡΙΚΙΩΤΗΣ μΚΩΣΤΟΠΟΥΛΟΣ is what your loop returns. Make it vertically

Jeff Over a year ago

well, if you don't want a row for each column (as you had in your example!) put the <tr> and </tr> outside the inner loop.

Daniel Paiva · Accepted Answer · 2018-02-05 12:28:19Z

0

You can have the excepted result. You will need to have a variable to be incremented with the values and returned at the end.

See my example:

// Check result
// Useful for debugging.
 if (!$result1) {
    $message  = 'Invalid query: ' . mysqli_error($con) . "\n";
    $message .= 'Whole query: ' . $query1;
    die($message);
}

$table = "<table>"; // table tag in the HTML
    while($row = mysqli_fetch_array($result1)){
    //Creates a loop to loop through results
    $table .="<tr>";
    $table .="  <th>CODE_NO:</th>";
    $table .="  <td>" . $row['CODE_NO'] . "</td>";
    $table .="</tr>";
    $table .="<tr>";
    $table .="    <th>FIRST_NAME:</th>";
    $table .="    <td>" . $row['FIRST_NAME'] . "</td>";
    $table .="</tr>";
    $table .="<tr>";
    $table .="    <th>SURNAME:</th>";
    $table .="    <td>" . $row['SURNAME'] . "</td>";
    $table .="</tr>";
    $table .="<tr>";
    $table .="   <th>Date of birth:</th>";
    $table .="   <td>" . $row['DOB'] . "</td>";
    $table .="</tr>";
    $table .="<tr>";
    $table .="    <th>Date of death:</th>";
    $table .="    <td> " . $row['DOD'] . "</td>";
    $table .="</tr>";
    }
$table .= "</table>"; //Close the table in HTML
echo $table;
?>`

This is a possible approach to get the result that you want. However you sent an array in the json format with the values and in the front end build the table using js.

edited Feb 5, 2018 at 12:28

answered Feb 5, 2018 at 11:24

Daniel Paiva

1212 silver badges10 bronze badges

7 Comments

user3725561 Over a year ago

I am not familiar with js, thank you for the answer though.

user3725561 Over a year ago

I also tried your code and even though I am not getting an error nothing shows in html.

Jeff Over a year ago

"nothing shows in html" - because of this $table = "</table>"; at the very end. should be $table .= "</table>";

Daniel Paiva Over a year ago

Yes. I have a misstake should be $table.="</table>"; insted of $table ="</table>";

user3725561 Over a year ago

You don't provide the increment bit. I 've added a variable $x = 1; where should I put the $x = $x +1; part in order to work?

|

Shaun O'Toole · Accepted Answer · 2020-09-07 22:19:52Z

0

Revised function generateTableFromResult from above that works correctly

function generateTableFromResult($result) {
   $html = "";
   $i = 0;
   $header = "<tr>";
   $body = "";
   while($row = mysqli_fetch_assoc($result)) {
      $i += 1;
      
      $body .= "<tr>\n";
      foreach($row as $column => $value) {
          if ($i == 1){
              $header .= "<th>$column</th>\n";
          }
         $body .= "<td>$value</td>\n";
      }
      $body .= "</tr>\n";
   }
   $header .= "</tr>\n";
   $html .= "<table> $header $body</table>";
   return $html;
}

answered Sep 7, 2020 at 22:19

Shaun O'Toole

11 bronze badge

Collectives™ on Stack Overflow

PHP function to loop SQL result and generate HTML tables

3 Answers 3

3 Comments

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related