Can't get my PHP code to query database

Hello, I’m serving a 1-table-database using XAMPP on my localhost and trying to do a simple query with PHP. I tried the code that my professor suggested, adapting it with my variables - i.e. my database is called Personen.
The error I’m getting when I run this file in a browser is 500 internal server error.

<?php
include "config/config.php";

    $pdoObject = new PDO("mysql:host=localhost;dbname=Personen;charset=utf8", "root", "");

$sql = "SELECT id, street, zip, location FROM addresses WHERE zip = ‘1010’ ";

$resultTable = array();
try {
    //iterieren für alle moeglichen zeilen
    foreach ($pdoObject -> query($sql) as $row) {
        $resultTable[] = $row;  //die Zeile in den Array schreiben
    }
} catch (PDOException $exception) {
    //error handling, wenn die Anfrage schief ging
    error_log("PDO ERROR: querying database: " . $exception->getMessage()."\n".$sql);
}

(I posted it for you to run online here but obviously there’s no database listening there.)

  1. The original code that I copied had a $ in front of the database’s name, so it was dbname=$dbName instead of dbname=Personen in line 4,
    and the $ was missing in the error_log line at the bottom from $sql, so it just said $exception->getMessage()."\n".sql); instead. Do you agree those were errors (my IDE was pretty convinced)?

  2. As far as I understand it, PDO is a class built-in to PHP. Is it possible that the way of writing this changed in recent PHP versions and the language level I’m using to compile it (roughly PHP 5 vs. 7) would affect the question if it works or not?

  3. Is there a way to get more useful error messages when writing PHP code? The standard web error codes are normally not helping me at all.

Greetings,

You have just queried the dataBase. You need to fetch the result.

$statement = $pdoObject->query($sql);
$result = $statement->fetch();

I don’t much about Object Oriented PHP but, I think this ^ is the problem. Also, Remove that query() part from the loop and put it before the loop. Then, fetch the result and then, loop over the result.

Also, it seems like that you are appending the result into the array but, you are missing += and I don’t think that you need to append the result to an array as, the result will be returned as one.

Thanks
Nikhil Seth

I tried your code, it works. You’re just not returning anything.

Under the line $resultTable[] = $row; //die Zeile in den Array schreiben

add

echo $resultTable[0]['street'];

You should get a result.


Just re-read your post and noticed the 500 error. Then the problem is elsewhere.


<?php

include "config/config.php";

$pdoObject = new PDO("mysql:host=localhost;dbname=Personen;charset=utf8", "root", "");

$sql = "SELECT street FROM addresses WHERE zip = '1010'";

$resultTable = array();

try {
    foreach ($pdoObject -> query($sql) as $row) {
        $resultTable[] = $row;
        echo $resultTable[0]['street'];
    }
} catch(PDOException $e) {
    error_log('PDO ERROR: querying database:'. $e->getMessage()."\n".$sql);
}

Could you try this please, just curious about something…

I initially didn’t want to do this, but we don’t want you picking up bad habits.
So here we go.

(But first, though, try to make it work with my previous suggestion.)


No offense to him, but wrong suggestion. This isn’t the right way to do it.
He might’ve wanted to challenge you, mind. Still wrong, though.

Here’s my suggestion, take it or leave it.

Disclaimer: the following might introduce new concepts. If in doubt about something, refer to Google or better yet, the PHP manual.

It can still be improved (with OOP, MVC, autoloader, namespaces and a .env file), but that’s way beyond the scope of the current problematic.

So for now, let’s do it this way.


We’ll need 3 files. They’ll each serve their own purpose.

  • config.php
  • db.php
  • addresses.php

config.php

Here we’ll define the credentials to connect to the database. Later, it’d be best to put this information in a .env file, to avoid hardcoding it in the code and have it sit in plain sight.

<?php

define('DB_HOST', 'localhost');
define('DB_NAME', 'Personen');
define('DB_USER', 'root');
define('DB_PASS', '');


db.php

Here we’ll set up the database connection once, that we’ll be able to use later on.

<?php

require 'config.php';

$db_options = [
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false
];

$db = new PDO('mysql:host='.DB_HOST.';dbname='.DB_NAME.';charset=utf8mb4', DB_USER, DB_PASS, $db_options);


addresses.php

The data we want to retrieve from the database and display. It’d be best to have yet another file to display the data, separate from the database query. But for now, this will do.

<?php

require 'db.php';

$sql = 'SELECT id, street, zip, location 
        FROM addresses
        WHERE zip = :zip';
try {
    $query = $db->prepare($sql);
    $query->bindValue(':zip', '1010', PDO::PARAM_STR);
    $query->execute();

    $addresses = $query->fetchAll();

} catch(PDOException $e) {
    return $e->getMessage();
}

foreach ($addresses as $address) {
    echo nl2br("
        <strong>Property ID:</strong> {$address->id} \n
        <strong>Street:</strong> {$address->street} \n
        <strong>Location:</strong> {$address->location} \n
        <strong>Zip code:</strong> {$address->zip}
    ");
}


And that’s pretty much it, here’s the result in a web browser:


Feel free to ask if there’s anything that needs clearing up. Hope this helps!


Edit: one more thing…

There’s no excuse to still be using PHP 5 in 2020. Please, please, please, download the latest version (7.4.6) and use that instead.

Ok, the differnce is it’s querying only the street attribute from addresses table instead of all the attributes yes?

Not quite, that was just to check if you’d get a result

It’s the '1010', actually. You were using this ‘1010’

I just figured out at least a couple pretty stupid mistakes of mine (that isn’t fixing the error though) (I might have avoided these by trying the task closer to when it was assigned, but probably not):
My professor gave us this sample code for querying a database - the values in the sql query being just examples.
Below that, he described the task to create a database, specifying the name of the table and columns that he wanted, and they weren’t the ones from the example (columns id, firstname, lastname, birthdate, not id, street, zip, location, the table is called persons, not adresses).
Also, I forgot to actually fill the db with records… (where’s the triple faceplam emoji)

Still, the way I see it, if all the syntax was fine, all this should have just brought back an empty result, or at least the error message if the query failed, right?
I also found the ´ ´ instead of ’ ’

New code:

<?php
include "config/config.php";

    $pdoObject = new PDO("mysql:host=localhost;dbname=Personen;charset=utf8", "root", "");

$sql = "SELECT id, firstname, lastname, birthdate FROM persons WHERE id = '4'";

$resultTable = array();
try {
    //iterieren für alle moeglichen zeilen
    foreach ($pdoObject -> query($sql) as $row) {
        $resultTable[] = $row;  //die Zeile in den Array schreiben
        echo $resultTable[0]['street'];
    }
} catch (PDOException $exception) {
    //error handling, wenn die Anfrage schief ging
    error_log(

Error ist still 500

Going through your advice now…

This way of doing it is still… impractical? For lack of a better word. What @snikhill wrote was correct.
You first want to fetch the results, and then loop through.

Fair enough, that’s a great task.

Happens to the best of us :grinning:

I tried to simulate the error but couldn’t. You’re getting a 500, so something else is going on… difficult to say what it is, though.

Did you create a new DB for this?

MVC has been introduced in our class and splitting the classes/functions up into separate files etc, it’s in the next exercise. This one was just meant as a really quick “proof of concept” namely “use PHP code to query yr database and return an error message if it fails”. Once I get this running, transforming it into best practise ways is up next…
I found some examples of good practise for using the PDO in the PHP documentation

I have my test database, I just created a new table “addresses” to get as close to your set up as possible.

Brilliant, that sounds great!

Yes, that’s the way to go.


First we need to get rid of that 500 though…

Greetings

Whoa, you guys chat a lot. So, @bennypr0fane, @ghostlovescore has already helped you. Any further assistance will rob you of the chance of debugging the code yourself.

Here is what I suggest:
De-structure the code.

Remove ForEach Loop

Just query the dataBase and fetch one result using [0] on the result array.

Also, at the same time: Keep looking at the log file. (If you are on Linux then, tail the file).

Just a question, Can you attach a screenshot of the Error?

Thanks
Nikhil Seth

Actually I have 7.4.5-2 wich is what my Linux distro is offering in the official repos. Is there anything in my code that would indicate to you I’m using PHP 5, or was that just in response to my initial question 2.?

Yes, a chatty one I am…

Which file to tail though?

Here’s the error message browser page + console:

so many elements to search the internet for…
Just many more hours of work before I can go make a mockup webshop that’ll look up a mockup database due on monday :cold_sweat:

e.g. what exactly does this query...as syntax do? Is it a keyword? There’s a usage example in the PHP docs, but can’t find an explanation of this specific keyword.

Hey,

I want you tail the php log file.

Found here: /var/log/php-fpm/

sudo tail -f /var/log/php-fpm/www-error.log

Super user privilege is required.

Also, why OPERA?

Actually, I don’t find the php Manual to be that “good” for everything.

I too got an Internal 500 error: As I called fetch() on a boolean type.

Turns out if the SQL query fails, it returns FALSE (Boolean) and hence, fetch() won’t work. I fixed up my query (In my case, I has a spelling mistake in tableName) and now, the fetch() works.

I don’t understnd the fetch() command, and, as I said, query() as. Can’t find examples of how you use it either. PHP docs show usage like this: PDO::query and this: PDO::fetch
It’s probably too early for me to be doing any of this since I’m not even that familiar with PHP syntax yet.

The PHP log file is not in /var/log/php-fpm/ on my machine

~]$ ls /var/log
audit  btmp.1  faillog   httpd    lastlog  pacman.log  samba     wtmp        Xorg.0.log.old
btmp   cups    gssproxy  journal  old      private     tallylog  Xorg.0.log

Probably be cause I’m running Apache off of Xampp. I found a log file here:

$ sudo tail -f /opt/lampp/logs/php_error_log 
[11-Apr-2020 16:30:15 Europe/Berlin] PHP Deprecated:  Directive 'track_errors' is deprecated in Unknown on line 0

So not a lot happening here, the file stays the same when I execute index.php
there’s also

$ sudo tail -f /opt/lampp/logs/error_log
[Thu May 21 16:02:30.085941 2020] [core:notice] [pid 3021] AH00094: Command line: '/opt/lampp/bin/httpd -E /opt/lampp/logs/error_log -D SSL -D PHP'
[Thu May 21 19:03:09.301877 2020] [mpm_prefork:notice] [pid 3021] AH00169: caught SIGTERM, shutting down
[Fri May 22 00:15:45.773542 2020] [ssl:warn] [pid 6089] AH01906: www.example.com:443:0 server certificate is a CA certificate (BasicConstraints: CA == TRUE !?)
[Fri May 22 00:15:45.773609 2020] [ssl:warn] [pid 6089] AH01909: www.example.com:443:0 server certificate does NOT include an ID which matches the server name
[Fri May 22 00:15:45.773696 2020] [suexec:notice] [pid 6089] AH01232: suEXEC mechanism enabled (wrapper: /opt/lampp/bin/suexec)
[Fri May 22 00:15:45.820930 2020] [ssl:warn] [pid 6090] AH01906: www.example.com:443:0 server certificate is a CA certificate (BasicConstraints: CA == TRUE !?)
[Fri May 22 00:15:45.820987 2020] [ssl:warn] [pid 6090] AH01909: www.example.com:443:0 server certificate does NOT include an ID which matches the server name
[Fri May 22 00:15:45.821149 2020] [lbmethod_heartbeat:notice] [pid 6090] AH02282: No slotmem from mod_heartmonitor
[Fri May 22 00:15:45.857705 2020] [mpm_prefork:notice] [pid 6090] AH00163: Apache/2.4.41 (Unix) OpenSSL/1.1.1e PHP/7.4.4 mod_perl/2.0.8-dev Perl/v5.16.3 configured -- resuming normal operations
[Fri May 22 00:15:45.857995 2020] [core:notice] [pid 6090] AH00094: Command line: '/opt/lampp/bin/httpd -E /opt/lampp/logs/error_log -D SSL -D PHP'

Same here. executing my code prints nothing to this file

Hey,

You know, PHP has internal server capability and It will show you the error logs immediately.
( I mean your error_log() will log the error to the terminal).

Use:

php -S localhost:8080 path/to/projectFolder

Also, I found a website for you.

Thanks
Nikhil Seth

There are several ways of doing things in PHP.

fetch() fetches a row from a result set associated with a PDOStatement object.

Consider the following:

// Say we have two users in our DB, "John Doe" and "Jane Doe"
// Here we want to fetch all users' name from table users 

$sql = $pdo->query('SELECT name FROM users');

while ($row = $sql->fetch()) {
  echo '<pre>';
  var_dump($row);
  echo '</pre>';
}

Result:

array(1) {
  ["name"]=>
  string(8) "John Doe"
}

array(1) {
  ["name"]=>
  string(8) "Jane Doe"
}

Now consider this way:

$sql = 'SELECT name FROM users';

foreach ($pdo->query($sql) as $row) {
  echo '<pre>';
  var_dump($row);
  echo '</pre>';
}

Result:

array(1) {
  ["name"]=>
  string(8) "John Doe"
}

array(1) {
  ["name"]=>
  string(8) "Jane Doe"
}

Or yet another way! I personally prefer this one, as we separate the querying / fetching and the loop.

$query = $pdo->query('SELECT name FROM users');
$query->execute();

$results = $query->fetchAll();

foreach ($results as $row) {
  echo '<pre>';
  var_dump($row);
  echo '</pre>';
}

Result:

array(1) {
  ["name"]=>
  string(8) "John Doe"
}

array(1) {
  ["name"]=>
  string(8) "Jane Doe"
}

See, 3 different ways to get the same result.


For now you’ve only been introduced to the second way:
foreach ($pdo->query($sql) as $row)

What actually happens here is that we’ll loop through $pdo->query($sql)

$pdo->query($sql) is nothing more than a PDOStatement object:

object(PDOStatement)#4 (1) {
  ["queryString"]=>
  string(22) "SELECT name FROM users" // <-- This is what interests us
}

So, for each user that we can find in our database, we’ll loop through that statement.

Since in our example we have two users, John and Jane Doe, $pdo->query($sql) will in fact be:

object(PDOStatement)#4 (1) {
  ["queryString"]=>
  string(22) "SELECT name FROM users"
}

