problem in saving date in database and displaying

[ssharifi]

I want to use 2 jumpmenu to let users select publishing date of their document(which only year is necessary to be filled ) and then I will save it in a correct format into db and display what the user filled such as: April 2010,2010,...
here is my code but it doesn't get the date and in my database it is saved 0000-00-00:
FORM:

<form action="publication.php"  method="post" enctype="multipart/form-data">
<table cellpadding=0 class="text" >
<tr><td>Publisher:</td>
<td><input type="text"  name="publisher"></td></tr>
<tr><td>Date</td>
<td><?php 
/******* building jump menu for the month********/
$today=time();
$monthName = array(1=> "January", "February", "March","April",  "May", "June",  "July", "August", "September", "October","November", "December");
echo "<select name='dateMO'>\n";
for ($n=1;$n<=12;$n++)
{echo "<option value=$n\n";
echo "> $monthName[$n]\n";}
echo "</select>";
//********Building jump menu for year******//
$startYr = date("Y", $today); //get the year from $today
echo "<select name='dateYr'>\n";
for ($n=$startYr-10;$n<=$startYr+1;$n++)
{echo  "<option value=$n";
echo "> $n\n";}
echo "</select>\n";
$date = $_POST['dateYr']."-";
$date .= $_POST['dateMO']."-";
$date .= NULL; //to put 00 at the end of date to be saved in db correctly ?> </td></tr>
<tr><td><input type="submit" id="add" value="add" name="submit"></td> </table></form> 

THE RELATED PHP CODE:

<?php
if (isset($_POST['publisher'])) {
$query=mysql_query("INSERT INTO publication(publisher,date)
VALUES ('{$_POST['publisher']}','$date')") or die(mysql_error());
//displaying :
$respublic= mysql_query("SELECT * FROM publication");
while($rowpublic=mysql_fetch_assoc($respublic))
{echo ($rowpublic["publisher"]). ',' . ($date); 
echo '<p>&nbsp;</p>';}

TABLE:

 CREATE TABLE publication(
  id int unsigned NOT NULL AUTO_INCREMENT,
   publisher varchar(100) NOT NULL,
   date DATE NOT NULL,
    PRIMARY KEY  (id)
);

I also tested it with 3 jumpmenu(instead of NULL) but it doesn't work either. what was my mistake?only date is not saved and displayed

[Atli]

Hey.

Is the form (your first snippet) and the PHP code (the second snippet) in the same file?

I ask because in your second snippet, you use a $date variable for the INSERT query, but you do not define it in that snippet, only in the first snippet. - To use the variable in the second snippet, it needs to be defined there.

P.S.
Your code is wide open to SQL Injection. In short, you should always use mysql_real_escape_string on all strings and dates before adding them to a SQL query.

[ssharifi]

yes,the first and second snippet are in one file(publication.php)

[Atli]

Ok, try replacing the second snippet with this:

<?php
if (isset($_POST['publisher'])) 
{
    // Fetch the publisher. You should ALWAYS do this
    // when handling user input. Never use the $_POST
    // data directly in a query.
    $publisher = mysql_real_escape_string($_POST['publisher']);
 
    // Insert the publisher
    $sql = "INSERT INTO publication(publisher,date)
            VALUES ('{$publisher}','$date')";
    $query=mysql_query($sql) or die(mysql_error());
 
    # var_dump($sql); exit;
 
    // Display all
    $respublic = mysql_query("SELECT * FROM publication");
    while($rowpublic=mysql_fetch_assoc($respublic))
    {
        // Note that I replaced $date with $row['date'] here.
        // I assume you wanted to print the date you retrieved
        // from the database, not the one you constructed earlier.
        echo $rowpublic["publisher"]. ',' . $row['date'];
        echo '<p>&nbsp;</p>';
    }
} 
?>

It's basically the same code you have, just formatted properly and with a few minor fixes. (They are explained in the comments in the code.)

If the code is still not working after you make these modifications, try uncommenting the code on line #14. It will print the query so we can see exactly what you are telling the SQL server to do. If the data is being incorrectly added, this is where the problem is.

Also note the formatting of the code; how I indent it. Makes it easier to read, don't you think? It's extremely important to format the code properly so you can easily read through it later; In case you ever need to hunt for a bug or modify the functionality.

[ssharifi]

I changed the code but i it still displays 0000-00-00 .i also checked var dump_sql and understood that NULL is entering into db

[Atli]

What did var_dump print, exactly?

From what you've told me, the likely reason why this is failing is because the $date variable isn't defined. Try moving the part of your first code snippet where it is created into the second snippet (or the one I posted).

[ssharifi]

hi,thanks for the help,It would print NULL, I also put print for $date but it would print the correct time. I moved the code before sql query and also replaced NULL with 00 for inserting day.it is now ok. only one small problem is left that because i want to ignore day i will do as follows:

$respublic= mysql_query("SELECT * FROM publication");
while($rowpublic=mysql_fetch_assoc($respublic))
{
     $timestamp=$rowpublic["date"];
     //print ($timestamp);--> it prints the correct time like 2010-04-00 
     $newdate=date("F Y",$timestamp);// i expect it to print April 2010
    //print ($newdate); for any date it prints January 1970  
    echo ($rowpublic["publisher"]). ',' . ($newdate); 
    echo '<p>&nbsp;</p>';
}

[ssharifi]

I replaced the inner code with

$timestamp=$rowpublic["date"];
$t=strtotime($timestamp);
$newdate=date('F Y',$t);
echo ($rowpublic["publisher"]). ',' . ($t);  
 

and it is correct now