PDO multiple queries

[The Little Guy]

I am learning PDO with MySQL, and was wondering how can I do 2 queries?

 

<?php
$sql = $pdo->prepare("select sql_calc_found_rows ...");
$pdo->execute();

 

With that how can I run select found_rows()?

 

Is it safe to do this?

<?php
$sql = $pdo->prepare("select sql_calc_found_rows ...");
$pdo->execute();
$numrows = $pdo->query("select found_rows()")->fetchColumn();

Edited January 12, 2013 by The Little Guy

[Jessica]

Use the built in num_rows functionality. Look in the manual.

[The Little Guy]

Their isn't a num_rows, that is for php's mysql and mysqli...

[Jessica]

Lazy.

 

http://us3.php.net/PDO

http://us3.php.net/manual/en/pdostatement.rowcount.php

 

If you're doing a select, yes do two queries. I don't even understand why there's a question of IF you can do two queries. 

Edited January 12, 2013 by Jessica

[Backslider]

With PDO you don't need to use 'sql_calc_found_rows'.

 

After your normal select, simply use this:

 

$rowcount = $pdo->query("SELECT FOUND_ROWS()")->fetchColumn();

Edited January 12, 2013 by Backslider

[The Little Guy]

But what if I have a limit in the query, and I want to know how many would have been returned, don't I still need sql_calc_found_rows

[The Little Guy]

Lazy.

 

http://us3.php.net/PDO

http://us3.php.net/manual/en/pdostatement.rowcount.php

 

If you're doing a select, yes do two queries. I don't even understand why there's a question of IF you can do two queries. 

 

The question doesn't contain an "IF" it's contains a "How". Sorry if I am being confusing.

 

Here is the full query I am asking about, this is how I am assuming it is done, correct me if I am wrong:

 

<?php
$sql = $pdo->prepare("select sql_calc_found_rows question, question_id, count(a.answer_id) total,
            match(question) against (:q in boolean mode) as score
            from questions q
            left join answers a using(question_id)
            where
            match(question) against (:q in boolean mode)
            and q.deleted < 4 and (a.deleted < 4 or a.deleted is null)
            group by q.question_id order by score desc limit $limit, $max");
$sql->bindParam(":q", $_GET["q"], PDO::PARAM_STR);
$sql->execute();
$numrows = $pdo->query("select found_rows()")->fetchColumn();

[kicken]

You have to fetch all the results from your first query before you can run your second query, otherwise you'll get an error regarding commands out of sync.

 

eg:

$sql = "select ...";
$stmt = $db->prepare($sql);
$stmt->exec();
$results = $stmt->fetchAll();

$sql = "select found_rows()";
$stmt = $db->query($sql);
$rows = $stmt->fetchColumn(0);
$stmt->closeCursor();

[Backslider]

I am assuming that you are paginating, in which case I would just use a regular COUNT(*) query - you always need two queries for pagination, even if you use sql_calc_found_rows (which may not perform as well).