PHP MySQL Connection Issue: Unable to Retrieve Data from Database

[mark01]

I'm facing a problem while trying to establish a connection between PHP and MySQL to retrieve data from a database. I'm using the mysqli extension for PHP, but I'm not able to fetch the expected results. Here's a simplified version of my code:

`<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "mydatabase";

// Create connection
$conn = new mysqli($servername, $username, $password, $database);

// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

// Attempt to fetch data
$query = "SELECT * FROM mytable";
$result = $conn->query($query);

if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
echo "ID: " . $row["id"] . " - Name: " . $row["name"] . "<br>";
}
} else {
echo "No data found.";
}

$conn->close();
?>
`
When I run this script, I get the "No data found." message even though I have data in the mytable table. I've confirmed my database credentials and table name, but I can't figure out what's causing this issue.

Could someone please review my code and provide insights into what might be causing this problem? Is there a common mistake I might be overlooking or any debugging steps I should take to diagnose the issue? Any help would be greatly appreciated. Thank you!

[pbismad]

mark01 a simplified version of my code:

The things you didn't show could be causing the problem. When you have code that doesn't work, you need to post the actual code.

Do you have php's error_reporting set to E_ALL and display_errors set to ON, preferably in the php.ini on your development system, so that php will help you by reporting and displaying all the errors it detects? Stop and start your web server to get any changes made to the php.ini to take effect and use a phpinfo() statement in a .php script file to confirm that the settings actually were changed.

Next, you always need error handling for statements that can fail. For database statements that can fail - connection, query, prepare, exec, and execute, the simplest way of adding error handling, without adding logic at each statement, is to use exceptions for errors (this is the default setting now in php8+), and in most cases simple let php catch and handle any database statement exception, where php will use its error related settings (see the paragraph above) to control what happens with the actual error information (database statement errors will 'automatically' get displayed/logged the same as php errors.)

Lastly, if there are no php or database statement errors (see the two paragraphs above), a likely cause is you have more than one database and you have selected one that doesn't have the expected data in it.

[Weedpacket]

pbismad

mark01 a simplified version of my code:

The things you didn't show could be causing the problem. When you have code that doesn't work, you need to post the actual code.

Or at least confirm that the "simplified" code still displays the same problem before posting it. Simplification helps if it means we don't have to dig through a mess of irrelevancies along the way.

[RayPooley]

mark01
The first thin I would ask is what does http://localhost/phpinfo.php reveal?
Are the mysqlnd and pdo_mysql modules being loaded?

I have all of my database query code in a class.

Here is the connection method.

function getconn(){
return mysqli_connect($this->dbhost, $this->dbuser, $this->dbpass, $this->dbname);
}

This is an example of a transaction call method.
This calls a stored procedure named "get_person_by_name" which requires 1 parameter but
would in your case containg a simple SQL statement "SELECT * FROM mytable".

function get_person_id_by_name(string $name)
{
    $query = mysqli_query($this->getconn(), "call get_person_by_name('" . $name ."')");
    return mysqli_fetch_array($query)[0];
}

This example returns the data as an array, Hence the mysqli_fetch_array($query)[0] index.
The ID column is th first column in the resultset returned.

You are using ASSOC which returns column names. These names are case sensitive btw,

[NogDog]

[Edit: Guess I was typing while pbismad was replying. 🙂]

Nothing is immediately jumping out at me (but I normally use PostgreSQL, not MySQL, so who knows?), so I might start by seeing if PHP can tell me anything:

<?php
error_reporting(E_ALL);
ini_set('display_errors', true); // set to false in production
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
// ...rest of code...

[mark01]

Thanks everyone for the support.