Query does not work as expected with bindParam but it does with raw input?

Question

I have a query that should show all content from a specific date, like "show me all articles from 01-01-2020 to now".

The date is in unix. And I'm using PHP 7.2.

If I use bindParam instead of the real number, it shows content from within that date and older as well.

If I just type the number (the unix date), it shows the content ok, meaning that the content is all within the date range.

HERE'S MY QUERY:

  $y = $conectarDB->prepare("
      SELECT DISTINCT SQL_CACHE
      contenidos.contenidoID AS contID, 
      titulo, 
      fecha, 
      tipoContenidoID
      WHERE fecha > ?
      ORDER BY contenidoID DESC
  ");
  $y->bindParam(1, $fecha);
  $y->execute();
  $resultado = $y->fetchAll(PDO::FETCH_ASSOC);

Tying to sort it, I forced the date ($fecha variable) to be an integer, just in case the problem was that it was being interpreted as a string:

$fecha = (int)$fecha;

MySQL nor PHP show any errors, and the query gets excecuted, but showing older results as well, not following the range.

fecha is char and it allocates the dates in unix, ie: 1577847600 — Rosamunda, Feb 12 '20 at 14:06
@Rosamunda So, have you stored dates in number format in `fecha` column in DB? — nice_dev, Feb 12 '20 at 14:08
Thanks for your comments, it helped me realize that being a char field, it was reading the number as a string, even with the explicit declaration. So I used PDO::PARAM_INT and it got sorted. — Rosamunda, Feb 12 '20 at 14:10
I was storing numbers in the date field. The field was being constructed like: `fecha char(25) NULL,` — Rosamunda, Feb 12 '20 at 14:15
I suppose downvotes reflect the fact that you failed to provide a reproducible example: a certain value that, being bigger than 1577847600, doesn't give a correct a positive result when compared to. — Your Common Sense, Feb 12 '20 at 16:56
@YourCommonSense as I already answer in your other comment: It's a comparison where the fecha field is less than... the number: `WHERE fecha > '1577847600'` — Rosamunda, Feb 14 '20 at 02:50
And even if that's the cause, it's not only _common courtesy_, buy the best way to improve the questions (and the community) is to actually _tell_ people **why** you downvoted in the first place... or they are going to keep posting bad questions again and again. Unless the downvote is just a punishment instead of a way to improve the community and get less bad questions. — Rosamunda, Feb 14 '20 at 02:51

Rosamunda · Accepted Answer · 2020-02-12T16:38:09.210

0

Just in case it helps someone in the future...

The problem was that the database field was a char type, instead of an integer.

So even with explicit declaration in PHP, MySQL kept considering it's contents as a string.

It was being read as:

  $y = $conectarDB->prepare("
      SELECT DISTINCT SQL_CACHE
      contenidos.contenidoID AS contID, 
      titulo, 
      fecha, 
      tipoContenidoID
      WHERE fecha > '1577847600'
      ORDER BY contenidoID DESC
  ");

Instead of:

  $y = $conectarDB->prepare("
      SELECT DISTINCT SQL_CACHE
      contenidos.contenidoID AS contID, 
      titulo, 
      fecha, 
      tipoContenidoID
      WHERE fecha > 1577847600
      ORDER BY contenidoID DESC
  ");

So, instead of just using:

  $y->bindParam(1, $fecha);

I've changed it to:

  $y->bindParam(1, $fecha, PDO::PARAM_INT);

To make MySQL understand that it is indeed an integer.

edited Feb 12 '20 at 16:38

answered Feb 12 '20 at 14:14

Rosamunda

14,620
10
40
70

1

It is highly unlikely to observe such a behavior. Mysql will correctly compare either `1577847600 > 1577847600`, `'1577847600' > 1577847600` or `1577847600 > '1577847600'`. What are exact values that give you incorrect results? – Your Common Sense Feb 12 '20 at 16:46
For instance, the value that I've posted: 1577847600 But I've compared a couple more, and the numbers where correct: I mean MySQL were showing up results with smaller numbers as well, besides the bigger ones as the query determined. – Rosamunda Feb 12 '20 at 23:24
Value 1577847600 compared to *what*? To compare you need 2 numbers, not one. – Your Common Sense Feb 12 '20 at 23:58
It's a comparison where the fecha field is less than... the number: `WHERE fecha > '1577847600'` – Rosamunda Feb 14 '20 at 02:49
1

Look. You just said, "MySQL were showing up results with smaller numbers as well". What are such numbers? Can you give an example of the other number that is being less than 1577847600 would show up? To prove your point you need two numbers, not one. An example of two numbers that will give you a positive result for a comparison num1 > num2 where num1 is less than num2. Ok, we know that num2 is 1577847600. What is the other number that is stored in the fecha field that gives you a false positive result? – Your Common Sense Feb 14 '20 at 05:44
I understand your point now. Next time I'll post a sample of the stuff that I have in my DB, so it can be better understood the problem. Thank you @Your Common Sense. I'll ask a better, more useful question next time. :) – Rosamunda Feb 15 '20 at 12:29
Imagine this is a court case. You are a prosecutor and mysql is a suspect. Would you really claim the suspect is guilty without *providing any evidence*? – Your Common Sense Feb 18 '20 at 12:15

Query does not work as expected with bindParam but it does with raw input?

HERE'S MY QUERY:

1 Answers1