Newbie Question - Please Help - PHPBuilder Forums

Newbie Question - Please Help

AZDoc

Hello,

I am working on a page that will display a table with information on members of our group and the events they have attended.

i have created 2 tables with one having information on the events that have taken place and the second table with the members names and the events they have attended.

EG:

Events

Event | EventDate | EventID

Trip A | 1/1/01 | 1
Trip B | 2/2/02 | 2
Trip C | 3/3/03 | 3

My second table has:

MemberEvents

Member | EventID

Joe | 1
Joe | 2
Joe | 3
Sam | 2
Sam | 3
Sally | 2

So what I would like to do is create a table via query where it displays the members name, first event attended, date of that event, last event attended, date of last evnet, and total count of events attended.

so it looks like this:

Joe | Trip A |1/1/01 | Trip C |3/3/03| 3
Sam | Trip B | 2/2/02| Trip C |3/3/03| 2
Sally | Trip B |2/2/02 | Trip B |2/2/02| 1

any advice you can give me for the MYSQL query would be wonderful..

Thanks for any help

Nabster

You first need to specify in your code all of these variables. IE what the db host is, user,password, etc etc

$table_name = "*whatever you need*";
$connection = @mysql_connect($db_host, $db_user, $db_pass) or die("Couldn't connect.");
$db = @mysql_select_db($db_name, $connection) or die("Couldn't select database.");

$sql = "SELECT * FROM $table_name ORDER BY ID desc";

$result = @mysql_query($sql, $connection) or die("Your data couldn't be posted.");

You first have to query the database, then do something to this extent:

<?		
 		while ($row = mysql_fetch_array($result))

	{
?>
<? echo $row['varname'] ?> //etc etc
<?		
		}
?>

AZDoc

Thanks Nabster,

But I already have all the code setup to run properly (I am using it on multiple pages), I just can't figure out the acutal MYSQL query to put in for this particular setup..

What do I put in the query to ask for the First event and date and the Last event and Date of each individual and then count up each individuals attended events...as shown in the table, so that as I add events it just updates the fields in the tables with the new "last event" and updates the counts on the fly?

so if a new event comes along on 4/4/04 and Sam and Joe attend, all I do is add the event to the events table with a new ID, and add that ID to the members table under Joe and Sam's names. After doing those two entries, the displayed final data table automatically updates the "last Event" "Date" and "Number Attended" fields without me having to edit the code...

That was my main question..

I could only get it to work in MS Access, but haven't been able to get the query to work in MYSQL

Thanks

AZDoc

OK...

Here is the SQL query used on MS Access that gives me the table that I was talking about above:

SELECT MemberEvents.ClubName, First(Events.EventName) AS FirstOfEventName, First(Events.EventDate) AS FirstOfEventDate, Last(Events.EventName) AS LastOfEventName, Last(Events.EventDate) AS LastOfEventDate, Count() AS Expr1
FROM Events INNER JOIN MemberEvents ON Events.EventID = MemberEvents.EventID
GROUP BY MemberEvents.ClubName
ORDER BY Count() DESC;

But if I use this query in MYSQL it doesn't like the 'First' or 'Last' commands as listed above...

Remember I have 2 tables - Events and MemberEvents (see examples above)

Does anyone have any ideas how to convert this MSAccess query into one that will work with MYSQL?

Please help! 😉

AZDoc

Anyone?

pete_bisby

SELECT A.Member, MIN( B.EventDate ) AS FirstEventDate, MIN( B.EventID ) AS FirstEvent, MAX( B.EventDate ) AS LastEventDate, MAX( B.EventID ) AS LastEvent, COUNT( B.EventID ) AS TotalEvents FROM MemberEvents A INNER JOIN Events B ON A.EventID = B.EventID GROUP BY A.Member ORDER BY TotalEvents DESC

The only problem here is that although you will be finding the first and last date of the event for each person, you may not necessarily be finding the corresponding event for that date. The only way to get round this would be to create a for/while loop and search for the corresponding EventID, depending on the member and the date of the event.

Now if MySQL could cope with sub-queries, life would be so easy - so instead we have to use our grey matter !!!

AZDoc

Pete,

That is so very close..

thanks for the help..

However my dates are entered as text fields instead of date fields in order to display in the normal format
(2/22/2002 instead of 2002-2-22) so the MIN/MAX doesn't work for that...although the count and ID listing
works perfectly..is there a way to use the date field and display it normally?

Would it work better if I created 3 tables?

One with Members Names and Member ID numbers
One with Event Names, Event ID numbers, and Event Dates
One with Members ID numbers and Event ID numbers correlating with events members have attended...

I appreciate all your help...just trying to learn all I can...I find this all very challenging and fun!

AZDoc

Ok...

I have fixed the dates in the table to be actual "date" fields and figured out how to display them properly.

So here is my current query:

SELECT MemberEvents.Name, DATE_FORMAT( MIN( Events.EventDate ) , '%m/%d/%y' ) AS FirstEventDate,
MIN( Events.EventID ) AS FirstEvent, DATE_FORMAT( MAX( Events.EventDate ) , '%m/%d/%y' ) AS
LastEventDate, MAX( Events.EventID ) AS LastEvent, COUNT( Test.EventID ) AS TotalEvents
FROM MemberEvents INNER JOIN Test ON MemberEvents.EventID = Events.EventID GROUP BY MemberEvents.Name
ORDER BY TotalEvents DESC

That gives me the correct names with correct dates and correct counts. It only lists the Event ID number
instead of the actual Event Name....so that is my last problem.

What do I put to pull the Event Name and display it using the corresponding EventID (see tables above)

Thanks
Almost Got It!

pete_bisby

Okay, I think I've got what you're after. I'll start with the three table definitions and a little bit of data, jsut to make sure that we're both singing from the same sheet !

CREATE TABLE Events (
  Event varchar(15) NOT NULL default '?',
  EventDate date NOT NULL default '0000-00-00',
  EventID int(12) NOT NULL default '0',
  PRIMARY KEY  (EventID)
) TYPE=MyISAM;

INSERT INTO Events VALUES ('TripA', '2001-01-01', 1);
INSERT INTO Events VALUES ('TripB', '2002-02-02', 2);
INSERT INTO Events VALUES ('TripC', '2003-03-03', 3);

CREATE TABLE Members (
  Member varchar(25) NOT NULL default '?',
  MemberID int(12) NOT NULL default '0',
  PRIMARY KEY  (MemberID)
) TYPE=MyISAM;

INSERT INTO Members VALUES ('Joe', 1);
INSERT INTO Members VALUES ('Sam', 3);
INSERT INTO Members VALUES ('Sally', 2);

CREATE TABLE MemberEvents (
  EventID int(12) NOT NULL default '0',
  MemberID int(12) NOT NULL default '0',
  PRIMARY KEY  (EventID,MemberID)
) TYPE=MyISAM;

INSERT INTO MemberEvents VALUES (1, 1);
INSERT INTO MemberEvents VALUES (2, 1);
INSERT INTO MemberEvents VALUES (2, 2);
INSERT INTO MemberEvents VALUES (2, 3);
INSERT INTO MemberEvents VALUES (3, 1);
INSERT INTO MemberEvents VALUES (3, 2);

Now that we've got the tables and data together, the following query should bring back your desired results:

