CODEWITHSUNDEEP
phpmysqlmysqli
brenhein
brenhein

Reputation: 31

MySQLi: $mysqli->query will not insert, and no error is returned

I am trying to insert simple data into my MySQL table via MySQLi, however it refuses to insert, with no error message reported.

I'd like to stress that this query functions normally when entered directly into PhpMyAdmin (of course, with the variables replaced)

<?php
session_start();
require_once('recaptchalib.php');
require_once('./inc/crypt.php');
require_once('./inc/mysql.php');

$privatekey = "-------------------------------- ";

$u = $mysqli->real_escape_string($_POST['username']);
$p = crypto($_POST['password']);
$e = $mysqli->real_escape_string($_POST['email']);

$resp = recaptcha_check_answer ($privatekey,
    $_SERVER["REMOTE_ADDR"],
    $_POST["recaptcha_challenge_field"],
    $_POST["recaptcha_response_field"]);

  if (!$resp->is_valid) {
      $_SESSION['loginError'] = "The captcha was entered incorrectly!";
      header("LOCATION: ./index.php?pg=1");
      die();
  }


$amt = 0;

if($data = $mysqli->prepare("SELECT * FROM users WHERE username=? OR email=?")){
    $data->bind_param("ss", $u, $e);
    $data->execute();
    $data->store_result();
    $amt = $data->num_rows();
    $data->close();
}
if($amt != 0){
    $_SESSION['loginError'] = "A user has already registered with either that email or username.";
    header("LOCATION: ./index.php?pg=1");
    die();
}

if(strlen($u) < 5){
    $_SESSION['loginError'] = "A username must be greater than 5 characters long.";
    header("LOCATION: ./index.php?pg=1");
    die();
}

if(strlen($e) <= 5){
    $_SESSION['loginError'] = "An email must be greater than 5 characters long.";
    header("LOCATION: ./index.php?pg=1");
    die();
}

if(!strstr($e, "@")){
    $_SESSION['loginError'] = "A valid email must be entered.";
    header("LOCATION: ./index.php?pg=1");
    die();
}

if(!ctype_alnum($u)){
    $_SESSION['loginError'] = "Your username contains an invalid character.";
    header("LOCATION: ./index.php?pg=1");
    die();
}
$ip = $_SERVER['REMOTE_ADDR']."r";

$mysqli->query("INSERT INTO users (`username`, `password`, `email`, `perms`, `ip`) VALUES ('".$u."', '".$p."', '".$e."', '0', '".$ip."')");


header("LOCATION: ./index.php?win=1");

?>

I also should note that using both $mysqli->query and prepared statements both did not work.

Here's my phpinfo() under the MySQLi section:

Client API library version  5.0.95
Client API header version   5.0.91
MYSQLI_SOCKET   /var/lib/mysql/mysql.sock

Directive   Local Value Master Value
mysqli.default_host no value    no value
mysqli.default_port 3306    3306
mysqli.default_pw   no value    no value
mysqli.default_socket   no value    no value
mysqli.default_user no value    no value
mysqli.max_links    Unlimited   Unlimited
mysqli.reconnect    Off Off

Last thing: I have a very similar insert statement being used in a different file that works.

Thanks in advance

Upvotes: 3

Views: 2804

Answers (1)

Conrad Lotz
Conrad Lotz

Reputation: 8818

Do not use single quotes instead use the ` likes this:

$mysqli->query("INSERT INTO `users` (`username`, `password`, `email`, `perms`, `ip`) VALUES ('".$u."', '".$p."', '".$e."', '0', '".$ip."')");

It should not have the single quotes around the field names. I tested in phpMyAdmin and as your example showed it failed but with my example above it worked.

Upvotes: 1

Related Questions