[MariaDB] "This page isn’t working"?

[Shohreh]

Hello,

This is a newbie question.

On a test host at home, I first installed nginx and PHP-FPM, and made sure phpinfo(); worked.

Next, I installed MariaDB and imported a database.

Finally, I tried connecting to it from PHP… and it failed with:
 

Quote

 

This page isn’t working

192.168.0.7 is currently unable to handle this request.
HTTP ERROR 500

 

Here's the script I used:

<?php
// Database settings
$db="mydb";
$dbhost="localhost";
$dbport=3306;
$dbuser="root";
$dbpasswd="test";

$pdo = new PDO('mysql:host='.$dbhost.';port='.$dbport.';dbname='.$db.'', $dbuser, $dbpasswd);
$pdo->exec("SELECT * FROM mytable" );

$res = $pdo->fetchAll();
foreach ( $res as $row ) {
    echo $row['id'];
}

$pdo = null;
?>

 

Any idea why? Could it be I'm missing a module in PHP? What should I check?

Thank you.

[Barand]

First step is to turn on PDO exception reporting and ensure PHP reporting is on.

[Shohreh]

Thanks. No change:

<?php
// Database settings
$db="mydb";
$dbhost="localhost";
$dbport=3306;
$dbuser="root";
$dbpasswd="test";

error_reporting(E_ALL);

$pdo = new PDO('mysql:host='.$dbhost.';port='.$dbport.';dbname='.$db.'', $dbuser, $dbpasswd);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$pdo->exec("SELECT * FROM mytable" );

$res = $pdo->fetchAll();
foreach ( $res as $row ) {
    echo $row['id'];
}

$pdo = null;
?>

 

[Shohreh]

If I first add an echo() the error message dispappears… but still no data:

<?php
// Database settings
$db="mydb";
$dbhost="localhost";
$dbport=3306;
$dbuser="root";
$dbpasswd="test";

error_reporting(E_ALL);

$pdo = new PDO('mysql:host='.$dbhost.';port='.$dbport.';dbname='.$db.'', $dbuser, $dbpasswd);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

//Required to remove "This page isn’t working"
echo("Here");

$pdo->exec("SELECT * FROM mytable" );


$res = $pdo->fetchAll();
foreach ( $res as $row ) {
    //Still no output
    echo $row['group_id'];
}

$pdo = null;
?>

So it looks like PHP can successfully connect to MariaDB, and I only have to find why I get no data back (it works with the CLI).

Edited October 3, 2022 by Shohreh

[Shohreh]

This works:

$stmt = $pdo->query('SELECT * FROM mytable');
while ($row = $stmt->fetch())
{
    echo $row['group_id'] . "\n";
}

 

[Barand]

46 minutes ago, Shohreh said:

$pdo->exec("SELECT * FROM mytable" );

PDO::exec() does not return a resultset (update, insert, delete only)

query() or execute() to get a resultset.

[Barand]

PS

When I run

$pdo->exec("SELECT * FROM student");
$res = $pdo->fetchAll();

I get

Fatal error: Uncaught Error: Call to undefined method PDO::fetchAll() 

 

Have you got error reporting switched on?

[mac_gyver]

php's error_reporting should (always) be set to E_ALL. when learning, developing and debugging code/query(ies), display_errors should be set to ON. when on a live/public server, display_errors should be set to OFF and log_errors should be set to ON. these setting should be in the php.ini on your system so that ALL php errors will get reported (putting the settings in your code won't cause fatal parse errors to be reported since your code never runs to cause the settings to take effect) and so that they can be changed in a single place. while you are making changes to the php.ini, set output_buffering of OFF. stop and start your web server to get any changes made to the php.ini to take effect and confirm that the settings actually got changed by using a phpinfo() statement in a .php file that you request through the web server.

next, when you make the PDO connection -

1. set the character set to match your database tables. so that no character conversion occurs over the connection.
2. as already mentioned, set the error mode to exceptions (this is actually the default now in php8, but set it anyways), so that all the rest of the PDO statements, after the connection (which always uses exceptions for errors), will also use exceptions for errors.
3. set emulated prepared queries to false, you want to run real, not emulated, prepared queries.
4. set the default fetch mode to assoc, so that you don't need to specify it in each fetch statement.

since you are just getting started with the PDO extension, save yourself a lot of typing with prepared queries by using simple positional ? prepared query place-holders and use implicit binding, by supplying an array of input values to the ->execute([...]) call.

[Shohreh]

Thanks. It's a bit above my head. i just needed to check PHP and MariaDB could work together before trying to install phpBB.

I'll read up on error management, etc.