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
– Logan Wayne, Commented Jun 22, 2015 at 2: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 — user557846
– user557846, Commented Jun 22, 2015 at 2:48
If you are learning it's better to switch from mysql_ functions to either mysqli or PDO asap. See why — piotrm
– piotrm, Commented Jun 22, 2015 at 2:50

Community · Accepted Answer · 2017-05-23 12:14:31Z

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.

richerlariviere · Accepted Answer · 2015-06-22 02:57:55Z

0

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.

answered Jun 22, 2015 at 2:57

richerlariviere

8193 gold badges14 silver badges29 bronze badges

1 Comment

Anthony Sawah Over a year ago

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.

Collectives™ on Stack Overflow

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

2 Answers 2

Note:

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Note:

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related