PHP/MySQL to PDO

Question

I want to change MySQL to PDO:

$mapa = mysql_fetch_array(mysql_query("select * from mapa where id = ".$postac['mapa']." limit 1"));
$mapa_d = mysql_query("select * from mapa_d where mapa = ".$mapa['id']." ");

PHP:

 $_SESSION['postac'] = $_POST['postac'];

try like this so far:

$stmt = $pdo->prepare("SELECT * FROM mapa WHERE id=:mapa");  
$stmt->bindValue(':mapa', $postac, PDO::PARAM_STR);  
$stmt->EXECUTE();  
$postac = $stmt->fetchAll(PDO::FETCH_ASSOC);

mysql update:

mysql_query("update postac set logged = 1 where id = ".$_SESSION['postac']." limit 1");

PDO:

$stmt = $pdo->prepare("update postac set logged = 1 where id:postac");  
$stmt->bindValue(':postac', $_SESSION, PDO::PARAM_STR);  
$stmt->EXECUTE();  
$_SESSION = $stmt->fetchAll(PDO::FETCH_ASSOC);

Does not work.

Please, don't ever say "doesn't work". That's the most irritatingly vague you can describe a problem. Do you get an error? Does your computer emit sparks and catch on fire like in Star Trek? Tell us **something**. Also, it's `execute()`. Case can matter, don't yell. — tadman, Aug 16 '16 at 07:48

score 3 · Accepted Answer · edited May 23 '17 at 12:22

Pre-Answer Note:

I assume you have already set up a PDO connection construct ($pdo) before trying to run your PDO queries.

$mapa = mysql_fetch_array(
          mysql_query("select * from mapa WHERE id = ".$postac['mapa']." limit 1"));
$mapa_d = mysql_query("select * from mapa_d WHERE mapa = ".$mapa['id']." ");

PHP:

 $_SESSION['postac'] = $_POST['postac'];

try like this so far:

$stmt = $pdo->prepare("SELECT * FROM mapa WHERE id=:mapa");  
$stmt->bindValue(':mapa', $postac, PDO::PARAM_STR);  
$stmt->EXECUTE();  
$postac = $stmt->fetchAll(PDO::FETCH_ASSOC);

PART 1:

Be Consistent

Your original statement uses a value $postac['mapa'] as an id reference in the MySQL_ query, but then your PDO statement you are passing the whole array as a value into the PDO query.

First, MySQL: id ==> $postac['mapa']

Second, PDO: id ==> $postac

So this is causing an immediate issue as you're passing a whole array in to PDO which is somehow expected to extract one value from this array. This array is being classed as a string with your PDO::PARAM_STR declaration so this is preventing the query from using this value, as it doesn't fit what it's told to expect.

Therefore this returns a NULL query.

So to fix it,

 $stmt = $pdo->prepare("SELECT * FROM mapa WHERE id=:mapa");  
 $stmt->bindValue(':mapa', $postac['mapa'], PDO::PARAM_STR);  
 $stmt->execute();  
 $postac = $stmt->fetchAll(PDO::FETCH_ASSOC);

Part 2:

Syntax

$stmt = $pdo->prepare("update postac set logged = 1 where id:postac");  
$stmt->bindValue(':postac', $_SESSION, PDO::PARAM_STR);  
$stmt->EXECUTE();  
$_SESSION = $stmt->fetchAll(PDO::FETCH_ASSOC);

As above, you're passing the whole $_SESSION array as a PARAM_STR value, so it's returning VOID /NULL. You also have a syntax fault that you're using WHERE id:postac, but you really mean WHERE id = :postac be careful of missing out syntax such as = !!.

PART 3:

Error Checking

It is well worth exploring and learning how to get useful error feedback on PHP PDO, as it will save you posting to StackOverfow X times a day (hopefully!)! There is a good answer here about how to setup PDO to output errors. It is also well worth browsing the PHP Manual for PDO error checking details.