mysqli cannot insert and update query into mysql database

Question

Please find my code below:

<?php
        //Insert New User to Database
        $username = "root";
        $password = "root";
        $hostname = "localhost";
        $db = "ab-cargo";
        $conn = mysqli_connect($hostname, $username, $password, $db);

        if (mysqli_connect_errno()){
            echo "Failed to connect to MySQL: " . mysqli_connect_error();
        }

        $user_id = $_SESSION['namechat'];
        $user_email = $_SESSION['emailchat'];
        $last_login = $datetime_formatted;

        mysqli_query($conn,"INSERT INTO users (`user_id`, `user_email`, last_login, isActive) VALUES ('".$user_id."', '".$user_email."', '".$last_login."', 1)");
        mysqli_query($conn,"UPDATE users SET last_login = ".$last_login.", isActive = 1 WHERE `user_email` = ".$user_email."");

    ?>

Please help me to find what's wrong with mysqli_query because it won't update and insert the data in my database, even though the connection is finely working (I know it because user is able to login).

Edit: Query insert is to input new user data into the database and if the user data is already in the database before, the update query will update last_login time/date only.

You started the session? You are open to SQL injections. Why `insert` then `update`? Check that status of each query execution. — chris85, Dec 21 '16 at 02:40
Just a tip, you don't need to concatenate the variables, you can just write them into the string. — yaakov, Dec 21 '16 at 02:41
Use prepared statements. Some thin in your data is probably causing this and as @chris85 pointed out you are open to sql injection. — e4c5, Dec 21 '16 at 02:41
This question has nothing to do with phpmyadmin which is a web based client to the **mysql** database — e4c5, Dec 21 '16 at 02:42
Your update syntax is incorrect. You have unquoted strings. Also `user_id` is not auto incrementing? — chris85, Dec 21 '16 at 02:42
Instead of editing the question why not use the comments? Anyway per your update there is no check for which query to execute so both queries execute. What is failing? — chris85, Dec 21 '16 at 02:45
Ah yes... I've made a mistake in above code. Based on @panda's answer. I know I have to use mysqli_real_escape_string to prevent MySQL injection. — anakpanti, Dec 21 '16 at 03:12
@TricksfortheWeb yes. concatenate the variables is also my mistake as well. Thank you for figuring it out. Oh also, user_id is not auto incrementing. Thank you for your help, all. — anakpanti, Dec 21 '16 at 03:14
To auto-increment, just set the mysql to autoincrement using phpmyadmin, and then make that `NULL`, instead of sending a variable. — yaakov, Dec 21 '16 at 17:42

score 1 · Accepted Answer · answered Dec 21 '16 at 02:44

You need to prevent MySQL injection with mysqli_real_escape_string. Read up more about this function here.

Use or die mysqli_error($conn) to check for errors in query.

Also, check if each query is successful before proceeding to the next one.

$user_id = mysqli_real_escape_string($conn, $_SESSION['namechat']);
$user_email = mysqli_real_escape_string($conn, $_SESSION['emailchat']);
$last_login = mysqli_real_escape_string($conn, $datetime_formatted);

$query1 = mysqli_query($conn,"INSERT INTO users (`user_id`, `user_email`, last_login, isActive) VALUES ('$user_id', '$user_email', '$last_login', 1)") or die mysqli_error($conn);

if ($query1) $success = 1;

if ($success) $query2 = mysqli_query($conn,"UPDATE users SET last_login = '$last_login', isActive = 1 WHERE `user_email` = '$user_email'");

if ($query2) echo 'User added';

thank you so much for your help. – anakpanti Dec 21 '16 at 03:08 — anakpanti, Dec 21 '16 at 03:08

score 0 · Answer 2 · answered Dec 21 '16 at 03:18

$_SESSION['namechat']= "1";     
    $_SESSION['emailchat']= "example@gmail.com";
    //SET DATE TIME ZONE
    date_default_timezone_set("Asia/Calcutta");
    $datetime_formatted = date("h:i:sa");


    $user_id = $_SESSION['namechat'];
    $user_email = $_SESSION['emailchat'];
    $last_login = $datetime_formatted;

    //HERE INSERT THE DATA INTO USER
    $sql = "INSERT INTO users (user_id, user_email, last_login, isActive) VALUES ('$user_id', '$user_email', '$last_login', '1')";

    if(mysqli_query($conn,$sql)){
        echo "sql inserted successfully";
    }
    else
    {
        echo "failed to insert".$sql."<br>".mysqli_error($conn);
    } 


    //HERE UPDATE THE DATA INTO USER
    $sql_up ="UPDATE users SET last_login ='$last_login', isActive = '1' WHERE user_email = '$user_email'";

    if(mysqli_query($conn, $sql_up)){
        echo "Data Updated";
    }
    else
    {
        echo "Failed to Updated the data".$sql_up."<br>".mysqli_error($conn);
    }

mysqli cannot insert and update query into mysql database

2 Answers2