Query In PDO?

[Mad programmer]

If you by different syntax mean the way of constructing a query in PDO then no. Except for binding the parameters. This is because pdo sends the query to the mysql server and afterwards your binded parameters.

 

As of php 5.5 the mysql_* functions will be removed(I think) and you will need to use pdo or the mysqli functions for your scripts.

 

MySQL itself cannot be shutdown since it is just a database software package, the interfaces for php are a different story

[justlukeyou]

Hi,

 

The following reads the userID when I am logged in and echoes the firstname of the user. However with my MYSQL code I was running the query against the user ID so it took less code. Is the first set of code correct, I take it isn't?

    <?php
    $id = intval($_SESSION['userID'])  ;
    $query = $db->prepare('SELECT * FROM users WHERE id = :id'); // You first PREPARE the query
    $query->bindParam(':id', $id );	// You bind the required parameters for your query
    $query->execute(); // This sends the query to the SQL server
     
    $user = $query->fetch(PDO::FETCH_ASSOC); // Specify the PDO::FETCH_ASSOC to fetch the data as an associative array (not required)
     

     echo $user['firstname']; 
    ?>	

$query = "SELECT * FROM users WHERE id = " . intval($_SESSION['userID']) . " LIMIT 1";
if ($result = mysql_query($query)) {

[Mad programmer]

That is correct, however PDO does not always require that much lines of code. The prepare functions are for safety purposes. It would be good practice to put these to use.

 

But in your case you typecast to an int by using intval which may also be passed directly to a sql query. You can do this with PDO like so:

 

(Example taken from php.net/pdo and modified a bit)

 

<?PHP

$users = $pdo->query("SELECT * FROM users WHERE id = {$id}")->fetch();

?>

 

Sorry for not using codetags but I am on my cell phone right now.

[justlukeyou]

Hi,

 

I tried to use this but I cant seem to full the fetch. To tell it that $id is from the the userID session.

    <?php
    $query = $pdo->query("SELECT * FROM users WHERE id = {$id}")->fetch(intval($_SESSION['userID'])); // You first PREPARE the query
    $query->bindParam(':id', $id );	// You bind the required parameters for your query
    $query->execute(); // This sends the query to the SQL server
     
    $user = $query->fetch(PDO::FETCH_ASSOC); // Specify the PDO::FETCH_ASSOC to fetch the data as an associative array (not required)
     
	echo $id;
     echo $user['firstname']; 
    ?>		

[Mad programmer]

Woa woa hold there you DO know how to use a variable in a string right?

 

Take my modified example from above and BEFORE that do this; $id = $_SESSION['userid']; bindParam is not working with query but only with prepared statements.. the fetch function is only taking pdo arguments such as PDO::FETCH_ASSOC.

 

My modified example is all you need, you're combining prepared pdo wirh normal query statements and that wont work.

[justlukeyou]

Hi,

 

Im totally confused now. I cant see where you are saying $id = $_SESSION['userid'];

 

Is that the one thing I need to do instead of declaring it beforehand?

    <?php
    $query = $pdo->query("SELECT * FROM users WHERE id = {$id}")->fetch();
    $query->bindParam(':id', $id);	// You bind the required parameters for your query
    $query->execute(); // This sends the query to the SQL server
     
    $user = $query->fetch(PDO::FETCH_ASSOC); // Specify the PDO::FETCH_ASSOC to fetch the data as an associative array (not required)
     
    echo '<pre>'; // I always PRE before I print an array which makes it more readable in the browser
    print_r($user); // This contains your fetched row from the sql server
     
    ?>

[jcbones]

Less code !== better code.

 

You need to look at what Mad Programmer is writing.  He didn't explicitly set your $id variable, he is asking that YOU set it yourself.  This is so that he doesn't feel like you are just copy pasting without learning.

 

So now, Mad Programmer has given you two ways to get your PDO results.

1.

 

<?php

$id = intval($_SESSION['userID']) ;

$query = $db->prepare('SELECT * FROM users WHERE id = :id'); // You first PREPARE the query

$query->bindParam(':id', $id );    // You bind the required parameters for your query

$query->execute(); // This sends the query to the SQL server



$user = $query->fetch(PDO::FETCH_ASSOC); // Specify the PDO::FETCH_ASSOC to fetch the data as an associative array (not required)





echo $user['firstname']; 

 

 

Which is perfectly valid code.  You are sending the intval of the user id held in session to PDO.

 

But, you asked for a shorter way, so he told you of another way that would shorten the whole process.  He didn't mean for you to mix the process.

2.

<?php
$id = intval($_SESSION['userID']);
$user = $db->query("SELECT * FROM users WHERE id = {$id}")->fetch(PDO::FETCH_ASSOC);
echo $user['firstname'];

 

See it is shorter, and just as protected as your original mysql function.

[justlukeyou]

Thanks,

 

I thought I had to define the id number here like my previous code.

$user = $db->query("SELECT * FROM users WHERE id = {$id}")->fetch(PDO::FETCH_ASSOC);