Mysql transfer

[XCalibre3]

Hey all, trying to do this but nothing works.

 

$sql = "SELECT * FROM `reservations` WHERE id = $_POST[delete]";

$sql1 = "INSERT INTO `history_reservations`
          (fname,lname,email,phone,dived,dropdown,reserved,resnum) WHERE id = $_POST[delete]";
          $result = $mysqli->query($sql1);
          $mysqli->close();

 

Basically delete a reservation and put it into a history table.

Edited April 27, 2023 by XCalibre3

[requinix]

First things first: you really shouldn't be letting anybody on your site screw around with your database. Because that's what you're doing now by putting $_POST values into your SQL like that.

Stop doing that and switch to prepared statements.

[XCalibre3]

1 hour ago, requinix said:

First things first: you really shouldn't be letting anybody on your site screw around with your database. Because that's what you're doing now by putting $_POST values into your SQL like that.

Stop doing that and switch to prepared statements.

I still only know the old code, so new to this.  This is what I came up with but doesn't work.

 

if(isset($_POST['delete'])) {
$stmt->bind_param("s",$_POST['delete']);
$mysqli  = new mysqli(`localhost`, `root`, '', `reservations`  );
$stmt = $mysqli -> prepare('DELETE FROM `reservations` WHERE id = ?');
$stmt -> bind_param('s', $id);
$stmt -> execute();

 

[requinix]

At first glance it seems fine - except for the fact that you're using backticks for some of your PHP strings, and they mean something completely different from regular ' and " quotes.

[XCalibre3]

23 minutes ago, requinix said:

At first glance it seems fine - except for the fact that you're using backticks for some of your PHP strings, and they mean something completely different from regular ' and " quotes.

Okay, I came up with this.  Still have learn insert and the other but thank you for the link.  Then Got to learn how to transfer to the history table and then delete it from the regular table.

 

if(isset($_POST['delete'])) {
$tb_Delete = new mysqli("localhost", "root", "", $database );
$stmt = $tb_Delete->prepare('DELETE FROM `reservations` WHERE id = ?');
$stmt->bind_param('i', $_POST['delete']);
$stmt->execute();
$stmt->close();
}

 

[XCalibre3]

UPDATE:

Is working

 

if(isset($_POST['flagged'])) {
  $tb_Update = new mysqli("localhost", "root", "", $database );
  $stmt = $tb_Update->prepare("UPDATE `reservations` SET flagged = 'Yes' WHERE id = ?");
  $stmt->bind_param('i', $_POST['flagged']);
  echo "<style> {background-color:red;}</style>";
  $stmt->execute();
  $stmt->close();
}

 

Edited April 27, 2023 by XCalibre3

[XCalibre3]

1 hour ago, requinix said:

At first glance it seems fine - except for the fact that you're using backticks for some of your PHP strings, and they mean something completely different from regular ' and " quotes.

Is there a prepeared statement that's ust mysqli, or does it always have to have PDO?  I can't find anyting on it.  Thank you.

II mean for fetch all and select all together

Edited April 27, 2023 by XCalibre3

[Strider64]

I know PDO can look confusing at first, but once you get the hang of using PDO it's so much easier that mysqli in my opinion.

Here's your code in PDO though I haven't tested it out.

 

<?php
if (isset($_POST['flagged'])) {
    try {
        $dsn = "mysql:host=localhost;dbname=$database;charset=utf8mb4";
        $options = [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false,
        ];
        $pdo = new PDO($dsn, "root", "", $options);

        $sql = "UPDATE `reservations` SET flagged = 'Yes' WHERE id = :id";
        $stmt = $pdo->prepare($sql);
        $flaggedId = $_POST['flagged'];
        $stmt->bindParam(':id', $flaggedId, PDO::PARAM_INT);
        $stmt->execute();

        echo "<style>body {background-color: red;}</style>";
    } catch (PDOException $e) {
        // Handle any errors though not for a production server
        echo "Error: " . $e->getMessage();
    }
}

Here's a nice link on how to use PDO -> https://phpdelusions.net/pdo
I still use that website when I have a brainfart. 🤣

Edited April 27, 2023 by Strider64

[XCalibre3]

12 hours ago, Strider64 said:

I know PDO can look confusing at first, but once you get the hang of using PDO it's so much easier that mysqli in my opinion.

Here's your code in PDO though I haven't tested it out.

 

<?php
if (isset($_POST['flagged'])) {
    try {
        $dsn = "mysql:host=localhost;dbname=$database;charset=utf8mb4";
        $options = [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false,
        ];
        $pdo = new PDO($dsn, "root", "", $options);

        $sql = "UPDATE `reservations` SET flagged = 'Yes' WHERE id = :id";
        $stmt = $pdo->prepare($sql);
        $flaggedId = $_POST['flagged'];
        $stmt->bindParam(':id', $flaggedId, PDO::PARAM_INT);
        $stmt->execute();

        echo "<style>body {background-color: red;}</style>";
    } catch (PDOException $e) {
        // Handle any errors though not for a production server
        echo "Error: " . $e->getMessage();
    }
}

Here's a nice link on how to use PDO -> https://phpdelusions.net/pdo
I still use that website when I have a brainfart. 🤣

Thanks for the info... looks hard though.

[XCalibre3]

Okay, I'm working on SELECT now and it won't work.

 

<?php
$database = calendar
$tb_Select = new mysqli("localhost", "root", "", $database );
$stmt = $tb_Select->prepare("SELECT * FROM reservations WHERE id = ?");
$stmt->bind_param("s", $_GET['id']);
$stmt->execute();
$stmt->store_result();
if($stmt->num_rows === 0) exit('No rows');
$stmt->bind_result($id, $fname, $lname);
while($stmt->fetch()) {
  $ids[] = $id;
  $names[] = $fname;
  $ages[] = $lname;
}
echo $ids;
var_export($ids);
$stmt->close();
?>

 

Edited April 27, 2023 by XCalibre3

[XCalibre3]

I see one issue lol, I'm trying to call ID before ID is defined.... I define i after.... again, I'm not used to this prepared statement stuff.  Sorry for so many questions.

But it also says: 

Parse error: syntax error, unexpected variable "$tb_Select" 

Edited April 27, 2023 by XCalibre3

[XCalibre3]

also tried this but doesn't recognize variable tb_Select

 

<?php
$database = calendar
$tb_Select = new mysqli("localhost", "root", "", $database );
$query = "SELECT * from `reservations` WHERE ID=? ";
$stmt = $mysqli->prepare($tb_Select, $query);
$stmt->bind_param("i", $id);
$stmt->execute();
$res = $stmt->get_result();
$data = $res->fetch_all(MYSQLI_ASSOC);

while($data = mysqli_fetch_array($stmt)){
        ?>
    <h2 align="center"> <?php echo $row['id']; ?> </h2><br>
    <div class="paracenter">

        <p id="cont"><?php echo $row['fname']; ?></p>
        <hr color="black" width="10%">

    </div>
    <?php } ?>


This either:

<?php
$database = 'calendar';
$tb_Select = new mysqli("localhost", "root", "", $database );
$sql = "SELECT * FROM reservations WHERE id=?";
$stmt = $tb_Select->prepare($sql);
$stmt->bind_param("i", $id);
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    echo $row['fname'];
}?>

 

Edited April 27, 2023 by XCalibre3

[XCalibre3]

AmI getting closer? lol... I'm really trying here...  This shows a blank page, no error reporting.

 

<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

$database = 'calendar';
$tb_Select = new mysqli("localhost", "root", "", $database );
$sql = "SELECT * FROM `reservations` WHERE id=?";
$stmt = $tb_Select->prepare($sql);
$stmt->bind_param('i', $id);
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
  echo $row['id'];
  echo $row['fname'];
  echo $row['lname'];
  echo $row['email'];
}?>

 

[kicken]

4 hours ago, XCalibre3 said:

$stmt->bind_param('i', $id);

You have not defined $id anywhere, so your query will be WHERE id=NULL which is always false thus you get no results.