Page 1 of 1
Needing assistance with a few PHP code tweaks.
Posted: Tue May 05, 2009 11:26 pm
by marconi
I am building a musician's website on a server running PHP4 alongside MySQL 5. One of the website features is an open source calendar that will be used to post performance dates. The calendar has already been set up and I have pieced together a bit of PHP code (see code and database image below) in order to pull upcoming performance information from the calendar database and post it to the website Home page. All of this is already in place, but I now need to make the following three refinements to the output, and I'm not quite sure how to go about it (needless to say, I'm very new to PHP programming). Any help here would be very much appreciated.
Refinement 1: I want to limit the output on the homepage so that it never shows more than the next two upcoming shows.
Refinement 2: The date and time for each show is currently formatted as follows: 5/1/2009 @ 04:00:00. I would like to change this to: May 1, 2009 @ 4:00pm.
Refinement 3: If no future shows are scheduled, I would like to post a message that says 'No events are currently scheduled".
I suspect that the changes I need are not that difficult to come by. It is just (again) that I am very new to all of this. Thanks in advance for any input.
Code: Select all
<?php
// Make an MySQL Connection
mysql_connect("server", "username", "password") or die(mysql_error());
mysql_select_db("database name") or die(mysql_error());
// Get all the data from the "example" table
$result = mysql_query("SELECT * FROM table")
or die(mysql_error());
echo "<table border='0'>";
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
// Print out the contents of the specified rows into a table
echo "<tr><td>";
echo"<h4>";
echo $row['m']. "/". $row['d']. "/". $row['y']. " @ " .$row['start_time'];
echo"</h4>";
echo "</td></tr>";
echo "<tr><td>";
echo $row['title'];
echo "<tr><td> </td></tr>"; //This places a blank row between events.
echo "</td></tr>";
}
echo "</table>";
?>
[img]

