CODEWITHSUNDEEP
phpmysqlmysqli
Andrew Fox
Andrew Fox

Reputation: 890

Having trouble inserting into mysql database using mysqli

I'm having trouble getting this to work. I've searched this site and found many other posts regarding this, but none seem to be working.

Here is my simple code:

if (isset($_POST['submit']))
{
$startDate = strtotime($_POST['from']);
$endDate = strtotime($_POST['to']);

for($i = $startDate; $i <= $endDate; $i = strtotime('+1 day', $i))
{
$date = date('Y-m-d',$i);
//echo $date . "<br>";
$sql = ("INSERT INTO `calendar` (`Cal_Date`) VALUES (`$date`)");
}
   if(!$result = $db->query($sql))
   {
   die('There was an error running the query [' . $db->error . ']');
   }

}

The form is just as simple and the dates are entered in "YYYY-MM-DD" format. What I'm trying to do is populate my database table with a range of dates. The only thing that happens is one row gets inserted and it is "0000-00-00" and I suspect this is because I've got that column set to Date, Not Null. When I echo the results, everything works perfectly, it's just getting it into the db doesn't seem to work. I've tried many other combinations of the INSERT line including:

$sql = $db->query("INSERT INTO `calendar` (`Cal_Date`) VALUES (`$date`)");

($db is from db_connect.php )

$sql = ("INSERT INTO `calendar` (`Cal_Date`) VALUES (`{$date}`)");

$sql = ("INSERT INTO calendar ('Cal_Date') VALUES ($date)");

$sql = ("INSERT INTO `calendar` (`Cal_Date`) VALUES (`$date`)");

...and I think even a couple of others. I do know that my db_connect.php is connecting to the database as I've got:

$sql = ("SELECT * FROM calendar");

further down the page and it's working fine. I've been going at this for far too long and I'm convinced I'm just missing something obvious. I would appreciate your feedback.

mySQL 5.5.24 PHP 5.3.13 Apache 2.2.22

Upvotes: 0

Views: 814

Answers (3)

ivoputzer
ivoputzer

Reputation: 6469

First off, there's no need to wrap you string in round brackets!

This should do the trick!

$sql = "INSERT INTO `calendar` (`Cal_Date`) VALUES ('$date')";

An even nicer yet harder to understand solution ( in terms of quotes ) : 

$sql = 'INSERT INTO `calendar` (`Cal_Date`) VALUES (\''.$date.'\')';

This one should work as well :

$sql = "INSERT INTO calendar (Cal_Date) VALUES ('$date')";

NOTE : The back-tick ( ` ) is used only for fieldnames and tablenames and needed only if your tablename or fieldname has spaces in it!

Use single or double quotes to delimit strings, dates, chars, varchars within SQL and nothing for booleans, nulls and numeric values!

Hope this helped!

Upvotes: 1

duellsy
duellsy

Reputation: 8577

Try it without the ` for your value, and flicking back to php... as in:

$sql = ("INSERT INTO calendar (`Cal_Date`) VALUES (".$date.")");

If that doesn't help, echo out the SQL and let us know exactly what the built SQL is, that will net you a faster fix

Upvotes: 2

eggyal
eggyal

Reputation: 125835

String literals should be quoted with the single-quote ' (or double-quote ", if ANSI_QUOTES is disabled) character, not backticks (which in MySQL are only for quoting SQL identifiers).

Upvotes: 1

Related Questions