Announcement

Collapse
No announcement yet.

MySQL help

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • MySQL help

    Hiya,

    I am trying to create a database for my users to submit to rather than having to manually update the HTML file myself.

    Currently I have got so far as being able to add to and edit/delete entries from the database, however now I now need some help with the code that would display the release date list.

    As it stands at the moment the static HTML looks something like this.

    Monday 29th Sept
    Release 1
    Release 2
    Release 3

    Monday 6th Oct
    Release 1
    Release 2
    Release 3

    Monday 13th Oct
    Release 1
    Release 2
    Release 3

    I was wondering if there was any way of sorting the releases by their release date.

    Thanks in advance.

  • #2
    If you insert the time as a timestamp, you can also get it as one. And sort on that table.
    Just use PHP to display the timestamp in a human readable format.

    Comment


    • #3
      I can get them to list them in order of the date, but I'd like to split them up like we do it at the moment.

      Comment


      • #4
        Well, you list the dates on ordering on the date, and the releases on the uniqueid so the oldest is listed first or the other way around.

        Comment


        • #5
          PHP Code:
          $lastDate '';
          while (
          $row mysql_fetch_assoc($result))
          {
              
          $curDate date('l d F'$row['timestamp']);

              if (
          $curDate != $lastDate)
              {
                  echo 
          '<br /><strong>' .  $curDate "</strong><br />\n";
              }

              
          // echo items here
              
          echo $row['releasename'] . "<br />\n";

              
          // Set last date
              
          $lastDate $curDate;

          That's with it the timestamp in unix format, if you've stored it as a mysql timestamp then you'll need to adjust your query to use the UNIX_TIMESTAMP function on the field.

          Comment


          • #6
            okay, i've done that, and put three different release dates into the database and it just shows like this:


            01 Jan 1970
            IzzyTest
            JakeDon't hate..appreciate!!!
            Jack Stevensla la la

            Comment


            • #7
              That is correct, i see a date, then the three results, going from a-z.
              If you want to have an order on the release dates that's not from unique id a-z or z-a, you have to order on a different field. Or add a field that lets you change the order.

              Comment


              • #8
                What's the structure of your table?

                Comment


                • #9
                  Originally posted by Floris View Post
                  That is correct, i see a date, then the three results, going from a-z.
                  If you want to have an order on the release dates that's not from unique id a-z or z-a, you have to order on a different field. Or add a field that lets you change the order.
                  yes but each one has a different 'release_date' i want to separate them into different lists.

                  Originally posted by Sn2 View Post
                  What's the structure of your table?
                  http://www.vbulletin.com/forum/attac...1&d=1222704869
                  Attached Files

                  Comment


                  • #10
                    Change the release_date column to timestamp type and set the default to CURRENT_TIMESTAMP.

                    Then in your query when you're choosing the fields do SELECT col1, col2, UNIX_TIMESTAMP(release_date) AS rdate, col3 FROM ... and the code I gave should work fine.

                    Comment


                    • #11
                      but the release dates are pre-set to be every monday, wont a timestamp be when the user submits their release?

                      Comment


                      • #12
                        It's whatever you tell it to be, but yes by default it would be the current time.

                        I didn't actually realise that each release would be a Monday and thought it would be whatever time it was entered into the system.

                        If you're letting the user choose the dates then you could use the code I gave earlier and remove the $curDate variable from it and use the normal field from your result instead. That would work.

                        Comment


                        • #13
                          so how would i edit this to show it like

                          DATE
                          releases

                          DATE
                          releases

                          DATE
                          releases

                          PHP Code:
                          <?php
                              
                          include('includes/config.php');
                              include(
                          'includes/functions.php');
                                  
                              
                          //Open a connection to the database
                              
                          $db mysql_connect($hostname$user$pass) or die ('Error connecting to mysql');
                              
                          mysql_select_db($name) or die ('Error connecting to mysql');
                              
                              
                          //generate and execute the sql query
                              
                          $query "SELECT * FROM $release_sched_db";
                                  
                              
                          $result mysql_query($query)
                              or die (
                          "Error in query: $query. " mysql_error());
                              
                              
                          $lastDate '';
                              while (
                          $row mysql_fetch_assoc($result))
                              {
                                  
                          $curDate formatDate($row->date);
                                  
                                  if (
                          $curDate != $lastDate)
                                  {
                                      echo 
                          '<br /><strong>' .  $curDate "</strong><br />\n";
                                  }

                                  
                          // echo items here
                                  
                          echo $row['act'] . $row['title'] . "<br />\n";

                                  
                          // Set last date
                                  
                          $lastDate $curDate;
                              }      
                          ?>

                          Comment


                          • #14
                            PHP Code:
                             <?php 
                                
                            include('includes/config.php'); 
                                include(
                            'includes/functions.php'); 
                                     
                                
                            //Open a connection to the database 
                                
                            $db mysql_connect($hostname$user$pass) or die ('Error connecting to mysql'); 
                                
                            mysql_select_db($name) or die ('Error connecting to mysql'); 
                                 
                                
                            //generate and execute the sql query 
                                
                            $query "SELECT * FROM $release_sched_db ORDER BY release_date, title"
                                     
                                
                            $result mysql_query($query
                                or die (
                            "Error in query: $query. " mysql_error()); 
                                 
                                
                            $lastDate ''
                                while (
                            $row mysql_fetch_assoc($result)) 
                                { 
                                    
                            $curDate formatDate($row['release_date']); 
                                     
                                    if (
                            $curDate != $lastDate
                                    { 
                                        echo 
                            '<br /><strong>' .  $curDate "</strong><br />\n"
                                    } 

                                    
                            // echo items here 
                                    
                            echo $row['act'] . $row['title'] . "<br />\n"

                                    
                            // Set last date 
                                    
                            $lastDate $curDate
                                }       
                            ?>
                            You'll probably want to run $row['act'] and $row['title'] through htmlspecialchars() or similar unless you completely trust whoever is entering the data.

                            Comment


                            • #15
                              okay THANK YOU So much!

                              its now split them into the two dates ive got, exept it was formatting the dates to 1970 and 1969, but i sorted that by changing the sql field to datetime!

                              Thanks!

                              Comment

                              widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
                              Working...
                              X