PHP MySQL novice to ninja 6th edition - databasetable.php

[benanamen]

I have now spent a considerable amount of time and effort to do what I can to jump on board the current update implementation but I just cant do it. I have considered your arguments and have spent several hours researching them. You asked for something more than ewwww, so I will just post the program flow. It speaks for itself.

To do an update this is what has to happen.

1. Call save method
2. Call insert method
3. Call query method
4. Execute insert query
5. Cause a database duplicate constraint error
6. Generate a Php Exception
7. Catch the exception
8. Call update method
9. Call query method
10. Execute update query

It violates “The Principle of Least Astonishment”. Ten steps to do a simple update! I just cant get past it.

To do an update using your code eliminating the save method

1. Call update method
2. Call query method
3. Execute update query

This is sensible and is not astonishing at all.

I appreciate the time you took to respond. I have learned several new things and will be doing some refactoring based on this thread and your book.

[TomB]

That’s rather disingenuous and you’ve inserted a few steps there that you normally wouldn’t recognise

To do an update without the update method you need to:

1. Read id (or other pk) from the from
   1a. Read additional keys from the form for composite keys
2. Call isset (multiple times for composite keys)
3. Read the return value
4. Evalulate the if statement
5. Run the else condition
6. Call the update method
7. Call the query method
8. Execute the update query

(It’s easy to bulk it out like you did )

So more realistically it’s 8.5 vs 10 steps for updating.

Let’s compare inserting. For inserting using your approach you need to:

1. Read id (or other pk) from the from
   1a. Read additional keys from the form for composite keys
2. Call isset (multiple times for composite keys)
3. Read the return value
4. Evaluate the if statement
5. Run the else condition
6. Call the insert method
7. Call the query method
8. Execute the insert query

Whereas using save you need to:

1. Call save method
2. Call insert method
3. Call query method
4. Execute insert query

That’s 4 vs 8 steps. Assuming you have a ratio of 1:1 inserts to updates you’d execute a lot less code using save. In reality a lot of tables see far more inserts than updates (logs, user registration details, shopping carts, etc).

And that’s for each form. Even by your own rather skewed numbers by the time you have reused the save method in four different forms you’ve saved work.

Again, you’re missing the main point: DRY. You don’t need to write the same logic for every single form you create.

Really, apples to apples you need to:

1. Determine whether you are doing an insert or update
2. Call either insert or update

The only difference between the approaches is that using save, step 1 is done in the database rather than the PHP code.

I think this is more of a philosophical issue and this kind of stack counting is meaningless. After all if it mattered, we would not use functions or objects at all and everything would be at the root level of a .php file.

When the form is submitted, do you really care whether you are dealing with a new or existing record? What you want to do is store the information in a database.

If you used a serialized array to store your data you’d do this:

$records[$data['id']] = $data;
file_put_contents($file, json_encode($records));

You don’t care if you’re overwriting an existing record or inserting a new one there. Why is a database any different?

@benanamen

I appreciate the time you took to respond. I have learned several new things and will be doing some refactoring based on this thread and your book.

Thanks, I’m glad you have found it useful

[benanamen]

Trust me @TomB, I really want to come to a meeting of the minds on this otherwise I wouldn’t keep coming back. I truly value your expertise. There are very few people on the forums that can teach me anything I don’t already know. Like you I am all about code reduction, best practices, etc. You have already taught me several things through this thread and your most excellent book which I am now recommending to everyone of every skill level.

I would like to explore the composite key issue that you have mentioned. Could you please provide a schema to test on that shows the problem.

So we have gone pretty far on the current implementation But what about other options if any and the pros/cons of them.

@rpkamp made one alternative suggestion which you said has at least two problems. What other options would you say there are, or is the current implementation thee “best” option?

You said Cyclonic Complexity doesn’t extend to the class or functions/methods, so what about a conditional in the save method? That eliminates the code repetition in the forms and doesn’t change the current forms implementation.

[TomB]

@benanamen

I would like to explore the composite key issue that you have mentioned. Could you please provide a schema to test on that shows the problem.

Literally any DB with a composite key will do it.

Let’s say you have a many-to-many relationship: Actor:Movie

Here’s some records:

[ 
  'actorId' => 1, 'movieId' => 1, 'characterName' => 'Darth Vader'
]
[ 
  'actorId' => 4, 'movieId' => 1, 'characterName' => 'Han Solo'
]
[ 
  'actorId' => 4, 'movieId' => 2, 'characterName' => 'Indiana Jones'
]

The problem is, there is no way to know whether we are updating or inserting without asking the database (or the user). Are we just updating the character name for that actor in that movie or are we inserting a new record?

I don’t think you can handle this without two trips to the database.

@benanamen

You said Cyclonic Complexity doesn’t extend to the class or functions/methods, so what about a conditional in the save method? That eliminates the code repetition in the forms and doesn’t change the current forms implementation.

As I said, please provide a save method that does this. I offered a suggestion: query the database to see if the record is already taken, but this still requires additional logic to handle composite keys.

• SELECT count(id) FROM table WHERE id = :id
• if ($count == 0) insert()
• else update();

But:

• You need additional logic to generate the relevant select query for composite keys (easily doable but extra code)
• Extra code to perform the select and check to see if the record currently exits
• Inserts require two trips to the database (The select, then the insert)
• Updates require two trips to the database (the select, then the update)

On the other hand, try/catch:

• Works with composite keys without any extra code
• Doesn’t need to perform a SELECT
• Doesn’t require writing the code for an additional select query
• Inserts require one trip to the database (just the insert)
• Updates require two trips to the database (insert, then update)

So this is faster for inserts and roughly the same speed for updates and handles composite keys without additional logic.