Why does my INSERT statement fail without error?

Question

I was trying to insert a row in a table but it is not inserted without showing any error:

$sql="(INSERT INTO as_registration (roll, registrationid, fullname, username, 
       password, dob, courseid, email, gender, phoneno, status) 
       VALUES ('".$roll."','".$regid."','".$name."','".$username."','"
       .$pwd."','".$dob."',".intval($c_id['courseid']).",'".$email."','"
       .$gender."',".$phone.",'".$status."'))";

Where is the error in this statement?

I can't even read that. Is it PHP or something? Is your phone number really not a string?! I assume you're cleaning all the values to prevent SQL injection somewhere else? How are you actually doing the insert? Why are there parens around the entire statement? — Dave Newton, Sep 08 '12 at 16:24
yes trying to insert through php. i have done without parens also but not inserted. — user1611964, Sep 08 '12 at 16:27
Did you check for mysql errors? What does it return? Add `echo $sql;`. What does it display? — Jocelyn, Sep 08 '12 at 16:29
First, are you sure that the query is actually executed? Perhaps the code just does not run it. If it is actually executed, try writing the query in an easier-to-read way. — Giulio Muscarello, Sep 08 '12 at 16:27
@GiulioMuscarello Did formatting for you . It was kind of hard to track the string like this. It gave me headache :( — MD. Sahib Bin Mahboob, Sep 08 '12 at 19:28

score 1 · Answer 1 · edited May 23 '17 at 10:24

You mustn't use parentheses at the beginning and at the end, try using this query please:

    $sql = "
INSERT INTO 
    as_registration (roll, registrationid, fullname, username, password, dob, courseid, email, gender, phoneno, status ) 
VALUES 
    ('". mysql_real_escape_string($roll) ."','". mysql_real_escape_string($regid) ."','" 
       . mysql_real_escape_string($name) ."','". mysql_real_escape_string($username) ."','"
       . mysql_real_escape_string($pwd) ."','". mysql_real_escape_string($dob) ."',"
       . mysql_real_escape_string(intval($c_id['courseid'])) .",'". mysql_real_escape_string($email) ."','"
       . mysql_real_escape_string($gender) ."','". mysql_real_escape_string($phone) ."','". mysql_real_escape_string($status) ."')";

If you use your code just like this then it's vulnerable for SQL Injection. I would strongly recommend using mysql_real_escape_string as you insert data into your database to prevent SQL injections, as a quick solution or better use PDO or MySQLi.

Besides if you use mysql_* to connect to your database, then I'd recommend reading the PHP manual chapter on the mysql_* functions, where they point out, that this extension is not recommended for writing new code. Instead, they say, you should use either the MySQLi or PDO_MySQL extension.

@llia Rostovtsev; your suggestion is topnotch, but the answer......not so much. There's a big chance the OP will have a hard time understanding it. And your assumption is that the OP uses **mysql_*** (which is very likely); however, if the OP used **mysqli**, your answer will throw an error. :-D Thanks for the laugh comment. — Kneel-Before-ZOD, Sep 10 '12 at 19:58
@Sam I was bending over the limits and provided for the uncertain question quite certain answer. Asker could write few lines to me and I would rearrange my answer depending on what would be said. Thank you too! ;) — Ilia Ross, Sep 10 '12 at 20:08

score 0 · Answer 2 · answered Sep 08 '12 at 16:26

0

Remove the first and last parentheses "(" and ")".

answered Sep 08 '12 at 16:26

tomsv

7,207
6
55
88

score 0 · Answer 3 · answered Sep 08 '12 at 16:31

The following code is equivalent to the code you posted.

$sql="INSERT INTO as_registration (roll, registrationid, fullname, username, password, dob, courseid, email, gender, phoneno, status )
      VALUES ('$roll', '$regid', '$name', '$username', '$pwd', '$dob', ".intval($c_id['courseid']).", '$email', '$gender', $phone, '$status')";

Documentation about double-quoted strings

score 0 · Answer 4 · answered Sep 08 '12 at 16:34

0

try;

$courceid = intval($c_id['courseid']);
$sql="(INSERT INTO as_registration
(roll, registrationid, fullname, username, password, dob, courseid, email, gender, phoneno, status )
VALUES
('$roll','$regid','$name','$username','$pwd','$dob','$courceid','$email','$gender','$phone','$status'))";

answered Sep 08 '12 at 16:34

Alfred

21,058
61
167
249

Alfred, interesting idea about taking `intval` out of the query but what makes you think it's the case? :) I have never encounter such problem, have you? Just out of the curiosity? – Ilia Ross Sep 08 '12 at 16:42

Aghilas Yakoub · Accepted Answer · 2012-09-11T08:38:56.917

-3

You can delete double quotes and '.' and '(' caracter

$sql="INSERT INTO as_registration (roll, registrationid, fullname, username, password, dob, courseid, email, gender, phoneno, status ) VALUES ('$roll','$regid','$name','$username','$pwd','$dob',{c_id['courseid']} ,'$email','$gender',$phone,'$status')"

edited Sep 11 '12 at 08:38

answered Sep 08 '12 at 16:24

Aghilas Yakoub

28,516
5
46
51

2

I don't think you can call the PHP function intval() by just putting it inside the string like that... – Joachim Isaksson Sep 08 '12 at 16:29
I'am happy to help you user1611964 – Aghilas Yakoub Sep 08 '12 at 16:31
@user1611964 As this answer solves your problem why don't select it as the answer? That will help future users to find out the correct answer more gracefully and quickly :) – MD. Sahib Bin Mahboob Sep 08 '12 at 19:56
In which MySQL version is `intval` a valid function? – Jocelyn Sep 10 '12 at 18:56
3

If this answer worked, your server must be powered by magic. `...ob',.intval($c_id['courseid']),'$em...` really? – Leigh Sep 10 '12 at 18:56
@AghilasYakoub; remove the **.intval($c_id['courseid']).**; just use **{c_id['courseid']}** instead. It's simpler and has no error. – Kneel-Before-ZOD Sep 10 '12 at 19:19

Why does my INSERT statement fail without error?

5 Answers5