Writing the attributes of a database in PHP

Question

I am writing an application in which user can enter a database name and I should write all of its contents in table with using PHP.I can do it when I know the name of database with the following code.

$result = mysqli_query($con,"SELECT * FROM course");

echo "<table border='1'>
<tr>
<th>blablabla</th>
<th>blabla</th>
<th>blablabla</th>
<th>bla</th>
</tr>";

while($row = mysqli_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['blablabla'] . "</td>";
  echo "<td>" . $row['blabla'] . "</td>";
  echo "<td>" . $row['blablabla'] . "</td>";
  echo "<td>" . $row['bla'] . "</td>";
  echo "</tr>";
  }
echo "</table>";

In this example I can show it since I know the name of table is course and it has 4 attributes.But I want to be able to show the result regardless of the name the user entered.So if user wants to view the contents of instructors there should be two columns instead of 4.How can I accomplish this.I get the table name with html.

Table:<input type="text" name="table">

Edit:Denis's answer and GrumpyCroutons' answer are both correct.You can also ask me if you didnt understand something in their solution.

Kinda.There is a page which is created with html which gets the input from the guest.I have a mysql database with all the tables in it and I try to show it with using PHP — Curriculaa, Apr 29 '16 at 13:38
you're asking for trouble if you don't use proper measures to protect your database here. Just a quick sidenote about that. — Funk Forty Niner, Apr 29 '16 at 13:38
I am not trying to secure anything since this is a programming question in a DB book,thanks for the info — Curriculaa, Apr 29 '16 at 13:39
@Curriculaa Can you clarify, what should be the input and output? — Praveen Kumar Purushothaman, Apr 29 '16 at 13:39
they obviously left that part out. You should be using a dropdown select with different values based on what you want to offer them to see. This of course, is *"my 2 cents"*. Edit: I think you're new at MySQL, am I right? If so, you've a lot to learn and about SQL injection. http://stackoverflow.com/q/60174/ - I hope that book covers that. If it doesn't, it's time to get the 21st century edition ;-) — Funk Forty Niner, Apr 29 '16 at 13:40
Yeah you are right.I think I can clarify it like this.Input is what a guest enters which is the name of a table in database.Output should be a table consisting of all the tuples in that table.I Can solve the problem by doing if tablename=instructor while loop write all of this,if tablename=course while loop write all of this but that would be too long in a big database.So I am guessing there should be a loop which can show all the tuples for a table — Curriculaa, Apr 29 '16 at 13:44
which by that comment ^ is similar to what you already asked http://stackoverflow.com/questions/36925780/displaying-a-random-databasemysql-to-html-in-php - correct? But using a different MySQL API. — Funk Forty Niner, Apr 29 '16 at 13:45
Yes it is similar but I think this question explains my problem in a better way — Curriculaa, Apr 29 '16 at 13:46
I guess there are a couple of ways to approach this. If the entire schema is known and you just let the user select the table, then you can safely do that by comparing the input to a known set of tables and, depending on the table selected, run code to display that specific table's contents. If the schema is *not* known then you can query the system tables to determine the schema and use that data in your logic. You may even be able to skip that and just loop through the items returned by `SELECT *` (a nested loop overall), but that wouldn't give you column headings. — David, Apr 29 '16 at 13:47
To (hopefully) answer this, you need to use what's called a `WHERE` clause. http://dev.mysql.com/doc/refman/5.7/en/where-optimizations.html --- http://dev.mysql.com/doc/refman/5.7/en/select.html — Funk Forty Niner, Apr 29 '16 at 13:47
David your first answer is possible and I can implement it like that but the problem is the following.Assume that you have 1000 tables.Then you have to write specific if statements for all of them — Curriculaa, Apr 29 '16 at 13:50
or as you stated in your comment *"if tablename=course while loop"* - Yes, `while(condition is true) { if(row=='xxx') { echo that row } }` type of thing. — Funk Forty Niner, Apr 29 '16 at 13:50
@Curriculaa: Querying the system tables from the schema would result in less code, but more complex code. It's a trade-off. Perhaps one that's worth it for 1000 tables though. — David, Apr 29 '16 at 13:53
The answer which is given by Denis does the functionality I want but it writes every attribute 2 times.It lists all ids with a header named id and with a header named 0.You can look at that code to see what I want to accomplish — Curriculaa, Apr 29 '16 at 14:03

GrumpyCrouton · Accepted Answer · 2016-04-29T14:14:01.290

