I thought PDO allowed that. I know MySQL itself (at least 5.1 that I use) does and other abstraction layers do.
Maybe it’s related to this issue where there’s a problem if you pass in a string (containing a number) as the limit:
http://stackoverflow.com/questions/2269840/php-pdo-bindvalue-in-limit
I’ll try right now.
It worked fine for me.
$dbh = new PDO(...);
$limit = 3;
$stmt = $dbh->prepare("SELECT * FROM users LIMIT ?");
$stmt->bindParam(1, $limit, PDO::PARAM_INT);
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
…returned 3 rows.
It spit out the array just fine.
So what function is it that you can loop thru to echo row data, like normally I use mysql_fetch_array
So what am I doing wrong here to get an empty result?
$dbtype = "mysql";
$dbhost = "localhost";
$dbname = "test";
$dbuser = "test";
$dbpass = "password";
$dbh = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser,$dbpass);
$limit = 3;
$stmt = $dbh->prepare("SELECT * FROM registry LIMIT ?");
$stmt->bindParam(1, $limit, PDO::PARAM_INT);
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
echo $result['governor'] . " " . $result['population'];
print_r($result) and you’ll see why
The array has 3 rows in it though
There is no $row[‘governor’]
There is a $row[0][‘governor’], $row[1][‘governor’], $row[2][‘governor’]
You need a loop to print multiple rows the same as if you used mysql_fetch_array
Okay cool, like this then?
while($row = $stmt->fetchAll(PDO::FETCH_ASSOC)) {
echo $row[0]['color'] . " " . $row[0]['class'] . "<br />";
}
sorry I switched field names on ya.
That works but how do I loop through all of the rows, so I can display them all w/o having to enter [1] and [2] etc, do I need to use a for loop inside the while? Basically it should mimic this, right?
while ($row = mysql_fetch_array($result)) {
echo $row[‘color’];
echo $row[‘size’];
etc.
}
Hi CatPDC,
Okay, so as to #3, how do I use prepared statements in a more general dbClass where I don’t know the table or LIMIT I am going to use ahead of time? How does that make this code re-usable if I can’t pass these value into it?
A little after the fact, but I too am building a CRUD group of classes and I have been working on how to make it more reusable. Here is an example how I did it for an Update class: [URL=“http://www.sitepoint.com/forums/showpost.php?p=4543196&postcount=8”]http://www.sitepoint.com/forums/showpost.php?p=4543196&postcount=8
foreach ($result as $row) {
echo $row['governor'] ...
}
or
for ($i = 0; $i < count($result); $i++) {
$row = $result[$i];
echo $row['governor']...
}
This is basic language syntax – you might want to read through some PHP tutorials on how to use arrays
PHP is really well documented on the php.net website
Are those variables really necessary? I bet not. Why waste lines and clutter your code with variable declarations, instead of writing:
$dbh = new PDO('mysql:host=localhost; dbname=test', 'test, 'password);
I know man, I just wasn’t sure if there was a special function like mysql_fetch_array that let’s you loop it with a while loop. Since this is in a Class i actually return all of the rows in an array and loop them in the object.
So you have a class with methods like “setTable” and “setFieldValue” that do one thing, set that value for the instance of the class?
What does the if statement do?
if ($stmt->execute()) {
$this->pdo->commit();
return true;
} else {
$this->pdo->rollback();
}
are you calling execute() in another method and this just checks whether that happend?
I’m reading up on it here: http://www.php.net/manual/en/pdo.begintransaction.php
Hi CatPDC,
The setter methods do simply set the properties of the class. Some people would do this in one array. Some do not like setters and choose to pass in variables; however to be clearer and to only set in the Crud classes the properties I need then I use the setters.
The transaction, commit, and rollback do what you have read in the manual and so if anything during the insert or update goes wrong then it will rollback.
The makeup of my CRUD class consists of interfaces like so:
nterface IGet {
function getByNameOrId();
function getLike();
}
interface IUniqueOrLast {
function getUnique();
function getLastInsertedId();
}
interface ISave {
function save($container); //update or insert
function saveNew(); //insert only
}
Then I have a base abstract class that has the methods that I want to share through out the different classes. so for my save class I do
class InsertUpdate extends Base implements ISave {
One of my methods in this InsertUpdate class is update()
function update() {
//Update
$sql = 'UPDATE ' . $this->table . ' SET ' . $this->field_value . ' = :update_value WHERE ' . $this->column_name .' = :value';
$stmt = $this->pdo->prepare(trim($sql));
$stmt->bindParam(':update_value', $this->update_value);
$stmt->bindParam(':value', $this->value);
//$this->pdo->beginTransaction();
try {
if ($stmt->execute()) {
$this->pdo->commit();
return true;
} else {
$this->pdo->rollback();
}
} catch (Exception $e){
return $e->getMessage(); //return exception
}
}
As earlier mentioned once the sql is prepared and the parameters are bound it tries to execute. If it goes well then the update is committed and if not it rolls back. If an exception occurs it will bubble up and get caught by my error handling class.
A more complete view of how this works is:
function testUpdate() {
// Wiring...
$container = new bucket_Container(new DbFactory);
$insertUpdate = $container->get('InsertUpdate');
//Insert record to Update
$insertUpdate->setTable('users');
$expression = array('insert');
$insertUpdate->setSelectExpression($expression);
$insertUpdate->setColName('uid_number, uid , display_name , firstName, lastName, password, user_info, signature, has_email');
$insertUpdate->setValue(
"(10004, btest, Bee Test, Bee, Test, c154da9694c9f27ccd17f112f4472cd32271845a3a8d279ff400a6e4466fcc8f, 0, 0, 0)"
);
$output =$insertUpdate->save($insertUpdate); // Insert
if(!empty($output)) { // if it does not exist.
$this->assertEqual(True, $output); //Insert
}
//Update
$insertUpdate->setTable("users");
$insertUpdate->setFieldValue("firstName");
$insertUpdate->setUpdateValue("Be");
$insertUpdate->setColName('uid_number');
$insertUpdate->setValue("10004");
$insertUpdate->update($insertUpdate);
if(!empty($output)) {
$this->assertEqual(True, $output);
}
$insertUpdate->setTable("users");
$insertUpdate->setFieldValue("display_name");
$insertUpdate->setUpdateValue("Be Test");
$insertUpdate->setColName('uid_number');
$insertUpdate->setValue("10004");
$insertUpdate->update($insertUpdate); //Update
if(!empty($output)) {
$this->assertEqual(True, $output);
}
}
Hope this helps.
Steve
Awesome, when I get done with my ditch digging day job this weekend I have some work to do!
Hi catpdc,
First of all, it’s important that you know that “PDO” and “prepared statements” are two seperate things. PDO is a class/interface for performing operations against a database, where as prepared statements are a feature of MySQL which is supported by both the PDO and the mysqli PHP extensions.
There are two reasons why you may use prepared statements. The first is that by design, prepared statements prevent SQL injection. They do this simply by only allowing “values” to be passed when executing a prepared statement. Because of this, any data sent to a prepared statement will be treated as a value, hence any special MySQL characters or keywords are not interpreted as MySQL code, but are merely treated as textual data.
The second reason you may use prepared statement is for performance reasons. Depending on the type of application and how’s it’s been designed, prepared statements can either increase or decrease performance. Unlike a normal SQL query, prepared statements require two separate requests to the server. The first request sends the prepared statement (e.g. “SELECT title FROM table WHERE author = ?”), while all subsequent requests send a reference to that prepared statement along with the accompanying data. If you’re application rarely re-uses prepared statements, then it’s likely they will decrease performance. On the other hand, if your application executes a set queries many times, but with different “values”, then prepared statements can increase performance.
I hope that’s slightly improved your understanding of PDO and prepared statements. Prepared statements are a new concept, so like any new concept, it’ll take a little while to properly grasp and understand; like when you go from procedural code to OOP, although it shouldn’t take near as long to fully grasp.
Yes, that helps me understand more for sure. The site I am building is a classifieds site, so it repeats the same query over and over every time someone clicks on an item for sale, exactly like craigslist. The only things that will change are category and post ID… so prepared statements should make that faster because it is calling the same query over and over simply by reference, and sending an array of ? dynamic values with it, to avoid resending the static SELECT code, correct?
Okay, I have prepared statements figured out in the basic sense.
In my normal SQL Queries for pagination I use a LIMIT with an offset and a # of rows I want per page, like so:
$limit = $offset . ', ’ . $rowsperpage;
The query might end up like LIMIT 30, 15 on page 3.
When I use this with prepared statements though, I get zero results. If I set LIMIT to 15 it works fine but “0, 15” doesn’t work. Here’s the code I am using:
$this->_queryString .= " LIMIT ?";
$stmt = $dbh->prepare($this->_queryString);
$stmt->bindParam(3, $this->_limit, PDO::PARAM_STR);
If I set it to PARAM_INT and pass it one value, say 15, I get 15 results. If I set it to string and create $limit like “0, 15” I get nothing… ideas?
I tried this too with no luck:
$this->_queryString .= " LIMIT ?, ?";
$stmt->bindParam(3, $this->_limit1, PDO::PARAM_INT); // $_limit1 = 0;
$stmt->bindParam(4, $this->_limit2, PDO::PARAM_INT); // $_limit2 = 15;
edit
It looks i can use OFFSET, forgot about that one.
LIMIT ?, ?
Remember, bind values, not expressions. You have to pass in the complete query you want to execute, not a partial query you’re going to append more syntax to.
I’ve verified this works, same example I gave on the last page but with an offset too, so you did something wrong with your code.
$stmt = $dbh->prepare("SELECT * FROM users LIMIT ?, ?");
$stmt->bindParam(1, $offset, PDO::PARAM_INT);
$stmt->bindParam(2, $limit, PDO::PARAM_INT);
$stmt->execute();