0

I have a wizard that collects information using whatsapp api which in beta according to the twilio docs. I can get it to send and receive information from users. I now want to use it to fill up appropriate fields in a mysql table called travel but i dont understand why it cannot fill up the fields appropriately. Here is the script that i am using

$twilio = new Client($sid, $token);

$number = $_POST['From'];
$body = $_POST['Body'];


$servername = "localhost";
$username = "root";
$password = "123456";
$dbname = "twilio";

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


$query = "select * from travel where telephone_number = '$number' AND msg = 'UNDONE'";
$result = mysqli_query($conn, $query);
$row = mysqli_fetch_assoc($result);

$rc = $result->num_rows;
$t = time();

$telephone_number = $row['telephone_number'];
$q1_field = $row['where_are_u_travelling_to'];
$q2_field = $row['where_are_you_travelling_from'];
$q3_field = $row['are_you_ready_to_go_on_next_bus'];
$q4_field = $row['how_many_people'];
$msg = $row['msg'];

if($rc == 0){
    $sql = "INSERT INTO travel (telephone_number,last_updated,msg)
            VALUES ('$number','$t','UNDONE')";

    if ($conn->query($sql) === TRUE) {
        echo "New record created successfully";
    }

    $message = $twilio->messages
                  ->create("$number",
                           array(
                               "body" => "Welcome To The Booking System.Where are you travellig to?",
                               "from" => "whatsapp:+14"
                           )
                  );


    print($message->sid);

}

//First
if(empty($q1_field) &&  empty($q2_field) && empty($q3_field) && empty($q4_field) && $msg == "UNDONE"){

    $sql_01 = "UPDATE travel SET
                    where_are_u_travelling_to='$body',
                    last_updated='$t' 
                WHERE telephone_number='$number'";

    if ($conn->query($sql_01) === TRUE) {
        echo "Record updated successfully";
    } else {
        echo "Error updating record: " . $conn->error;
    }

    $message = $twilio->messages
                  ->create("$number",
                           array(
                               "body" => "Where are you travelling from?",
                               "from" => "whatsapp:+14"
                           )
                  );


    print($message->sid);
    exit();
}

//Second
if(!empty($q1_field) &&  empty($q2_field) && empty($q3_field) && empty($q4_field) && $msg == "UNDONE"){

    $sql_02 = "UPDATE travel SET    
                where_are_u_travelling_from='$body',
                last_updated='$t' 
                WHERE telephone_number='$number'";

    if ($conn->query($sql_02) === TRUE) {
        echo "Record updated successfully";
    } else {
        echo "Error updating record: " . $conn->error;
    }


    $message = $twilio->messages
                  ->create("$number",
                           array(
                               "body" => "Are you ready to go on the next bus?",
                               "from" => "whatsapp:+14"
                           )
                  );


    print($message->sid);
    exit();
}

//Third
if(!empty($q1_field) &&  !empty($q2_field) && empty($q3_field) && empty($q4_field) && $msg == "UNDONE"){
    $sql_03 = "UPDATE travel SET 
                are_you_ready_to_go_on_next_bus='$body',
                last_updated='$t' 
                WHERE telephone_number='$number'";

    if ($conn->query($sql_03) === TRUE) {
        echo "Record updated successfully";
    } else {
        echo "Error updating record: " . $conn->error;
    }
    $message = $twilio->messages
                  ->create("$number",
                           array(
                               "body" => "How many people are travelling?.",
                               "from" => "whatsapp:+14"
                           )
                  );


    print($message->sid);
    exit();
}

//Fourth
if(!empty($q1_field) &&  !empty($q2_field) && !empty($q3_field) && empty($q4_field) && $msg == "UNDONE"){
    $sql_04 = "UPDATE travel SET 
                    how_many_people='$body',
                    last_updated='$t',msg='DONE' 
                WHERE telephone_number='$number'";

    if ($conn->query($sql_04) === TRUE) {
        echo "Record updated successfully";
    } else {
        echo "Error updating record: " . $conn->error;
    }
    $message = $twilio->messages
                  ->create("$number",
                           array(
                               "body" => "Thank you for travelling with us.Yur ticket has been booked.",
                               "from" => "whatsapp:+14"
                           )
                  );


    print($message->sid);
    exit();
}

This is the mysql schema

CREATE TABLE `travel` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `telephone_number` VARCHAR(50) NULL DEFAULT NULL,
    `where_are_u_travelling_to` VARCHAR(50) NULL DEFAULT NULL,
    `where_are_you_travelling_from` VARCHAR(50) NULL DEFAULT NULL,
    `are_you_ready_to_go_on_next_bus` VARCHAR(50) NULL DEFAULT NULL,
    `how_many_people` VARCHAR(50) NULL DEFAULT NULL,
    `msg` VARCHAR(50) NULL DEFAULT NULL,
    `last_updated` VARCHAR(50) NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
AUTO_INCREMENT=1
;

Only the very first update keeps updating and not other fields.Why is this?.

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
Gandalf
  • 1
  • 29
  • 94
  • 165
  • Your script is wide open to [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) in either the `MYSQLI_` or `PDO` API's – RiggsFolly Nov 14 '18 at 10:56
  • Some sensible code indentation would be a good idea. It helps us read the code and more importantly it will help **you debug your code** [Take a quick look at a coding standard](http://www.php-fig.org/psr/psr-2/) for your own benefit. You may be asked to amend this code in a few weeks/months and you will thank me in the end. – RiggsFolly Nov 14 '18 at 10:57

0 Answers0