1

There is a registration interface where you must enter your billing information after logging in. The goal is to record the entered data in the MySQL database and update the "billable" value to "1", however I get a syntax error.

The code is (PhP 7.3):

<?php

session_start();
if(!isset($_SESSION['username'])) {
    header('location: login.php');
    exit();
}

include_once('dbconnect.php');

$username = $_SESSION["username"];
$result = mysqli_query($conn, "SELECT * FROM users WHERE username = '$username'");
while($row = mysqli_fetch_array($result)) {

    if ($row['billable'] == 0) {

        $error = false;
        if(isset($_POST['btn-updatedetails'])) {

        $firstname = mysqli_real_escape_string($conn, $_REQUEST['firstname']);
        $lastname = mysqli_real_escape_string($conn, $_REQUEST['lastname']);
        $phone = mysqli_real_escape_string($conn, $_REQUEST['phone']);
        $company = mysqli_real_escape_string($conn, $_REQUEST['company']);
        $country = mysqli_real_escape_string($conn, $_REQUEST['country']);
        $county = mysqli_real_escape_string($conn, $_REQUEST['county']);
        $city = mysqli_real_escape_string($conn, $_REQUEST['city']);
        $street = mysqli_real_escape_string($conn, $_REQUEST['street']);
        $postcode = mysqli_real_escape_string($conn, $_REQUEST['postcode']);

        if(empty($firstname)) {
            $error = true;
            $errorFirstname = 'This field cannot be left blank.';
        }    

        if(empty($lastname)) {
            $error = true;
            $errorLastname = 'This field cannot be left blank.';
        } 

        if(empty($phone)) {
            $error = true;
            $errorPhone = 'This field cannot be left blank.';
        }  

        if(empty($country)) {
            $error = true;
            $errorCountry = 'This field cannot be left blank.';
        } 

        if(empty($county)) {
            $error = true;
            $errorCounty = 'This field cannot be left blank.';
        } 

        if(empty($city)) {
            $error = true;
            $errorCity = 'This field cannot be left blank.';
        } 

        if(empty($street)) {
            $error = true;
            $errorStreet = 'This field cannot be left blank.';
        } 

        if(empty($postcode)) {
            $error = true;
            $errorPostcode = 'This field cannot be left blank.';
        } 

        if(!$error){
            $sql = "INSERT INTO users (firstname, lastname, phone, company, country, county, city, street, postcode) VALUES ('$firstname', '$lastname', '$phone', '$company', '$country', '$county', '$city', '$street', '$postcode') WHERE username = '$username' AND UPDATE users SET billable='1' WHERE username = '$username'";
            if(mysqli_query($conn, $sql)){
                $successMsg = 'Success!';
            }else{
                echo 'Error '.mysqli_error($conn);
            }

        }

    }

        ?>
        <form method="POST" action="<?php echo htmlspecialchars($_SERVER['PHP_SELF']); ?>" autocomplete="off">
        Firstname:
        <input type="text" name="firstname" id="firstname"><br>
        Lastname:
        <input type="text" name="lastname" id="lastname"><br>
        Phone:
        <input type="text" name="phone" id="phone"><br>
        Company (optional):
        <input type="text" name="company" id="company"><br>
        Country:
        <input type="text" name="country" id="country"><br>
        County:
        <input type="text" name="county" id="county"><br>
        City:
        <input type="text" name="city" id="city"><br>
        Street:
        <input type="text" name="street" id="street"><br>
        Postcode:
        <input type="text" name="postcode" id="postcode"><br>

        <input type="submit" name="btn-updatedetails">
        </form>

        <?php


} else {
        echo "<3";
    }
}

mysqli_close($conn);

This page returns the following error:

Error You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE username = 'Erik' AND UPDATE users SET billable='1' WHERE username = 'Erik' at line 1 

MySQL database:

sql database

Where did I make a mistake?

Thank you in advance for your answer!
Have a nice day!

10
  • 1
    why are you doing this? UPDATE users SET billable='1' WHERE username = '$username'" Commented Jul 25, 2019 at 11:22
  • @Regolith same question. Commented Jul 25, 2019 at 11:23
  • 1
    you dont need where in insert statement Commented Jul 25, 2019 at 11:24
  • Okay. I deleted the first WHERE. The current code: $sql = "INSERT INTO users (firstname, lastname, phone, company, country, county, city, street, postcode) VALUES ('$firstname', '$lastname', '$phone', '$company', '$country', '$county', '$city', '$street', '$postcode') AND UPDATE users SET billable='1' WHERE username = '$username'"; Now I'm getting the following error: "Error You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AND UPDATE users SET billable='1' WHERE username = 'Erik'' at line 1 " Commented Jul 25, 2019 at 11:28
  • 1
    I suppose you understand you can directly INSERT the billable property at 1 ? Or use default value in your schema. Why update after ? Commented Jul 25, 2019 at 11:29

1 Answer 1

1

You did a SELECT to start with so I don't see why you want to INSERT another row for the same user. I think you just want to do a single UPDATE as such:

UPDATE users 
SET
    firstname = '$firstname',
    lastname = '$lastname',
    phone = '$phone',
    company = '$company',
    country = '$country',
    county = '$county',
    city = '$city',
    street = '$street',
    postcode = '$postcode',
    billable = 1
WHERE username = '$username'
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.