How do I display my mysql table column headers in my php/html output?

Question

2 Questions...

Scenario:

I would like to query my database table via a form and then display the results that occur (if there are results) and my current situation is that it does work but it clears the form completely and leaves my to an empty page with just the results if that makes sense. I would like to see my results on the same page that I'm entering the form data in. I would like to see my data in a table based format, kind of like this...

|Name   | Age|
|-------|----|
|Anthony| 20 |

I have a separate design to make it look pretty later on. This is my setup so far....

displayform.html:

<html>
<form method="post" name="display" action="display.php" />
Enter the name you like to display the data from MySQL:<br>
<input type="text" name="name" />
<input type="submit" name="Submit" value="display" />
</form>
</html>

and display.php

<?php
mysql_connect("localhost", "toor", "FakePassword") or die("Connection Failed");
mysql_select_db("FakeDatabase")or die("Connection Failed");
$name = $_POST['name'];
$query = "select name, age from test WHERE name = '$name'";
$result = mysql_query($query);
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
    echo "<tr>";
echo "<td>".$line['name']."</td>";
echo "<td>".$line['age']."</td>";
echo "<br>\n";
echo "</tr>";
}
?>

Correct me if I'm wrong. When a user submits the form, it will show results from the database, but you still want to have form above? — Logan Wayne, Jun 22 '15 at 02:46
no need for ajax you just need both the form and php on one page, submit the form to the same page, run the php as needed — , Jun 22 '15 at 02:48
If you are learning it's better to switch from mysql_ functions to either mysqli or PDO asap. [See why](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) — piotrm, Jun 22 '15 at 02:50

score 2 · Accepted Answer · edited May 23 '17 at 12:14

Note:

You can just make a single file out of it to achieve your wanted output
Use mysql_real_escape_string() to sanitize the passed-on value to prevent SQL injections
You should use mysqli_* instead of the deprecated mysql_* API

Form them in a single file like this (display.php):

<html>
  <form method="post" name="display" action="display.php" />
  Enter the name you like to display the data from MySQL:<br>
    <input type="text" name="name" />
    <input type="submit" name="Submit" value="display" />
  </form>

  <?php
    mysql_connect("localhost", "toor", "FakePassword") or die("Connection Failed");
    mysql_select_db("FakeDatabase")or die("Connection Failed");

    if(!empty($_POST["name"])){ /* WE ADD THIS PART SO WHEN NO PASSED DATA IS FOUND, IT WILL NOT GENERATE "UNIDENTIFIED VARIABLE" ERROR */

      $name = mysql_real_escape_string($_POST['name']); /* SANITIZE THE VALUE OF THIS VARIABLE */
      $query = "select name, age from test WHERE name = '$name'";
      $result = mysql_query($query);
      echo "<table>";
      while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
        echo "<tr>";
        echo "<td>".$line['name']."</td>";
        echo "<td>".$line['age']."</td>";
        echo "<br>\n";
        echo "</tr>";
      } /* END OF WHILE LOOP */
      echo "</table>";
    } /* END OF NOT EMPTY NAME */
  ?>

</html>

If you're interested with mysqli_*, I recommend that you use prepared statement. Just replace your php part to this:

  <?php

    /* ESTABLISH YOUR CONNECTION FIRST */
    $con = new mysqli("localhost", "toor", "FakePassword", "FakeDatabase");
    if (mysqli_connect_errno()) {
      printf("Connect failed: %s\n", mysqli_connect_error());
      exit();
    }

    if(!empty($_POST["name"])){

      if($stmt = $con->prepare("SELECT name, age FROM test WHERE name = ?")){
        $stmt->bind_param("s",$_POST["name"]); /* BIND THE PASSED-ON VALUE TO THE QUERY */
        $stmt->execute(); /* EXECUTE THE QUERY */
        $stmt->bind_result($name,$age); /* BIND THE RESULT TO THESE VARIABLES */
        echo "<table>";
        while($stmt->fetch()){ /* FETCH ALL RESULTS */
          echo "<tr>";
          echo "<td>".$name."</td>";
          echo "<td>".$age."</td>";
          echo "<br>\n";
          echo "</tr>";
        } /* END OF WHILE LOOP */
        echo "</table>";
        $stmt->close();
      } /* END OF PREPARED STATEMENT */

    } /* END OF NOT EMPTY NAME */
  ?>

Thank you so much for your input Logan. I used the mysqli setup better, and it is better definitely for security purposes. Thank you so much also for your comments those help me a lot. — Anthony Sawah, Jun 22 '15 at 16:23

score 0 · Answer 2 · answered Jun 22 '15 at 02:57

You must use this query if you want the columns names: SHOW COLUMNS FROM test

This is a working example.

Also, be careful with SQL injection. And if you didn't receive it, my web server thrown a warning into my screen:

The mysql extension is deprecated and will be removed in the future: use mysqli or PDO instead

<?php
mysql_connect("localhost", "toor", "FakePassword") or die("Connection Failed");
mysql_select_db("FakeDatabase")or die("Connection Failed");


$query = "SHOW COLUMNS FROM test";
$result = mysql_query($query);
echo "<tr>";
while ($header = mysql_fetch_array($result, MYSQL_ASSOC)) {
    echo "<td>". $header['Field']."</td>";
}
echo "<br>\n";
echo "</tr>";

$name = $_POST['name'];
$query = "select name, age from test WHERE name = '$name'";
$result = mysql_query($query);
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
    echo "<tr>";
    echo "<td></td>";
    echo "<td>".$line['name']."</td>";
    echo "<td>".$line['age']."</td>";
    echo "<br>\n";
    echo "</tr>";
}
?>

EDIT

I answered the main question as described in the question title. Next time, you should post one question... per question. Good luck.

Thank you a lot for your input, especially with the SHOW COLUMNS tip. I thought that the second question wasn't big so I put it all in one. It would kind've be a hassle for everyone to answer those small questions. — Anthony Sawah, Jun 22 '15 at 16:24

How do I display my mysql table column headers in my php/html output?

2 Answers2

Note: