Raw SQL - row count and last insert id

rf1234

Hi Allan,

I would like to use the Editor database object for some of my own sql queries. I could make this work:

$statement = ('SELECT DISTINCT rfa_id FROM rfa_has_rfp 
                           WHERE rfp_id = :rfp_id');

$row =
$db ->raw()
    ->bind(':rfp_id', $rfpId)
    ->exec($statement)
    ->fetch(PDO::FETCH_ASSOC); // a one-dimensional array is returned SINGLE

$rowCount =
$db ->raw()
    ->bind(':rfp_id', $rfpId)
    ->exec($statement)
    ->count();

For the row count and the last insert id: is there a simpler way to do this? Do I really need to repeat the entire statement? I looked at this but I couldn't figure it out: https://editor.datatables.net/docs/1.6.1/php/class-DataTables.Database.Result.html

My OO programming skills are pretty close to zero. So please forgive me for the dumb question ...

tangerine

I can't be much help here, but any "last insert id" statement will only be relevant after an INSERT.

Also, if your opening statement is expected to return multiple rows, you could try this:
$result = [your first "raw" query] ;
$count = count($result) ;

rf1234

Agreed about the last insert id. Using PHP count is also ok.

But I would like to understand the right syntax doing this with the Editor db object after having done the select or the insert. To me it doesn't sound sensible to do it the way I did it because it repeats everything. (Using my own db object it is no problem; but I want to use the Editor object because I sometimes have locking issues mixing the usage of both db objects.)

This is how I do both jobs using my own db object which is based on this: http://culttt.com/2012/10/01/roll-your-own-pdo-php-class/
(The only modification I made is adding the charset like this $dsn = 'mysql:host=' . $this->host . ';dbname=' . $this->dbname . ';charset=' . $this->charset;)

You see I don't need to repeat everyhting if I want to get the rowCount or the last insert id. This is what I also want to achieve using the Editor db object ...

$dbh->query('SELECT DISTINCT rfa_id FROM rfa_has_rfp 
             WHERE rfp_id = :rfp_id');
$dbh->bind(':rfp_id', $rfpId);
$row = $dbh->singleAssoc(); // a one-dimensional array is returned SINGLE
(int)$rowCount = $dbh->rowCount();

if ((bool)$rowCount) { // the rfa already exists
    $rfa_id = $row["rfa_id"];
} else {   // rfa needs to be inserted before rfa_has_rfp!!
    $dbh->query('INSERT INTO rfa
             (type, about, status, updater_id, creator_id)  
               VALUES
             (:type, :about, :status, :updater_id, :creator_id)');
    $dbh->bind(':type', 'G');
    $dbh->bind(':about', 'R');
    $dbh->bind(':status', 'W');
    $dbh->bind(':updater_id', $_SESSION['id']);
    $dbh->bind(':creator_id', $_SESSION['id']);
    $row = $dbh->execute();
    $rfa_id = $dbh->lastInsertId();
}
return $rfa_id;

tangerine

My underlying point is a guess that your own db object and Editor's db object are pretty similar if not identical. Doing a var_dump() of both objects for comparison purposes would tell you.
Alternatively just try a count($result) on the result returned by your SELECT.

rf1234

Maybe, but I am not experienced enough to get this. My object certainly does not have a raw() method for example.
I guess it won't be more than a three liner for Allan to answer my question. He built it ...
@ Allan, in case you find the time ... Many thanks in advance!

allan

Save the result of the exec() method! It is a Result instance which you can then run methods on.

$statement = ('SELECT DISTINCT rfa_id FROM rfa_has_rfp
                           WHERE rfp_id = :rfp_id');
 
$result =
$db ->raw()
    ->bind(':rfp_id', $rfpId)
    ->exec($statement);

$row = $result->fetch(PDO::FETCH_ASSOC);
$rowCount = $result->count();

Allan

tangerine

That'll teach me not to try answering questions about Editor....

rf1234

Thanks guys! I appreciate your support with this great tool!