By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,460 Members | 1,193 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,460 IT Pros & Developers. It's quick & easy.

problem in saving date in database and displaying

P: 11
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:
Expand|Select|Wrap|Line Numbers
  1. <form action="publication.php"  method="post" enctype="multipart/form-data">
  2. <table cellpadding=0 class="text" >
  3. <tr><td>Publisher:</td>
  4. <td><input type="text"  name="publisher"></td></tr>
  5. <tr><td>Date</td>
  6. <td><?php 
  7. /******* building jump menu for the month********/
  8. $today=time();
  9. $monthName = array(1=> "January", "February", "March","April",  "May", "June",  "July", "August", "September", "October","November", "December");
  10. echo "<select name='dateMO'>\n";
  11. for ($n=1;$n<=12;$n++)
  12. {echo "<option value=$n\n";
  13. echo "> $monthName[$n]\n";}
  14. echo "</select>";
  15. //********Building jump menu for year******//
  16. $startYr = date("Y", $today); //get the year from $today
  17. echo "<select name='dateYr'>\n";
  18. for ($n=$startYr-10;$n<=$startYr+1;$n++)
  19. {echo  "<option value=$n";
  20. echo "> $n\n";}
  21. echo "</select>\n";
  22. $date = $_POST['dateYr']."-";
  23. $date .= $_POST['dateMO']."-";
  24. $date .= NULL; //to put 00 at the end of date to be saved in db correctly ?> </td></tr>
  25. <tr><td><input type="submit" id="add" value="add" name="submit"></td> </table></form> 
Expand|Select|Wrap|Line Numbers
  1. <?php
  2. if (isset($_POST['publisher'])) {
  3. $query=mysql_query("INSERT INTO publication(publisher,date)
  4. VALUES ('{$_POST['publisher']}','$date')") or die(mysql_error());
  5. //displaying :
  6. $respublic= mysql_query("SELECT * FROM publication");
  7. while($rowpublic=mysql_fetch_assoc($respublic))
  8. {echo ($rowpublic["publisher"]). ',' . ($date); 
  9. echo '<p>&nbsp;</p>';}
Expand|Select|Wrap|Line Numbers
  1.  CREATE TABLE publication(
  2.   id int unsigned NOT NULL AUTO_INCREMENT,
  3.    publisher varchar(100) NOT NULL,
  4.    date DATE NOT NULL,
  5.     PRIMARY KEY  (id)
  6. );
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
Apr 10 '10 #1
Share this Question
Share on Google+
7 Replies

Expert 5K+
P: 5,058

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.

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.
Apr 10 '10 #2

P: 11
yes,the first and second snippet are in one file(publication.php)
Apr 10 '10 #3

Expert 5K+
P: 5,058
Ok, try replacing the second snippet with this:
Expand|Select|Wrap|Line Numbers
  1. <?php
  2. if (isset($_POST['publisher'])) 
  3. {
  4.     // Fetch the publisher. You should ALWAYS do this
  5.     // when handling user input. Never use the $_POST
  6.     // data directly in a query.
  7.     $publisher = mysql_real_escape_string($_POST['publisher']);
  9.     // Insert the publisher
  10.     $sql = "INSERT INTO publication(publisher,date)
  11.             VALUES ('{$publisher}','$date')";
  12.     $query=mysql_query($sql) or die(mysql_error());
  14.     # var_dump($sql); exit;
  16.     // Display all
  17.     $respublic = mysql_query("SELECT * FROM publication");
  18.     while($rowpublic=mysql_fetch_assoc($respublic))
  19.     {
  20.         // Note that I replaced $date with $row['date'] here.
  21.         // I assume you wanted to print the date you retrieved
  22.         // from the database, not the one you constructed earlier.
  23.         echo $rowpublic["publisher"]. ',' . $row['date'];
  24.         echo '<p>&nbsp;</p>';
  25.     }
  26. ?>
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.
Apr 10 '10 #4

P: 11
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
Apr 11 '10 #5

Expert 5K+
P: 5,058
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).
Apr 11 '10 #6

P: 11
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 is now ok. only one small problem is left that because i want to ignore day i will do as follows:

Expand|Select|Wrap|Line Numbers
  1. $respublic= mysql_query("SELECT * FROM publication");
  2. while($rowpublic=mysql_fetch_assoc($respublic))
  3. {
  4.      $timestamp=$rowpublic["date"];
  5.      //print ($timestamp);--> it prints the correct time like 2010-04-00 
  6.      $newdate=date("F Y",$timestamp);// i expect it to print April 2010
  7.     //print ($newdate); for any date it prints January 1970  
  8.     echo ($rowpublic["publisher"]). ',' . ($newdate); 
  9.     echo '<p>&nbsp;</p>';
  10. }
Apr 11 '10 #7

P: 11
I replaced the inner code with
Expand|Select|Wrap|Line Numbers
  1. $timestamp=$rowpublic["date"];
  2. $t=strtotime($timestamp);
  3. $newdate=date('F Y',$t);
  4. echo ($rowpublic["publisher"]). ',' . ($t);  
and it is correct now
Apr 11 '10 #8

Post your reply

Sign in to post your reply or Sign up for a free account.