SELECT Members.Member, MIN(FirstEvents.Event) AS FirstEvent, DATE_FORMAT(MIN( FirstEvents.EventDate ),'%m/%d/%y') AS FirstEventDate, MIN(FirstEvents.EventID) AS FirstEventID,
MAX(LastEvents.Event) AS LastEvent, DATE_FORMAT(MAX( LastEvents.EventDate),'%m/%d/%y') AS LastEventDate, MAX(LastEvents.EventID) AS LastEventID, COUNT(Events.EventID) AS TotalEvents
FROM Members INNER JOIN MemberEvents ON Members.MemberID = MemberEvents.MemberID
INNER JOIN Events ON MemberEvents.EventID = Events.EventID
INNER JOIN Events AS FirstEvents ON Events.EventID = FirstEvents.EventID
INNER JOIN Events AS LastEvents ON Events.EventID = LastEvents.EventID
INNER JOIN MemberEvents AS FirstMemberEvents ON FirstEvents.EventID = FirstMemberEvents.EventID AND Members.MemberID = FirstMemberEvents.MemberID
INNER JOIN MemberEvents AS LastMemberEvents ON LastEvents.EventID = LastMemberEvents.EventID AND Members.MemberID = LastMemberEvents.MemberID
GROUP BY Members.Member
ORDER BY TotalEvents DESC

It was a case of using the tables a couple of times, one set for fiding the first (MIN) and then another set for the last (MAX). You can't use the same individual table for the two different grouping functions.

Hope this is what you're after !

AZDoc

That doesn't work for me..

I get an error

"Unknown column 'FirstEvents.Event' in 'field list'"

Did you mean to have it like this?

SELECT Members.Member, MIN(Events.Event) AS FirstEvent, DATE_FORMAT(MIN(Events.EventDate ),'%m/%d/%y') AS FirstEventDate, MIN(Events.EventID) AS FirstEventID, 
MAX(Events.Event) AS LastEvent, DATE_FORMAT(MAX( Events.EventDate),'%m/%d/%y') AS LastEventDate, MAX(Events.EventID) AS LastEventID, COUNT(Events.EventID) AS TotalEvents 
FROM Members INNER JOIN MemberEvents ON Members.MemberID = MemberEvents.MemberID 
INNER JOIN Events ON MemberEvents.EventID = Events.EventID 
INNER JOIN Events AS FirstEvents ON Events.EventID = FirstEvents.EventID 
INNER JOIN Events AS LastEvents ON Events.EventID = LastEvents.EventID 
INNER JOIN MemberEvents AS FirstMemberEvents ON FirstEvents.EventID = FirstMemberEvents.EventID AND Members.MemberID = FirstMemberEvents.MemberID 
INNER JOIN MemberEvents AS LastMemberEvents ON LastEvents.EventID = LastMemberEvents.EventID AND Members.MemberID = LastMemberEvents.MemberID 
GROUP BY Members.Member 
ORDER BY TotalEvents DESC

Because if I run it like this, the events still are only listed as MIN and MAX (in highest and lowest alphabetical order)...and don't correspond to the correct EventID in any table..

hmm...still lost..my tables are almost identical to yours..

pete_bisby

Well, if your table definitions are different to mine, then I would have expected an error if you are running the same SQL query, which is the reason why I included the table definitions in my response.

I created the tables, which are normalised, in relation to your 3 brief table descriptions in one of your previous postings.

Compare my table definitions to yours and either amend the tables or amend the query.

The main reason why your query isn't working is because you've changed the table alias names back to the original table name - you need to include the Events and MemberEvents tables more than once, or else the query will not work.

AZDoc

Please forgive my ignorance...I am still climbing the learning curve 😉....

I have set up my tables with definitions virtually exactly as yours above...

I am able to run the query you gave me (with minor name changes for fields) but still get the wrong event names with the event ID numbers....

Query:

