PDO Questions

@site-developerMar 15.2019

Folks,

For 2yrs now, in many forums I’ve been told to learn pdo. Been telling every programmer that said this that I will soon. Soon like next week or next fortnight and so on. And now, it’s 2yrs passed and I still haven’t started it. And so, I thought I will start it tonight.
Reading pdodelusions.com which everyone kept recommending.
As I read along and come across things I really do not understand, I will bother you people in this thread.
And yes, after 2yrs of starting on php, I am still a beginner. 😉

Where is that Benanamen who kept bugging me to start on pdo from forum to forum ? Lol! 😀
He’ll probably be glad that I finally hopped-on board! 😉 Right Benny ?

to post a comment

PHP

5 Comments(s) _↴

@site-developerauthorMar 15.2019 — #"PDO is a Database Access Abstraction Layer".

Q1. Just what is "abstraction layer" ?

Q2. I do not understand this part about "Connecting DSN":

_"Connecting. DSN

PDO has a fancy connection method called DSN. It's nothing complicated though - instead of one plain and simple list of options, PDO asks you to input different configuration directives in three different places:

database driver, host, db (schema) name and charset, as well as less frequently used port and unix_socket go into DSN;

username and password go to constructor;

all other options go into options array.

where DSN is a semicolon-delimited string, consists of param=value pairs, that begins from the driver name and a colon:

mysql:host=localhost;dbname=test;port=3306;charset=utf8mb4
driver^ ^ colon ^{^param=value} pair ^{^semicolon}

Note that it's important to follow the proper format - no spaces or quotes or other decorations have to be used in DSN, but only parameters, values and delimiters, as shown in the manual.

Here goes an example for mysql:

<i>
 </i>$host = '127.0.0.1';
 $db   = 'test';
 $user = 'root';
 $pass = '';
 $charset = 'utf8mb4';
 
 $dsn = "mysql:host=$host;dbname=$db;charset=$charset";
 $options = [
 PDO::ATTR_ERRMODE            =&gt; PDO::ERRMODE_EXCEPTION,
 PDO::ATTR_DEFAULT_FETCH_MODE =&gt; PDO::FETCH_ASSOC,
 PDO::ATTR_EMULATE_PREPARES   =&gt; false,
 ];
 try {
 $pdo = new PDO($dsn, $user, $pass, $options);
 } catch (PDOException $e) {
 throw new PDOException($e-&gt;getMessage(), (int)$e-&gt;getCode());
 }

_

"

What is the real difference beteen the pdo version shown above in pdodelusions.com and this following mysqli_ version which I am familiar with ? They both ask for user inputs regarding mysql database. Nothing different.

<i>
 </i>&lt;?php 
 
 //Connect to Mysql Database in this input format: "Server", "MySql User", "Mysql User Password", "MySql Database Name". 
 $conn = mysqli_connect("localhost","root","","followingbrowser"); 
 
 if (!$conn) 
 { 
 //Error Message to show user in technical/development mode to see errors.
 die("Database Error : " . mysqli_error($conn)); 
 
 <i>	</i>//Error Message to show User in Layman's mode to see errors.
 <i>	</i>die("Database error."); 
 <i>	</i>exit(); 
 } 
 
 ?&gt;

Frankly, the mysqli_ version is simpler. Is it not ?

The tutorial says about PDO DSN: "PDO asks you to input different configuration directives in three different places:

database driver, host, db (schema) name and charset, as well as less frequently used port and unix_socket go into DSN;"

Well, we also do that with mysqli_ giving host name, username, user password to connect to mysql with mysqli. So, what is the real significantly wow wow about PDO DSN here ? I'm scratching my head.

https://phpdelusions.net/pdo

@site-developerauthorMar 15.2019 — #pdodelsuions.com says this:

"Important notes for the late mysql extension users:

Unlike old mysql_* functions, which can be used anywhere in the code, PDO instance is stored in a regular variable, which means it can be inaccessible inside functions - so, one has to make it accessible, by means of passing it via function parameters or using more advanced techniques, such as IoC container."

Q1.

Now, is not this a nuisance about being inaccessible in functions ? Was not pdo supposed to make our lives easier than mysqli_ ?

Let's read more ...

