PDO connection

[joon1]

I am trying PDO connection for the first time with a new version of my web application. (XAMPP on Window 10)

...
try {
$conn = new PDO("mysql:host=$serverName;dbname=$dbName", $userName, $passWord); 
 
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
echo "Connected successfully"; 
} catch(PDOException $e) {
echo "Connection failed: " . $e->getMessage(); 
} 

$myTableInsert=("INSERT INTO myTable (myNumeric)  
VALUES (1234)");  
$myTableInsertEXE=mysqli_query($conn, $myTableInsert); 

Byt the result of the code above is the following

Connected successfully
Warning : mysqli_query() expects parameter 1 to be mysqli, object given in pdo1.php on line 18

What might be wrong in your think?

[droopsnoot]

If you open the connection using a PDO function, you need to carry on using PDO functions to run queries, recover results - you can’t use a mysqli function for that, you can’t mix the two.

Exactly as the error message says, your mysqli function requires a mysqli connection object as the first parameter, not a PDO connection.

[joon1]

@droopsnoot

you need to carry on using PDO functions to run queries, recover results

How can I carry on using PDO functions to run queries, inserting myNumeric to myTable ?

[droopsnoot]

Have you read through any of the PDO documentation? PDO is a complete library. As a quick-start, I’d do this:

$myTableInsert=("INSERT INTO myTable (myNumeric)  
VALUES (1234)");  
$myTableInsertEXE=$conn->query($myTableInsert); 

or something like that. I must admit that by sheer force of habit, I tend to prepare every statement, which might be a bit over the top for your particular example. As soon as you need to put a variable into the query, though, that’s when you need to prepare rather than just concatenate it into the string.

This tutorial gets linked to quite a lot on here, might be worth a look through for more information: (The only proper) PDO tutorial - Treating PHP Delusions

[joon1]

@droopsnoot

only proper) PDO tutorial - Treating PHP Delusions

I am on the way of the link above and made the code below by reading the first part of the link above.

declare(strict_types=1);
error_reporting(-1);
ini_set('display_errors', '1'); 

$host = "localhost";
$user="root";
$pass="************";
$charset="utf36mb4";  
$dbname="myDB"; 
$dsn="mysql:host=$host;dbname=$dbname;charset=$charset"; 

$option = [
    PDO: : ATTR_ERRMODE                  => PDO: : ERRMODE_EXCEPTION,
    PDO: : ATTR_DEFAULT_FETCH_MODE       => PDO: : FETCH_ASSOC,
    PDO: : ATTR_EMULATE_PREPARES         => false,
]; 

try {
$PDO = new PDO(dsn, $user, $pass, $options); 
} catch(PDOException $e) {
throw new PDOException($e->getMessage(), (int)$e->getcode()); 
} 
$stmt = $pdo-> query ('SELECT myNumeric FROM myTable'); 

while ($row = $stmt -&_fetch()) {
 echo $row['myNumeric'] . "n";
}

The quote below is the result of the code above.

Parse error : syntax error, unexpected ‘:’, expecting ‘]’ in pdo2.php on line 16

How can I fix the line 16 “PDO: : ATTR_ERRMODE => PDO: : ERRMODE_EXCEPTION,” in the code above for removing the Parse error above?

[spaceshiptrooper]

That’s because you have a lot of problems with your spacing. You need to stop making all of those random spaces because it’s going to keep throwing you that error. Whenever that error happens, it usually means you’re not putting in the right syntax. All of your PDO constants are incorrect. You need to go back and fix them. The double colons should not have any spaces nor the PDO constants on both sides of the colons.

@joon1

$stmt -&_fetch

You’re going to have a problem here too as it’s incorrect. Should be the arrow operator and not the pass by reference operator.

[joon1]

<?php 
 
declare(strict_types=1);
error_reporting(-1);
ini_set('display_errors', '1'); 

$host = "localhost";
$user="root";
$pass="************";
$charset="utf36mb4";  
$dbname="myDB"; 
$dsn="mysql:host=$host;dbname=$dbname;charset=$charset"; 

$options = [
    PDO::ATTR_ERRMODE                          => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE         => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES             => false,
]; 

try {
$PDO = new PDO($dsn, $user, $pass, $options); 
} catch(PDOException $e) {
throw new PDOException($e->getMessage(), (int)$e->getcode()); 
} 
$stmt = $pdo-> query ('SELECT myNumeric FROM myTable'); 

while ($row = $stmt ->fetch()) {
 echo $row['name'] . "
";
}

The modified code above produces the result below.

Fatal error : Uncaught PDOException: SQLSTATE[HY000] [2019] Unknown character set in E:\xampp\htdocs\ab1a\ab101\pdo4.php:23 Stack trace: #0 {main} thrown in pdo4.php on line 23

What’s wrong of it?

The changing like the following produces the same fatal error above.

$charset="utf36mb4";

// from the above
// to the below
 
$charset="utf36"; 

[spaceshiptrooper]

@joon1

What’s wrong of it?

I don’t think there is such a thing as UTF-36. I’ve always used UTF-8 so I wouldn’t know if UTF-36 is correct or not.

[droopsnoot]

Indeed, and the article that I linked to does not use utf36 either. The mysql doc seems to have this list: MySQL :: MySQL 5.7 Reference Manual :: 10.10 Supported Character Sets and Collations

@joon1, where are you getting your character set from?

[joon1]

<?php 
 
declare(strict_types=1);
error_reporting(-1);
ini_set('display_errors', '1'); 

$host = "localhost";
$user="root";
$pass="************";
$charset="utf8";  
$dbname="myDB"; 
$dsn="mysql:host=$host;dbname=$dbname;charset=$charset"; 

