...

View Full Version : Finding next confirmed item by date



friz
03-07-2010, 06:29 PM
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
Bad code deleted - see next post
?>
<div class='nav'> should contain the links to the next/previous confirmed gig page.

Structure of db table

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! :thumbsup:

friz
03-09-2010, 03:54 AM
... and then I got an idea :rolleyes:

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


<?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 ( 0 == $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:


//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


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?

JAY6390
03-09-2010, 04:17 AM
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

friz
03-09-2010, 08:16 PM
Thx :thumbsup:
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

AND datum >'$datum'
ORDER BY datum

AND datum >='$datum'
AND cal_id <> $cal_id
ORDER BY datum

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 :confused:

JAY6390
03-09-2010, 08:21 PM
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


$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 />";
}

friz
03-09-2010, 08:50 PM
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

$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
http://img709.imageshack.us/img709/640/navyo.th.png (http://img709.imageshack.us/i/navyo.png/)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum