The following warnings occurred:
Warning [2] Undefined variable $unreadreports - Line: 26 - File: global.php(961) : eval()'d code PHP 8.1.2-1ubuntu2.14 (Linux)
File Line Function
/global.php(961) : eval()'d code 26 errorHandler->error
/global.php 961 eval
/showthread.php 28 require_once





× This forum is read only. As of July 23, 2019, the UserSpice forums have been closed. To receive support, please join our Discord by clicking here. Thank you!

  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Retrieving details from a Database to table
#1
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>
  Reply
#2
im using 4.2.2 btw (wen this post get old)
  Reply
#3
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:
}
  Reply
#4
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?
  Reply
#5
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:
}
  Reply
#6
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
  Reply
#7
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();
  Reply
#8
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:
}
?>
  Reply
#9
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 } ?>
  Reply
#10
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
  Reply


Forum Jump:


Users browsing this thread: 3 Guest(s)