How to use a php variable?

Question

I have such a dilema and headache, I'm working in PHP and Mysql using dates.

In Workbench and phpmyadmin this works fine and returns me some rows

SELECT * FROM reserva WHERE STR_TO_DATE( '2017-04-07 19:00:00' , '%Y-%m-%d %H:%i:%s') BETWEEN fechaini AND fechafin

Where fechafin and fechaini are DATETIME Fields, in php I do this INSERT, it works fine, and I'm filling the DB with this.

public function storeReserva($fechaini) {

     $uuid = uniqid('', true);

    $stmt = $this->conn->prepare("INSERT INTO reserva(unique_id, fechaini, fechafin) VALUES(?, ? , ? + INTERVAL 2 HOUR)");
    $stmt->bind_param("sss", $uuid, $fechaini, $fechaini);
    $result = $stmt->execute();
    $stmt->close();

    // check for successful store
    if ($result) {
        $stmt = $this->conn->prepare("SELECT * FROM reserva WHERE unique_id = ?");
        $stmt->bind_param("s", $uuid);
        $stmt->execute();
        $reserva = $stmt->get_result()->fetch_assoc();
        $stmt->close();

        return $reserva;

    } else {

        return false;
    }
}

I want to verify if the new fechaini isnt between in any row of the fields

public function storeReserva2($fechaini) {

    $stmt1 = $this->conn->prepare("SELECT * FROM reserva WHERE STR_TO_DATE( '.$fechaini' , '%Y-%m-%d %H:%i:%s') BETWEEN fechaini AND fechafin");
    $result1 = $stmt1->execute();
    $stmt1->close();
    if ($result1 !== null)
    {

        $uuid = uniqid('', true);
        $stmt = $this->conn->prepare("INSERT INTO reserva(unique_id, fechaini, fechafin) VALUES(?, ? , ? + INTERVAL 2 HOUR)");
        $stmt->bind_param("sss", $uuid, $fechaini, $fechaini);
        $result = $stmt->execute();
        $stmt->close();

        // Revisa si se hizo la reserva con su id unica
        if ($result) {
            $stmt3 =  $this->conn->prepare("SELECT * FROM reserva WHERE unique_id = ?");
            $stmt3->bind_param("s", $uuid);
            $stmt3->execute();
            $reserva = $stmt3->get_result()->fetch_assoc();
            $stmt3->close();

            return $reserva;

        }

The statement doesnt work, it returns something diferent from null whatever the dates are. I'm guessing i'm using the php variable $fechaini wrong if you can help me with the correct syntax.

PD: I tested with and without the dot ($fechaini) I'm not that kinda dumb (I can't see what the sentente return, i'm using android studio... is there a way to see what the PHP echo or print? I'm not an advanced even not a intermediate php user. )

Why do you `STR_TO_DATE` what is already in the right format? — tadman, Apr 08 '17 at 00:51
I read that is necesary to use "Between" so it will read it as a date — Ricardo Alan Vargas Gamboa, Apr 08 '17 at 00:53
I just want the correct way to use the $fechaini variable i get from my app, the variable have de yyyy-mm-dd HH:mm:ss format, i use it to insert. — Ricardo Alan Vargas Gamboa, Apr 08 '17 at 00:56
echo the $fechaini variable right before the bind statement, and post what it shows. — Sloan Thrasher, Apr 08 '17 at 01:06
Also, what PHP or MySQL error are you getting and on which line. If you aren't seeing an error, turn error reporting on. — Sloan Thrasher, Apr 08 '17 at 01:07
No errors at all, I'm printing the error message and i got nothing... What you mean with turning error reporting on? And well i can put an echo, but where i see it? I mean i'm returning a JSON object, you want to return the $fechaini itself? i did it and it returns me the date e.g. "2017-07-07 20:00:00" — Ricardo Alan Vargas Gamboa, Apr 08 '17 at 01:31
Just to emphasize a couple of things, sorry if any of these is repetitive: (1) `STR_TO_DATE` is really not needed (2) `$fechaini` in `$this->conn->prepare()` must be without a dot, (3) Try to `echo` the very string that is written for `prepare()` (i.e. maybe use a variable to construct and hold the string and `echo` the variable), (4) Please try to debug error - maybe the following links can help: [MySQLi prepared statements error reporting](http://stackoverflow.com/q/2552545/2298301) and [How to get mysqli error in different environments?](http://stackoverflow.com/q/22662488/2298301) — Dhruv Saxena, Apr 08 '17 at 21:24

score 1 · Answer 1 · answered Apr 10 '17 at 06:50

Well, i solved this, using mysqli, this works, i was managing wrong the result, but using the num of rows, it was what i wanted. Thanks everyone. hope this helps about how manage the return result of a query.

public function storeReserva2($fechaini) {




$servername = "localhost";
$username = "root";
$password = "";
$dbname = "android_api";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "SELECT * FROM reserva WHERE STR_TO_DATE( '$fechaini' , '%Y-%m-%d %H:%i:%s') BETWEEN fechaini AND fechafin";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
   echo "fechas existentes";
   return false;

} else {

     $uuid = uniqid('', true);
            $stmt = $this->conn->prepare("INSERT INTO reserva(unique_id, fechaini, fechafin) VALUES(?, ? , ? + INTERVAL 2 HOUR)");
            $stmt->bind_param("sss", $uuid, $fechaini, $fechaini);
            $result = $stmt->execute();
            $stmt->close();

            // Revisa si se hizo la reserva con su id unica
            if ($result) {
                $stmt3 =  $this->conn->prepare("SELECT * FROM reserva WHERE unique_id = ?");
                $stmt3->bind_param("s", $uuid);
                $stmt3->execute();
                $reserva = $stmt3->get_result()->fetch_assoc();
                $stmt3->close();

                return $reserva;

            }

}
$conn->close();




    }

How to use a php variable?

1 Answers1