Meeting Schedule

[chadrt]

I am trying to write a simple method of displaying our meetings schedule for North Idaho Area Narcotics Anonymous it pulls from a MySQL database and I want it to look like this:

 

    COEUR D'ALENE

Sunday:

Group 1 Name  Time

Group 2 Name  Time

Group 3 Name  Time

 

Monday:

Group 1 Name  Time

Group 2 Name  Time

 

      POST FALLS

Sunday:

Group 1 Name    Time

 

Ok so my problem is I dont know how to make php create the heading only if there are meetings in that heading so if there are no meetings in POST FALLS then it wont make a list for post falls or if there are no meetings in POST FALLS on Sunday then it wont show the SUNDAY heading.  This way it will be maintenance free page just add/delete meetings in the database then go from there.  Each meeting name will be a link to an ID corresponding to the unique ID in the database so you click it then read all the info on that meeting.  It will be a way for NA members to view the schedule from their mobile phones easily.

 

I have written loops but nothing that would leave out a heading based on lack of a given entry.  Any help would be most appreciated.  Thank you!!!

 

Chad

[Muddy_Funster]

Should be fairly straight forward, can you post your current working code and a table+field list?

[chadrt]

I am starting from scratch here the whole list was daunting to say the least.  I am a "cut and paste scripter" I have done some orriginal works in php that were impressive to me but nothing on a grand scale.

 

I can send you a read only username and password to my mysql server for the database and table in question.  I am working on the code as we speak.  I would only send that in the form of a pm, even though its only readonly I would prefer not to invite the world in there. 

 

Chad

[TOA]

What is the db structure?

[Muddy_Funster]

sure, pm me the login and I'll post the requred info back in a format that we can all work with (I won't post any content, just structure and table/field names).

 

If you could still throw up what you have so far regarding the PHP it would also give us a starting point for helping with that too (no sense us getting you to code a loop for something that can be done in a loop you already have).

[chadrt]

I have gleaned this code from Tizag:

 

<?php

mysql_connect("***", "***", "***") or die(mysql_error());
mysql_select_db("nameetings") or die(mysql_error());

$result = mysql_query("SELECT * FROM meetings") 
or die(mysql_error());  

echo "<table border='1'>";
echo "<tr> <th>Group Name</th> <th>Time</th> </tr>";
while($row = mysql_fetch_array( $result )) {

echo "<tr><td>"; 
echo $row['name'];
echo "</td><td>"; 
echo $row['time'];
echo "</td></tr>"; 
} 

echo "</table>";
?>

 

But this does no sorting what so ever in respect to the meeting Cities or Days.  Somehow I have to come up with a statement that select the entire the city column and makes a list that is based on unique entries I think then sort the entries in the DB based on the unique entries then I was thinking of using a string replace for the days of the week that would display them correctly.

 

Am I on the right track?

[chadrt]

I did this to generate the links for each of the meetings that will point to the full meeting details page (yet to be created).  Plus I changed it from pulling all the data to only what is needed by this page. (SELECT ID, name, time) instead of (SELECT *)

 

<?php
// Make a MySQL Connection
mysql_connect("***", "***", "***") or die(mysql_error());
mysql_select_db("nameetings") or die(mysql_error());

// Get all the data from the "example" table
$result = mysql_query("SELECT ID, name, time FROM meetings") 
or die(mysql_error());  

echo "<table border='1'>";
echo "<tr> <th>Group Name</th> <th>Time</th> </tr>";
while($row = mysql_fetch_array( $result )) {



echo "<tr><td><a href=\"details.php?id=".$row['ID']."\">"; 
echo $row['name'];
echo "</a></td><td>"; 
echo $row['time'];
echo "</td></tr>"; 
} 

echo "</table>";
?>

[Muddy_Funster]

ok, here's the table info:

 

IDtinyint(4) - auto_inc - PK

namevarchar(40)

timevarchar(10)

dayvarchar(9)

buildingvarchar(40)

cityvarchar(30)

addressvarchar(40)

notestext

opentinyint(1)

smokingtinyint(1)

literaturetinyint(1)

handicaptinyint(1)

birthdaytinyint(1)

speakertinyint(1)

rotateformattinyint(1)

candlelighttinyint(1)

NewChangedtinyint(1)

activetinyint(1)

 

well there is certainly plenty going on...this could take a while 

[chadrt]

I have been doing some reading on the net and found a couple nice little queries.

 

1) SELECT DISTINCT column FROM table

2) foreach

 

