Fun with databases: struggeling with Database, $database, DB:: and weird values on insert

Kirby 3
1

good morning,

let’s start with weird values submitted to my database.

This is, what I try to insert:

$data = [
    'user_id'  => 'abcdef',
    'geaendert_von' => 'abcdef',
    'datum'  => '2021-03-21',
    'anfang'  => '10:12',
    'ende'  => '14:55',
    'feiertag'  =>  0 ,
    'schlusssschicht'  =>  0,
    'pause'  =>  0,
    'dauer' => 275,
    'anmerkungen'  => 'nix'
];

$id = Db::insert('arbeitszeiterfassung', $data);

10 Columns, 10 values.
$id returns 0, something went wrong

So I do this:

echo '<pre>';
echo Database::instance()->lastQuery();
echo '</pre>';

to look at the query submitted, which is resulting in:

INSERT INTO 
   `arbeitszeiterfassung` 
  (`arbeitszeiterfassung`.`user_id`, 
  `arbeitszeiterfassung`.`geaendert_von`, 
  `arbeitszeiterfassung`.`datum`,
  `arbeitszeiterfassung`.`anfang`,
  `arbeitszeiterfassung`.`ende`,
  `arbeitszeiterfassung`.`feiertag`, ,
  `arbeitszeiterfassung`.`pause`,
  `arbeitszeiterfassung`.`dauer`, )
VALUES (
   :value_RhZP1jXB, 
   :value_ULO5sVyJ, 
   :value_YlmiecJ1, 
   :value_RUSTtqGz, 
   :value_2oGBibHx, 
   :value_UchTCFgV, 
   :value_6tDhK8rd, 
   :value_dy4Tr4OT, 
   :value_h1OQPfX3, 
   :value_QXQak6Z3)

What puzzles me first are the weird values. Where do they come from?

But second: I submitted 10 columns and 10 values, but submitted are 8 columns and 10 values.
Look at arbeitszeiterfassung.feiertag, ,
A double comma? Where did the column between them go? And why did the last column
anmerkungen disappear?

And another question at last: how are the objects Database, $database, DB:: related. Can I use them all at the same time?

2

This one looks like a typo with 4 s?

For the other missing one, doublecheck column name and accepted content type.

3

Thank you for correcting me.

Removing the 4th s and checking the last column-name lead to the full list of columns in the statement (and to a successful insert). But does that mean, that lastQuery() doesn’t show what I sent, but what mysql made of it instead?

Concerning the weird values: By dumping() DB::instance() I found that these values are keys in an associative array binding the real values.

So this part of my questions seems to be solved.

Can somebody explain why there are different Objects Database, Db:: and if can use them interchangingly?

4

The Database class is the main database class which creates a new Database instance.

The Db class offers a set of shortcuts to work with the database instance (connection with the instance, querying tables etc).

5

Thank you for the reply.

Maybe the docs are pure fun when you’re an IT-pro. Unfortunately I’m not. Thats why I have to ask some more questions like:

why do I have to create a new Database-instance with $database = new Database(); when I already have one (if it is configured in config.php)?

I found out I can use for example Database::instance()->lastQuery() and Database::instance()->lastId(), but it doesn’t work with all methods of Database. Bug or feature?

I understand that Db is an alias for Database(). But there are only 5 methods I can use with Db:: mentioned in the docs. The basic ones like Db::insert or Db::select are missing. You don’t find them in the docs for $database either. The only place I detected them was in docs/guide/database. Did I overlook the one place where all methods for database-manipulation are described together?

last question: why is $database not mentioned in the Reference-menu on the left side of the doc’s page in the Objects part? For me it is the natural place to look for it.

6

You don’t have to.

Putting the database settings into the config doesn’t automatically create a database instance. If you only put the config data in and then call…

Database::instance()

…it will return null.

As soon as you run for example

$users = Db::select('users');

A connection is automatically established.

So lets say you put the following code in a template (with a working configuration in your config file)

var_dump(Database::instance()); // null

$comments = Db::table('comments'); // query an existing table
dump(Database::instance()); // Database object

I don’t understand this question.

No.

// data classes
    'database'  => 'Kirby\Database\Database',
    'db'        => 'Kirby\Database\Db',

Database is an alisas for the Kirby\Database\Database class, Db an alias for 'Kirby\Database\Db class.

Well, the docs are auto-generated from Reflections, and both the Database class and the Db class have magic __call/__callstatic methods plus the Db class also has some shortcuts in the form of

Db::$queries['avg'] = function (string $table, string $column, $where = null): float {
    return Db::table($table)->where($where)->avg($column);
};

These are the ones you find in the guide.

There are quite a lot of methods not in the sidebar, but in the All classes section. The list of objects would otherwise get very long.

7

Hey, thank you for taking the time to answer!

Seems like my understanding of the kirby-universe (and probably some php-techniques) is still insufficient.

I will need a while to fully digest what you told me and I might come back sooner or later with further questions :wink: