Can't get my PHP code to query database

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

Excellent.

So now we’re ready to query that database!

Two questions:

  • What happens when you just go to http://localhost
  • Why is there an alt in front of index.php

alt???


Additional question, what is HÜs?
Hüs

When you’re trying to access localhost/3-Verbindung-... ?

What happens if you try localhost/HÜs/3-Verbindung-... ?

I see the XAMPP dashboard/startpage

That’s just a file where I tried some slightly different code, I already deleted it.
The proper index.php returns the exact same error message

Any idea though why PHPStorm keeps opening the page on port 8000, while XAMPP is set to 80?
Remember PHPStorm has a webserver built-in (also set to port 80 though) - how can I tell which one is actually being used?

Exactly… and, I don’t know. You’d have to check if you can change that (the opening on port 8000 part)

PS: Did you see my question about HÜs?

Saw it now. It’s just a subfolder of the XAMPP webroot
It’s all messed up, the PHPStorm webserver was actually set to run on port 8000. I now set it to 80 AND removed its configuration from the project.
XAMPP 's apache is also set to run on port 80. However, when I open the index.php by clicking the browser icon in PHPStorm, it still opens it on port 8000. AND I’m also finally getting useful output ( :woozy_face:):

Fatal error: Uncaught PDOException: SQLSTATE[HY000] [1049] Unknown database ''Personen'' in /opt/lampp/htdocs/HÜs/3-Verbindung-Datenbank/index.php:5 Stack trace: #0 /opt/lampp/htdocs/HÜs/3-Verbindung-Datenbank/index.php(5): PDO->__construct('mysql:host=loca...', 'root', '') #1 {main} thrown in /opt/lampp/htdocs/HÜs/3-Verbindung-Datenbank/index.php on line 5

Did you restart PHPStorm after making these changes?

Well at least now we’re getting a response from the database!

I don’t remember if I restarted it then, but did now and the error persisted. I had unnecessary ’ ’ around the name of the database. After removing these, the query works! Just reposting the working code for th record:

<?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 = '5'";

$resultTable = array();
try {
    foreach ($pdoObject -> query($sql) as $row) {
        $resultTable[] = $row;
        echo $resultTable[0]['firstname'];
    }
    //iterieren für alle moeglichen zeilen
} catch (PDOException $exception) {
    error_log("PDO ERROR: querying database: " . $exception->getMessage()."\n".$sql);
}
1 Like

Recapping all the problems and solutions:

  1. server errors 500 and 502: I was unable to connect to the database because my php files were not in the webroot directory.
    solution:

…which is the default web root of XAMPP. I’m guessing a different webroot directory can be specified in the server config, but I don’t know whether or not it has to be under the server root directory.

  1. I had trouble telling which of the available webservers (built-in from PHPStorm vs. Xampp vs. the system Apache on my Linux. Xampp runs in the /opt/lampp directory) were being used. I looked into configuring the behavior of the PHPStorm, but not successfully yet, it’s a bit complicated in that regard.
    Solution: Don’t use the browser buttons provided in PHPStorm’s code editor for running the files. I decided to use Xampp and type localhost/relativepath/from/xampp-webroot into the browser’s address bar. That way, any configurations PHPStorm might have regarding the webserver are bypassed.

  2. My code was initially querying a non-existant database, it was just an example.
    Solution: Query correct fields from the actual database I wanted. Don’t forget to also create that db first and fill it with some records.

  3. When querying the right values, they were not printed to the screen.
    solution:

or

print_r is also possible.

  1. The way this code is structured is far from best practise. It’s just a simple query only useful for a one-time demonstration.
    Solution: numerous remarks are in this post how connection to a database can be structured professionally, go through the whole thing. Simple examples also here and here
1 Like