CODEWITHSUNDEEP
phpmysqlpdoprepared-statement
Elastic Lamb
Elastic Lamb

Reputation: 353

PDO - prepared statements

I've got two recursive functions:

1)

function getCategories($id)
{
    global $con;
    $select = $con->prepare('SELECT * FROM categories WHERE parent_category_id = :parent_category_id OR (parent_category_id IS NULL AND :parent_category_id IS NULL)');
    $select->bindValue(':parent_category_id', $id, PDO::PARAM_NULL || PDO::PARAM_INT);
    $select->execute();
    // fetching.........
    for() ... getCategories(.......);
}

2)

$select = $con->prepare('SELECT * FROM categories WHERE parent_category_id = :parent_category_id OR (parent_category_id IS NULL AND :parent_category_id IS NULL)');

function getCategories($id)
{
    global $select;
    $select->bindValue(':parent_category_id', $id, PDO::PARAM_NULL || PDO::PARAM_INT);
    $select->execute();
    // fetching.........
    for() ... getCategories(.......);
}

Which is better/faster? Is it better to preapre the statment one time only?

Upvotes: 0

Views: 165

Answers (3)

Leri
Leri

Reputation: 12525

Second one should be faster because you don't call not needed statements. The idea of prepared statements is that you must prepare it once. But the best way to find out is profiling.

Here's simple way:

$start = microtime(true);

for ($i = 0; $i < 1000000; $i++){
     //your code here
}

echo microtime(true) - $start;

Upvotes: 1

s.webbandit
s.webbandit

Reputation: 17028

Second variant is faster, because you prepare "template" (with prepare() method) and then you send values to template and execute query in every iteration.

Upvotes: 0

matthias.p
matthias.p

Reputation: 1544

Your second function is definitely a lot faster, especially when it is executed very often. Because that is the main reason why prepared statements were invented: your SQL server has to parse and optimize your query only once. But in your first solution you would not even need prepared statements. So in my opinion a profiling test is only needed to find out how much faster the second one is and not to find out wether it is faster.

Upvotes: 0

Related Questions