Retrieving details from a Database to table

[sire]

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>

[sire]

im using 4.2.2 btw (wen this post get old)

[faguss]

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:

}

[sire]

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?

[faguss]

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:

}

[sire]

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

[faguss]

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();

[sire]

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:

}

?>

[mudmin]

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 } ?>

[sire]

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