Quickly wrote this up, commented it (This way you can easily learn what's going on, you see), and tested it for you.

    <form method="GET">
        <input type="text" name="table">
    </form>

    <?php


    //can be done elsewhere, I used this for testing. vv
    $config = array(
         'SQL-Host' => '',
         'SQL-User' => '',
         'SQL-Pass' => '',
         'SQL-Database' => ''
    );
    $con = mysqli_connect($config['SQL-Host'], $config['SQL-User'], $config['SQL-Pass'], $config['SQL-Database']) or die("Error " . mysqli_error($con));
    //can be done elsewhere, I used this for testing. ^^


    if(!isSet($_GET['table'])) { //check if table choser form was submitted.
        //In my case, do nothing, but you could display a message saying something like no db chosen etc.
    } else {

    $table   = mysqli_real_escape_string($con, $_GET['table']); //escape it because it's an input, helps prevent sqlinjection.

    $sql     = "SELECT * FROM " . $table; // SELECT * returns a list of ALL column data
    $sql2    = "SHOW COLUMNS FROM " . $table; // SHOW COLUMNS FROM returns a list of columns

    $result  = mysqli_query($con, $sql);
    $Headers = mysqli_query($con, $sql2);

    //you could do more checks here to see if anything was returned, and display an error if not or whatever.

    echo "<table border='1'>";
    echo "<tr>"; //all in one row

    $headersList = array(); //create an empty array

    while($row = mysqli_fetch_array($Headers)) { //loop through table columns
         echo "<td>" . $row['Field'] . "</td>"; // list columns in TD's or TH's.
         array_push($headersList, $row['Field']); //Fill array with fields
    } //$row = mysqli_fetch_array($Headers)

    echo "</tr>";

    $amt = count($headersList); // How many headers are there?

    while($row = mysqli_fetch_array($result)) {
         echo "<tr>"; //each row gets its own tr
         for($x = 1; $x <= $amt; $x++) { //nested for loop, based on the $amt variable above, so you don't leave any columns out - should have been <= and not <, my bad
             echo "<td>" . $row[$headersList[$x]] . "</td>"; //Fill td's or th's with column data
        } //$x = 1; $x < $amt; $x++
             echo "</tr>";
    } //$row = mysqli_fetch_array($result)

    echo "</table>";
    }
    ?>

I tested your answer.It writes all the data correctly but it displays the values of the last attribute as empty. — Curriculaa, Apr 29 '16 at 14:09
Oh, my bad I know why, I started the array at 1. Try again please — GrumpyCrouton, Apr 29 '16 at 14:12
I figured it out as well you dont need to edit it.Your answer is also correct and gave me a window to see things differently.Thanks for the help.I dont know if I can accept both your and Denis's answer at the same time — Curriculaa, Apr 29 '16 at 14:13
You can only accept one, I would accept the one that was most helpful to you. Whichever that may be. :) — GrumpyCrouton, Apr 29 '16 at 14:14
@Curriculaa Also, since you are a bit new around here, I just wanted to say I did need to edit it - if I didn't future readers of the question may have issues. Thank you for marking my answer! :) — GrumpyCrouton, Apr 29 '16 at 14:17

Denis Alimov · Answer 2 · 2016-04-29T14:11:19.427

1

$tablename = $_POST['table'];
$result = mysqli_query($con,"SELECT * FROM $tablename");

$first = true;

while($row = mysqli_fetch_assoc($result))
{
  if ($first)
  {
    $columns = array_keys($row);
    echo "<table border='1'>
      <tr>";
     foreach ($columns as $c)
     {
       echo "<th>$c</th>";
     }

     echo "</tr>";
     $first = false;
  }
  echo "<tr>";
  foreach ($row as $v)
  {
   echo "<td>$v</td>";
  }
  echo "</tr>";
}
echo "</table>";

edited Apr 29 '16 at 14:11

answered Apr 29 '16 at 13:53

Denis Alimov

2,861
1
18
38

I checked the code.This is what I wanted but it writes every attribute two times. – Curriculaa Apr 29 '16 at 13:58
Sure, just get the idea that from first row you should take rows keys to fetch table columns. If you remove this from while loop, just take first row, fetch table columns and dont forget to write first row values. – Denis Alimov Apr 29 '16 at 14:02
try again please, I edited my answer to mysqli_fetch_assoc – Denis Alimov Apr 29 '16 at 14:11
I solved it thanks for the help.It was obvious that it was caused because of array since it listed te attributes as 0,1,2 etc.Forgive me I forgot to wrote that in here – Curriculaa Apr 29 '16 at 14:14

score -1 · Answer 3 · edited Apr 29 '16 at 15:05

<?php 
  $table_name = do_not_inject($_REQUEST['table_name']);
  $result = mysqli_query($con,'SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_NAME='. $table_name);
?>
<table>
  <?php
    $columns = array();
    while ($row = mysql_fetch_assoc($result)){
      $columns[]=$row['COLUMN_NAME'];
      ?>
        <tr><th><?php echo $row['COLUMN_NAME']; ?></th></tr>
      <?php
    }

    $result = mysqli_query($con,'SELECT * FROM course'. $table_name);
    while($row = mysqli_fetch_assoc($result)){
      echo '<tr>';
      foreach ($columns as $column){
        ?>
          <td><?php echo $row[$column]; ?></td>
        <?php
      }
      echo '</tr>';
    }
  ?>
</table>

Your code is messy, lacks documentation, and doesn't work properly. — GrumpyCrouton, Apr 29 '16 at 14:24

Writing the attributes of a database in PHP

3 Answers3