Unable to input data in database - weird problem

[benoit1980]

Hello,

 

I cannot work this one out, I am trying to post some data into mysql using mysqli_real_escape_string but nothing happens, All I am getting is:

"You are already already registered!"

 

The database is empty and the phone_number field is set to "unique.

 

Now What I do not understand is that the database is empty, no duplicate records in it but the mysqli_affected_row detects a duplicate or else something which I do not yet understand why it is happening.

The query seems to be working ok(at least the output looks ok) but is not inputting the record in the database because mysqli_affected_rows is detecting a row.

 

Here is my new code:

 

<?php
require_once("config.php"); 
$dat_date = date('Y-m-d H:i:s');

$db = new mysqli("localhost", $username, $password, $db_name);
$query = sprintf("INSERT INTO $tbl_name (id, date, phone_number, email, code, recommended, terms, notifications, name, surname, age, city, leader, department) VALUES (NULL, '$dat_date', '%s', '%s', '%s', '%s', '%s','%s', '%s', '%s', '%s', '%s', NULL, NULL)",
   $db->real_escape_string($phone_number),
   $db->real_escape_string($email),
   $db->real_escape_string($code), 
   $db->real_escape_string($recommended), 
   $db->real_escape_string($terms), 
   $db->real_escape_string($notifications), 
   $db->real_escape_string($name), 
   $db->real_escape_string($surname),
   $db->real_escape_string($age),
   $db->real_escape_string($city),
   $db->real_escape_string($leader),  
   $db->real_escape_string($department) );  
   
if(mysqli_affected_rows($db) <= 0){
    
    echo '<div align="center" class="text-not-correct">You are already already registered!<br></div>';
    echo $query;

mysqli_close($db);

}else{
 echo 'something';
}
?> 

 

 

Now the output of my query is:

 

 

INSERT INTO data_xxxxx (id, date, phone_number, email, code, recommended, terms, notifications, name, surname, age, city, leader, department) VALUES (NULL, '2013-07-10 07:24:33', '0035679303062', 'someemail@gmail.com', '44w787', 'o\'reilly', 'I have read and agree to the Terms and Conditions','Yes I agree to receive VIP notifications', 'o\'reilly', 'o\'reilly', '19', 'epinal', NULL, NULL)

 

 

 

It seems that everythin is correct, any idea what did I miss please?

 

Now the mysqli_real_escape_string seems to be working ok.

 

Rmember, the phone mysql field is set as "unique".

 

 

Thank you!

 

 

Ben

 

[cyberRobot]

You need to execute the query:

http://php.net/manual/en/mysqli.query.php

[benoit1980]

Thank you cyberRobot,

 

Sorry for my lack of knowledge about Mysqli but this is new to me.

I tried to add this but I am getting an error:

 

$query->execute();

 

Can you please help me?

 

Thank you,.

 

Ben

[AbraCadaver]

Thank you cyberRobot,

 

Sorry for my lack of knowledge about Mysqli but this is new to me.

I tried to add this but I am getting an error:

 

$query->execute();

 

Can you please help me?

 

Thank you,.

 

Ben

What error?  That would seem to be an important piece of information wouldn't you say?

[benoit1980]

:-)))

 

Sorry about that, here is what I get:

 

Fatal error: Call to a member function execute() on a non-object in /home/piabyl/public_html/vieck3/header.php on line 126

 

Line 126 refers to

$query->execute();

 

Thank you,

 

Ben

[cyberRobot]

The function you're looking for is mysqli_query(). More information (including examples) can be found here:

http://php.net/manual/en/mysqli.query.php

[AbraCadaver]

The function you're looking for is mysqli_query(). More information (including examples) can be found here:

http://php.net/manual/en/mysqli.query.php

Also, query() is a member of the $db object not the $query string variable. Pass $query into query().

[dogguy2010]

I am very new to all of this and I am not sure if this is your problem or not, but maybe.

 

Shouldn't your query look like:

 

$query = sprintf("INSERT INTO ......  ");

 

yours is currently:

 

$query = sprintf("INSERT INTO ......  );

 

I think you are missing the set of quotation marks at the end of the query.

I hope that helps.

[jcbones]

No his sprintf is called right. The problem is that the OP is trying to run the query two different ways, without understanding the difference.

 

The preferred way with mysqli is to prepare the sql, then bind the parameters, then execute the query, then bind the results, then print the results.  This is so that the mysqli class can apply all needed sanitation and validation.  If you choose to use the sprintf to write the query string, adding in your own validation and sanitation, then you must use the mysqli::query function.  This bypasses all of the sanitation and validation available in the mysqli class, and runs the query straight to the database (much like the old mysql functions).

[benoit1980]

Hi All,

 

I have changed this if(mysqli_affected_rows($db) <= 0){ to if(mysqli_affected_rows($db) >= 1){ and it works now.

 

I have a few questions for you.

1)Is it normal that when I echo the query, I see the backslashes from mysqli_real_escape_string but in the database nothing is showing, is this right?

 

2)JcJones, I am sorry but I do not understand what you mean by "The problem is that the OP is trying to run the query two different ways", could you please explain this at a beginner level  :-))...still learning PHP via Books and Youtube...I do not find it easy at all....

 

Thank you,

 

Ben

[benoit1980]

Nope my mistake, it does not work.

 

When I echo the query, copy it and paste it in mysql it works but from the php to mysql automatically, it will not work.....weird...

 

Thank you,

 

Ben

[jcbones]

Sure, you started off trying to use mysqli as a object, then jumped to using the procedural functions.  

 

Try this: UNTESTED code, so don't overwrite your work.

<?php
require_once("config.php"); 
$dat_date = date('Y-m-d H:i:s');
 
$db = new mysqli("localhost", $username, $password, $db_name);
$query = sprintf("INSERT INTO $tbl_name (id, date, phone_number, email, code, recommended, terms, notifications, name, surname, age, city, leader, department) VALUES (NULL, '$dat_date', '%s', '%s', '%s', '%s', '%s','%s', '%s', '%s', '%s', '%s', NULL, NULL)",
   $db->real_escape_string($phone_number),
   $db->real_escape_string($email),
   $db->real_escape_string($code), 
   $db->real_escape_string($recommended), 
   $db->real_escape_string($terms), 
   $db->real_escape_string($notifications), 
   $db->real_escape_string($name), 
   $db->real_escape_string($surname),
   $db->real_escape_string($age),
   $db->real_escape_string($city),
   $db->real_escape_string($leader),  
   $db->real_escape_string($department) );  //use sprintf to setup the escaped values into a properly formed sql string.
 
if(!$db->query($query)) { //if the query fails to run.
trigger_error($db->error); //show us the error.
}
 
if($db->affected_rows != 1){ //if the affected rows does not equal 1, the database didn't  add the row.  (the database should ONLY EVER return 1 or 0 for this query.
    
    echo '<div align="center" class="text-not-correct">You are already already registered!<br></div>';
    echo $query;
$db->close(); //close the database connection.  If we are using the object, keep it in the object, not procedural.
 
}else{
 echo 'something';
}
?>