Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New Coder
    Join Date
    Oct 2007
    Posts
    73
    Thanks
    26
    Thanked 0 Times in 0 Posts

    Finding next confirmed item by date

    I've written a script that shows all my upcoming gigs sorted by date.

    If you click the gebeurtenis_info.php?c=$cal_id link, you go to a page with more info about the gig.

    On this page I would like a link that goes to the next confirmed date and a link that goes to the previous confirmed date. When there's no previous or next confirmed date date, there shouldn't be a link or at least not a click-able one.

    I just can't figure out how to do this.

    This is the code of the info page:
    PHP Code:
    <?php
    Bad code deleted 
    see next post
          ?>
    <div class='nav'> should contain the links to the next/previous confirmed gig page.

    Structure of db table
    Code:
    Field  	Type
    cal_id 	int(11)
    datum 	date      => gigdate
    naam 	varchar(50)
    straat 	varchar(150)
    nummer 	varchar(10)
    gemeente 	varchar(50)
    gem_buit 	varchar(150)
    aanvang 	time 
    aanwezig 	time
    check 	time
    paid 	        int(3)
    andere 	varchar(250)
    bid 	        int(3)
    type 	        varchar(8)  => gig or rehearsal
    status 	varchar(12) => confirmed, option or cancelled
    aanmaak_datum 	datetime 
    mod_datum 	timestamp
    wis_datum 	datetime
    Any ideas??
    Thanks in advance!
    Last edited by friz; 03-09-2010 at 03:32 AM. Reason: Deleted bad code

  • #2
    New Coder
    Join Date
    Oct 2007
    Posts
    73
    Thanks
    26
    Thanked 0 Times in 0 Posts
    ... and then I got an idea

    I've rewritten the previous code (better iteration and more secure) and added what I've found so far:

    PHP Code:
    <?php
          
    require("includes/menu.php");
          require(
    "includes/functions.php");

    if(isset(
    $_GET['action'])) $action=$_GET['action'];
    if(isset(
    $_POST['action'])) $action=$_POST['action'];
    if(isset(
    $_REQUEST['action'])) $action=$_REQUEST['action'];

    setlocale(LC_TIME'dutch');
    $cal_id=mysql_real_escape_string($_GET['c']);

    include(
    "conf.inc.php");
        
    $connection mysql_connect($host$user$pass) or die ("Niet in geslaagd verbinding te krijgen!");
        
    mysql_select_db($db) or die ("Geen verbinding verkregen met de database!");



    echo
    "<br />
         <div class='optredens-inhoud'>"
    ;

    $query "SELECT UNIX_TIMESTAMP(a.datum) AS dedag,
                                a.naam AS event,
                                a.straat,
                                a.nummer,
                                a.gemeente,
                                a.gem_buit,
                                DATE_FORMAT(a.aanwezig, '%H h %i') as aanwezig,
                                DATE_FORMAT(a.check, '%H h %i') as soundcheck,
                                DATE_FORMAT(a.aanvang, '%H h %i') as aanvang,
                                a.paid,
                                a.andere,
                                a.status,
                                a.type,
                                b.bandnaam,
                                c.name,
                                c.code,
                                c.id AS city_id,
                                p.naam AS panaam,
                                p.site
                           FROM optredens a,
                                city c,
                                bands b,
                                pa p
                           WHERE a.cal_id='$cal_id'
                           AND a.bid=b.bid
                           AND a.paid = p.paid
                           AND a.gemeente = c.id"
    ;

    $result mysql_query($query);
            if ( !
    $result ) {
                echo 
    "<span class='info-span'>Fout bij ophalen van gegevens...</span>";
            } else {

            
    $numrows mysql_num_rows($result);
            if ( 
    == $numrows ) {
                echo 
    "<span class='info-span'>Geen gegevens gevonden...</span>";
            } else {
         
    $info=mysql_fetch_array($result);
        }
    }

    $straat $info['straat'];
    $nummer $info['nummer'];
    $gemeente $info['name'];
    $gem_buit $info['gem_buit'];
    $postcode $info['code'];
    $city_id $info['city_id'];
    $datum date('Y-m-d',$info['dedag']);

    //navigatie

      
    $link_vorige=mysql_fetch_array(mysql_query("SELECT cal_id AS vorige
                                                      FROM optredens
                                                      WHERE status='Bevestigd'
                                                      AND datum <'$datum'
                                                      ORDER BY datum DESC"
    ));

        
    $link_volgende=mysql_fetch_array(mysql_query("SELECT cal_id AS volgende
                                                      FROM optredens
                                                      WHERE status='Bevestigd'
                                                      AND datum >'$datum'
                                                      ORDER BY datum"
    ));

    echo 
    "<div class='nav'>
            <span class='nav-links'><a href=gebeurtenis_info.php?c="
    .$link_vorige['vorige'].">&lt;&lt; Vorige</a></span>
            <a href=gebeurtenissen.php>Overzicht</a>
            <span class='nav-rechts'><a href=gebeurtenis_info.php?c="
    .$link_volgende['volgende'].">Volgende &gt;&gt;</a></span>
          </div><br />"
    ;

    //Info tabel
    echo "<span class='info-span'>".strftime('%a %d %b %Y',$info['dedag'])." - ".$info['bandnaam']."</span>
    <table class='tabel-optreden-info'>
          <CAPTION>"
    .$info['type']." Info</CAPTION>
          <tr>
          <td width='200'>Event/Plaats</td><td>"
    .$info['event']."</td>
          </tr>
          <tr>
          <td>Adres</td>"
    ;
          if (
    $info['name']=='Nederland'){
                               echo 
    "<td>$straat ($gem_buit - $gemeente)</td>";
                }else{
                             echo 
    "<td>$straat $nummer - $postcode $gemeente</td>";
                }
     echo
    "</tr>
          <tr>
          <td>Aanwezig</td><td>"
    .$info['aanwezig']."</td>
          </tr>
          <tr>
          <td>Soundcheck/Linecheck</td><td>"
    .$info['soundcheck']."</td>
          </tr>
          <tr>
          <td>Aanvang</td><td>"
    .$info['aanvang']."</td>
          </tr>
          <tr>
          <td>PA</td>"
    ;

                if (
    is_null($info['site'])) {
                                echo 
    "<td>".$info['panaam']."</td>";
                  }else{
                              echo 
    "<td><a href=http://".$info['site']." target='_blank'>".$info['panaam']."</a></td>";
              }

     echo
    "</tr>
          <tr>
          <td>Status</td><td>"
    .$info['status']."</td>
          </tr>
          <tr>
          <td>Extra Callsheet info</td><td>"
    .nl2br($info['andere'])."</td>
          </tr>
         </table>"
    ;
    This part is almost completely what I was looking for:
    PHP Code:
    //navigatie

    $link_vorige=mysql_fetch_array(mysql_query("SELECT cal_id AS vorige
                                                      FROM optredens
                                                      WHERE status='Bevestigd'
                                                      AND datum <'$datum'
                                                      ORDER BY datum DESC"
    ));
    $link_volgende=mysql_fetch_array(mysql_query("SELECT cal_id AS volgende
                                                      FROM optredens
                                                      WHERE status='Bevestigd'
                                                      AND datum >'$datum'
                                                      ORDER BY datum"
    ));

    echo 
    "<div class='nav'>
            <span class='nav-links'><a href=gebeurtenis_info.php?c="
    .$link_vorige['vorige'].">&lt;&lt; Vorige</a></span>
            <a href=gebeurtenissen.php>Overzicht</a>
            <span class='nav-rechts'><a href=gebeurtenis_info.php?c="
    .$link_volgende['volgende'].">Volgende &gt;&gt;</a></span>
          </div><br />"

    There's just 1 problem... when I have 2 or more gigs on the same date, it only shows the first gig of that date.
    I've tried changing it to
    PHP Code:
    AND datum >= '$datum'
    AND cal_id <> '$cal_id' 
    Now it shows 2 gigs on the same date, but of course it gets stuck on that date switching between the gigs with the same date.
    I can't check on the name of the event (naam), because both gigs could be at the same venue. The same goes for straat and all other data.

    Any ideas how I could solve this?

  • #3
    Regular Coder
    Join Date
    Dec 2009
    Location
    UK
    Posts
    495
    Thanks
    0
    Thanked 58 Times in 58 Posts
    Just read this quickly but I noticed these three lines

    if(isset($_GET['action'])) $action=$_GET['action'];
    if(isset(
    $_POST['action'])) $action=$_POST['action'];
    if(isset(
    $_REQUEST['action'])) $action=$_REQUEST['action'];

    The first two are redundant since if either of them have an action key, the $_REQUEST will set it, since it contains the combination of their contents along with the cookie contents

    See http://php.net/manual/en/reserved.variables.request.php for more information
    My site: JayGilford.com
    Resources:
    PHP Pagination Class | Getting all page links | Handling PHP Errors properly
    If you like a users help, show your appreciation with the rep and thanks buttons :)

  • Users who have thanked JAY6390 for this post:

    friz (03-09-2010)

  • #4
    New Coder
    Join Date
    Oct 2007
    Posts
    73
    Thanks
    26
    Thanked 0 Times in 0 Posts
    Thx
    So, if I read correctly, I should only use those needed. And the $request one if I need all 3?

    Any ideas on the previous question?

    I've tried
    PHP Code:
    AND datum >'$datum'
    ORDER BY datum 
    PHP Code:
    AND datum >='$datum'
    AND cal_id <> $cal_id
    ORDER BY datum 
    PHP Code:
    AND datum >='$datum'
    AND cal_id <> $cal_id
    ORDER BY datum ASC 
    None of those work
    I really have no clue on how to do this

  • #5
    Regular Coder
    Join Date
    Dec 2009
    Location
    UK
    Posts
    495
    Thanks
    0
    Thanked 58 Times in 58 Posts
    Yes. As for the problem, you aren't looping through each of the returned rows, only the first one. You need to change your code a little to something like this

    PHP Code:
    $result mysql_query("SELECT cal_id AS volgende 
                                                      FROM optredens 
                                                      WHERE status='Bevestigd' 
                                                      AND datum >'$datum' 
                                                      ORDER BY datum"
    );
    while(
    $link_volgende mysql_fetch_array($result)) {

    echo 
    "<div class='nav'> 
            <span class='nav-links'><a href=gebeurtenis_info.php?c="
    .$link_vorige['vorige'].">&lt;&lt; Vorige</a></span> 
            <a href=gebeurtenissen.php>Overzicht</a> 
            <span class='nav-rechts'><a href=gebeurtenis_info.php?c="
    .$link_volgende['volgende'].">Volgende &gt;&gt;</a></span> 
          </div><br />"
    ;


    My site: JayGilford.com
    Resources:
    PHP Pagination Class | Getting all page links | Handling PHP Errors properly
    If you like a users help, show your appreciation with the rep and thanks buttons :)

  • #6
    New Coder
    Join Date
    Oct 2007
    Posts
    73
    Thanks
    26
    Thanked 0 Times in 0 Posts
    But if I do that it will show the 'nav' div over 300 times (for all entries).

    I've tested with a while loop for the previous (vorige) and next (volgende) link
    PHP Code:
      $vorige_result=mysql_query("SELECT cal_id AS vorige
                                  FROM optredens
                                  WHERE status='Bevestigd'
                                  AND datum <'$datum'
                                  ORDER BY datum DESC"
    );

        
    $volgende_result=mysql_query("SELECT cal_id AS volgende
                                    FROM optredens
                                    WHERE status='Bevestigd'
                                    AND datum >'$datum'
                                    ORDER BY datum"
    );

    echo 
    "<div class='nav'>";
    while(
    $link_vorige mysql_fetch_array($vorige_result)) {
            echo
    "<span class='nav-links'><a href=gebeurtenis_info.php?c=".$link_vorige['vorige'].">&lt;&lt; Vorige</a></span>";
          }
           echo
    " <a href=gebeurtenissen.php>Overzicht</a>";
    while(
    $link_volgende mysql_fetch_array($volgende_result)) {
            echo
    "<span class='nav-rechts'><a href=gebeurtenis_info.php?c=".$link_volgende['volgende'].">Volgende &gt;&gt;</a></span>";
          }
          echo
    "</div><br />"
    This is the result


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •