Inserting data into mysql table using PHP

Question

$dbc=mysql_connect('127.0.0.1', 'root', '1234','aliendatabase')
or die('Failed!');

$query = "INSERT INTO alien_abduction(first_name, last_name, when_it_happened, how_long, " .
"how_many, alien_description, what_they_did, fang_spotted, other, email) " .
"VALUES ('$first_name', '$last_name', '$when_it_happened', 
'$how_long', '$how_many', " .
"'$alien_description', '$what_they_did', '$fang_spotted', '$other', 
'$email')"; 

$result=mysql_query($query)
or die("Failed to upload!!!!");

mysql_close($dbc);

This code is unable to execute $result line (so outputs Failed to upload!!!!) but it is able to establish connection. I have cross-checked the table column name and variables and it seems fine.

MySQL version 5.7

**WARNING**: If you're just learning PHP, please, do not use the [`mysql_query`](http://php.net/manual/en/function.mysql-query.php) interface. It’s so awful and dangerous that it was removed in PHP 7. A replacement like [PDO is not hard to learn](http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/) and a guide like [PHP The Right Way](http://www.phptherightway.com/) explains best practices. Your user data is **not** [properly escaped](http://bobby-tables.com/php.html) and there are [SQL injection bugs](http://bobby-tables.com/) and can be exploited. — tadman, Dec 21 '17 at 19:22
[If only there were a way to get more info about the nature of the error.](http://php.net/mysql_error) — Bill Karwin, Dec 21 '17 at 19:25
Okay, I will learn it but can I know why this code doesn't run? I was referring to a book called Head First PHP & MySQL. — Aman Sharma, Dec 21 '17 at 19:30
A book? Wait, what is a BOOK?!?! ... :D Switch all your `mysql_*` to `mysqli_*` at the very least. — IncredibleHat, Dec 21 '17 at 19:39
@IncredibleHat It doesn't work. I have tried it. I didn't even connect to the server. — Aman Sharma, Dec 21 '17 at 19:41

score 0 · Accepted Answer · answered Dec 21 '17 at 19:34

0

Please check the Datatypes of the columns and do the following to find the error:

$result=mysql_query($query) or die(mysql_error($dbc));

answered Dec 21 '17 at 19:34

somanalytics

65
3

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' , , , , , , no, , )' at line 1 Which line 1 is it referring to? – Aman Sharma Dec 21 '17 at 19:42
Aman, can you please do an echo of the SQL and paste it here – somanalytics Dec 21 '17 at 19:50
echo 'Thanks for submitting the form.
'; echo 'You were abducted ' . $when_it_happened; echo ' and were gone for ' . $how_long . '
'; echo 'Number of aliens: '.$how_many.'
'; echo 'Describe them: ' . $alien_description . '
'; echo 'The aliens did this: '.$what_they_did."
"; echo 'Was Fang there? ' . $fang_spotted . '
'; (Error is in this line as I figured out.) echo 'Other Comments: '.$other.'
'; echo 'Your email address is ' . $email; – Aman Sharma Dec 21 '17 at 19:55
I hope it is readable or I can upload an image. – Aman Sharma Dec 21 '17 at 19:56
1

try: $query = "INSERT INTO alien_abduction(first_name, last_name, when_it_happened, how_long, " . "how_many, alien_description, what_they_did, fang_spotted, other, email) " . "VALUES ('".stripslashes($first_name)."', '".stripslashes($last_name)."', '".stripslashes($when_it_happened)."', '".stripslashes($how_long)."', '".stripslashes($how_many)."', '".stripslashes($alien_description)."', '".stripslashes($what_they_did)."', '".stripslashes($fang_spotted)."', '".stripslashes($other)."', '".stripslashes($email)."')"; – somanalytics Dec 21 '17 at 19:57
Thanks a lot. It worked. Just one two more questions. Q.1) Don't we need to add a semi-colon after $email") as all statements end with semi-colon in MySQL. Q.2) mysql_select_db(); is necessary even though I have passed the fourth argument in mysql_connect(); – Aman Sharma Dec 21 '17 at 20:02

score -2 · Answer 2 · edited Dec 21 '17 at 20:18

-2

The problem is with your quoting way. As single quote won't be used to hold variables. So in single quote, they are simply strings, not the variables.

If you want that $variable replaced with value then rewrite the whole query in single quote like below

$query = 'INSERT INTO alien_abduction(first_name, last_name, when_it_happened, how_long, how_many, alien_description, what_they_did, fang_spotted, other, email) '.
 ' VALUES (' ".$first_name." ', ' ".$last_name . " ', ' ". $when_it_happened . " ', ' " .$how_long . " ', ' " . $how_many . " ', " . " " . $alien_description . " ', ' " . $what_they_did . " ', ' " . $fang_spotted . " ', ' " . $other . " ', ' " . $email . " ')' ;

I hope that will solve the issue.

edited Dec 21 '17 at 20:18

SamHecquet

1,818
4
19
26

answered Dec 21 '17 at 19:48

Er. Amit Joshi

611
5
21

1

We can use double quotes everywhere. It isn't a problem as it recognizes the variable too. – Aman Sharma Dec 21 '17 at 19:57
Problem is single quote not the double quotes – Er. Amit Joshi Dec 21 '17 at 20:20
I don't understand why Er. Amit Joshi and I got -1 for actually solving your problem. – Benjamin Racette Dec 21 '17 at 21:15
The string literal saved into `$query` uses double quotes, so the variables are parsed. The fact that these variables are *also* surrounded by single quotes inside the double quotes is irrelevant because it is SQL-level quoting, not PHP level. On top of that, even if unparsed variables was the issue, it would simply insert the unparsed variable names into the database without raising an error. – GSerg Dec 21 '17 at 23:23
Kindly look into this post https://stackoverflow.com/questions/3446216/what-is-the-difference-between-single-quoted-and-double-quoted-strings-in-php thanks bro for @Benjamin Racette we are not wrong at all. :) – Er. Amit Joshi Dec 22 '17 at 05:27

Inserting data into mysql table using PHP

2 Answers2