Issue inserting data into MySQL with PHP

Question

I am new to PHP. I've been working on a simple form that submits data into a MySQL database and have hit a snag. I'm just not sure where the error in the code is, any help would be appreciated.

<?php

$host      = 'localhost'; // hostname
$username  = 'root'; // MySQL Username
$password  = 'root'; // MySQL Password
$db_name   = 'idp'; // Database name
$tbl_name  = 'data'; // Table name

// Attempt MySQL Connection
mysql_connect("$host", "$username", "$password")or die("Cannot connect.");
// Attempt database connection
mysql_select_db("$db_name")or die("Cannot select DB.");

$name      = $_POST['name'];
$mbr_name   = $_POST['mbr_name'];
$mbr_tel   = $_POST['mbr_tel'];
$date      = $_POST['date'];

$sql     = "INSERT INTO $tbl_name(name, mbr_name, mbr_tel, date)VALUES('$name',       '$mbr_name', '$mbr_tel', '$date')";
$result  = mysql_query($sql);

if($result) {
echo "Entry Successful";
echo "<br>";
echo "<a href='form.php'>Return to Form</a>";
} else {
echo "<strong>Error</strong>";
}

mysql_close();
?>

This might not be the problem, but try putting actual values within the mysql_connect and mysql_select_db functions instead of variables: mysql_connect("$host", "$username", "$password")or die("Cannot connect."); mysql_select_db("$db_name")or die("Cannot select DB."); — Joel Murphy
– Joel Murphy, Commented Oct 14, 2013 at 17:42
you could do a $result = mysql_query($sql) or die(mysql_error()); but really, don't use the mysql_* functions anymore for new code, they're deprecated. — Wrikken
– Wrikken, Commented Oct 14, 2013 at 17:43
Ahh I forgot to include that. It returns 'Error' so it appears that it connects to the database fine, but won't input the data into the table. — sbdthru
– sbdthru, Commented Oct 14, 2013 at 17:44
And, if you must use mysql, at least use mysql_real_escape_string(), — Wrikken
– Wrikken, Commented Oct 14, 2013 at 17:45

Community · Accepted Answer · 2017-05-23 11:50:13Z

1

My guess is, one of the input parameters you are getting has unescaped ' or " character(s) in it. Thus, your query breaks due to the unintended quote characters. To verify if this is the case, introduce another print statement for the sql just after you have created your sql statement and run it in manually in your query browser and see if it works.

Further, the if($result) { block in your code prints out a success or failure message irrespective of why the failure occured, so the output information is not particularly useful either.

You should have a look at the [addslashes][1] method to escape those quote characters.

Your query will then become somthing like

$sql = "INSERT INTO $tbl_name(name, date)VALUES('".addslashes($name)."', '".$date."')";

Also, I would suggest having a look at pdo for your sql queries instead of mysql_* functions.

edited May 23, 2017 at 11:50

CommunityBot

11 silver badge

answered Oct 14, 2013 at 18:24

Anshul Goyal

78k42 gold badges161 silver badges196 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Anerty · Accepted Answer · 2013-10-14 17:49:27Z

0

Try you query like that :

$sql     = "INSERT INTO $tbl_name(name, mbr_name, mbr_tel, date)VALUES('".$name."',       '".$mbr_name."', '".$mbr_tel."', '".$date."')";

answered Oct 14, 2013 at 17:49

Anerty

1011 gold badge1 silver badge9 bronze badges

2 Comments

aynber Over a year ago

This results in the same query. If there's sql injection code, this would not stop it. It would not fix the OP's issue.

Anerty Over a year ago

this line of code is only to show (one of many ways) to escape variable inside a query string, protection against XSS attack, encoding issues, new lines, .... should be done before this step, and there is a lot of classes (Zend_DB_* classes) that could be implemented.

user2819330 · Accepted Answer · 2013-10-14 18:52:14Z

0

Remove the "" on your mysql_connect($host,$username,$password); mysql_select_db($db_name); Its a good habit

$sql = "INSERT INTO $tbl_name(name, mbr_name, mbr_tel, date)VALUES('{$name}','{$mbr_name}','{$mbr_tel}', '{$date}')";

Also check your data type for date. If the date you are posting is current , you can use sql function instead of the POST for date for example:

$sql = "INSERT INTO $tbl_name(name, mbr_name, mbr_tel, date)VALUES('{$name}','{$mbr_name}','{$mbr_tel}',NOW())";

answered Oct 14, 2013 at 18:52

user2819330

Comments

Anerty · Accepted Answer · 2013-10-15 11:39:20Z

0

your code is deprecated, please refer to Zend classes loader, with the Zend_db_* classes.

[http://framework.zend.com/manual/1.12/fr/zend.db.statement.html][1]

PS : Zend is a Framework, you could use any popular framework like Yii, CakePhp and others.

answered Oct 15, 2013 at 11:39

Anerty

1011 gold badge1 silver badge9 bronze badges

Collectives™ on Stack Overflow

Issue inserting data into MySQL with PHP

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related