displaying a table and hear automatically

[garynobles]

I've been coding in php for a while but I always define the tables (and headers) in hard code. I'm wanting to automate the creation of a table (table selected via a GET request - select * from $_GET["table"] - not important for now). I've been reading that you need to first count the columns and get them as row 0 then fetch the data using an auto increment for the rows i++. I learn from examples, and I just can't find anything I can manipulate, this surely is a common task? I'm using PDO (postgresql) so to get the code under way:

$sql = "SELECT * FROM rsp.bibliografia"; //will be the result of a get request
$result = $conn->prepare($sql); 
$result->execute();

Any help is greatly appreciated.

[Barand]

Are you looking for something like this?

$sql = "SELECT whatever FROM wherever";
$res = $db->query($sql);
$row = $res->fetch(PDO::FETCH_ASSOC);
echo "<table border='1'><tr><th>";
echo join('</th><th>', array_keys($row)) . "</th></tr>\n";
do {
    echo "<tr><td>"  . join('</td><td>', $row) . "</td></tr>\n";
} while ($res->fetch(PDO::FETCH_ASSOC));
echo "</table>\n";

[requinix]

Woah now, back up a minute.

(table selected via a GET request - select * from $_GET["table"] - not important for now)

On the contrary: it's important enough that it needs to be addressed now, not later.

 

There is no good reason why you should ever have to take user input as the name of a database table. Tables should not be created or used like that. If you have a specific set of tables that does not change and the user chose a table to get data from, that's one thing and you're not using the user input as the table name but as an identifier that happens to be the same value, but if you don't know ahead of time what sort of table a user may choose then there's a problem.

 

You also said

automate the creation of a table

but it's not clear to me whether you mean a database table or an HTML table.

 

So here's a quick question to answer: what tables can the user choose from? With a fixed list I would say the best thing is to keep a mapping of columns to labels, like

"id" => "Bibliography ID",
"authorname" => "Author",
"pubyear" => "Year",

and you vary the SQL to suit the table you're using (eg, SELECTing different columns or using a specific sort order). You can do the mapping in the query too like

SELECT id AS `Bibliography ID`, authorname AS `Author`, ...

and get the labels automatically.

[garynobles]

In response to Barand, great! Expect it repeats the first row repeatedly for the number of rows, rather than displaying all the data e.g. 

ID_SITO ID_UNITA ID_UR_A DAT1 DAT2 RESPONSABILE ID

S1 S1-1   -350 220 Sforza 4

S1 S1-1   -350 220 Sforza 4

S1 S1-1   -350 220 Sforza 4

... it continues for 500+ rows

 

As for requinix, I understand your concerns, the users are not creating tables, I want to display the tables via php/html.

So they cannot make new tables, I'm using hyperlinks with GET rather than POST, these are coming from a specific schema, so it can't be manipulated to access system tables.

Edited September 28, 2017 by garynobles

[Barand]

Oops!

 

That should have been

while ($row = $res->fetch(PDO::FETCH_ASSOC));
       ˆˆˆˆˆˆ

[garynobles]

Of course! I was staring at that line but I couldn't see it.

 

One additional thing, I feel like I'm being cheeky...

 

On one of the tables I get:

 

Warning: join(): Invalid arguments passed in /var/www/rhp.archaeolabs.nl/tables.php on line 42

 

Warning: array_keys() expects parameter 1 to be array, boolean given in /var/www/rhp.archaeolabs.nl/tables.php on line 40

Warning: join(): Invalid arguments passed in /var/www/rhp.archaeolabs.nl/tables.php on line 40

 

 

 

line 40 = 

echo join('</th><th>', array_keys($row)) . "</th></tr>\n";

 

line 42 =

 echo "<tr><td>"  . join('</td><td>', $row) . "</td></tr>\n";

 

so somewhere I have a boolean - I have some data stored as double format, would that be it, is there a way to code around this or should I change the 'double' to something else like decimal?

[Barand]

Looks like no rows were returned and the $row variable contains false after that first call.

[garynobles]

So I guess I need an if statement, how close am I with this?

if $row!=0 {
}else{
echo"empty table";}

location in the code

$sql = "SELECT whatever FROM wherever";
$res = $db->query($sql);

//here should be the start of an if statement: if $row!=0 {}else{echo"empty table";}

while ($row = $res->fetch(PDO::FETCH_ASSOC));
echo "<table border='1'><tr><th>";
echo join('</th><th>', array_keys($row)) . "</th></tr>\n";
do {
    echo "<tr><td>"  . join('</td><td>', $row) . "</td></tr>\n";
} while ($res->fetch(PDO::FETCH_ASSOC));
echo "</table>\n";

[Barand]

You do not want that extra while()

 

$sql = "SELECT ...";
$res = $db->query($sql);
$row = $res->fetch(PDO::FETCH_ASSOC);
if (!$row) {
    echo "No data";
} else {
    echo "<table border='1'><tr><th>";
    echo join('</th><th>', array_keys($row)) . "</th></tr>\n";
    do {
        echo "<tr><td>"  . join('</td><td>', $row) . "</td></tr>\n";
    } while ($row = $res->fetch(PDO::FETCH_ASSOC));
    echo "</table>\n";
}

[mac_gyver]

i recommend that you simply fetch all the data into a php array variable. see the ->fetchAll() method.

 

you can then simply test what's in that array in the presentation logic. if it's empty, there were no rows matched by the query. if it's not empty, you can get the keys from the zero'th element. then simply loop over the array to produce the data output.