[RESOLVED] Date from PHP Code to mySQL

cabcree

I've been looking for an answer for some issues all afternoon. I finally got to this point...and can't find the answer (I think I'm going cross-eyed looking through all these questions/answers). I want to add the current date when someone adds their information. Here is the code I'm using:

$today = date("Y-m-d");

if (mysqli_connect_errno()) {
  printf("Connect failed: %s/n", mysqli_connect_error());
  exit();
} else {
  $sql = "INSERT INTO faculty_mod1 (fname,lname,center,score1,date1)
	VALUES ('".$_POST["fname"]."',
'".$_POST["lname"]."',
'".$_POST["center"]."',
'".$_POST["score1"]."',
'".$today["date1"]."')";
  $res = mysqli_query($mysqli, $sql);

if ($res === TRUE) {
	echo "A record has been inserted.";
} else {
	printf("Could not insert record: %s\n", mysqli_error($mysqli));
}

mysqli_close($mysqli);
}

With this current set up I get this error: Could not insert record: Incorrect date value: '2' for column 'date1' at row 1 . Which is slightly differently from the "0" I was getting my doing the date as date("m-d-y");.

Thanks for any help...

laserlight

date() returns a string, so $today is a string. $today["date1"] is a character in the string at index "date1". Since string indices are numeric, "date1" probably was treated as 0, thus you get '2' as '2' is the character at $today[0].

cabcree

okay...that makes sense. I changed the DATE in the database to string and it worked. Thanks for your help!

Piranha

By default the date should be yy-mm-dd or yyyy-mm-dd, you can read more about date types here.

But if you want the current date you don't have to insert it. Instead you can use curdate in the query to get the date. Something like this:

$sql = "INSERT INTO faculty_mod1 (fname,lname,center,score1,date1)
    VALUES ('".$_POST["fname"]."',
'".$_POST["lname"]."',
'".$_POST["center"]."',
'".$_POST["score1"]."',
CURDATE())";

Note that you should make sure that queries is safe agains database injection. The best way is probably prepared queries since you use mysqli functions. Unfortunately I can't show you since I don't know how to use them myself.

laserlight

I changed the DATE in the database to string and it worked.

Actually, I think the more correct fix is to use $today instead of $today["date1"].

Piranha

I suggest that you don't change the data type in the database, if you don't have it as the date data type you will have problems with queries. As an example, how would you write that you want all dates between 2007-04-24 and 2007-04-06 (yyyy-mm-dd)?

cabcree

okay...that did EXACTLY what I wanted it to. Thanks so much!

cabcree

Piranha wrote:
I suggest that you don't change the data type in the database, if you don't have it as the date data type you will have problems with queries. As an example, how would you write that you want all dates between 2007-04-24 and 2007-04-06 (yyyy-mm-dd)?

So many answers at once...I changed it back to date...and used your suggestion above and it worked as I wanted it to. Thanks!

laserlight

Note that you should make sure that queries is safe agains database injection. The best way is probably prepared queries since you use mysqli functions. Unfortunately I can't show you since I don't know how to use them myself.

Based on what the PHP Manual says on mysqli prepared statements, I think it would be:

$sql = "INSERT INTO faculty_mod1 (fname, lname, center, score1, date1)
        VALUES (?, ?, ?, ?, ?)";
if ($stmt = mysqli_prepare($mysqli, $sql)) {
    mysqli_stmt_bind_param($stmt, 'sssds',
                           $_POST["fname"],
                           $_POST["lname"],
                           $_POST["center"],
                           $_POST["score1"],
                           $today["date1"]);
    mysqli_stmt_execute($stmt);
}

What I am not clear about is what is the type to use for a datetype.

cabcree

laserlight wrote:

Based on what the PHP Manual says on mysqli prepared statements, I think it would be:

$sql = "INSERT INTO faculty_mod1 (fname, lname, center, score1, date1)
        VALUES (?, ?, ?, ?, ?)";
if ($stmt = mysqli_prepare($mysqli, $sql)) {
    mysqli_stmt_bind_param($stmt, 'sssds',
                           $_POST["fname"],
                           $_POST["lname"],
                           $_POST["center"],
                           $_POST["score1"],
                           $today["date1"]);
    mysqli_stmt_execute($stmt);
}

What I am not clear about is what is the type to use for a datetype.

It definitely doesn't like CURDATE() if I do this...hmmm...

laserlight

It definitely doesn't like CURDATE() if I do this...hmmm...

hmm... maybe try this instead:

$sql = "INSERT INTO faculty_mod1 (fname, lname, center, score1, date1)
        VALUES (?, ?, ?, ?, CURDATE())";
if ($stmt = mysqli_prepare($mysqli, $sql)) {
    mysqli_stmt_bind_param($stmt, 'sssd',
                           $_POST["fname"],
                           $_POST["lname"],
                           $_POST["center"],
                           $_POST["score1"]);
    mysqli_stmt_execute($stmt);
}

cabcree

laserlight wrote:

hmm... maybe try this instead:

$sql = "INSERT INTO faculty_mod1 (fname, lname, center, score1, date1)
        VALUES (?, ?, ?, ?, CURDATE())";
if ($stmt = mysqli_prepare($mysqli, $sql)) {
    mysqli_stmt_bind_param($stmt, 'sssd',
                           $_POST["fname"],
                           $_POST["lname"],
                           $_POST["center"],
                           $_POST["score1"]);
    mysqli_stmt_execute($stmt);
}

Not liking that either...tried a couple different things...I'll keep working on it. 🙂 All suggestions welcome though!