- This image illustrates the database structure
- db_structure.jpg (39.87 KiB) Viewed 1140 times
[/img]
Re: Needing assistance with a few PHP code tweaks.
Posted: Wed May 06, 2009 1:14 am
by mickd
I'll give you some ideas/functions that you could use to start you off, and hopefully you're able to figure out some yourself =) If not, just reply back with what you have (if it doesn't work).
1. Limit the amount of rows that it retrieves from the database to 2 (look up about mysql LIMIT). You might want to sort the result of the query in some way so that it only returns the 2 most recent entries, for example.
2. You can probably play with and use
strtotime and then
date to do that.
3.
mysql_num_rows is a function you can use to tell you how many results were returned by your query to the database. So, you would have an if statement to check how many rows are returned, and display accordingly.
Good luck!
Re: Needing assistance with a few PHP code tweaks.
Posted: Wed May 06, 2009 11:50 am
by marconi
thanks so much for the reply. A friend of mine suggested that I try the following code in order to only display the next two upcoming events:
Code: Select all
SELECT * FROM `table` WHERE `event_date` >= NOW() ORDER BY `event_date` DESC LIMIT 0, 2
this did not work however, as I kept getting an error indicating that `event_date` is not a column name (which is true). the problem I am having is that the date in the table is distributed across three columns (m, d, y,) and I do not know how to refer to all three in a collective way that allows me to replace what my friend named 'event_date' with something that PHP/mySQL can work with.
Thus far, what I have managed to do is to simply limit the current output to two records.
Code: Select all
$result = mysql_query("SELECT * FROM table LIMIT 0, 2")
The next step is to modify the code such that those two records are the next two 'upcoming' events in the table. and then, of course, I will need to format the dates as specified in my first posting, and finally, ensure that an appropriate message displays whenever no future dates exist in the table.
I apologize for my naïveté here. at a high level I understand what needs to be done. I just don't quite yet have the arsenal of syntax and functions that are needed to do it. If you can muster up some additional assistance, that would be great.
Thanks again
Re: Needing assistance with a few PHP code tweaks.
Posted: Wed May 06, 2009 8:49 pm
by mickd
In mySQL you can combine multiple conditions in a query using AND and OR. You could use replace what your friend did. For example,
SELECT * FROM `table` WHERE d > $day AND m >= $month AND y >= $year LIMIT 2
You can use PHP's
getdate function to get the day, month and year.
Personally though, I don't like the way they separated day/month/year into 3 tables, and wouldn't of made it like that..
The above doesn't sort tho, just makes sure none of the events have passed.
Re: Needing assistance with a few PHP code tweaks.
Posted: Wed May 06, 2009 10:55 pm
by marconi
Thank you again for the tip. I also get the sense that the separate m,d,y columns in the table are not ideal. I guess that is just the way the application designer did it. Anyway, at this point, out of principle, I am just trying to get this to work.
So, I see that the getdate function pulls a whole host of date and time information pertaining to the current date. So, for instance, in my code, I might assign the following variable just after the database connection is made:
But then the question is -- 'how do I extract just the day, month, and year information from the getdate(); function and utilize it in the SELECT / FROM / WHERE statement? I see that you utilize $day, $month, and $year variables, but I'm just not sure how to assign those to specific values that are retrieved by getdate();
Again, I'm still rather new to PHP.
Re: Needing assistance with a few PHP code tweaks.
Posted: Thu May 07, 2009 6:33 am
by mickd
No problem
The getdate() function returns an array which holds all the information you need, so you would do something like this to access it.
Code: Select all
$today = getdate();
echo $today['seconds'];
// prints out the current seconds of the time
echo $today['minutes'];
echo $today['hours'];
echo $today['mday'];
// month day
//etc
// from the example of what else it has from the php manual
Array
(
[seconds] => 40
[minutes] => 58
[hours] => 21
[mday] => 17
[wday] => 2
[mon] => 6
[year] => 2003
[yday] => 167
[weekday] => Tuesday
[month] => June
[0] => 1055901520
)
// so you could do
$year = $today['year'];
// etc
Hope that's clear.
Re: Needing assistance with a few PHP code tweaks.
Posted: Thu May 07, 2009 2:28 pm
by pickle
Rather than worrying about all the date properties in PHP, just leave it to MySQL:
Code: Select all
SELECT
*
FROM
`pec_mssgs`
WHERE
`y` >= YEAR(NOW()) AND
`m` >= MONTH(NOW()) AND
`d` >= DAYOFMONTH(NOW()) AND
ORDER BY
`y` ASC,
`m` ASC,
`d` ASC,
`start_time` ASC
LIMIT 2
You're right though, it would be MUCH better to have the start date in it it's entirety in one field (either UNIX timestamp or MySQL DATETIME stamp).
Re: Needing assistance with a few PHP code tweaks.
Posted: Thu May 07, 2009 7:08 pm
by marconi
Thanks so much for these coding recommendations. I unfortunately don't have time to work on them tonight, but will try to get to it in the next day or so and then post the results.
Marconi
Re: Needing assistance with a few PHP code tweaks.
Posted: Fri May 08, 2009 5:43 pm
by marconi
Using a very slightly modified version of the recommendation made by Briney Mod (I had to remove the back ticks from around the column names in order to get the statement to work.), I have now managed to limit the output on the homepage so that it never shows more than the next two upcoming shows; Thank you! (See code below).
As mentioned in my initial posting, I now have two more refinements to take care of:
Refinement 2: The date and time for each show is currently formatted as follows: 5/1/2009 @ 04:00:00. I would like to change this to: May 1, 2009 @ 4:00pm.
Refinement 3: If no future shows are scheduled, I would like to post a message that says 'No events are currently scheduled". (I take it that this will be accomplished with an If / Else statement. I tried putting one together but could not get it to work.)
Any more insights and tips would be greatly appreciated.
Code: Select all
<?php
// Make an MySQL Connection
mysql_connect("", "", "") or die(mysql_error());
mysql_select_db("") or die(mysql_error());
// Get all the data from the "example" table
$result = mysql_query("SELECT * FROM table WHERE y >=Year(NOW()) AND m >=Month(NOW()) AND d >=DayOfMonth(NOW()) ORDER BY y ASC, m ASC, d ASC, start_time ASC LIMIT 0, 2") or die(mysql_error());
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
// Print out the contents of the specified rows into a table
echo "<tr><td>";
echo"<h4>";
echo $row['m']. "/". $row['d']. "/". $row['y']. " @ " .$row['start_time'];
echo"</h4>";
echo "</td></tr>";
echo "<tr><td>";
echo $row['title'];
echo "<tr><td> </td></tr>"; //This places a blank row between events.
echo "</td></tr>";
}
echo "</table>";}
?>
Re: Needing assistance with a few PHP code tweaks.
Posted: Fri May 08, 2009 5:47 pm
by Benjamin
I removed your database password from your post. Also, please use
Re: Needing assistance with a few PHP code tweaks.
Posted: Fri May 08, 2009 5:49 pm
by pickle
Inside your <h4></h4> is where you output the date. It might be easiest to build a UNIX timestamp with
mktime() and/or
strtotime(), then use
date() to get the date in a new format.
You can use
mysql_num_rows() on your resultset to see if there were 0 rows returned.
Re: Needing assistance with a few PHP code tweaks.
Posted: Fri May 08, 2009 6:24 pm
by marconi
Modster -- Thanks so much for catching my db oversight; been a long day. I went ahead and changed the actual pw as well.
Re: Needing assistance with a few PHP code tweaks.
Posted: Fri May 08, 2009 7:47 pm
by marconi
Pickle -
Two out of three; you are a lot of help. Following your mysql_num_rows() suggestion, I now have an If / Then conditional in place such that a message prints out whenever there are no future events stored in the table. (See big block of code below)
I remain a bit uncertain about formatting the printed date and time for events however. I'm able to create a variable that is set to the mktime() function, and understand how to then format and print the date based on that variable.
Code: Select all
$dt = mktime();
echo date('M d, Y @ g:iA', $dt);
However, it remains unclear to me how I then integrate this technique into the existing code so that the actual event dates being pulled from the table can be properly formatted. If you could be a bit more explicit in your suggestion, that would be great.
Thanks again.
Code: Select all
<?php
// Make an MySQL Connection
mysql_connect("", ""," ") or die(mysql_error());
mysql_select_db(" ") or die(mysql_error());
// Get all the data from the "example" table
$result = mysql_query("SELECT * FROM table WHERE y >=Year(NOW()) AND m >=Month(NOW()) AND d >=DayOfMonth(NOW()) ORDER BY y ASC, m ASC, d ASC, start_time ASC LIMIT 0, 2") or die(mysql_error());
// this gets the number of rows in result:
$numrows = mysql_num_rows($result);
// Conditional that says to print 'No events are listed at this time' if the number of rows in result is 0
if($numrows == 0){
echo "<H4> <I>No events are scheduled at this time. </I><H5>";
}
else{
echo "<table border='0'>";
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
// Print out the contents of the specified rows into a table
echo "<tr><td>";
echo"<h4>";
echo $row['m']. "/". $row['d']. "/". $row['y']. " @ " .$row['start_time'];
echo"</h4>";
echo "</td></tr>";
echo "<tr><td>";
echo $row['title'];
echo "<tr><td> </td></tr>"; //This places a blank row between events.
echo "</td></tr>";
}
echo "</table>";
}
?>
Re: Needing assistance with a few PHP code tweaks.
Posted: Fri May 08, 2009 10:38 pm
by mickd
Code: Select all
// example from the manual
echo mktime(0, 0, 0, 12, 32, 1997);
// mktime(hour, minute, second, month, day, year);
You would have to get it from your database, which is basically using the old code that printed the date, but put them into mktime instead.
Code: Select all
//replace this:
echo $row['m']. "/". $row['d']. "/". $row['y']. " @ " .$row['start_time'];
// with this:
// breaks apart the 04:00:00 into an array of 3 parts, split by :
$start_time = explode(':', $row['start_time']);
// puts all the information into mktime
$dt = mktime($start_time[0], $start_time[1], $start_time[2], $row['m'], $row['d'], $row['y']);
// displays it in your new order!
echo date('M d, Y @ g:iA', $dt);
Re: Needing assistance with a few PHP code tweaks. [Solved]
Posted: Sat May 09, 2009 11:23 am
by marconi
mickd -- it worked like a charm; thanks so much for all of your helpful input. As a total PHP newbie, getting this functionality to work has been a real challenge. I have learned tons from the exercise though. Anyway, in the interest of completeness and sharing with the community, all of the final code is posted below.
Code: Select all
<?php
// This is the code for the upcoming events snippet. It pulls 2 events from the calendar and posts them to the right column of the site.
// Make an MySQL Connection
mysql_connect("host", "user name", "password") or die(mysql_error());
mysql_select_db("database name") or die(mysql_error());
// Get all the data from the table
$result = mysql_query("SELECT * FROM table name WHERE y >=Year(NOW()) AND m >=Month(NOW()) AND d >=Day(NOW()) ORDER BY y ASC, m ASC, d ASC, start_time ASC LIMIT 0, 2") or die(mysql_error());
// this gets the number of rows in result:
$numrows = mysql_num_rows($result);
// Conditional that says to print 'No events are listed at this time' if the number of rows in result is 0
if($numrows == 0){
echo "<p>No upcoming events are scheduled at this time.</p>";
}
else{
echo "<table border='0'>";
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
// Print out the contents of the specified rows into a table
echo "<tr><td>";
// breaks apart the 04:00:00 into an array of 3 parts, separated by a ':'
$start_time = explode(':', $row['start_time']);
// puts all the information into mktime
$dt = mktime($start_time[0], $start_time[1], $start_time[2], $row['m'], $row['d'], $row['y']);
// displays it in your new order!
echo"<H4>";
echo date('M d, Y @ g:ia', $dt);
echo"</H4>";
echo "</td></tr>";
echo "<tr><td>";
echo $row['title'];
echo "<tr><td> </td></tr>"; //This places a blank row between events.
echo "</td></tr>";
}
echo "</table>";
}
?>