"The connection has to be made only once! No connects in every function. No connects in every class constructor. Otherwise, multiple connections will be created, which will eventually kill your database server. Thus, a sole PDO instance has to be created and then used through whole script execution."

Q2.

Now that is good. Don't have to type "conn" nearly in every single function's parameter when dealing with mysql db. On mysql_ extension why was it necessary to connect everytime ? Was the extension dropping the connection every now and then without us writing lines of code for it to drop it ? It was doing auto dropping, was it ?

Let's read more ...

Q3.

"It is very important to set charset through DSN - that's the only proper way because it tells PDO which charset is going to be used. Therefore forget about running SET NAMES query manually, either via query() or PDO::MYSQL_ATTR_INIT_COMMAND. Only if your PHP version is unacceptably outdated (namely below 5.3.6), you have to use SET NAMES query and always turn emulation mode off."

Can someone show me an example what they mean ?

Show me a mysqli_ example and then a pdo one. That way, when I look at the mysqli_ code I will understand what pdodelusions.com is blabbering about. Also, I can compare the 2.

@site-developerauthorMar 15.2019 — #On pdodelusions.com, I am now reading ....

"Running queries. PDO::query()

There are two ways to run a query in PDO. If no variables are going to be used in the query, you can use the PDO::query() method. It will run your query and return special object of PDOStatement class which can be roughly compared to a resource, returned by mysql_query(), especially in the way you can get actual rows out of it:

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

while ($row = $stmt->fetch())

{

echo $row['name'] . "n";

}

Also, the query() method allows us to use a neat method chaining for SELECT queries, which will be shown below."

Q1. What is "object" ? Yeah, I keep hearing oop and procedural style programming but I really do not understand the former. As for the latter, all I understand is that the code flows serially from top to bottom and you don't go jumping up and down the file like a frog. But yet again, do not you ? Do not we when we create and use fu7nctions where the function definitions are at the top and throughtout our code flow we reference it to the function (which is residing at the top of the code flow) ?

In other words, I do not see the difference between using a function and this oop style.

Anyone care to explain the real difference showing a sample code of a procedural style comparing it with a sample code from oop style ?

Q2. Above, it was talking about mysqli resource. What is a resource ? Yeah, I used mysqli_ for 2yrs now but I still don't understand what a mysql resource is. So, pdo does not have this thing called resource ? Why ? Is resource a bad thing ? Is the "object" thing in oop style or pdo a substitute to the mysqli_ resource ? Enlighten me on this subject, someone.

@NogDogMar 15.2019 — #PDO has 2 main advantages over MySQLi, at least for me:

1. It supports many different database engines, not just MySQL. (I use it regularly at work with PostgreSQL.)

2. It makes prepared statements with bound parameters easier to use and more readable -- none of those repeated "?" place-holders and the "ssdsss" strings in the mysqli binding function.

<i>
 </i>$pdo = new PDO($dsn, $usr, $pwd);
 $pdo-&gt;setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 $sql = "
 SELECT *
 FROM my_table
 WHERE
 col_1 = :col1 AND
 col_2 = :col2
 ";
 try {
 $stmt = $pdo-&gt;prepare($sql);
 $stmt-&gt;execute(array(
 ':col1' =&gt; $some_variable,
 ':col2' =&gt; $another_variable
 ));
 $result = $stmt-&gt;fetchAll(PDO::FETCH_ASSOC);
 }
 catch(PDOException $e) {
 error_log("Database error:n".var_export($e, true));
 die("Database error, data logged.");
 }

@site-developerauthorMar 15.2019 — #@NogDog#1601796

Thanks NogDog,

Do reply to my other questions so I do not come at a standstill and get put off from pdo and put it aside again for another 2yrs.

Also, the ghost of mysqli_ is still lingering on my mind and so best I get my procedural style codings' questions answered on my other latest opened threads so I can finally nail the mysqli_ and procedural style in the coffin for good and not look back. What you say ? When you get the time, do reply to my other threads.

Cheers!

Also in #PHP _↴

Path variable linking to field name"[RESOLVED] error on page why query is empty.

Success!

Help @site-developer spread the word by sharing this article on Twitter...

Tweet This

PDO Questions

5 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

PDO Questions

5 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

5 Comments(s) _↴

Also in #PHP _↴