I have been able to display a list of cities from the table and I have played with doing a foreach to create a list of meetings but think I need to run a distinct inside of each of the distinct's for the days of the week somehow using the foreach to extract the data associated with it.  IDK will step away from the keyboard for a minute to clear my brain.

[Muddy_Funster]

nah, SELECT DISTINCT only returns one row for each value in a column, even if there are 100 rows with that value, nesting selects in the way you are thinking about tends to not work too well and slows things down.  PHP is much better at the contitional data checking that you want to do.  And foreach is a php loop that basicly does what you are already doing with your while loop, foreach is normaly used with internal arrays within PHP.  What I think would be the best way to do it would be to nest a few if statements inside the while loop.  have the if statements check the values terurned from the database, and if they are not assigned, negglect to echo them to the page.  It's just going to take me some time to come up with the proper code to do that (alough chances are someone else well get there before I do - and probably with better code aswell- I'll work on it when I can untill that happens).

[Muddy_Funster]

Just want to clarify a couple of things:

1 - is there a reason that date and time are both varchar fields?

2 - I assume that there is only going to be records added to the table when there is an event actualy happening, and that you won't be adding anything for any days / citys that won't be holding anything?

3 - how open are you to having your database re-structured?

[chadrt]

Well there are no dates because the meetings happen every week not just on a particular day.  The reason that the time field is varchar is because I dont know enough about mysql php to create that situation for a day of the week/time only characteristic.  I wouldnt mind at all actually if the database was restructured I might even learn a thing or two about how it works reading the code.  And no I wont be adding anything for any days or cities that wont hold anything in the db.  I will simply delete the record/add a new record.  Although as I think about it if the day time was set in another way it may open a door for me to add the ability to say show Tuesday as the first day in the list if today is Tuesday or organize the output based on the time of the meeting without hassles.  Let me know if you more access than read only, thank you so much for your help!  All the time we hear of folks that loose their meeting schedules or what have you and I think with building a small little site dedicated to small browser of the cell phone I am building a nearly platform independant solution to the problem.  And I have these little QR Codes that I can post at the meetings for those that have Android or iPhone type smart phones and they can just instantly go to the mobile site.  Because of NA I have almost 8 years of clean time from drugs and alcohol and this is how I repay them by keeping up on the technology.  Of course they dont expect anything in return but it feels good anyway. 

 

Chad

 

[Muddy_Funster]

OK, sorry this has taken so long, but I have been ocupied by other things recently.  This is where I have got to so far:

 

change your table and make it two tables:

id| int(8 ) - PK - auto_inc

name| varchar()

address1| varchar()

address2| varchar()

address3| varchar()

zip| varchar()

city| varchar()

you can set the lengths on the varchars as you think best.

 

id| int(12) - PK - auto_inc

v_id| int(8 ) - FK

type| varchar()

event_day| date(0)

event_time| time(0)

open| intyint(1)

smoking| tintyint(1)

literature| tintyint(1)

handicap| tintyint(1)

birthday| tintyint(1)

speaker| tintyint(1)

rotateformat| tintyint(1)

candlelight| tintyint(1)

newchange| tintyint(1)

active| tintyint(1)

 

The tables are named "venue" and "event" respectivly.

 

And here's some sample code for the results:

$qry = 'SELECT name, city, type, event_day, event_time FROM venue RIGHT JOIN event ON (venue.id = event.v_id) WHERE event.event_day => CURDATE() ORDER BY city, name, type, day, time ASC';
$result = mysql_query($qry) or die ('Fatal error returning information from the database! -- '.mysql_error());
while ($row = mysql_fetch_assoc($result)){
if($city != $row['city']){
echo '<br><h4>'.$row['city'].'</h4><br>';
$city = $row['city'];
}
if($name != $row['name']){
ehco '<br><h5>'.$row['name'].'</h5><br>';
$name = $row['name'];
}
echo '<a href="yourURLlink">'.$row[type].' on '.$row['day'].' @ '.$row['time'].'</a><br>';
}

 

Let me know how you get on.

 

[chadrt]

Ok so I think I understand the whole thing here with the two tables but I have a couple questions what is the "type" field?  And there needs to be a section for the Group/Meeting Name.  The venu would be like St. Pius Church but the group would be something like the name of the meeting or the group that meets there "Courage to Change" etc.

[Muddy_Funster]

that's what the type was for, I didn't know exactly what you would want to call the refference, so just went with something generic (shoulda cleared that up when I posted it) do please feel free to rename it whatever you wish.