[SOLVED] queries and arrays

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
penciLneck
Forum Newbie
Posts: 15
Joined: Fri Nov 28, 2003 10:13 am
Location: Daarsit

[SOLVED] queries and arrays

Post 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
Last edited by penciLneck on Wed Feb 11, 2004 1:56 pm, edited 2 times in total.
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Post 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.
penciLneck
Forum Newbie
Posts: 15
Joined: Fri Nov 28, 2003 10:13 am
Location: Daarsit

Post 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.
penciLneck
Forum Newbie
Posts: 15
Joined: Fri Nov 28, 2003 10:13 am
Location: Daarsit

Post 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?
penciLneck
Forum Newbie
Posts: 15
Joined: Fri Nov 28, 2003 10:13 am
Location: Daarsit

Post by penciLneck »

solved myself, ordered by eventid and date, that did the trick.
Post Reply