I have 2 tables QueueR(first step table) and Processing(second step table). I want to copy the first row(FIFO) from my filled-up QueueR table to Processing table(they are both in the same database). My objectives are to transfer the top record and hopefully my QueueR table will update automatically(delete the copied record and reindex itself)

    <?php
    $servername = "localhost";
    $username = "root";
    $password = "kuyahajji";
    $dbname = "fqueue";

    // Create connection
    $conn = new mysqli($servername, $username, $password, $dbname);
    // Check connection
    if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
    }
    date_default_timezone_set("Asia/Manila");
    $Time = time();
    $sql = "SELECT No, TimeIssued, Type FROM QueueR WHERE No = '1';
    $result = $conn->query($sql);

    if ($conn->query($sql) === TRUE) {
    $row1 = 'No';
    $row2 = 'TimeIssued';
    $row3 = $Time;
    $row4 = 'Type';

    //Insert Data to another table
    $sql = "INSERT INTO Processing (No, TimeIssued, TimeProcessed, Type)
    VALUES ($row1, $row2, $row3, $row4);";
    if (mysqli_multi_query($conn, $sql)) {
    echo "Queue Number Processed";
    } else {
    echo "Error: " . $sql . "<br>" . mysqli_error($con);
    }
    //echo "No: " . $row["No"]. " - Name: " . $row["TimeIssued"]. " " . $row["TimeProcessed"]." " . $row["Type"]. "<br>";
    }
    else {
    echo "Error: " . $sql . "<br>" . $conn->error;
    }
    $link_address1 = 'display.php';
    echo "<a href='".$link_address1."'>Back</a>";
    $Time = time();

    $conn->close();
    ?>

      What is the problem you're having?

      I can see a couple of things.

      One, all of the operations should be done in a single transaction so that if any of them fail the tables aren't in an inconsistent state.

      Two, the selection and insertion can be done in a single statement (INSERT ... SELECT...) except for the TimeProcessed column, which, since it's just an integer, could perhaps be interpolated or otherwise use the database's time functions.

      Three is how you're selecting the "top" item in the queue, which is apparently the one where No=1. How are you determining which row should have No=1? Once you've removed No=1 what happens when there is no longer a row with No=1? If this is a queue, wouldn't the next record to process just be the one with the earliest TimeIssued value? Or were you planning to UPDATE every record in the table every time you processed one row?

      Oh, another thing:

      $result = $conn->query($sql);

if ($conn->query($sql) === TRUE) {

      You run the query and collect the result, and then you immediately run the same query again and this time try to compare it to a boolean — instead of just seeing if $result is a valid result, or if the connection's errno is zero.

        Write a Reply...