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
– tadman, Commented Apr 8, 2017 at 0:51
I read that is necesary to use "Between" so it will read it as a date — Ricardo Alan Vargas Gamboa
– Ricardo Alan Vargas Gamboa, Commented Apr 8, 2017 at 0: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
– Ricardo Alan Vargas Gamboa, Commented Apr 8, 2017 at 0:56
echo the $fechaini variable right before the bind statement, and post what it shows. — Sloan Thrasher
– Sloan Thrasher, Commented Apr 8, 2017 at 1:06
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 and How to get mysqli error in different environments? — Dhruv Saxena
– Dhruv Saxena, Commented Apr 8, 2017 at 21:24

Ricardo Alan Vargas Gamboa · Accepted Answer · 2017-04-10 06:50:07Z

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();




    }

Collectives™ on Stack Overflow

How to use a php variable?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related