Retrieving details from a Database to table

The following warnings occurred:

Warning [2] Undefined variable $unreadreports - Line: 26 - File: global.php(961) : eval()'d code PHP 8.2.25 (Linux)

File	Line	Function
/global.php(961) : eval()'d code	26	errorHandler->error
/global.php	961	eval
/printthread.php	16	require_once

Warning [2] Undefined property: MyLanguage::$archive_pages - Line: 2 - File: printthread.php(287) : eval()'d code PHP 8.2.25 (Linux)

File	Line	Function
/printthread.php(287) : eval()'d code	2	errorHandler->error
/printthread.php	287	eval
/printthread.php	117	printthread_multipage

Retrieving details from a Database to table - Printable Version

+- UserSpice (https://userspice.com/forums)
+-- Forum: Miscellaneous (https://userspice.com/forums/forumdisplay.php?fid=28)
+--- Forum: Documentation (https://userspice.com/forums/forumdisplay.php?fid=30)
+--- Thread: Retrieving details from a Database to table (/showthread.php?tid=631)

Pages: 1 2 3

Retrieving details from a Database to table - sire - 07-05-2017

im trying to Retrieve details from customers Database and display a simple table
i spent a few weeks on trying to make it work with the standards for userspice
(thus im asking help)

Code:
<?php

Code:
$user = 'admin';

Code:
$pass = '1admin';

Code:
$db = new PDO( 'mysql:host=localhost;dbname=my_database', $user, $pass );

Code:
$sql = "SELECT * FROM customers";

Code:
$query = $db->prepare( $sql );

Code:
$query->execute();

Code:
$results = $query->fetchAll( PDO::FETCH_ASSOC );

Code:
?>

Code:
<table class="table">

Code:
<tr>

Code:
<th>ID</th>

Code:
<th>First Name</th>

Code:
<th>Surname</th>

Code:
<th>Address</th>

Code:
<th>Email</th>

Code:
</tr>

Code:
<?php foreach( $results as $row ){

Code:
echo "<tr><td>";

Code:
echo $row['id'];

Code:
echo "</td><td>";

Code:
echo $row['first_name'];

Code:
echo "</td><td>";

Code:
echo $row['surname'];

Code:
echo "</td><td>";

Code:
echo $row['address'];

Code:
echo "</td><td>";

Code:
echo $row['email'];

Code:
echo "</td>";

Code:
echo "</tr>";

Code:
}

Code:
?>

Code:
</table>

Retrieving details from a Database to table - sire - 07-05-2017

im using 4.2.2 btw (wen this post get old)

Retrieving details from a Database to table - faguss - 07-05-2017

Code:
$db->findAll("customers");

Code:
if (!$db->error()  &&  $db->count()>0) {

Code:
$customers = $db->results(true);

Code:
echo "<TABLE CLASS=\"table\"><TR>";

Code:
foreach ($customers[0] as $key=>$value)

Code:
echo "<TH>$key</TH>";

Code:
echo "</TR>";

Code:
foreach ($customers as $customer) {

Code:
echo "<TR>";

Code:
foreach ($customer as $value)

Code:
echo "<TD>$value</TD>";

Code:
echo "</TR>";

Code:
}

Code:
echo "</TABLE>";

Code:
}

Retrieving details from a Database to table - sire - 07-06-2017

thank you faguss!!
what if i had some column's that i want to show and the others hide and some only display first half of text?

Retrieving details from a Database to table - faguss - 07-06-2017

Code:
$db->findAll("customers");

Code:
$exclude = ["id","lname","password"];

Code:
$halve   = ["fname"];

Code:
if (!$db->error() && $db->count()>0) {

Code:
$customers = $db->results(true);

Code:
echo "<TABLE CLASS=\"table\"><TR>";

Code:
foreach ($customers[0] as $key=>$value)

Code:
if (!in_array($key,$exclude))

Code:
echo "<TH>$key</TH>";

Code:
echo "</TR>";

Code:
foreach ($customers as $customer) {

Code:
echo "<TR>";

Code:
foreach ($customer as $key=>$value)

Code:
if (!in_array($key,$exclude)) {

Code:
echo "<TD>";

Code:
if (in_array($key,$halve))

Code:
echo substr($value, 0, strlen($value)/2);

Code:
else

Code:
echo "$value";

Code:
echo "</TD>";

Code:
}

Code:
echo "</TR>";

Code:
}

Code:
echo "</TABLE>";

Code:
}

Retrieving details from a Database to table - sire - 07-09-2017

thanks for your help how can i use the

Code:
<?php echo $value['customer_id'] ?>

and for other column's names aswell

how can i just have a list of column that i want so i dont have to go back to the pages and edit each page and put in exclude column

sorry im some what new the the PDO stuff as i am working on a mobile bootstrap table list

thanks
aaron Smile

Retrieving details from a Database to table - faguss - 07-09-2017

Not sure what you mean. Something like that?

Code:
<?php echo $customers[0]['customer_id'] ?>

If you're reusing code then you could :

copy the table code to a separate file and use
Code:
include "separate_file.php"
or enclose the code into a function which you'd call when you need it

Code:
function get_customers_table() {

Code:
$html = "";

Code:
$db->findAll("customers");

Code:
$exclude = ["id","lname","password"];

Code:
$halve = ["fname"];

Code:
if (!$db->error() && $db->count()>0) {

Code:
$customers = $db->results(true);

Code:
$html .= "<TABLE CLASS=\"table\"><TR>";

Code:
foreach ($customers[0] as $key=>$value)

Code:
if (!in_array($key,$exclude))

Code:
$html .= "<TH>$key</TH>";

Code:
$html .= "</TR>";

Code:
foreach ($customers as $customer) {

Code:
$html .= "<TR>";

Code:
foreach ($customer as $key=>$value)

Code:
if (!in_array($key,$exclude)) {

Code:
$html .= "<TD>";

Code:
if (in_array($key,$halve))

Code:
$html .= substr($value, 0, strlen($value)/2);

Code:
else

Code:
$html .= "$value";

Code:
$html .= "</TD>";

Code:
}

Code:
$html .= "</TR>";

Code:
}

Code:
$html .= "</TABLE>";

Code:
}

Code:
return $html

Code:
}

Code:
echo get_customers_table();

Retrieving details from a Database to table - sire - 07-09-2017

i mean that id like to echo each value independently

like this way of doing then i can edit pur column

Code:
<?php foreach( $results as $row ){

Code:
echo "<tr><td>";

Code:
echo $row['id'];

Code:
echo "</td><td>";

Code:
echo $row['first_name'];

Code:
echo "</td><td>";

Code:
echo $row['surname'];

Code:
echo "</td><td>";

Code:
echo $row['address'];

Code:
echo "</td><td>";

Code:
echo $row['email'];

Code:
echo "</td>";

Code:
echo "</tr>";

Code:
}

Retrieving details from a Database to table - mudmin - 07-09-2017

So basically, if you do

Code:
$query = $db->query("SELECT * FROM customers")->results();

Then you can do

Code:
foreach ($customers as $c){ ?>

//any column you want to echo out you can just do so in the format

Code:
<?=$c->columnName?>

Code:
<?php } ?>

Retrieving details from a Database to table - sire - 07-11-2017

i got the code to the right pleases and its not showing up
i have been using "admin_users.php" as a reference

Code:
//PHP Goes Here!

Code:
$query = $db->query("SELECT * FROM customers")->results();

and

Code:
<?php

Code:
foreach ($customers as $c){ ?>

Code:
<tr>

Code:
<th><?=$c->id?></th>

Code:
<th><?=$c->name?></th>

Code:
</tr>

Code:
<?php } ?>

btw thank you @faguss @mudmin for helping me