1

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?

ThomasK
  • 2,210
  • 3
  • 26
  • 35
  • 1
    _"Since I like to write the smallest amout of code when doing small lookups in the database..."_ I just have to ask. Why? "Small code" doesn't always mean **good** code. – Uueerdo Sep 28 '18 at 16:41
  • No, I don't think it's possible to do what you want. You need to do a separate query to get the primary key, then create the SQL that uses it dynamically. – Barmar Sep 28 '18 at 16:42
  • In general, there's no way to refer to table or column names dynamically in SQL. – Barmar Sep 28 '18 at 16:43
  • I guess you may achieve this with a function. But I don't see any true benefit doing this. – Felippe Duarte Sep 28 '18 at 16:44
  • @Barmar yeah, I think he realizes that it'll need a second query, and wants to make a function "u" that will automatically query the database for the primary before every update, and then assume the last function argument is the value that should be used for the dynamically assumed WHERE condition. He's not looking to write the smallest query so much as the smallest amount of code to execute almost any simple update. – Uueerdo Sep 28 '18 at 16:47
  • @Uueerdo Look at the every end of the question -- he's asking if there's some other way that allows embedding the `SHOW KEYS` directly into the query instead of doing two queries. – Barmar Sep 28 '18 at 16:49
  • @Barmar oh, oops, sorry. There I go again not reading the whole question, it must be early on a Friday. – Uueerdo Sep 28 '18 at 16:52
  • @Uueerdo Yep, that's why I wrote `!#/bin/bash` in a shell scripting question this morning. :) – Barmar Sep 28 '18 at 16:53
  • There's allways a question as to why one would do anything :P and for the most part, it's usually not a good idea in the end anyways.. Any wrapper drives more resources, slows down the execution time, etc. etc. But regardless of that, there seems to be a wrapper for just about anything - mainly because it makes coding faster or easier to remember... To me, it is to make it just more fun to se small code do greate things :) – ThomasK Sep 28 '18 at 17:21
  • It seems like you're trying to design your own ORM layer. There are already plenty of well-written libraries like Laravel Eloquent. – Barmar Sep 28 '18 at 18:47
  • Yes. But a lot of these Framework is quite heavy for small applications and small sites like local clubs and such... therefore I just wrote a small SIUD wrapper that’s good enough for wery low traffic sites.. and for checking purposes... – ThomasK Sep 29 '18 at 19:35

0 Answers0