SQL query not working with prepared statement php

Question

I'm trying to create a basic php register form but I can't figure out why it isn't working. It keeps showing me the error "error=sqlerror3" for the if statement of the sql and it just doesn't insert the values on the table.

Here's my table (idTmp has auto_increment):

CREATE TABLE `users_tmp` (
  `idTmp` int NOT NULL,
  `ipUser` varbinary(17) NOT NULL,
  `cellUser` int NOT NULL,
  `placeUser` longtext NOT NULL,
  `uidUsers` longtext NOT NULL,
  `emailUsers` longtext NOT NULL,
  `pwdUsers` longtext NOT NULL,
  `dataUser` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `stateUser` longtext NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Database connection dbh.inc.php:

<?php

$servername = "localhost";
$username = "root";
$password = "123Ciuronay";
$dbname = "icproducoes_users";

$conn = mysqli_connect($servername, $username, $password, $dbname);

if (!$conn){
    die("Connection failed: " .mysqli_connect_error());
}

Php code:

<?php


if(isset($_POST['registo'])){
    require 'dbh.inc.php';

$username = mysqli_real_escape_string($conn, $_POST['uid']);
$email = $_POST['email'];
$residencia = $_POST ['res'];
$tel = $_POST['tel'];
$check = $_POST['check1'];

//GETS THE IP

$ip = $_SERVER['REMOTE_ADDR'];

// GENEREATES RANDOM PASSOWRD
function genPassword($length = 6) {
    $chars = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
    $count = mb_strlen($chars);

    for ($i = 0, $result = ''; $i < $length; $i++) {
        $index = rand(0, $count - 1);
        $result .= mb_substr($chars, $index, 1);
        $result = strtoupper($result);
    }
    return $result;
}


$password = genPassword();

if(isset($check)){
$check = "YES";

} else {$check = "NO";}



// INSERTS THE VALUES ON DATABASE
$stateUser = "Active";
$sql = "INSERT INTO users_tmp (`ipUser`, `cellUser`, `placeUser`, `uidUsers`, `emailUsers`, `pwdUsers`, `advertising`, `dataUser`, `stateUser`) VALUES ( ?, ?, ?, ?, ?, ?, ? , now(), ?)";

$stmt = mysqli_stmt_init($conn);
if(!mysqli_stmt_prepare($stmt, $sql)){
    header("Location: ../registo-gratis.php?error=sqlerror3");
    exit();

} else {

    mysqli_stmt_bind_param($stmt, "ssssssss",  $ip , $tel, $residencia, $username, $email, $password, $check, $stateUser);
    mysqli_stmt_execute($stmt);
}
}
else{

    echo'  <script>setTimeout(function () {    
        window.location.href = "../registo-gratis.php"; 
    },5000);</script>";';
}
?>

Instead of making up an error, try checking for [mysqli errors](http://php.net/manual/en/mysqli.error.php) to find out why it failed. Maybe it's because there's no `advertising` column in your table. — aynber, May 12 '20 at 18:06

score 0 · Accepted Answer · answered May 12 '20 at 18:21

0

In your INSERT query, you're trying to modify the advertising column, which does not exist in your CREATE TABLE query. As a result, mysqli_stmt_prepare sees that you're trying to write into a column that does not exist and returns an error.

answered May 12 '20 at 18:21

Aivaras Kriksciunas

862
1
8
11

Thank you! Solved my problem! – Nayana Ciuro May 13 '20 at 18:10

SQL query not working with prepared statement php

1 Answers1