transfer from mysqli to pdo

Mark1 · July 22, 2023

I have this code, which supply my pages with metadata based on url:

<?php

$url = isset( $_SERVER[ 'REQUEST_URI' ] ) ? basename( $_SERVER[ 'REQUEST_URI' ] ) : false;

$querysl="SELECT * from pslpages where page_url='$url'";

$queryres=mysqli_query($myConnection,$querysl);

$pagerow=mysqli_num_rows($queryres);

$pagedata=mysqli_fetch_assoc($queryres);

$page_title='';

$page_description='';

$page_keywords='';

$page_canonical_url='';

$page_body='';

if($pagerow>0)

{

$page_title=$pagedata['page_title'];

$page_description=$pagedata['page_description'];

$page_keywords=$pagedata['page_keywords'];

$page_canonical_url=$pagedata['page_canonical_url'];

$page_body=$pagedata['page_body'];

}

else{

$page_title='Mark - ';

$page_description='Mark - ';

$page_keywords='Mark - ';

$page_canonical_url='';

$page_body='';

}

?>

<!DOCTYPE html>

<head>

...which work so far fine, but I need to upgrade it to pdo (connection and querying). How to do it?

requinix · July 24, 2023

On 7/22/2023 at 7:44 AM, Mark1 said:

How to do it?

By understanding what the mysqli code you have now does, understanding how PDO works, and then translating between the two.

It's like translating between two spoken languages (but considerably easier): you hear what someone says, you understand what they're saying, and then you repeat their thoughts using different words.

    $queryres=mysqli_query($myConnection,$querysl);

    $pagerow=mysqli_num_rows($queryres);

    $pagedata=mysqli_fetch_assoc($queryres);

That runs a query, gets the number of rows it returned, and fetches the (first) row from the results. See what PDO offers to do that.

mac_gyver · July 24, 2023

when you make the connection using the PDO extension -

name the connection variable $pdo or similar so that you can search/distinguish between code that has and has not been converted.
set the character set to match your database table's character set, so that no character conversion occurs over the connection (this should always be done, but is rarely set.)
set the error mode to exceptions for all the statements that can fail (this is the default setting now in php8+)
set emulated prepared queries to false (you want to run real prepared queries whenever possible.)
set the default fetch mode to assoc (so that you don't need to specify it in each fetch statement.)

since you are probably doing this because you want to convert queries that have external, unknown, dynamic values being put directly into them, into prepared queries, converting from a non-prepared query to a prepared query is simple, when using the PDO extension -

remove, and keep for later, the php variables that are being put into the sql query statement.
remove any quotes that are around the php variables, any {}, and any extra quotes and concatenation dots that were used to get the php variables into the sql query statement.
put a ? place-holder into the sql query statement where each php variable was at.
call the PDO ->prepare(...) method. supplying it with the sql query statement. this returns a PDOStatement object.
call the PDOStatement ->execute([...]) method, supplying it with an array consisting of the variables you removed in step #1.
for a query that returns a result set, fetch the data from the query into an appropriately named php variable. the three commonly used fetch methods are - fetch() (fetch one row at a time), fetchAll() (fetch all the rows at once), and fetchColumn() (fetch a single column from a single row.)

ginerjm · July 25, 2023

Some notes on taking a closer look at your code:

$url = isset( $_SERVER[ 'REQUEST_URI' ] ) ? basename( $_SERVER[ 'REQUEST_URI' ] ) : false;
$querysl = "SELECT * from pslpages where page_url='$url'";

How can you run a query if the url is set to 'false'?

And what is the point of setting your local vars to null after doing the fetch when you then immediately set them to something else depending upon whether there was a row returned from the query?

Mark1 · July 27, 2023

Let me rephrase and simplify my question.

I have a database `mydb` with the table `metatags` having 3 columns: `page_id`, `page_url`, `page_title`.

When the pages are created dynamically-I'm using `page_id` as a base to supply different data, values from different rows of database to different webpages.

$page_id = filter_input(INPUT_GET, 'page_id', FILTER_VALIDATE_INT);

if (!$page_id) {

include '../errors/page-not-found.php';

}

$sql = "SELECT page_id, page_title FROM metatags WHERE page_id=:page_id;";

$metatags= pdo($pdo, $sql, [$page_id])->fetch();

if (!$metatags) {

include '../errors/page-not-found.php';

}

But, for static pages I have to use URL (I think) as a base, to make database to know which row of data to supply to the open webpage.

And that's my problem: how to query those rows based on url.

I'm started with connection:

<?php

try {

$pdo = new PDO('mysql:host=mysql;dbname=mydb;charset=utf8mb4', 'myuser', 'mypassword');

} catch (PDOException $e) {

$output = 'Unable to connect to the database server: ' . $e->getMessage();

}

After connection script I need to rewrite my old script using obsolete mysqli_query (), mysqli_num_rows() and mysqli_fetch_assoc () in PDO format; also based on url, and I don't know how to do it.

<?php

$url = isset( $_SERVER[ 'REQUEST_URI' ] ) ? basename( $_SERVER[ 'REQUEST_URI' ] ) : false;

$querysl="SELECT * from metatags where page_url='$url'";

$queryres=mysqli_query($myConnection,$querysl);

$pagerow=mysqli_num_rows($queryres);

$pagedata=mysqli_fetch_assoc($queryres);

And of course display on the page.

<?php echo $page_title; ?>

Sign In

transfer from mysqli to pdo

Recommended Posts

Mark1

Link to comment

Share on other sites

requinix

Link to comment

Share on other sites

mac_gyver

Link to comment

Share on other sites

ginerjm

Link to comment

Share on other sites

Mark1

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information