Query In PDO?

[justlukeyou]

Hi,

 

After almost completing my site I am now trying to update it into PDO. I thought I would start with a query. However when I Google stuff like "

 

 

<?php
$query = mysql_query("SELECT eventname, firstname, eventdetails, eventlocation, eventcountry, eventid, eventstart, eventend, logo, company, id, supplierdetails1, eventid
FROM users
JOIN eventjobs ON eventjobs.organiserid = users.id
ORDER BY eventid DESC
LIMIT 8 ");
while($row = mysql_fetch_array($query)) {
?>

 

I found this but it doesn't explain what $db is. Is the connection code? If so I cant even connect to my database using PDO.

 

$result = $db->query('SELECT username FROM users');
foreach($result as $row) {
echo $row['username'].'<br />'; // Output all usernames from the database.
}

[kicken]

$db is likely an instance of PDO, for example:

$db = new PDO(...);

Look in the PHP manual for the parameter information to build a correct function call.

[justlukeyou]

Thanks,

 

Is there a method I can use to build up a query and to test how it is working?

[Mad programmer]

Hello,

 

I'd like to suggest that you look into pdo's prepare function. First you build up your query with pdo prepare and then execute it. After that you may return the data in this way:

 

$pdo->fetchAll(PDO::FETCH_ASSOC);

 

Which returns all data as an associative array. You might find this link usefull

 

http://php.net/pdo

Edited March 31, 2013 by Mad programmer

[justlukeyou]

Thanks,

 

Are there any very basic templates for PDO to practice with?

[Jessica]

Yes. In the manual.

[justlukeyou]

Aha,

 

I got this to work with a connection. Now I just need to build the whole query I was originally using.

<?php
$result = $db->query('SELECT firstname FROM users');
foreach($result as $row) {
echo $row['firstname'].'<br />'; // Output all usernames from the database.
}
?>

Edited April 1, 2013 by ignace

[justlukeyou]

Okay,

 

So I got this working but will MySQL one day all be depracted and any site made from MySQL fail to work?

 

Is this the PDO I should be using...

<?php
$result = $db->query('SELECT eventname, eventlocation, eventcountry, eventid, logo, company, id, supplierdetails1, eventid FROM users
JOIN eventjobs ON eventjobs.organiserid = users.id
ORDER BY eventid DESC
LIMIT 8 ');
foreach($result as $row) {

?>

Edited March 31, 2013 by justlukeyou

[kicken]

The SQL text syntax (ie the SELECT ... part) for the most part does not change. The only changes you would need to make to the query text is to change any variables to placeholders and then use ->prepare and ->execute rather than ->query to run the queries.

 

Since the query in your original post does not contain and variables, you can just copy it verbatim into PDO's query method to run it.

 

The code in your last post looks fine and should work ok.

Edited March 31, 2013 by kicken

[justlukeyou]

Thanks,

 

Im really pleased I have a test that now works. Is it possible to convert MySQL to PDO by replacing one phrase for another with something like word?

 

For example could I just do find...

 

$query = mysql_query

 

and replace with...

 

$result = $db->query

[kicken]

No, not really. The api's and methodologies differ a bit too much for something like that. For example there is no $db->real_escape_string to replace mysql_real_escape_string. There is $db->quote which is similar, but the proper method to use is prepared statements where to completely remove the variables from your query text and use placeholders instead. Read the pages I linked above on prepare and execute for details and examples.

[justlukeyou]

Thanks,

 

The problem is I dont understand a single word of the PHP site. Every time I read it I get more confused.

 

What is it that the prepare does.

 

For example how do I a perform where clause if I cant use a variable?

 

How would I say echo "username where userid = $id"

[Mad programmer]

You can use variables That is where the prepare function is for. Like so:

 

$query = $db->prepare('SELECT *FROM pages WHERE offline = :offline');

$query->bindParam(':offline', $offlineVariable);

 

Prepare does as the name is called, it prepares the data for the sql server. Such as escaping the data etc.

[justlukeyou]

Hi,

 

I tried this which echoes the id (which I am declaring) but it does not echo the first name. Can anyone advise why this is please?

 

I can do this in MySQL.

			
		<?php
		$id = 441;
		
	$query = $db->prepare('SELECT * FROM users WHERE id = :id');
$query->bindParam(':id', $id);	

echo $id;
echo $firstname;
echo $row['firstname'];
                ?>		

Edited April 1, 2013 by justlukeyou

[Mad programmer]

You forgot to call $query-execute(); which executes the prepared statement. After that you may retrieve the data so: $query->fetch(); which returns the data as an array;

[Jessica]

You were also given the answers to the exact same questions almost a year ago:

http://forums.phpfreaks.com/topic/263338-using-pdo/

[justlukeyou]

Thanks Mad Programmer,

 

You have the ability to explain things in a way many other people dont have.

 

I looked up $query-execute(); but I dont understand how it should be used. I have run the query now all I am trying to do is to echo.

 

http://php.net/manual/en/pdo.query.php

		<?php
		$id = 441;
		
	$query = $db->prepare('SELECT * FROM users WHERE id = :id');
$query->bindParam(':id', $id);	
$query->execute();
{
echo $id;
echo $firstname;
echo $row['firstname'];
}
                ?>

Edited April 1, 2013 by justlukeyou

[justlukeyou]

http://www.php.net/manual/en/pdo.exec.php

 

On this page it says:

 

"This function cannot be used with any queries that return results. This includes SELECT, OPTIMIZE TABLE, etc."

 

Now Im even more confused.

Edited April 1, 2013 by justlukeyou

[Mad programmer]

True, but I was talking about execute and not exec

This is what the docs say:

PDO::exec() executes an SQL statement in a single function call, returning the number of rows affected by the statement.

You do NOT want the affected rows but you want to retrieve the rows themself which is done like this:

 

<?php
$id = 441;
		
$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 '<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

?>

Please compare the above piece of code to what you have written and note the differences.

[justlukeyou]

Thanks,

 

Its become clearer now.

 

How would I echo a specific row?

[Mad programmer]

Have you tried to run my supplied code example? You will see that $user is an array.

 

The array contains your database columns so you echo them like this:

 

echo $user['username'];

[justlukeyou]

Thanks,

 

That works fine now. Previously it echoes everything in the table under the ID.

 

Im failing to see how it easier to MySQL. It just seems different.

[justlukeyou]

Also, I was declaring my ID number like this. Is this considered PDO? Is there anyway I can determine if something is MySQL and something is PDO?

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

Now it seems I have to declare the variable before I use it in the query, is that right? Edited April 1, 2013 by justlukeyou

[Mad programmer]

Well MySQL is the language for the database itself while PDO is an abstraction layer for the database.

 

You can still use variables in a string like you did right there, however the bindParam also makes sure your data is safe and it protects against sql injections.

[justlukeyou]

Thanks,

 

So I do just need to deploy different syntax?

 

Could MySQL be switched off one day so any site using MySQL will just error? If so that will make it incredibly difficult to correct it.

 

It was hard enough planning how I wanted the site to appear.