24 Replies Latest reply: Mar 22, 2012 8:18 AM by dream_sugar RSS

    PHP/mySQL code help please

    noiseboy1970

      Hi all,

       

      Thanks to David Powers fab tutorial I am now well on my way to competeing my first dynamique website using PHP/mySQL, (thanks David ), however I am stuck on one particular area....can anyone please help?

       

      I know that the mySQL database uses the format YYYY/MM/DD for dates, and that is fine. I've sorted out how to display in a more regular format (eg. 1st September 2011) for my application, but I am stuck on how to convert a more regular formatted date entered by the site user into the mySQL format.

       

      Ideally I would like for the users entry or say either 1/9/11 or 1-9-11 (or something along those lines, to be recognised and displayed on the entry form, but to have a backend function that converts the data to mySQL format. I seem to be reading plenty of forum posts which say this can be done, but unfortunately I can't quite seem to fathom how, or where the code should sit. Would someone mind taking a look at my code page to actually identify what I need to insert, and equally as importantly...where!!!

       

      Along these lines...is it possible to have a mini-calendar date picker that could be embedded next to the date entry field to ensure that a valid date is entered and in the correct format? Where could this be sourced?

       

      Thanks in advance of some much needed help

       

      Mark

        • 1. Re: PHP/mySQL code help please
          MurraySummers CommunityMVP

          Check the date picker widget that is available via DW's Widget browser....

          • 2. Re: PHP/mySQL code help please
            noiseboy1970 Community Member

            Thanks Murray, will take a look at that.
            Would still appreciate guidance on the wider problem if anyone can assist. Thanks

            • 3. Re: PHP/mySQL code help please
              MurraySummers CommunityMVP

              This general method is terriffic at converting various representations of dates to UNIX timestamps which can then be used with the PHP date() command -

               

              echo date("Y-m-d", strtotime('1/9/11'))

               

              You feed strtotime() a string representation of a date, and then use date() with the desired format to convert it back to a more standard representation of that date.  Try it....

              • 4. Re: PHP/mySQL code help please
                noiseboy1970 Community Member

                Thanks again Murray, but still a little confused as to where this would sit in the code, and it's precise format!!!
                Would you be in a position to advise further if I were to post the code?

                 

                Thanks...Mark

                • 5. Re: PHP/mySQL code help please
                  MurraySummers CommunityMVP

                  Actually I believe MySQL expects dates to be YYYY-MM-DD format.  And you would use the code I suggested just before you are inserting the data into the MySQL table.  You could embed it in the query string, for example -

                   

                  $query = "INSERT INTO table (field1, field2, ...) VALUES ('value1'," . $date("Y-m-d", strtotime($your_date)) . "', value3, ...)"

                   

                  Does that help?

                  • 6. Re: PHP/mySQL code help please
                    noiseboy1970 Community Member

                    This is the code that Dreamweaver has created at the foot of the form, and I suspect therefore the piece of code that takes care of inserting the data into the mySQL database.

                    I am guessing that the line I have underlined is where you are refering to, but clearly the terminology is a little different in places.

                    I know i'm probably being a complete nuisance (and possibly quite thick!!), but can you assist further??

                     

                    Really appreciate your help....Mark

                     

                     

                    $editFormAction = $_SERVER['PHP_SELF'];

                    if (isset($_SERVER['QUERY_STRING'])) {

                      $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);

                    }

                     

                    if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {

                      $insertSQL = sprintf("INSERT INTO gig_guide (`date`, town, venue, private_or_public, start_time) VALUES (%s, %s, %s, %s, %s)",

                                           GetSQLValueString($_POST['date'], "date"),

                                           GetSQLValueString($_POST['town'], "text"),

                                           GetSQLValueString($_POST['venue'], "text"),

                                           GetSQLValueString($_POST['type'], "text"),

                                           GetSQLValueString($_POST['start_time'], "text"));

                     

                      mysql_select_db($database_online, $online);

                      $Result1 = mysql_query($insertSQL, $online) or die(mysql_error());

                     

                      $insertGoTo = "index.php";

                      if (isset($_SERVER['QUERY_STRING'])) {

                        $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";

                        $insertGoTo .= $_SERVER['QUERY_STRING'];

                      }

                      header(sprintf("Location: %s", $insertGoTo));

                    }

                    • 7. Re: PHP/mySQL code help please
                      MurraySummers CommunityMVP

                      Change this -

                       

                      GetSQLValueString($_POST['date'], "date"),

                       

                      to this -

                       

                      GetSQLValueString(date('Y-m-d'),strtotime($_POST['date']), "date"),

                       

                      See if that works....

                      • 8. Re: PHP/mySQL code help please
                        noiseboy1970 Community Member

                        No, that has not worked, and out of curiosity I also tried it as d-m-Y as this is the format that I would usually enter a date into a form.

                         

                        Any other suggestions?

                        • 9. Re: PHP/mySQL code help please
                          MurraySummers CommunityMVP

                          No, that has not worked

                           

                          What happens when you try it?  How does it not work?

                           

                          What is the value you placed in $_POST['date']?

                           

                          Show me the modification to the code that you have made, please.

                          • 10. Re: PHP/mySQL code help please
                            noiseboy1970 Community Member

                            Using the revised code as follows I tried to enter the date in the format 23-12-2011 and then submitted the form with all other fields also completed. Using the SQL standard date format previously this has worked fine, and returns the details on the required web-page. With this new code the details did not return, and on checking the database the 'date' column has a value of 0000-00-00.

                             

                            As mentioned I tried a slight re-jig of the code to d-m-Y as this was the order I was entering the values but again with no joy.   Mark

                             

                             

                            $editFormAction = $_SERVER['PHP_SELF'];

                            if (isset($_SERVER['QUERY_STRING'])) {

                              $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);

                            }

                             

                            if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {

                              $insertSQL = sprintf("INSERT INTO gig_guide (date, town, venue, private_or_public, start_time) VALUES (%s, %s, %s, %s, %s)",

                                                   GetSQLValueString(date('Y-m-d'),strtotime($_POST['date']), "date"),

                                                   GetSQLValueString($_POST['town'], "text"),

                                                   GetSQLValueString($_POST['venue'], "text"),

                                                   GetSQLValueString($_POST['type'], "text"),

                                                   GetSQLValueString($_POST['start_time'], "text"));

                             

                              mysql_select_db($database_online, $online);

                              $Result1 = mysql_query($insertSQL, $online) or die(mysql_error());

                             

                              $insertGoTo = "index.php";

                              if (isset($_SERVER['QUERY_STRING'])) {

                                $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";

                                $insertGoTo .= $_SERVER['QUERY_STRING'];

                              }

                              header(sprintf("Location: %s", $insertGoTo));

                            }

                            • 11. Re: PHP/mySQL code help please
                              MurraySummers CommunityMVP

                              GetSQLValueString(date('Y-m-d'),strtotime($_POST['date']), "date"),

                               

                              Make that line look like this -

                               

                              GetSQLValueString(date('Y-m-d',strtotime($_POST['date'])), "date"),

                               

                              This was my mistake - sorry.  The MySQL DATE datatype should be in the format of YYYY-MM-DD.  If that's the datatype of this 'date' field, then this code should work.

                              • 12. Re: PHP/mySQL code help please
                                noiseboy1970 Community Member

                                Murray, thank you sooooo much...you have just beome my new personal hero!!! lol That works perfectly.

                                 

                                Now all I have to do is work out how to display the date in that same format (as entered) on the update entry screen. If i can't figure it out...trust me, i'll be straight back to you!!!

                                 

                                Thanks again

                                 

                                Mark

                                • 13. Re: PHP/mySQL code help please
                                  noiseboy1970 Community Member

                                  "Now all I have to do is work out how to display the date in that same format (as entered) on the update entry screen. If i can't figure it out...trust me, i'll be straight back to you!!!"

                                   

                                  Unless of course you are feeling extremely helpful and would like to suggest a method staight off?????

                                   

                                  Mark

                                  • 14. Re: PHP/mySQL code help please
                                    MurraySummers CommunityMVP

                                    date("m/d/Y", strtotime($row_rsWhatever['date'])

                                     

                                    assuming "m/d/Y" is the format you want to use.

                                     

                                    Take advantage of http://www.php.net/manual/en for the arcana of this and other PHP commands.

                                    • 15. Re: PHP/mySQL code help please
                                      noiseboy1970 Community Member

                                      This is becoming really frustrating for me (so god knows how I am to you!!!!), but I usually pick things up so fast and yet this whole PHP/SQL thing is eluding me!!

                                       

                                      I can see (I think) how the code would work, but am struggling to see where it fits into place with my own Dreamweaver 'Update Record' code, as follows. I have spent a couple of days trawling through PHP.net and various other sites and forums before bringing my stupidity to the public domain, but I just can't seem to crack it.

                                       

                                      This one last time then i'll leave you in peace??????

                                       

                                      <?php

                                      if (!function_exists("GetSQLValueString")) {

                                      function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")

                                      {

                                        if (PHP_VERSION < 6) {

                                          $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

                                        }

                                       

                                        $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

                                       

                                        switch ($theType) {

                                          case "text":

                                            $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

                                            break;   

                                          case "long":

                                          case "int":

                                            $theValue = ($theValue != "") ? intval($theValue) : "NULL";

                                            break;

                                          case "double":

                                            $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";

                                            break;

                                          case "date":

                                            $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

                                            break;

                                          case "defined":

                                            $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;

                                            break;

                                        }

                                        return $theValue;

                                      }

                                      }

                                       

                                      $editFormAction = $_SERVER['PHP_SELF'];

                                      if (isset($_SERVER['QUERY_STRING'])) {

                                        $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);

                                      }

                                       

                                      if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "form1")) {

                                          $updateSQL = sprintf("UPDATE gig_guide SET `date`=%s, town=%s, venue=%s, private_or_public=%s, start_time=%s WHERE `index`=%s",

                                                             GetSQLValueString($_POST['date'], "date"),

                                                             GetSQLValueString($_POST['town'], "text"),

                                                             GetSQLValueString($_POST['venue'], "text"),

                                                             GetSQLValueString($_POST['type'], "text"),

                                                             GetSQLValueString($_POST['start_time'], "text"),

                                                             GetSQLValueString($_POST['index'], "int"));

                                       

                                        mysql_select_db($database_online, $online);

                                        $Result1 = mysql_query($updateSQL, $online) or die(mysql_error());

                                       

                                        $updateGoTo = "index.php";

                                        if (isset($_SERVER['QUERY_STRING'])) {

                                          $updateGoTo .= (strpos($updateGoTo, '?')) ? "&" : "?";

                                          $updateGoTo .= $_SERVER['QUERY_STRING'];

                                        }

                                        header(sprintf("Location: %s", $updateGoTo));

                                      }

                                       

                                      $colname_updatePost = "-1";

                                      if (isset($_GET['index'])) {

                                        $colname_updatePost = $_GET['index'];

                                      }

                                      • 16. Re: PHP/mySQL code help please
                                        MurraySummers CommunityMVP

                                        So, I don't know what your question is.  I thought my previous suggestion worked for you.  What is it you want to do now, and where do you want to do it?

                                        • 17. Re: PHP/mySQL code help please
                                          noiseboy1970 Community Member

                                          Sorry Murray..

                                           

                                          The code that you originally provided, which worked a treat, allows the user to enter the date of an event into a form in the format d-m-y and the code then converts it to SQL format of yyyy-mm-dd. That bit is great.

                                           

                                          I ran the website, entered some dummy data, and on the events listing page it correctly displayed all the gigs in date order as required. I had previously worked out how to show the date in the format 25th January, 2011 as an example.

                                           

                                          However, the site then has an option to UPDATE a particular event record, and this pulls the data from that particular event into a form very much identical to the first one where your code came into play, but of course it shows the date in the format yyyy-mm-dd. I had not previously thought about this as a potential problem.

                                           

                                          For the sake of continuity it would be good if the date displayed and being edited on the update page could be in the same format as when the event is first entered onto the database...i.e. d-m-y, and it is this bit that I am now struggling with

                                           

                                          Does that make sense?

                                           

                                          Regards

                                           

                                          Mark

                                          • 18. Re: PHP/mySQL code help please
                                            MurraySummers CommunityMVP

                                            Does that make sense?

                                             

                                            Not exactly.  How do I know what the format *is* when the data is first entered into the database?  How are you controlling that format?

                                            • 19. Re: PHP/mySQL code help please
                                              noiseboy1970 Community Member

                                              As originally entered in the form that you first took a look at for me (i.e. d-m-y). I have tried this in both 1/6/2011 and 01-06-2011 and both get converted fine by the code you gave me earlier and are stored in mySQL without problems (as 2011-06-01).

                                               

                                              This can then be seen correctly on the front end website in the format I gave it, 1st June 2011.

                                               

                                              The problem is when the UPDATE RECORD form is used (code provided in previous message), when the date gets displayed in the format 2011-06-01 instead of as 01/06/2011 or similar European format

                                               

                                              M

                                              • 20. Re: PHP/mySQL code help please
                                                MurraySummers CommunityMVP

                                                So show me the code for how you populate the update form field, not for how you are updating the database.  That's where the conversion would be done.  NOW if you also want to change the format of the data in the database when this record is updated, then you would make exactly the same change I recommended previously.

                                                • 21. Re: PHP/mySQL code help please
                                                  noiseboy1970 Community Member

                                                  Would have to guess that this is where the code tells the form where to get its initial data?

                                                   

                                                  <div class="cms_frame_edit">

                                                      <form action="<?php echo $editFormAction; ?>" id="form1" name="form1" method="POST">

                                                        <table width="300" border="0">

                                                          <tr>

                                                            <td width="98">Date</td>

                                                            <td width="192"><label for="date"></label>

                                                            <input name="date" type="text" id="date" value="<?php echo $row_updatePost['date']; ?>" />

                                                          </tr>

                                                          <tr>

                                                            <td>Town</td>

                                                            <td><label for="town"></label>

                                                            <input name="town" type="text" id="town" value="<?php echo $row_updatePost['town']; ?>" /></td>

                                                          </tr>

                                                          <tr>

                                                            <td>Venue</td>

                                                            <td><label for="venue"></label>

                                                            <input name="venue" type="text" id="venue" value="<?php echo $row_updatePost['venue']; ?>" /></td>

                                                          </tr>

                                                          <tr>

                                                            <td>Type</td>

                                                            <td><label for="type"></label>

                                                            <input name="type" type="text" id="type" value="<?php echo $row_updatePost['private_or_public']; ?>" /></td>

                                                          </tr>

                                                          <tr>

                                                            <td>Start Time</td>

                                                            <td><label for="start_time"></label>

                                                            <input name="start_time" type="text" id="start_time" value="<?php echo $row_updatePost['start_time']; ?>" /></td>

                                                          </tr>

                                                        </table>

                                                        <p>

                                                          <input type="submit" name="update" id="update" value="Submit" />

                                                          <input name="index" type="hidden" id="index" value="<?php echo $row_updatePost['index']; ?>" />

                                                        </p>

                                                        <input type="hidden" name="MM_update" value="form1" />

                                                      </form>

                                                  • 22. Re: PHP/mySQL code help please
                                                    MurraySummers CommunityMVP

                                                    <input name="date" type="text" id="date" value="<?php echo date("m-d-Y", strtotime($row_updatePost['date'])); ?>" />

                                                    • 23. Re: PHP/mySQL code help please
                                                      noiseboy1970 Community Member

                                                      Murray...you sir are a true gem, and now deserve to be left alone!!!

                                                       

                                                      Thank you very much for all your help.

                                                       

                                                      Cheers, Mark

                                                      • 24. Re: PHP/mySQL code help please
                                                        dream_sugar

                                                        Murray, you are my hero too!