CODEWITHSUNDEEP
phpinsertmysqli
Anuj Hari
Anuj Hari

Reputation: 543

INSERT with mysqli

I have a register page which inserts the registration data into a database. It goes along the lines of this:

if ($_POST['password'] == $_POST['conf_pass']){
        $user = $_POST['username'];
        $pass = $_POST['password'];

        $hash = md5(rand(0,1000));

        $accres = mysqli_query($connection, "INSERT INTO accounts (street1, suburb, city, postcode, username) VALUES($address, $suburb, $city, $postcode, $username)");
        $account_id = mysqli_insert_id($accres);
        $peopleres = mysqli_query($connection, "INSERT INTO people (lastname, firstname, accounts_id, birthdate, phonenumber, username, password, email, hash) VALUES($lastname, $firstname, $account_id, $birthdate, $phonenumber, $username, $password, $email, $hash)");

        $peoplerows=mysqli_fetch_assoc($peopleres);
        $person_id=$peoplerows[0];

        mysqli_query("INSERT INTO PeopleToRole (role_id) VALUES(1)");
        $email = $_SESSION['email'];
        $p->addContent("User Registered");
}

I originally programmed all of this using postgres (while hosted locally on an apache server) but had to change to mysqli because the host website was already working with mysqli.

So, this code returns the User Registered on the page, therefore the if statement is working. But for some reason the insert statement won't insert anything into the database.

Do i have some sort of formatting error? or something small that i've missed? Any and all help would be appreciated. Thanks

Upvotes: 0

Views: 232

Answers (2)

Nir Alfasi
Nir Alfasi

Reputation: 53525

You forgot the quotes in the query, for example, you should change:

"INSERT INTO accounts (street1, suburb, city, postcode, username) VALUES($address, $suburb, $city, $postcode, $username)"

to:

"INSERT INTO accounts (street1, suburb, city, postcode, username) VALUES('$address', '$suburb', '$city', '$postcode', '$username')"

That said, working like this makes your code vulnerable to sql injection (as cfreak mentioned in the comments above).

Here's a small example from the manual that shows how you can use bind_param() to make the code more secure:

$mysqli = new mysqli('localhost', 'my_user', 'my_password', 'world');

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$stmt = $mysqli->prepare("INSERT INTO CountryLanguage VALUES (?, ?, ?, ?)");
$stmt->bind_param('sssd', $code, $language, $official, $percent);

$code = 'DEU';
$language = 'Bavarian';
$official = "F";
$percent = 11.2;

/* execute prepared statement */
$stmt->execute();

printf("%d Row inserted.\n", $stmt->affected_rows);

/* close statement and connection */
$stmt->close();

Upvotes: 3

user1864610
user1864610

Reputation:

A common problem: you're not bothering to check your queries for errors, so when something goes wrong you don't know what's happened.

Check the return value of mysqli_query() for FALSE, and if you find it, check mysqli_error($connection) for an error message.

For example:

$accres = mysqli_query($connection, "INSERT INTO accounts (street1, suburb, city, postcode, username) VALUES($address, $suburb, $city, $postcode, $username)");
if ($accres === false) {die(mysqli_error($connection));}

Do something similar for your other queries. When you have an error message, fix it, or come back and ask again.

Upvotes: 1

Related Questions