Page 1 of 1

[SOLVED] queries and arrays

Posted: Tue Feb 10, 2004 4:54 pm
by penciLneck
Ok so I posted this before and no one answered, so i guess I'll just try again.

this is what I'm doing: pulling related results out of two mysql tables, namely events and instances. There are lots of events and each event can have multiple instances. I'm looking to display them on the page logically and without duplicate events.

First I query the results:

Code: Select all

$q = "SELECT events.title,events.text,events.image,instances.location,DATE_FORMAT(instances.date,'%d %M %Y,') as date,TIME_FORMAT(instances.time,'%l:%i %p') as time FROM events,instances WHERE events.eventid=instances.eventid ORDER BY instances.date desc ";
$result= mysql_query($q, $connection) or die 
("Could not execute query : $q." . mysql_error());
Then put them into an array:

Code: Select all

while ($row=mysql_fetch_array($result))
{
$title=$rowї"title"];
$text=$rowї"text"];
$image=$rowї"image"];
$location=$rowї"location"];
$date=$rowї"date"];
$time=$rowї"time"];

//and print them out
echo "$title";
echo "$text";
echo "$location";
echo "$date";
echo "$time";
}

Ok thats just fine and dandy except for one thing, my results are duplicated - if there is more than one instance to an event a duplicate event is displayed with that instance as well.

eg.
1.applepicking,date1,location1,time1
2.applepicking,date2,location2,time2
3.ciderdrinking,date1,location1,time1

but I want:
1.applepicking,date1,location1,time1,date2,location2,time2

How am I going wrong? Should I be using two queries? Or a multidimensional array?

Please enlighten me...

much appreciated,

pN

Posted: Wed Feb 11, 2004 2:54 am
by microthick
In these types of situations, I always follow this method.

1) Output one record.
2) Remember the event that you just outputted.
3) Before you output the next record, compare the record with the last record, and only output what you need.

So, you can modify your code slightly to do what you need.

Code: Select all

$prevtitle = "";

while ($row=mysql_fetch_array($result)) 
{ 
$title=$rowї"title"]; 
$text=$rowї"text"]; 
$image=$rowї"image"]; 
$location=$rowї"location"]; 
$date=$rowї"date"]; 
$time=$rowї"time"]; 

// before printing this info out, check to see if it's the same event
if ($prevtitle == $title) {
    // then it's the same event and this is just a different instance.
    echo $date." ".$location." ".$time;
} else {
    // then it's a totally new event
    echo "$title"; 
    echo "$text";
    echo "$location"; 
    echo "$date";
    echo "$time"; 
}

// now, remember this event as the past event.
$prevtitle = $title;
}
This code is very rough, but I'm sure you'll be able to adjust it accordingly.

Posted: Wed Feb 11, 2004 5:52 am
by penciLneck
Thanks a lot Microthick, for the second time. It was you who helped me the first time I posted here.

I've tried your code, and it works like a charm. You are the master.

thanks,

pN

p.s. I really like your site design, very clean and professionally laid out.

Posted: Wed Mar 03, 2004 8:11 am
by penciLneck
just realised that this still produces repeated events when I order by instances.date - its fine if I just order by title but as soon as the sql is odered by one of the many instances dates, some events repeat but strangely a couple do work as desired. Any ideas Microthick?

Posted: Wed Mar 03, 2004 8:21 am
by penciLneck
solved myself, ordered by eventid and date, that did the trick.