$options = [
    PDO::ATTR_ERRMODE                          => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE         => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES             => false,
]; 

try {
$PDO = new PDO($dsn, $user, $pass, $options); 
} catch(PDOException $e) {
throw new PDOException($e->getMessage(), (int)$e->getcode()); 
} 
$stmt = $pdo = query ('SELECT myNumeric FROM myTable'); 

while ($row = $stmt ->fetch()) {
 echo $row['name'] . "
";
}

The code above produces the result below.

Fatal error : Uncaught Error: Call to undefined function query() in pdo4.php:25 Stack trace: #0 {main} thrown in pdo4.php on line 25

It still produces the Fatal error above.

@droopsnoot

where are you getting your character set from?

I don’t quite understand the question above but I guess the answer below might be correct.

Seoul, Korea

[droopsnoot]

@joon1

I don’t quite understand the question above but I guess the answer below might be correct.

No, what I meant was, the article on the site I linked to used “utf8mb4”,but you’d changed that to “utf36mb4” and I wondered why you had done that. I see you’ve changed it back to utf8 now.

@joon1

The code above produces the result below.

It will, see the typo in this line:

$stmt = $pdo = query ('SELECT myNumeric FROM myTable'); 

You’ve had quite a few similar typos, perhaps take a minute to go over your code and check for things like that. Compare it to your code in post #7 to see the difference.

[joon1]

@droopsnoot

I wondered why you had done that

phpmyadmin which I downloaded recently has the option with utf8, utf16, and utf36 when I created the database “myDB”.
When I made PDO connection code, I did choose utf36 because I guessed this might be the newest and utf36 might also be the newest.
That was the reason why I had done that.
Anyway I am back to “utf8” not.

$stmt = $pdo = query ('SELECT myNumeric FROM myTable');
// as I change the above 
// to the below.
$stmt = $pdo-> query ('SELECT myNumeric FROM myTable'); 

It produces the following notice. This was why I tried to change “->” to “=” on line 26.

Notice : Undefined variable: pdo in pdo4.php on line 26

[droopsnoot]

OK, spot the difference between where you create your database connection:

$PDO = new PDO($dsn, $user, $pass, $options); 

and where you try to use it:

$stmt = $pdo-> query ('SELECT myNumeric FROM myTable'); 

[joon1]

<?php 
 
declare(strict_types=1);
error_reporting(-1);
ini_set('display_errors', '1'); 

$host = "localhost";
$user="root";
$pass="************";
$charset="utf8";  
$dbname="myDB"; 
$dsn="mysql:host=$host;dbname=$dbname;charset=$charset"; 

$options = [
    PDO::ATTR_ERRMODE                          => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE         => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES             => false,
]; 

try {
$pdo = new PDO($dsn, $user, $pass, $options); 
} catch(PDOException $e) {
throw new PDOException($e->getMessage(), (int)$e->getcode()); 
} 

$stmt = $pdo -> query ('SELECT myNumeric FROM myTable'); 

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

I got my target result with the code above. Thank you very much.

[joon1]

By the way,
I am not accustomed to the code “->”.
What does it mean by “->” in the code below?

$e->getMessage()

Could you rephrase the code above in English?

(int)$e->getcode()

Could you rephrase the code above in English?

$stmt = $pdo → query (‘SELECT myNumeric FROM myTable’);

Could you rephrase the code above in English?
And what is the full word of $stmt which is from https://phpdelusions.net/pdo#dsn
Does it, I guess, stand for “statement”?

$row = $stmt ->fetch()

Could you rephrase the code above in English?

[droopsnoot]

-> is used in object-oriented programming - in this case, $PDO is an object of a particular class, and query is a function within that object. You are using -> to link the query to the object.

(Seems from the above that I can’t really explain it in English after all. I’m sure someone will be along to point you to a guide to object-oriented programming soon.)

@joon1

And what is the full word of $stmt which is from https://phpdelusions.net/pdo#dsn
Does it, I guess, stand for “statement”?

It’s just a name, you could call it anything (with some exceptions) but it’s always a good idea to have variable names with some meaning. I usually call my database connection $dbc for example - short enough to not be a pain to type out, but enough there to remind me what it is.

[spaceshiptrooper]

@joon1

What does it mean by “->” in the code below?

It has a lot of names, but I usually call it the “arrow” operator. Like @droopsnoot said, it’s for accessing objects and methods. Some languages like C++ uses this for specific accessors like pointers. PHP is one of those languages that uses it for its primary object-orientation whereas other languages use the “dot” operator.

@joon1

Which one is the “database connection” that you call between (1) and (2) in the code below?

The connection itself starts when you instantiate the instance of that class. So it would be #2. The stuff in your #1 is only setting a string so that you can later use it in your connection.

[joon1]

@droopsnoot

I usually call my database connection $dbc for example - short enough to not be a pain to type out, but enough there to remind me what it is.

Which one is the “database connection” that you call between (1) and (2) in the code below?

(1) $dbc1="mysql:host=$host;dbname=$dbname;charset=$charset";

(2) $dbc2 = new PDO($dbc1, $user, $pass, $options); 

[joon1]

@spaceshiptrooper

So it would be #2. The stuff in your #1 is only setting a string so that you can later use it in your connection.

Thank you very much

[joon1]

@droopsnoot

$PDO is an object of a particular class, and query is a function within that object.

With the code below, are the statements below both (1) and (2) correct?
(1) “$pdo” in the code below is an object.
(2) “$query” in the code below is a function.

$stmt = $pdo -> query ('SELECT myNumeric FROM myTable');