SELECT Members.Name, MIN( FirstEvents.EventName )  AS FirstEvent, DATE_FORMAT( MIN( FirstEvents.EventDate ) ,  '%m/%d/%y'  )  AS FirstEventDate, MIN( FirstEvents.EventID )  AS FirstEventID, MAX( LastEvents.EventName )  AS LastEvent, DATE_FORMAT( MAX( LastEvents.EventDate ) ,  '%m/%d/%y'  )  AS LastEventDate, MAX( LastEvents.EventID )  AS LastEventID, COUNT( Events.EventID )  AS TotalEvents
FROM Members
INNER  JOIN MemberEvents ON Members.MemberID = MemberEvents.MemberID
INNER  JOIN Events ON MemberEvents.EventID = Events.EventID
INNER  JOIN Events AS FirstEvents ON Events.EventID = FirstEvents.EventID
INNER  JOIN Events AS LastEvents ON Events.EventID = LastEvents.EventID
INNER  JOIN MemberEvents AS FirstMemberEvents ON FirstEvents.EventID = FirstMemberEvents.EventID AND Members.MemberID = FirstMemberEvents.MemberID
INNER  JOIN MemberEvents AS LastMemberEvents ON LastEvents.EventID = LastMemberEvents.EventID AND Members.MemberID = LastMemberEvents.MemberID
GROUP  BY Members.Name
ORDER  BY TotalEvents DESC

The only changes I made is that Members.Member is actually Members.Name for my table, and Events.Event is actually Events.EventName for the name of the event.
Otherwise it is identical in definitions and query as your post.

The output is this:

Name FirstEvent FirstEventDate FirstEventID LastEvent LastEventDate LastEventID TotalEvents
walkabt | Biosphere | 12/30/01 | 1 | Woods Canyon | 05/17/03 | 23 | 20
CEO 2B | Biosphere | 12/30/01 | 1 | Woods Canyon | 05/17/03 | 23 | 15
Doc | Biosphere | 02/10/02 | 3 | Woods Canyon | 05/17/03 | 23 | 15
Gloffer | D Highway | 01/14/02 | 2 | Woods Canyon | 12/15/02 | 18 | 11

The problem is that the Event with the ID of 1 is actually "Tombstone" , ID 2 is "Laguna Seca", ID 3 is "Lake Havasu", ID 23 is "Pinetop", etc

So the names aren't displaying correctly - only showing as MIN - Biosphere and MAX - Woods Canyon based on alphabetical order, with no relation to the eventID......

Man...I am lost now..

pete_bisby

With the query that you have now, there is no relationship between the MemberEvents and the Events for the minimum and maximum dataed events.

The only way this is going to work is for you to send me your table structures and some sample data, so I know exactly what you're looking at.

AZDoc

OK...I will try and get this to you.
I want to thank you upfront for all of your help.

Here are the table structures:

Events |
  `EventName` varchar(25) NOT NULL default '',
  `EventDate` date NOT NULL default '0000-00-00',
  `EventID` int(11) NOT NULL default '0',
  PRIMARY KEY  (`EventID`)
) TYPE=MyISAM |

MemberEvents | 
  `Name` text NOT NULL,
  `MemberID` tinyint(4) NOT NULL default '0',
  `EventID` int(11) NOT NULL default '0',
  PRIMARY KEY  (`MemberID`,`EventID`)
) TYPE=MyISAM |

Name is not important in the MemberEvents table (basically for my reference only) the only important items are MemberID and EventID

Members | 
  `MemberID` int(12) NOT NULL auto_increment,
  `Name` varchar(25) NOT NULL default '',
  `Realname` text NOT NULL,
  `Year` year(4) NOT NULL default '0000',
  `Exterior` text NOT NULL,
  `Interior` text NOT NULL,
  `Profile` text NOT NULL,
  `Drives` text NOT NULL,
  `Dinners` text NOT NULL,
  `Show` text NOT NULL,
  `Tech` text NOT NULL,
  `Track` text NOT NULL,
  `Other` text NOT NULL,
  `Email` text NOT NULL,
  `Clubmail` text NOT NULL,
  `Firstevent` text NOT NULL,
  `Firsteventdate` text NOT NULL,
  `Lastevent` text NOT NULL,
  `Lasteventdate` text NOT NULL,
  `Events` smallint(6) NOT NULL default '0',
  `Type` text NOT NULL,
  PRIMARY KEY  (`MemberID`)
) TYPE=MyISAM |

The last five fields (not including 'type') will be removed when we get the query right - for now they are just entered as text in order to display them, but this makes them non-functional.

Here is the current query:

