Having trouble inserting into mysql database using mysqli

Question

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

see here http://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php — John Woo, Nov 18 '12 at 06:22
Why not just store in your database the start and end dates, rather than separate records for every date in between? Are you sure that you want this data structure? — eggyal, Nov 18 '12 at 07:31
@eggyal yes, I do want it that way. I'm making an employee work schedule and I need all of the dates in there. — Andrew Fox, Nov 18 '12 at 07:36

duellsy · Answer 1 · 2012-11-19T23:31:32.390

2

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

edited Nov 19 '12 at 23:31

answered Nov 18 '12 at 06:37

duellsy

8,497
2
36
60

I entered in exactly as you have, but no luck. I edited my post to show this being tried as well as my server, sql and php versions. Thank you for the reply – Andrew Fox Nov 18 '12 at 07:34
1

Can you echo $sql in each of those cases so we can see what the actual SQL is, might be easier to determine what's going on that way – duellsy Nov 18 '12 at 07:37
1

`INSERT INTO calendar (Cal_Date) VALUES ('2012-10-01')` – Andrew Fox Nov 18 '12 at 07:42
@duellsy your snippet wont work simply because fields shouldn't be wrapped with quotes.. while dates have to be processed as strings ( otherwise it would be a subtraction )! – ivoputzer Nov 19 '12 at 17:43
@Ivo thanks, updated. I actually copy/pasted the original SQL and just edited the values section, missed that it was using quotes in the columns section. – duellsy Nov 19 '12 at 23:32
@duellsy check the answer below ;) i listed all the alternatives you have to make your query work properly! – ivoputzer Nov 20 '12 at 06:48
@duellsy got confused on that one... sorry im new to stackoverflow! ;) hahaha – ivoputzer Nov 20 '12 at 07:52

score 1 · Accepted Answer · answered Nov 18 '12 at 07:23

1

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).

answered Nov 18 '12 at 07:23

eggyal

122,705
18
212
237

I did actually know that, although I see why you would think I didn't. I have been trying literally everything I can find on this, and adding those back ticks was something mentioned in another post. Thank you for the reply. – Andrew Fox Nov 18 '12 at 07:32
@AndrewFox: So, to be clear, using string quotations has solved your problem? – eggyal Nov 18 '12 at 07:34
Yes, that is correct. I tried `$sql = ("INSERT INTO calendar (Cal_Date) VALUES ('$date')");` and it didn't work either. – Andrew Fox Nov 18 '12 at 07:38
sorry, I pasted the wrong part and couldn't edit it fast enough :) – Andrew Fox Nov 18 '12 at 07:40
1

Better yet, use [prepared statements](http://stackoverflow.com/a/60496/623041), into which you pass your variables as parameters. – eggyal Nov 18 '12 at 07:40
Embarrassing. `$sql = $db->query("INSERT INTO calendar (Cal_Date) VALUES ('$date')");` worked. I must have added the back ticks or single-quotes somewhere along the way when trying to fix a different problem...a problem that I don't know what it was because I've tried so many different things. Ugh. Anyways, thank you :) – Andrew Fox Nov 18 '12 at 07:58

ivoputzer · Answer 3 · 2012-11-20T06:48:46.380

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!

Having trouble inserting into mysql database using mysqli

3 Answers3