PHP: Taking the last row's id number from one mysql table and using it to update a field in the first row of another table

View Original

I'm trying to put the 'id' field value of the last row from users_data table to into the first row of the field_num field of the field_numbers table but this is not updating properly. I can verify that $last_id gets set to the most recent id number in the field_numbers table.

The issue with getting the UPDATE line to work near the bottom... Your help is greatly appreciated!

if ($_SERVER["REQUEST_METHOD"] == "POST") {//Check it is coming from a form

    //mysql credentials
    $mysql_host = "buythosecarscom.fatcowmysql.com";
    $mysql_username = "[secret]";
    $mysql_password = "[secret]";
    $mysql_database = "buythatcar";
    
//header("Location: survey_two.html");

    $u_q1 = filter_var($_POST["question_1"], FILTER_SANITIZE_STRING); //set PHP variables like this so we can use them anywhere in code below
    $u_q2 = filter_var($_POST["question_2"], FILTER_SANITIZE_STRING);
    $u_q3 = filter_var($_POST["question_3"], FILTER_SANITIZE_STRING);
        $u_q4 = filter_var($_POST["question_4"], FILTER_SANITIZE_STRING);
        $u_q4b = filter_var($_POST["question_4b"], FILTER_SANITIZE_STRING);
        $u_q5 = filter_var($_POST["question_5"], FILTER_SANITIZE_STRING);        
        $u_q6 = filter_var($_POST["question_6"], FILTER_SANITIZE_STRING);
        $u_q7 = filter_var($_POST["question_7"], FILTER_SANITIZE_STRING);
        $u_q8 = filter_var($_POST["question_8"], FILTER_SANITIZE_STRING);
        $u_q9 = filter_var($_POST["question_9"], FILTER_SANITIZE_STRING);
        $u_q10 = filter_var($_POST["question_10"], FILTER_SANITIZE_STRING);

    //Open a new connection to the MySQL server
    $mysqli = new mysqli($mysql_host, $mysql_username, $mysql_password, $mysql_database);
    
    //Output any connection error
    if ($mysqli->connect_error) {
        die('Error : ('. $mysqli->connect_errno .') '. $mysqli->connect_error);
    }   
    
    $statement = $mysqli->prepare("INSERT INTO users_data (question_1, question_2, question_3, question_4, question_4b, question_5, question_6, question_7, question_8, question_9, question_10) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); //prepare sql insert query
    //bind parameters for markers, where (s = string, i = integer, d = double,  b = blob)
    $statement->bind_param('sssssssssss', $u_q1, $u_q2, $u_q3, $u_q4, $u_q4b, $u_q5, $u_q6, $u_q7, $u_q8,  $u_q9, $u_q10); //bind values and execute insert query
    

    if($statement->execute()){

//This does not work to update the field_num record at id =1 to $last_id's value
$last_id = $mysqli -> insert_id;
$sql = "UPDATE field_numbers SET field_num= '$last_id' WHERE id=1";

//This correctly returns that value of the last_id... so I know it's set right
echo "New record has id: " . $last_id;
        print "Hello " . $mysqli-> insert_id . "!, your message has been saved!";
                print "Hello  $last_id";
    }else{
        print $mysqli->error; //show mysql error if any
    }
}
?>

Answer

Solution:

Looks like you need to execute the $sql query... see the modified code below for how to execute a mysqli sql update query.

<?php
    //mysql credentials
    $mysql_host = "buythosecarscom.fatcowmysql.com";
    $mysql_username = "[secret]";
    $mysql_password = "[secret]";
    $mysql_database = "buythatcar";
    
//header("Location: survey_two.html");

    $u_q1 = filter_var($_POST["question_1"], FILTER_SANITIZE_STRING); //set PHP variables like this so we can use them anywhere in code below
    $u_q2 = filter_var($_POST["question_2"], FILTER_SANITIZE_STRING);
    $u_q3 = filter_var($_POST["question_3"], FILTER_SANITIZE_STRING);
        $u_q4 = filter_var($_POST["question_4"], FILTER_SANITIZE_STRING);
        $u_q4b = filter_var($_POST["question_4b"], FILTER_SANITIZE_STRING);
        $u_q5 = filter_var($_POST["question_5"], FILTER_SANITIZE_STRING);        
        $u_q6 = filter_var($_POST["question_6"], FILTER_SANITIZE_STRING);
        $u_q7 = filter_var($_POST["question_7"], FILTER_SANITIZE_STRING);
        $u_q8 = filter_var($_POST["question_8"], FILTER_SANITIZE_STRING);
        $u_q9 = filter_var($_POST["question_9"], FILTER_SANITIZE_STRING);
        $u_q10 = filter_var($_POST["question_10"], FILTER_SANITIZE_STRING);

    //Open a new connection to the MySQL server
    $mysqli = new mysqli($mysql_host, $mysql_username, $mysql_password, $mysql_database);
    
    //Output any connection error
    if ($mysqli->connect_error) {
        die('Error : ('. $mysqli->connect_errno .') '. $mysqli->connect_error);
    }   
    
    $statement = $mysqli->prepare("INSERT INTO users_data (question_1, question_2, question_3, question_4, question_4b, question_5, question_6, question_7, question_8, question_9, question_10) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); //prepare sql insert query
    //bind parameters for markers, where (s = string, i = integer, d = double,  b = blob)
    $statement->bind_param('sssssssssss', $u_q1, $u_q2, $u_q3, $u_q4, $u_q4b, $u_q5, $u_q6, $u_q7, $u_q8,  $u_q9, $u_q10); //bind values and execute insert query
    

    if($statement->execute()){
       $last_id = $mysqli -> insert_id;
       echo "New record has id: " . $last_id;
       print "Hello " . $mysqli-> insert_id . "!, your message has been saved!";
       print "Hello  $last_id";
      
       $sql = "UPDATE field_numbers SET field_num= '$last_id' WHERE id=1";

       if($mysqli->query($sql) === TRUE) {
            echo "cool";
            }else{
       echo "awful: " . $mysqli->error;
         }
      }else{
        print $mysqli->error; //show mysql error if any
    }
?>

Source