Help with basic PDO connection

otuatail · Mar 13, 2023

Hi I used to write PHP years ago but have been told that my server is to upgrade from PHP4.4 to PHP7. I am out of my depth here and can’t do local host. I have put together a simple DB and PDO connection. Can someone help me with this? I have a simple mySQL database table called PDO. It has 5 records of Name_F, Name_S, DOB. I am trying to iterate the table so that I can display the records on a web page. I was told on another forum to use $stmt. What does stmt even stand for?
Thanks for your help.

 <?php
error_reporting(E_ALL);
ini_set('display_errors', '1');

define ('HOSTNAME1', 'mysql09.iomart.com'); 
define ('USERNAME1', 'otoogc692');
define ('PASSWORD1', 'mauritius');
define ('DATABASE1', 'otoogc692');

function ConnectDB()
{
    static $conn = null;
    if ($conn === null)
	{
        $host = HOSTNAME1;
        $user = USERNAME1;
        $pass = PASSWORD1;
        $MyDB = DATABASE1;

    $conn = new PDO("mysql:host=$host; dbname=$MyDB; charset=UTF8", $user, $pass, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ
    ]);
}

return $conn;
}
?>

<html>
<body>

<?
echo "Hi There here is the list.";
$conn = connectDB();
$q = $conn->query("SELECT * FROM PDO");
$stmt = $conn->prepare($q);
$stmt->execute(); 
// What is stmt?

while ($row = $stmt->fetch())
{
	echo $stmt['First_N') . "<br/>";
}

?>
</body>
</html>

pbismad · Mar 13, 2023

otuatail can’t do local host

Why not? There's some evidence from the symptoms you have reported that code you have written/changed hasn't taken effect because you are trying to do this on your web hosting where something is caching the previous result.

otuatail I was told on another forum to use $stmt.

I'm betting that was from this part of a reply -

call the ->prepare() method for the resulting sql query statement. this returns a PDOstatement object, and should be named $stmt or similar.

The value is a PDOStatement object. Naming it $stmt is just a naming convention, so that anyone looking at the code will be able to deduce what it is, just like naming the PDO connection, $pdo, so that anyone looking at the code can deduce what database extension you are using.

otuatail · Mar 14, 2023

Does s.t.m.t stand for anything. Can't be 4 random letters

Weedpacket · Mar 14, 2023

otuatail Does s.t.m.t stand for anything. Can't be 4 random letters

otuatail See @pbismad's reply. In the real world you'd use a name that means something. What that would be depends on what you're actually trying to do with your program.

otuatail · Mar 14, 2023

It can't work. echo $stmt['First_N') should have been echo $row['First_N'). Also this code has been on the server for 24 hours now. I have downloded opera and this dosn't work even with that small correction.

while ($row = $stmt->fetch())
{
	echo $stmt['First_N') . "<br/>";
}

NogDog · Mar 14, 2023

otuatail

No, in your example, $stmt is a PHP PDOStatement object, not a result array. You are using it to fetch one result row, which you assign to variable $row. Therefore, you want to echo $row['First_N'].

otuatail · Mar 14, 2023

I am trying to return a dataset of recoeds. There are 5 records which in need a While{} loop

pbismad · Mar 14, 2023

otuatail echo $stmt['First_N') . "<br/>";

This line ^ contains a php syntax error, e.g. the incorrect closing ). The code never runs at all, regardless of using the correct variable name $row.

This is another reason to use a localhost development system, when learning, developing, and debugging code/query(ies). You will be able to set the php error related settings in the php.ini, so that ALL errors will be reported and displayed. Putting these settings into your code won't help you with php syntax errors, since your code never runs in this case to set the settings.

The rest of the code is a random collection of statements, having nothing to do with reading and understanding the php documentation for what you are trying to do. You are using the non-prepared query() method, which is what you should be using, since this sql query statement doesn't have any values being supplied to it. Instead of going on to fetch the data at that point, you are incorrectly trying to use the PDOStatement object in $q, as the input parameter to a prepare() method call (another reason to name variables as to what type of data is in them.). After you fix the php syntax, this would be producing some type of php runtime error about $q not being a string.

otuatail · Mar 14, 2023

I know what you are saying. I can't do local host. the SQL is requesting * from PDO. and I need to loop through the result set. if

$q = $conn->query("SELECT * FROM PDO");
$stmt = $conn->prepare($q);
$stmt->execute();

is only for returning a single row then what should I be using to return a set of records. Help required on returning a set of rows is needed.

P.S. didn't realise I had used the wrong bracket.

otuatail · Mar 14, 2023

I still get

Warning: PDO::prepare() expects parameter 1 to be string, object given in /vhost/vhost15/d/e/s/desmond-otoole.co.uk/www/music/music.php on line 38

But I am supplying one argument $q. $q is the query that needs to be prepared.

otuatail · Mar 14, 2023

You are using the non-prepared query(). I thought that

$q = $conn->query("SELECT * FROM PDO");

preduces a query on the SQL and

$stmt = $conn->prepare($q);
$stmt->execute();

is needed to get the data. This is code given to me by the forum phpfreaks.

otuatail · Mar 14, 2023

https://phpdelusions.net/pdo#query

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

I was told on another forum that ALL sql had to be prepared
SQL
Prepare
Execute

but the above example does not use prepare or execute. It dosn't matter if I use local host or not. I am after an example of the use of returning a record set.

NogDog · Mar 14, 2023