SELECT Members.Name, MIN( FirstEvents.EventName )  AS FirstEvent, DATE_FORMAT( MIN( FirstEvents.EventDate ) ,  '%m/%d/%y'  )  AS FirstEventDate, MIN( FirstEvents.EventID )  AS FirstEventID, MAX( LastEvents.EventName )  AS LastEvent, DATE_FORMAT( MAX( LastEvents.EventDate ) ,  '%m/%d/%y'  )  AS LastEventDate, MAX( LastEvents.EventID )  AS LastEventID, COUNT( Events.EventID )  AS TotalEvents 
FROM Members 
INNER  JOIN MemberEvents ON Members.MemberID = MemberEvents.MemberID 
INNER  JOIN Events ON MemberEvents.EventID = Events.EventID 
INNER  JOIN Events AS FirstEvents ON Events.EventID = FirstEvents.EventID 
INNER  JOIN Events AS LastEvents ON Events.EventID = LastEvents.EventID 
INNER  JOIN MemberEvents AS FirstMemberEvents ON FirstEvents.EventID = FirstMemberEvents.EventID AND Members.MemberID = FirstMemberEvents.MemberID 
INNER  JOIN MemberEvents AS LastMemberEvents ON LastEvents.EventID = LastMemberEvents.EventID AND Members.MemberID = LastMemberEvents.MemberID 
GROUP  BY Members.Name 
ORDER  BY TotalEvents DESC

What else can I give you to help?

Thanks Again

AZDoc

ttt

pete_bisby

OK, I've re-created your tables - the only exception is with the Members table, where I have only included the MemberID and Realname fields.

The following query:

SELECT Members.Realname, MIN(FirstEvents.EventName) AS FirstEventName, DATE_FORMAT(MIN( FirstEvents.EventDate ),'%m/%d/%y') AS FirstEventDate, MIN(FirstEvents.EventID) AS FirstEventID, 
MAX(LastEvents.EventName) AS LastEventName, DATE_FORMAT(MAX( LastEvents.EventDate),'%m/%d/%y') AS LastEventDate, MAX(LastEvents.EventID) AS LastEventID, COUNT(Events.EventID) AS TotalEvents 
FROM Members INNER JOIN MemberEvents ON Members.MemberID = MemberEvents.MemberID 
INNER JOIN Events ON MemberEvents.EventID = Events.EventID 
INNER JOIN Events AS FirstEvents ON Events.EventID = FirstEvents.EventID 
INNER JOIN Events AS LastEvents ON Events.EventID = LastEvents.EventID 
INNER JOIN MemberEvents AS FirstMemberEvents ON FirstEvents.EventID = FirstMemberEvents.EventID AND Members.MemberID = FirstMemberEvents.MemberID 
INNER JOIN MemberEvents AS LastMemberEvents ON LastEvents.EventID = LastMemberEvents.EventID AND Members.MemberID = LastMemberEvents.MemberID 
GROUP BY Members.Realname 
ORDER BY TotalEvents DESC

will produce the following results:

Realname  |  FirstEventName  |  FirstEventDate  |  FirstEventID  |  LastEventName  |  LastEventDate  |  LastEventID  |  TotalEvents  

Joe  |  Trip A  |  01/01/01  |  1  |  Trip C  |  03/03/03  |  3  |  3 
Sam  |  Trip B  |  02/02/02  |  2  |  Trip C  |  03/03/03  |  3  |  2 
Sally  |  Trip B  |  02/02/02  |  2  |  Trip B  |  02/02/02  |  2  |  1

The query requires you to have these multiple instances of the same tables (i.e. FirstMemberEvents and LastMemberEvents are the MemberEvents table), to allow you to find the first and last occurrences (min and max) for each member. If you remove them, then the query will not run.

I also put members that did not have an event, and events that no members attended , to make sure that the query is running correctly - which it did (thankfully!).

Hope this puts you on the right track.

AZDoc

Still no go...🙁

please see PM

AZDoc

Anyone have any ideas?

Please speak up! I need to get this finished...

Thanks