object(PDOStatement)#4 (1) {
  ["queryString"]=>
  string(22) "SELECT name FROM users"
}

Then, we want to get the result of each statement into an associative array.

That’s your foreach (x AS y)

To understand as, you just need to understand foreach.

The foreach construct provides an easy way to iterate over arrays. foreach works only on arrays and objects, and will issue an error when you try to use it on a variable with a different data type or an uninitialized variable.

So, using foreach ($pdo->query($sql) as $row)

we’re basically saying

foreach (object as array)

And this how we get our data in the form of an array.

foreach ($pdo->query($sql) as $row) {
  echo '<pre>';
  var_dump($row);
  echo '</pre>';
}

Result:

array(1) {
  ["name"]=>
  string(8) "John Doe"
}

And, finally, if we only want to return the name of the person, we can do:

foreach ($pdo->query($sql) as $row) {
  echo $row['name'];
}

Result:

John Doe

1 Like

Thank you a lot for all this help!
I haven’t reviewed this latest piece yet, juist wanted to give you a quick update. Whatever is wrong on my system, it’s not just the code: I can’t even run a file echo.php with

<?php
echo 'test';

in it. I get exactly the same error as all the other code I tried to run

Happy to help!

Looks like an issue with XAMPP?

I haven’t used it in a long time, but…

Make sure that all services have been started (Apache, mysql, etc).

Enable port 8080 in the network tab

In volumes, mount /opt/lampp and click explore

All your files should go into \xampp\htdocs

So I’d place a test.php file which echoes ‘test’ in htdocs and see what’s returned

If that still doesn’t work… I really don’t know.

What’s your IDE? You could look up "xampp _ijt" for clues I guess?

That’s exactly what one of my colleagues just suggested and it worked.

I wasn’t aware of that, and so happened to not show up in all the pages I looked up about lamp. I actually knew it from Apache a while back that a web root is specified and everything must be under that. It had slipped my mind.

It had 80 set as default.

I use PHPStorm. Still haven’t figured out what its policy is for determining which port it should run the code on.
For some reason, it opens the page on port 8000, that returns 500.
When I edit the port to 80, it’s “404 object not found” then