Technically, it doesn't all have to be prepared, as the $pdo->query() method will attempt to run whatever SQL you supply it as is. But from a security standpoint (and possibly other good reasons), it's generally best to use prepared statements. In your simple example where the query is just SELECT name FROM users, there is no security risk since no external values are being used in the query. However, the moment you try to do something more likely to be done in an actual app, such as $stmt = $pdo->query("SELECT * FROM users WHERE name = '{$_GET['user_name']}');, you are now at serious risk of an SQL injection attack. Therefore, the preferred method would be more like:

$stmt = $pdo->prepare("SELECT * FROM users WHERE name = :user_name);
$stmt->execute([:user_name => $_GET['user_name']]);
while ($row = $stmt->fetch()) {
    // do something with each result row
}

otuatail · Mar 14, 2023

Okay. I am using the code supplied on the website.
https://phpdelusions.net/pdo#query

I am using a server with PHP5.4 is that the reason? PDO does not work on this version.
Error: Fatal error: Cannot use object of type stdClass as array in /vhost/vhost15/d/e/s/desmond-otoole.co.uk/www/music/music.php on line 43

<?php
error_reporting(E_ALL);
ini_set('display_errors', '1');

define ('HOSTNAME1', 'mysql09.iomart.com'); 
define ('USERNAME1', 'otoogc692');
define ('PASSWORD1', 'mauritius');
define ('DATABASE1', 'otoogc692');

function ConnectDB()
{
    static $conn = null;
    if ($conn === null)
	{
        $host = HOSTNAME1;
        $user = USERNAME1;
        $pass = PASSWORD1;
        $MyDB = DATABASE1;

    $conn = new PDO("mysql:host=$host; dbname=$MyDB; charset=UTF8", $user, $pass, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ
    ]);
}

return $conn;
}
?>

<html>
<body>

<?
echo "Hi There here is the list!!!";
$conn = connectDB();
$stmt = $conn->query("SELECT * FROM PDO");
//$stmt = $conn->prepare($q);
//$stmt->execute(); 
// What is stmt?

while ($row = $stmt->fetch())
{
	echo $row['First_N'] . "<br/>";
}

?>
</body>
</html>

otuatail · Mar 14, 2023

Your code

$stmt = $pdo->prepare("SELECT * FROM users WHERE name = :user_name);
$stmt->execute([:user_name => $_GET['user_name']]);
while ($row = $stmt->fetch()) {
    // do something with each result row
    Replace // do something with each result row with
    echo $row['First_N'] . "<br/>";
    // Cannot use object of type stdClass as array
}

pbismad · Mar 14, 2023

The error has nothing to do with php version. You are fetching the data as an object, but attempting to use array syntax to access it.

You were already told elsewhere what to set the default fetch mode to -

and set the default fetch mode to assoc, you are setting it to obj, which is not what you are using in the example code and is probably not what your existing code is using with the fetched data, which will require you to make even more changes to your existing code.

Programming is a writing and reading activity. You must at some point actually read the words and syntax making up the code you are using and be able to describe to yourself what they are doing.

otuatail PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ

Even if you have no specific experience with php or the PDO extension, doesn't reading the above line of code hint that you are setting something called the default fetch mode to OBJ (an abbreviation of the word object)?

Weedpacket · Mar 14, 2023

otuatail I was told on another forum that ALL sql had to be prepared
SQL
Prepare
Execute

If you aren't using any placeholders to supply variables to the query, then pdo_query does both the preparation and execution steps immediately. If there aren't any placeholders in the SQL statement then there's nothing to do between preparation and execution so they don't need to be split into two steps. This is why the manual page for pdo_query says that the method "prepares and executes an SQL statement without placeholders".

otuatail I am using a server with PHP5.4 is that the reason? PDO does not work on this version.

No, PDO has been around since 5.1.

Yes, because you're telling your PDO connection to return results as objects instead of arrays. That is what the

        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ

setting you've used does.

See PDO constants for a list of the other FETCH_* options available. Some of them make PDO give you arrays, some give you objects.

otuatail · Mar 15, 2023

Yes you are correct. FETCH_ASSOC. I did try this. and this did not work. TRhis results in crashing the web page with
This page isn’t working

function ConnectDB()
{
    static $conn = null;
    if ($conn === null)
	{
        $host = HOSTNAME1;
        $user = USERNAME1;
        $pass = PASSWORD1;
        $MyDB = DATABASE1;

    $conn = new PDO("mysql:host=$host; dbname=$MyDB; charset=UTF8", $user, $pass, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => FETCH_ASSOC 
    ]);
}
return $conn;
}
?>

<html>
<body>

<?echo "This list isnt working";
$pdo = connectDB();
$stmt = $pdo->query("SELECT * FROM PDO");
ecPDO");
while ($row = $stmt->fetch())
{
	echo $row['First_N'] . "<br/>";
	
}

?>

I have been there.

otuatail · Mar 15, 2023

Notice: Use of undefined constant FETCH_ASSOC - assumed 'FETCH_ASSOC

NogDog · Mar 15, 2023

otuatail Use of undefined constant FETCH_ASSOC

As per the other PDO constants you used in that command, it requires the "PDO" class as part of the identifier: PDO::FETCH_ASSOC (i.e., the constant is specific to the PDO class)

Help with basic PDO connection

Ootuatail

Ppbismad

Ootuatail

Weedpacket

Ootuatail

NogDog

Ootuatail

Ppbismad

Ootuatail

Ootuatail

Ootuatail

Ootuatail

NogDog

Ootuatail

Ootuatail

Ppbismad

Weedpacket

Ootuatail

Ootuatail

NogDog