PDOdb v1.3.8 — Raw WHERE segments + decimal input fix

[decMuc]

PDOdb v1.3.8 — Raw WHERE segments + decimal input fix

This release delivers two things people kept asking for: truly flexible raw WHERE blocks for complex queries, and a reliable way to accept localized decimal input (e.g. "12,20") without MySQL shouting about truncated data.

Version 1.3.7 was skipped and not published because the initial decimal normalizer didn’t run on all code paths (notably rawQuery() / CALL ...). This is fixed in 1.3.8.

Added

• whereRaw() and orWhereRaw()

  • Lets you inject arbitrary WHERE fragments, including grouped/nested AND/OR logic, function calls, and multi-column comparisons.
  • Supports ? placeholders with a separate values array for proper PDO prepared binding, e.g.:

    $db->whereRaw('(base_name LIKE ? OR sku LIKE ?)', [$search, $search]);

  • Can be combined with the existing query builder methods and will be included in get(), update(), delete(), etc.
  • Raw segments are intentionally not validated for column/operator names — only their parameters are bound.

• Automatic normalization of decimal-comma input

  • Values like "12,20" or "1.234,50" are now converted to SQL-friendly dot-decimals before binding.
  • This now also runs at the central parameter-binding layer, so it applies to stored procedure calls (CALL ...) as well, not just to inserts/updates where the column type is known.

Changed

• The internal WHERE builder now supports mixing typed helpers (whereInt(), whereStr(), …) with raw segments and keeps the correct order.
• The central parameter binding now normalizes decimal-like strings before calling bindValue(), so MySQL/MariaDB numeric columns (DECIMAL, FLOAT, DOUBLE) no longer produce
  Data truncated for column ...
  just because the client sent a comma.

Fixed

• Decimal input using a German-style comma could cause

  SQLSTATE[01000]: Warning: 1265 Data truncated for column 'price' at row 1
  
  
  

---

This discussion was created from the release PDOdb v1.3.8 — Raw WHERE segments + decimal input fix.

[decMuc]

Input normalization for German-style decimals (and dates next)

I got tired of converting German-style input over and over again.

In many of my projects the users (and forms) work with German formatting,
so prices come in like "12,20" or even "1.234,50". MySQL (and PDO) expect
"12.20", so up to now I always had to do the same boring step in PHP:
replace the comma, remove thousand separators, and hope it hits a DECIMAL
column.

Because this pattern keeps repeating and doesn’t add real value to do it
manually every time, I moved that logic into PDOdb itself:

• if the value looks like a German decimal, PDOdb normalizes it before binding,
• if the column is known to be numeric, it will normalize there as well,
• this now also applies to rawQuery() / CALL ... so procedure calls don’t need
  special handling anymore.

That way you can just pass user input as-is and let the DB layer prepare it
for MySQL.

What’s next?
I plan to do the same for date inputs. In German UIs you usually get something
like "10.11.2025", but MySQL wants "2025-11-10". I want to add a small date
normalization step in the same place, so localized dates can go straight into
the DB layer and get stored as proper YYYY-MM-DD (or YYYY-MM-DD HH:MM:SS).

About whereRaw / orWhereRaw

I also added the requested whereRaw() / orWhereRaw() so you can inject your own
grouped/nested WHERE logic. I tested the basic cases, but not very extensively.
Right now I don’t have a lot of time to come up with many edge-case patterns,
so I’m relying on feedback here. If you hit a combination that doesn’t render
in the expected order, please post it and I’ll adjust the builder.

Goal: less boilerplate in controllers/forms, more convenience in PDOdb, and
keep the SQL builder flexible enough for the complex stuff you sometimes need.

I know some people will say “normalization doesn’t belong in the DB layer /
PDO wrapper”, but in this case I disagree.

The reason is simple: this is the only place where ALL input to the database
passes through. If you solve it there, you solve it once. If you don’t solve
it there, you have to solve it in every single controller, form handler,
import job, API endpoint, … forever.

Why it makes sense here:

1) It’s a localization problem, not a business rule.
The app talks German to the user ("12,20"), but the database talks SQL
("12.20"). That’s pure format conversion.

2) The DB layer already knows the column types.
That means it’s the only layer that can say: “this field is DECIMAL, so
I’m allowed to normalize it.” A random controller doesn’t always know that.

3) It stays non-breaking.
If the incoming value is already in SQL format, we don’t touch it.

4) It reduces boilerplate in every project that uses this library.
Otherwise every project builds the same str_replace(',', '.', …) helper
20 times.

5) It’s opt-in by detection.
We normalize only if the value actually looks like a German decimal.

So yes, in a “pure” layered architecture you could say “localization stays in
the app layer”, but in practice this is the most effective and least
repetitive place to fix it.