PHP with PDO problem mysqli_stmt::fetch()

Question

I need to convert my code to PDO and I don't know where is the problem.

$user = $_SESSION['user_email'];
$get_user = "SELECT * FROM users where user_email='$user'"; 

//This 2 lines was there in original
$run_user = mysqli_query($con,$get_user); //original
$row=mysqli_fetch_array($run_user); //original

//Converting to PDO
$run_user = $con->prepare($get_user); 
$run_user->execute();
$row = $run_user->fetch(PDO::FETCH_ASSOC); //line 27

// line 38 and 39
$user_id = $row['user_id']; 
$user_name = $row['user_name'];

My errors are:

Warning: mysqli_stmt::fetch() expects exactly 0 parameters, 1 given in /Applications/XAMPP/xamppfiles/htdocs/network/includes/header.php on line 27

Notice: Trying to access array offset on value of type null in /Applications/XAMPP/xamppfiles/htdocs/network/includes/header.php on line 38

Notice: Trying to access array offset on value of type null in /Applications/XAMPP/xamppfiles/htdocs/network/includes/header.php on line 39

You can't connect as mysqli and then use the PDO API. – Nigel Ren Nov 13 '20 at 17:37 — Nigel Ren, Nov 13 '20 at 17:37

Ro Achterberg · Answer 1 · 2020-11-13T17:52:47.940

You're mixing the mysqli and PDO interfaces. This isn't possible. See this demo on how to do the same thing in PDO.

<?php

// Create a database using PDO, by passing a DSN (Data Source Name), username, and password.
// Replace yourserverhost, yourusername, yourpassword with the real credentials.
// Depending on the charset of your tables, you may want to change utf8mb4 as well,
// although this is a typical value.
$pdo = new PDO(
    'mysql:host=yourserverhost;dbname=yourdatabasename;charset=utf8mb4',
    'yourusername',
    'yourpassword'
    );

$user = $_SESSION['user_email'];

$sql = 'SELECT * FROM users where user_email=?';
$statement = $pdo->prepare($sql);
$statement->bindValue(1, $user, PDO::PARAM_STR);
$statement->execute();

$row = $statement->fetch(PDO::FETCH_ASSOC);

$user_id = $row['user_id']; 
$user_name = $row['user_name'];

Your initial query is rewritten with a positional parameter (?).
The query is then made into a prepared statement to avoid SQL injection. The variable is bound using bindValue, starting at index 1. Other flavors of parameter binding are available.
PDOStatement::execute() runs the query.
PDOStatement::fetch(PDO::FETCH_ASSOC) fetches your row.
Do stuff with $row['user_id'] and $row['user_name'].

Point taken, although the comments were left in there _on purpose_, to provide a bit of line-by-line context and comparison to the original query. I added the PDO constructor with some comments as per your excellent suggestion. — Ro Achterberg, Nov 13 '20 at 17:53

Dharman · Accepted Answer · 2020-11-13T18:44:39.323

1

To convert your code to PDO you must ensure that you connect with PDO first. These are two very different APIs and you can't mix them.

First connect:

$pdo = new PDO("mysql:host=localhost;dbname=test;charset=utf8mb4", 'user', 'pass', [
    \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
    \PDO::ATTR_EMULATE_PREPARES => false
]);

Then prepare and execute a statement. Pay attention to never include variables directly in SQL. Every piece of data should be bound separately. In your SQL, put ? where the data should be bound to, and then pass an array with values in execute()

$run_user = $pdo->prepare('SELECT * FROM users where user_email=?'); 
$run_user->execute([
    $_SESSION['user_email']
]);

Now you can fetch the data. Make sure that your SQL actually fetched something before trying to access it.

$row = $run_user->fetch(PDO::FETCH_ASSOC);
if($row) {
    $user_id = $row['user_id']; 
    $user_name = $row['user_name'];
}

edited Nov 13 '20 at 18:44

answered Nov 13 '20 at 17:52

Dharman

30,962
25
85
135

It's better but still there is error: mysqli_stmt::fetch() expects exactly 0 parameters, 1 given. I dont understand why its still using mysqli if i have in my connections new PDO DB. It still searching for mysqli. Thank you for your help – EmirTheBest7 Nov 13 '20 at 18:42
@EmirTheBest7 That is because you are still connecting via mysqli. Find `new mysqli` or `mysqli_connect` in your code and remove it – Dharman Nov 13 '20 at 18:44
@EmirTheBest7 Also, use a different name for your PDO variable. I just noticed I made a typo. – Dharman Nov 13 '20 at 18:45

PHP with PDO problem mysqli_stmt::fetch()

2 Answers2