0

I already googled and thereby also searched stackoverflow a lot on this, but the more I searched the more "?" popped up in my face and I must admit that I feel like I can't put all these things together on my own, so I hope I can articulate myself well enough here so that you can hopefully help me out in this...^^

So, I want to compare dateranges inputted by a user to dateranges stored in my MYSQL DB in the format "2019-02-02 00:00:00". Yes, all the dates in the DB are set to midnight.

The daterange coming from the front-end was formatted on the php side to resemble this structure. Therefore, I took the timestamp from the front-end and put it into the date() function:

date("Y-m-d 00:00:00", $timestamp)

Now, later on I'm a SQL query with which I determine whether there is overlap between the two dateranges or not, it looks like this:

   $result = $connection->query("SELECT id
   FROM reservierung
   WHERE status != 'abgelehnt'
   AND benutzer != $benutzerID
   AND arbeitsplatz = $arbeitsplatzID
   AND $formattedStartDate <= ende
   AND $formattedEndDate >= anfang
   ");

I then evaluate the query, like this:

  $queryResult = $result->rowCount();

But php throws an error because the query has failed an returns the boolean false. Therefore I conclude that something must have gone wrong with the datecomparison, because if I remove it, the query succeeds.

I know that MYSQL only stores strings (at least our DB definitely does), but I dont know what my date is (a string? an object?) and I don't know what I need to convert into what to make this comparison happen xD I usually always extracted the dates and made my comparisons outside the queries, but this is a lot more "verbose" and probably unnecessary if one knows how to compare inside mysql. I read about DATE_FORMAT() already and that comparing timestamps instead of dates would work too, but I dont know which method would be more efficient (in terms of producing less and better structured code) and again, I don't know what needs to be what inside the mysql query to make the comparison work on a mere functional/syntactical level...^^

EDIT: Here is the php error, just FYI:

[Thu Feb 07 15:29:04.739350 2019] [:error] [pid 20986] [client 127.0.0.1:47518] PHP Fatal error: Uncaught Error: Call to a member function rowCount() on boolean in /var/www/html/include/Buchen.php:76\nStack trace:\n#0 /var/www/html/include/Buchen.php(199): checkQueryResult(false)\n#1 /var/www/html/include/Buchen.php(149): reservationConflictsWithAlreadyExistingReservationByUser(Object(PDO), '2019-02-07', '2019-02-28', '66', '145', Array)\n#2 /var/www/html/include/Buchen.php(24): checkForValidityOfReservationTimeframe(Object(PDO), Array, 'D.E.I.3', '2', '66', '145')\n#3 /var/www/html/include/Buchen.php(11): mainframe('1549494000', '1551308400', '2', 'D.E.I.3', 'Andreas', 'Lang', NULL)\n#4 {main}\n thrown in /var/www/html/include/Buchen.php on line 76, referer: http://localhost/view/reservieren.php

JSONBUG123
  • 111
  • 10
  • 2
    For starters, `AND $formattedStartDate <= ende` should be `AND '$formattedStartDate' <= ende`. In fact it should be `AND ? <= ende`. – Salman A Feb 07 '19 at 15:00
  • 2019-02-02 = 2015. See about sql injection and the importance of prepared and bound queries. – Strawberry Feb 07 '19 at 15:03
  • @JSONBUG123 I presume you're using some kind of database library so use parameters instead of stuffing $ variables directly in the query. – Salman A Feb 07 '19 at 15:05
  • *But php throws an error because the query has failed* : which error are you getting ? – GMB Feb 07 '19 at 15:07
  • Um, what exactly are "database libraries"...?^^ This is not a very "professional" library since its just used for an internal software to keep track of who sits at which workstation for what time. This approach you see here is used all over the tool, so well, i dont know if our database is using these libraries or not...^^ – JSONBUG123 Feb 07 '19 at 15:07
  • @GMB PHP fatal error: Call to a member function rowCount() on boolean. As I said, the query fails so "false" is returned, which cant be used by rowCount() function. – JSONBUG123 Feb 07 '19 at 15:08
  • 1
    You would need to trap the mysql error and report it, so we can see it and analyze it. – GMB Feb 07 '19 at 15:09
  • 1
    For me, Salmans tip to use '$formattedStartDate' already did the trick :) EDIT: @GMB I will edit in the php error, the comments dont give me enough characters to print it down here. However, as I said, the problem already is resolved because the query now works and therefore no false is returned by the query and therefore rowCount() doesnt receive any booleans anymore :) – JSONBUG123 Feb 07 '19 at 15:14
  • 1
    "For starters, AND $formattedStartDate <= ende should be AND '$formattedStartDate' <= ende. In fact it should be AND ? <= ende" @SalmanA 's tip in fact isn't that great because of possible SQL injections in fact the topicstarter should be using [prepared statements](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1) instead. – Raymond Nijland Feb 07 '19 at 15:25
  • You've edited in the PHP error which results from the rowCount call failing, but we need to know why the query is failing (as you'll have realised, rowCount fails because $result is false, and not a result object. $result being false means the query failed). You need an `if` statement to check whether `$result === false`, and if so, then echo the result of [mysqli_error()](http://php.net/manual/en/mysqli.error.php) (probably `$connection->error` in your case, if $connection is a mysqli connection object) so you can see what fault SQL returned. The error you're seeing is a symptom not the cause – ADyson Feb 07 '19 at 15:29
  • oh right you meant using place holders (prepared statements) from the start @SalmanA i missed that i think i need caffaine badly.. – Raymond Nijland Feb 07 '19 at 18:27

0 Answers0