1

For a project on school I have to create a site for a company. I was working on the register system when I stumbled upon this problem. For some reason my query won't work on my databse. I've tried inputting the query in phpmyadmin, which worked fine, but if i want to use the query in php, it doesn't work. The database I'm using was premade, and it isn't auto incremented so I have to add all the values of all the rows when I want to insert new data.

This is the connection to my database I am using I use the header messages to indicate if the signup was succesfull:

function dbConnectionRoot () {

        $dbServername = "localhost";
        $dbUsername = "root";
        $dbPassword = "";
        $dbName = "wideworldimporters";

        $connection = mysqli_connect($dbServername, $dbUsername, $dbPassword, $dbName);

        return $connection;
    }

And this is the insert script I'm using:

$sql = "INSERT INTO customers (
                    CustomerID, CustomerName, CustomerCategoryID, 
                    PhoneNumber, FaxNumber, EmailAddress, 
                    HashedPassword, BillToCustomerID, BuyingGroupID, 
                    PrimaryContactPersonId, AlternateContactPersonID, DeliveryMethodID, 
                    DeliveryCityID, PostalCityId, AccountOpenedDate, 
                    StandardDiscountPercentage, IsStatementSent, IsOnCreditHold, 
                    PaymentDays, WebsiteURL, DeliveryAddressLine1, 
                    DeliveryPostalCode, PostalAddressLine1, PostalPostalCode, 
                    LastEditedBy, ValidFrom, ValidTo) 
                    VALUE (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";
                $statement = dbConnectionRoot()->prepare($sql);
                mysqli_stmt_init(dbConnectionRoot());

                if (dbConnectionRoot()->connect_errno) {
                    header("Location: signup:php?error=connection");
                    exit();
                } elseif(!dbConnectionRoot()->query($sql)) {
                    header("Location: signup.php?error=queryerror");
                    exit();
                } else {
                    //First we has the password. This is because if a hacker were to hack into the database, it could only see the hashed passwords.
                    // We use this hashing method(bcrypt) because it is always updated when there is a security breach.
                    $hashedPassword = password_hash($password, PASSWORD_DEFAULT);
                    $maxCustomerID++;
                    //Again, we are making a prepared statement for security. This time we use 27 s'es because we want 27 different variables
                    $statement->bind_param("sssssssssssssssssssssssssss", 
                    $maxCustomerID, $fullName, $customerCategory, $phoneNumber, $faxNumber, $email, $hashedPassword, $billToCustomerID, 
                    $buyingGroupID, $primaryContactPersonId, $alternateContactPersonID, $deliveryMethodID, $deliveryCityID, $postalCityId, $accountOpenedDate, 
                    $standardDiscountPercentage, $isStatementsent, $isOnCreditHold, $paymentDays, $websiteURL, $deliveryAddressLine1, $deliveryPostalCode, 
                    $postalAddressLine1, $postalPostalCode, $lastEditedBy, $validFrom, $validTo);
                    $statement->execute();
                    return($statement);
                    //A message that the signup was succesfull
                    header("Location: signup.php?signup=success");
                    exit();

And this is the register form snippet

<form action="signupfunctions.php" method="post">
    <label>Volledige naam: </label><input type="text" name="FullName"><br>
    <label>E-mail adres: </label><input type="text" name="EmailAddress"><br>
    <label>Wachtwoord: </label><input type="password" name="Password"><br>
    <label>Herhaal uw wachtwoord: </label><input type="password" name="PasswordRepeat"><br>
    <label>Telefoon nummer: </label><input type="text" name="PhoneNumber"><br>
    <label>Fax nummer: </label><input type="text" name="FaxNumber"><br>
    <button type="submit" name="signupbutton" >Registreer</button>
</form>
Skidoods
  • 11
  • 1
  • First, don't call `dbConnectionRoot()` every time you need the connection. Call it _once_, and store the result in a variable. What is the purpose of calling `mysqli_stmt_init()` in this? – Patrick Q Dec 05 '19 at 21:31
  • What debugging have you done? What _does_ happen? – Patrick Q Dec 05 '19 at 21:31
  • @PatrickQ The `mysqli_stmt_init()` checks if the connection is ready for a prepare. I have not used a debugger yet, but I've tried a couple of header adjustments, and the problem seem to be in the sql query. Everything works fine until we get to the `elseif(!dbConnectionRoot()->query($sql))` . Then I get a this header message `signup.php?error=queryerror` – Skidoods Dec 05 '19 at 22:07
  • You've already done the `prepare()` on the line prior. – Patrick Q Dec 05 '19 at 22:08
  • I thought the `mysqli_stmt_init()` was needed to see if the query with the connection was right, but i guess it is unnecessary – Skidoods Dec 05 '19 at 22:12
  • "Everything works fine until ... " that's because you haven't bound the values to the parameters yet. I highly suggest you go through the PHP manual's resources for [mysqli prepared statements](https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php). – Patrick Q Dec 05 '19 at 22:15
  • Does this answer your question? [mysqli\_fetch\_assoc() expects parameter / Call to a member function bind\_param() errors. How to get the actual mysql error and fix it?](https://stackoverflow.com/questions/22662488/mysqli-fetch-assoc-expects-parameter-call-to-a-member-function-bind-param) – Dharman Dec 05 '19 at 22:49

1 Answers1

0

You have a syntax error in your SQL statement. It should be: INSERT INTO ... VALUES, not VALUE.

lafor
  • 12,472
  • 4
  • 32
  • 35