Since I like to write the smallest amout of code when doing small lookups in the database, mostly for testing/checking purposes, I wrote a small "wrapper" for it.
Instead of the following:
$dbh->prepare("UPDATE table SET multiple = ?, fields = ? WHERE id = ? LIMIT 1");
$dbh->execute($val1,$val2,$id);
I can reduce it to this:
$db->u('table SET multiple, fields',[$val1, $val2, $id]);
When there's no WHERE in the sql-string, it automatically assume it should use the table primary kay column as identifier.
But id is not neccecary the name of the primary key all the time. So for it to work porperly, I added this code to the wrapper:
$PRI = $dbh->query("SHOW KEYS FROM $T WHERE Key_name = 'PRIMARY'")->fetchColumn(4);
$T is part of the wrapper, and cotains the table name.
$PRI is the used in the main query...
This returns the name of the primary key in that table so I can use it in the query.
Of course this cannot work in all situations, depending on the table setup, but for the most part, it does.
It is also possible to do this:
$db->u('table SET multiple, fields WHERE column = ?',[$val1, $val2, $id]);
When WHERE column = ? is added (WHERE in particular) there's no need for that extra query though... It's all about reducing the writing, and to not need to rememeber each primary columns ;)
So, is there a way to include the SHOW KEYS query inside the main query - that would look like this:
SELECT fields FROM table WHERE (SHOW KEYS ...) AND other = ? AND colums = ?
So one wouldn't have to run two queries at once?