WTF is wrong with this INSERT?

I’m tearing my hair out! I’ll be damned if I can figure out what’s wrong with this INSERT statement. I pulled all my error-checking out (nulls, dupes and stuff) and simplified this snippet as much as I can.

  1. I know I’m connected to the DB.

  2. I know I have the variables.

  3. I see “Bingo” so I know I went by the INSERT.

  4. I never see “success” OR “error.”

  5. Table definition included as image.
    tblPerson

     <?php     
     // Declare Connection Variables
     	$servername = "xxxxxx";
     	$email = "xxxxxxxx";
     	$passwrd = "xxxxxxxxxx";
     	$dbname = "xxxxxxxx";	
     	
     // Create connection
     	$dbconnect = mysqli_connect($servername, $email, $passwrd, $dbname);	
     	
     // Check connection
     	if (!$dbconnect) {
     		die("Connection failed: " . mysqli_connect_error());
             mysqli_close($dbconnect);
     		}
     	echo "Connected successfully";
    
     //if submit is not blanked i.e. it is clicked.
     echo $_REQUEST['email'];
     echo $_REQUEST['passwrd'];
     echo $_REQUEST['submit'];
     $email=$_REQUEST['email'];
     $passwrd=$_REQUEST['passwrd'];
     $politics=0;
     $gender=0; 
     $race=0;
     $orientation=0;
     $income=0;
     $region=0;    
     echo Variables;
     echo $email;
     echo $passwrd; 
    
         $sql = "INSERT INTO person (politics, gender, race, orientation, income, region, email,passwrd) VALUES ($politics, $gender, $race, $orientation, $income, $region, $email, $passwrd)";
     echo Bingo;
    
     if ($dbconnect->query($sql) === TRUE) {
         echo "New record created successfully";
     } else {
         echo "Error: " . $sql . "<br>" . $dbconnect->error;
     }
     mysqli_close($dbconnect);
     ?>
    

Thanks for responding. This is embarrassing!

  • Admin Edited: Added Code Tags

I didn’t know the code would get scrunched???

This is my first post.

You can edit your post and add bbcode [ code ] [ /code ] tags (without the spaces.)

For the posted query you should be getting an error due to un-quoted string data values. Perhaps it is hidden in the ‘view source’ of the page, depending on what else you may be outputting before the posted code, or it is being discarded, if you happen to have a redirect after the posted code, with php’s output_buffering turned on.

At a minimum, you don’t have php’s error related settings set up to report and display all errors (the two echo statements of undefined constants, which php then assumed were strings should be producing php errors.) You should ALWAYS have error_reporting set to E_ALL, and when learning, developing, and debugging code, have display_errors set to ON, in the php.ini on your system.

Also, almost all database errors, for a database dependent page, are fatal errors and the code should halt execution upon an error. If you instead use exceptions for errors and in most cases let php catch and handle the exception, it will use its error related settings to control what happens with the actual error information (database errors will get displayed or logged the same as php errors.) You would then be able to remove any error handling logic you have now and forget about adding any unless there’s an error that your application code needs to handle (the only errors in this category are duplicate or out of range user submitted data being inserted/updated.)

Next, don’t put external/unknown data directly into an sql query statement. User a prepared query and since the mysqli extension is overly complicated and inconsistent, use the much simpler PDO extension instead.

Thank you! Thank you! Thank you!

I’m experienced web builder but I’ve only been working with PHP for two weeks and I’ve been trying to bootstrap what I’m doing by copying code from the web. I’ve gone through six of them and none of them do what’s presented.

It looks like I’ve bitten off a little more than I can chew.

I am very grateful for your reply and will check out PDO prepared statements.

I’ve been to two dozen websites about this PDO and no two agree on anything.

If I can talk to the database I’ll be off and running, but right now I’m not even crawling. Here’s what I got:

Connect:

$dbconnect = new PDO("mysql:host=$servername;dbname=$dbname", $dbuser, $dbpasswrd);
$dbconnect->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Insert:

36 $sql = “INSERT INTO person VALUES (?, ?, ?, ?, ?, ?, ?, ?)”;
37 $stmt= $pdo->prepare($sql);
38 $stmt->execute([$poltics, $gender, $race, $orientation, $income, $region, $email, $passwrd]);

Error:

Notice : Undefined variable: pdo in E:\web\peoplespoll\htdocs\TestBed\insertuser2.php on line 37

Fatal error : Uncaught Error: Call to a member function prepare() on null in E:\web\peoplespoll\htdocs\TestBed\insertuser2.php:37 Stack trace: #0 {main} thrown in E:\web\peoplespoll\htdocs\TestBed\insertuser2.php on line 37

I read that to mean $pdo is not defined???
I don’t mean to abuse your good nature, but WTF?

Notice : Undefined variable: pdo in E:\web\peoplespoll\htdocs\TestBed\insertuser2.php on line 37

$pdo does not exist on line 37 in insertuser2.php. So there could be a few causes for this error.
a) you are not including the right php files
b) your $pdo variable is initialized in a function or class and does not exist outside that function or class. See for information variable scope on php.net.
c) you forgot a return statement in your function

You use $dbconnect for your connection object but then try to access the object with $pdo. Thus, your problem.

Download my PDO Bumpstart Database to see how to do it right.

Try with the below query

@salnazi, yeah, NO!

You NEVER EVER put variables in a query. You need to use Prepared Statements.

1 Like

Why not? What is the rationale?

https://www.owasp.org/index.php/SQL_Injection

Thank you for the tutorial. I assume this also applies to UPDATE?

It applies anytime you are using user supplied data. That means, anything anywhere that a user gave you. However, when you start using them, you end up using them everywhere for everything.

Sponsor our Newsletter | Privacy Policy | Terms of Service