I'm trying to display the information from the database to the html page but it is only displaying the message "connected successfully". The name of the database is "admin" and the table within that database is called "users". I have no idea how to get past the message and just display the table I have created.
Index page (index.php):
<?php
include_once('connection.php');
$query="select * from users";
$result=mysql_query($query);
?>
<!DOCTYPE html>
<html>
<title>
<head> Fetch Data Frome Database</head>
</title>
<body>
<table align="center" border="1px" style="width:250px; line-height: 30px;">
<tr>
<th colspan="4"><h2>Account Record</h2></th>
</tr>
<t>
<th>ID</th>
<th>Username</th>
<th>Password</th>
</t>
<?php
while($rows=mysql_fetch_assoc($result))
{
?>
<tr>
<td><?php echo $rows['ID'];?></td>
<td><?php echo $rows['username'];?></td>
<td><?php echo $rows['password'];?></td>
</tr>
<?php
}
?>
</table>
</body>
</html>
CONNECTION PAGE (connection.php):
<?php
//Include your own values for username, password and dbase name
$host = "localhost";
$username = "root";
$password = "root";
$dbname = "admin";
// Create connection
$conn = new mysqli($host, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
?>
Try putting all of your PHP into a single block
<?php
include_once('connection.php');
$query="select * from users";
$result=mysql_query($query);
while($rows=mysql_fetch_assoc($result))
{
echo "<tr>";
echo "<td>".$rows['ID']."</td>";
echo "<td>".$rows['username']."</td>";
echo "<td>".$rows['password']."</td>";
echo "</tr>";
}
?>
First problem is that your while loop is not set up correctly. The equals operator will pass the value of mysql_fetch_assoc($result) to $rows so this just wont do anything. Just think that a single equals sign does not mean equal it really means becomes. So rows becomes result. If you have two equals signs you are comparing the two values but this wont help you either. What your really need to do is first set rows to zero then say while the rows are less then the result echo the id echo the username echo the password and add 1 to rows.
So the while loop should look more like this
$rows = 0;
while($rows < mysql_fetch_assoc($result) ){
$rows++
?>
<tr>
<td><?php echo $rows['ID'];?></td>
<td><?php echo $rows['username'];?></td>
<td><?php echo $rows['password'];?></td>
</tr>
<?php
}
}
Assuming that $result=mysql_query($query) actually has a value this should work.
If you need to test if your result is good use PHP's array print to check print_r($result); This will echo all the results to the screen.
So I prefer using this method since its a little better than any other method I have tried. Plus I tuck the connection into a class and it makes it soo much easier to handle db connections.
DB CONNECTION Page:
class Database
{
private $host = "localhost";
private $db_name = "root";
private $username = "root";
private $password = "admin";
public $conn;
public function dbConnection()
{
$this->conn = null;
try
{
$this->conn = new PDO("mysql:host=" . $this->host . ";dbname=" . $this->db_name, $this->username, $this->password);
$this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $exception)
{
echo "Connection error: " . $exception->getMessage();
}
return $this->conn;
}
}
Now I create a class for the connection. I usually name it the site name.
require_once "db/config/page/location.php";
class localhost {
// Create conn to db
private $conn;
public function __construct()
{
$database = new Database();
$db = $database->dbConnection();
$this->conn = $db;
}
public function runQuery($sql)
{
$stmt = $this->conn->prepare($sql);
return $stmt;
}
}
Note: when you would like to use the connection on any page. You can do the following: EG Index Page:
<?php
include_once('class.php');
$lHost = new localhost();
//calling the users
$stmt = $lHost->runQuery('SELECT * FROM `users`');
$stmt->execute();
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>
<!DOCTYPE html>
<html>
<title>
<head> Fetch Data Frome Database</head>
</title>
<body>
<table align="center" border="1px" style="width:250px; line-height: 30px;">
<tr>
<th colspan="4"><h2>Account Record</h2></th>
</tr>
<t>
<th>ID</th>
<th>Username</th>
<th>Password</th>
</t>
<?php
foreach ($users as $key => $user) {
?>
<tr>
<td><?php echo $user['ID'];?></td>
<td><?php echo $user['username'];?></td>
<td><?php echo $user['password'];?></td>
</tr>
<?php
}
?>
</table>
</body>
</html>Feel free to ask me any questions.
</div>
Try this in your file. Use this file as one first to test.
<html>
<head>
<title>Selecting Table in MySQLi Server</title>
</head>
<body>
<?php
$dbhost = 'localhost:3306';
$dbuser = 'root';
$dbpass = '';
$dbname = 'admin';
$conn = mysqli_connect($dbhost, $dbuser, $dbpass,$dbname);
if(! $conn ) {
die('Could not connect: ' . mysqli_error());
}
echo 'Connected successfully<br>';
$sql = 'SELECT name FROM tutorials_inf';
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
while($row = mysqli_fetch_assoc($result)) {
echo "Name: " . $row["name"]. "<br>";
}
} else {
echo "0 results";
}
mysqli_close($conn);
?>
</body>
</html>
Note: You can test for errors using mysqli_error()such as echo mysqli_error(); after your select query.