I am trying to pull information from my SQL database into an html table. When I attempt this I get "0 results" however, I am able to connect to my DB, and also the SQL runs in MySQL Workbench completely fine. It appears that $result is not greater than 0 and I am not sure why that would be the case. It was working previously when I didn't include Joins in my SQL query, however like i said it works in MySQL workbench fine.
<html>
<head><title>Employee</title>
</head>
<pre>
<body>
<center><strong><a href="manager.html">Main Page</a></strong></center>
<?php
$servername = "localhost";
$username = "root";
$password = "root";
$conn = new mysqli($servername,$username,$password);
if($conn->connect_error){
die("connection failed: " . $conn->connect_error);
}
$sql = "SELECT first_name, last_name, email, address.address,
address.district, address.postal_code, address.phone, country.country
FROM staff
JOIN address ON staff.address_id = address.address_id
JOIN city ON address.city_id = city.city_id
JOIN country ON city.country_id = country.country_id";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
echo"<table>";
echo("<table border = \"1\">");
print("<tr>");
print("<th>First Name</th>");
print("<th>Last Name</th>");
print("<th>Email</th>");
print("<th>Address</th>");
print("<th>District</th>");
print("<th>Postal Code</th>");
print("<th>Phone</th>");
print("<th>Country</th>");
while($row = $result->fetch_assoc()) {
echo "<tr><td>" . $row["staff.last_name"]. "</td><td>" . $row["staff.first_name"].
"</td><td>" . $row["staff.email"]. "</td><td>" . $row["address.address"] . "</td><td>" .
$row["address.district"] . "</td><td>" . $row["address.postal_code"] . "</td><td>" .
$row["address.phone"] . "</td><td>" . $row["country.country"] . "</td></tr>";
}
} else {
echo "0 results";
}
echo"</table>";
$conn->close();
?>
</body>
</pre>
</html>
The mysqli_connect() function has 4 parameters, the 4th is the name of your database
<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = 'Something';
$conn = new mysqli($servername,$username,$password, $dbname);
If in doubt, See the manual
You should also get into the habit of testing if a database access has worked or not before continuing, the error message is normally very good at helping you debug your code
$result = $conn->query($sql);
if (!$result) {
echo $conn->error;
exit;
}
adding php code that used for to get data from the table embed inside html file is not a good thing.so it's better if you're using separate php file to retrieve data from the table in a way that you can protect your database's table information.
I used a php file to retrieve data and converted that result as json data output in php file.that json output read from 'setData.js' file and that data set for the table inside the html file.
i added that example into my github,so that you can refer it. here's my github repository.