How to compile Propel Criteria to SQL

php sql criteria propel
smileua picture smileua · Sep 9, 2009 · Viewed 9.8k times · Source

How can I compile Propel Criteria to clear SQL? I've tried $criteria->toString(); but this is not I expected. Also I've tried ModelPeer::doSelectStmt($criteria) but it returned raw sql (required parameters substitution)

Answer

Hans L picture Hans L · Sep 18, 2009

First of all, it's important to note that Propel uses PDO with prepared statements, so you're not going to get a fully "built-out" SQL statement in PHP. Using the Criteria->toString() is a good start, but as Peter mentions a lot of the work is indeed done by the BasePeer::createSelectSql() method.

Here's the most complete way (from Propel) to see what the SQL will look like (with placeholders) and the parameters that will be substituted in:

$params = array(); // This will be filled with the parameters
$sql = BasePeer::createSelectSql($criteria, $params);

print "The raw SQL: " . $sql . "\n";
print "The parameters: " . print_r($params, true) . "\n";

Note that you may get better mileage from just logging the queries at the database level. Of course, if PDO is configured (or supports) to use native db prepared statements, then you may still be seeing placeholders in the db too.

Related questions

How do I use a complex criteria inside a doctrine 2 entity's repository?

Lets say I have a table that holds information about festivals. Each festival has a start and end date. I want to select all the festivals that are live (that happen) on a given date. Meaning, I want to select …

Read More
How can I prevent SQL injection in PHP?

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example: $unsafe_variable = $_POST['user_input']; mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')"); That's …

Read More
MySQL query String contains

I've been trying to figure out how I can make a query with MySQL that checks if the value (string $haystack ) in a certain column contains certain data (string $needle), like this: mysql_query(" SELECT * FROM `table` WHERE `column`.contains(…

Read More