Changing code from MySQL to PDO

Question

I created a CMS Script written using MySQL syntax.

I want to replace MySQL syntax with PDO syntax. Can someone help me do this and explain to me how to do this ?

This is the code from the script.

<?php $querytemp = mysql_query("select * from main_setting") or die (mysql_error()); $row = mysql_fetch_object($querytemp); include "inc/upcenter_block.php"; echo " <div class='headmenu'>$row->news1</div> <div class='bodymenu'> <p>".nl2br($row->news)."</p> </div> "; include "inc/downcenter_block.php"; ?> 

Answer 1

First, if you want to switch from mysql_* to PDO

you will need to change all your codes in a script, not just the only one that just doesn't work

and if you are going to change the codes from mysql_ * to PDO

you will need to change the database connection using PDO

here is a sample for this:

 // here we set the variables $dbhost = "localhost"; $dbname = "testcreate"; $dbuser = "root"; $dbpass = "mysql"; // here we are using ( try {} ) to catch the errors that will shows up and handle it in a nicer way try { $db = new PDO('mysql:host='.$dbhost.';dbname='.$dbname.';charset=utf-8', ''.$dbuser.'', ''.$dbpass.''); $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); } catch (PDOException $e) { echo 'Error : <br>' . $e->getMessage(); } 

---

 // here we set the varible for the connection = then starting the cennction with new POD(); $db = new PDO('mysql:host='.$dbhost.';dbname='.$dbname.';charset=utf-8', ''.$dbuser.'', ''.$dbpass.''); 

---

 // here we set an Attribute to handle the errors $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // you dont need to use it in our case because we already catching the error and handling it in out way 

---

  // here we catch the error then handling it by echo a msg and then we used // $e->getMessage(); to get the error msg that should be throwing in the page catch (PDOException $e) { echo 'Error : <br>' . $e->getMessage(); } 

--------------------------------------------

now that we are done with the connection i will show you how to query and retrieve tables

  // this is how we will use query $qr = $db->query() // and this is how to fetch it by taking the query variable and use the arrow then fetch $ro = $qr->fetch() 

iwill will show you an example for your code

 $querytemp = mysql_query("select * from main_setting") or die (mysql_error()); $row = mysql_fetch_object($querytemp); 

we will change it to

 $querytemp = $db->query("select * from main_setting"); $row = $querytemp->fetch(PDO::FETCH_OBJ); 

now you can use $row->news with PDO

and now you can easily change your codes to PDO

Answer 2

Converting this script will be something like a string:

 // $pdo = connection try { $stmt = $pdo->prepare("select * from main_setting"); $stmt->execute(); $row = $stmt->fetch(PDO::FETCH_OBJ); } catch (\PDOException $e) { // something went wrong } // ... 

Here is an explanation:

• PDO::prepare will create a prepared statement from a MYSQL query and save it in a variable.
• PDOStatement::execute will execute the prepared statement using an array of parameters passed to it (in this case, no, because the request has no parameter).
• PDOStatement::fetch will display the results of the last run. By default, it is selected into an array. If you pass PDO::FETCH_OBJ , it will select the object.

PDO uses exceptions by default, which means you can use a try-catch block to detect an error (such as a PDOException ).

Also note:

 $stmt = $pdo->prepare("select * from main_setting"); $stmt->execute(); 

can be shortened using PDO::query so that:

 $stmt = $pdo->query("select * from main_setting");