Ordering rows

webdiva

Hi,

I need some PHP Code help.

The website is: http://www.glenridgeseniorcenter.com

On the side of the page, there is a part called "Upcoming Events"

I have it pulling the rows from the database.

However, when I enter the information into the database, it puts that row first, and is displaying the information out of order.

I want the dates to be in order. I'm not sure how to do it. Here is the code I have right now:

<?
$username="";
$password="";
$database="";

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$result=mysql_query("select * from event_2 where month=". 

date("n"));
for($i=1;$i<=mysql_num_rows($result);$i++){
						$row=mysql_fetch_array($result);
?>
<b><? echo $row['month'] ?>/<? echo $row['day'] ?></b> - <? echo $row['event'] ?><br>




          <?
	}
	?>

paulnaj

Hi,

Use an ORDER BY clause in your SQL.

Paul.

webdiva

Can you help me out with adding it? Im new to PHP and only know minor stuff about it.

paulnaj

Hi again,

Thing is, we don't edit code for people here, just try and help them understand. This requires some effort on your part.

Read the MySQL Tutorial ... it's especially for people trying to get started.

In particular the section on sorting rows ... you'll be pleasantly surprised how easy it is!!

Paul 🙂

leatherback

Hi Webdiva,

The order by clause besically tells the script how the output shoudl be sorted. In your case, you want it to be sorted by date, and probably descending (Newest first:

As the end of the $query you would add:

order by date desc

$result=mysql_query("select * from event_2 where month=". date("n")."order by date desc");

This is not tested, but should help you get the idea.

Oh, I just noticed.. you do not have a date colum, but day / month / .. colums. This is a flaw in the database design. But you now know how to sort by a certain colum. You need now to sort by more then one colum. You can do that, by placing a comma between different fields.

void_function

I wouldn't call it a flaw, but a matter of elegancy. Nothing wrong with using separate column for day and month, but maybe you should consider, webdiva, using the DATE type:

http://dev.mysql.com/doc/mysql/en/datetime.html

and functions to compare dates and extract date elements:

http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html

So, for example, the elegant way, for your case, using date as a DATE type, would be:

"SELECT * FROM event_2 WHERE MONTH(date)=".date("n")."ORDER BY date"

I assume you'd want ascending sorting (ie. starting from the first event to occur), so don't type anything (DESC) after "ORDER BY date".

webdiva

This is the following code I have altered:

<?
$username="glensen_glen";
$password="pass";
$database="glensen_features";

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$result=mysql_query("SELECT * FROM event_2 WHERE MONTH(day)=".date("n")."ORDER BY day");
					for($i=1;$i<=mysql_num_rows($result);$i++){
						$row=mysql_fetch_array($result);
?>
<b><? echo $row['month'] ?>/<? echo $row['day'] ?></b> - 
<? echo $row['event'] ?><br>
              <?
		}
		?>

And now I am recieving this error:
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/glensen/public_html/upevents.php on line 9

leatherback

Hi,

THat means that you are trying to retrieve the number of rows from a non-existing result dataset. THis usually happens when something goes wrong in your query.

After a query, you should perform some error trapping/ Easiets would be to add

or die('Something went wrong: '. mysql_error())

after the mysql_query(...) command.

void_function

In addition to what leatherback said, did you alter your table and change the "day" column into a DATE type?