Hello I have the following script
show.php
<?php
include "db.php"; // <== db connection
include "sys.php"; //<== main system
// statment to show $result as table
?>
I need the correct statement that reads my table data and shows it as table
I have 1 table with 7 rows:
username| name| age| etc
They are about 500 records that needs to be displayed 20 per page
How do I build the correct statement ?
First you need to use mysql or mysqli (recommended) to fetch the data from the database. As you would want to limit the number of items to 20 it would be wise to add a limit condition in your sql instead of filtering away items later, this would be essential for performance. Lastly simply loop through the data generating a new table row for each row in the database.
<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "my_db");
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
// remember to sanitize potential user input
$start = (int) (!empty($_GET['start']) ? $_GET['start'] : 0);
$end = (int) (!empty($_GET['end']) ? $_GET['end'] : 20);
$query = "SELECT username, name, age FROM users limit $start, $end";
if ($stmt = $mysqli->prepare($query)) {
/* execute statement */
$stmt->execute();
/* bind result variables */
$stmt->bind_result($username, $name, $age);
?>
<table>
<tr>
<th>Username</th>
<th>Name</th>
<th>Age</th>
<th></th>
<th></th>
<th></th>
<th></th>
</tr>
<?php
/* fetch values */
while ($stmt->fetch()) {
// do something with the fetched variables here
?>
<tr>
<td><?php echo htmlentities($username); ?></td>
<td><?php echo htmlentities($name); ?></td>
<td><?php echo htmlentities($age); ?></td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
<?php
}
/* close statement */
$stmt->close();
?></table><?php
}
/* close connection */
$mysqli->close();
?>
More information would be required (the contects of db.php and what sys.php is used for).
Assuming db.php contains a class for accessing your database, you need to create an db instance and initialize it with your connection information . The following does not necessarily reflect your setup or db class structure.
$db = new Db();
$host = "localhost";
$username = "bob";
$dbname = "bobs_db";
$password = "opensesame";
$db->connect( $host, $dbname, $username, $password );
If succesful you then run a query:
$query = "SELECT username, name, age FROM users";
$db->query( $query );
$users = $db->fetch_all();
// Go through users using a foreach
For displaying 20 users per page you need to do what's called pagination which makes use of MySQL's LIMIT keyword. Will add more to this answer as soon as you give more information.
