Page 1 of 1

ORDER BY DATE with IF/ELSE

Posted: Tue Aug 17, 2004 5:12 am
by sryuuza
I need to make it do that my part of my lists organize by date, and not
by number, i also need the numbers to list correctly, instead of 1,10,5,50
I dont want to add a leading zero to those numbers, and sometimes they
will even be like .5 and such... i believe i must make these declarations in the foreach field towards the bottom (note there are two foreach)

here is a link...
http://djmdevelopment.com/propertieslist.php

and here is the code

Code: Select all

<?php

//make the database connection
$conn = mysql_connect("**************", "**********", "*********");
mysql_select_db('djmdevelopment', $conn);

//organize data
$sql = 'SELECT * FROM propertiesList'; 

// add recognized names in here.. 
$recognized = array(
'id'=>array('id',0,1),
'date'=>array('date',0,1),
'model'=>array('model',0,1),
'sqft'=>array('sqft',0,1),
'price'=>array('price',0,1),
'bed'=>array('bed',0,1),
'bath'=>array('bath',0,1),
'garage'=>array('garage',0,1));

//DESC/ASC order
$addon = ''; 
foreach($_GET as $k => $v) 
{ 
  if(isset($recognized[$k])) 
    $addon .= (empty($addon) ? ' ORDER BY ' : ', ') . 
				($recognized[$k][0]) . 
				($recognized[$k][1 + (int)(bool)($v)] ? ' ASC' : ' DESC');
} 
 

$sql .= $addon; 

//create a query
$result = mysql_query($sql, $conn) or die(mysql_error());

print "<table id='maintb' align='center'>\n";
print "<tr bgcolor='#fdf8f0'>
		<td colspan='7' align='center' valign='middle'>
		<h1 id='toptitle'>Properties List</h1>
		</td>
		</tr>\n";

//get field names
//$kv = rand(0,1);
print "<tr id=releases_header>\n";
//while ($field = mysql_fetch_field($result)){
print "  
<th width='75' align='left'><a href='propertieslist.php?date=1'>Date</a></th>
<th width='150' align='left'><a href='propertieslist.php?model=1'>Model</a></th>
<th width='75' align='left'><a href='propertieslist.php?sqft=1'>Squareft</a></th>
<th width='75' align='left'><a href='propertieslist.php?price=1'>Price</a></th>
<th width='75' align='left'><a href='propertieslist.php?bed=1'>Bed</a></th>
<th width='75' align='left'><a href='propertieslist.php?bath=1'>Bath</a></th>
<th width='75' align='left'><a href='propertieslist.php?garage=1'>Garage</a></th>\n";
//end while
print "</tr>\n\n";

//get row data as an associative array
$counter = 1;
while ($row = mysql_fetch_assoc($result)){
	if ($counter++ % 2){
		print "<tr class=releases_rowstyle_dark>\n";
		} else {
		print "<tr class=releases_rowstyle_light>\n";
		} // end if;
	//look at each field
	foreach ($row as $col=>$val){
		if ($col !='id')
		print "  <td>$val</td>\n";
	} //end foreach
	print "</tr>\n\n";
}//end while

print "</table>\n";
?>

Posted: Tue Aug 17, 2004 10:31 am
by feyd
what column type is 'date' ? If it's an int or date types, it should sort correctly.. if it's a text type, either switch it to a numeric/date or sorting it by CHAR_LENGTH, then by value should sort it right..

Posted: Tue Aug 17, 2004 5:49 pm
by sryuuza
I am using the DATE function of MySQL, and it works great....

but how do i make it MM-DD-YYYY instead of the default YYYY-DD-MM ??

would i put that in my php as a if else statement in the foreach?

##EDIT##
Opps, it is defualt YYYY-MM-DD
##EDIT##

Posted: Tue Aug 17, 2004 5:58 pm
by feyd

Code: Select all

SELECT *, DATE_FORMAT(`date`, '%m-%d-%Y') `date` FROM propertiesList
http://dev.mysql.com/doc/mysql/en/Date_ ... tions.html