asked on

PHP MySQL

How can I reorder a recordset in mySQL and PHP? I want order by work_id desc.

Here is the script calling to display the recordset

				 {if $dateList}

					   {foreach name=date from=$dateList item=date}

						   <div class="home-post-hd">{$date.work_posted_on|date_format:"%B %d, %Y"}</div>
						   {foreach name=work from=$date.date_work item=work}
                           	<div class="home-post-in">

								  <div class="home-post-in-des">

									<a href="post-description.php?work_id={$work.work_id}" style="color:#000000;"><strong>Title:</strong> {$work.work_title} - <strong>Aircraft:</strong> {$work.work_aircraft_type}</a> - <strong>Location (zip code):</strong> {$work.work_zipcode} <br />

									 <!--<strong>Description </strong>:- <a href="post-description.php?work_id={$work.work_id}">{$work.work_description|substr:0:10}</a><br />-->

								  </div>

							   </div>
                           {/foreach}
						   {*{foreach name=work from=$workList item=work}
							
						   {if $work.work_posted_on == $date.work_posted_on}

							   <div class="home-post-in">

								  <div class="home-post-in-des">

								   <a href="post-description.php?work_id={$work.work_id}" style="color:#000000;"><strong>Location (zip code):</strong> {$work.work_zipcode} - <strong>Title</strong>:- {$work.work_title} - <strong>Aircraft -:</strong> {$work.work_aircraft_type}</a> <br />

									 <strong>Description </strong>:- <a href="post-description.php?work_id={$work.work_id}">{$work.work_description|substr:0:10}</a><br />

								  </div>

							   </div>

							  {/if}					 

						   {/foreach}*}

					    {/foreach}
					 {*{if $pageLink}

					{$pageLink}

				{/if}*}

				

					{else}
					<center><div style="font-size:16px; color:#FF0000;">There are no Posts to Show.</div></center>

			      

				  {/if}

Open in new window

Here is the DB call:

	$dateList = array();
	
	foreach($resDate as $val )
	{	
		$workList = array();
		$resWork = $objDyn->getWorksByDate($val["work_posted_on"]);
		/*echo "<pre>";
			print_r($resWork);*/
		foreach($resWork as $data )
		{		
			$workList[] = $data;
			$i++;
			if($i == 10){
				break;
			}
		}
		$val['date_work'] = $workList;
		$dateList[] = $val;		
		if($i == 10){
			break;
		}
	}

Open in new window

Here is the Function

	function getWorksByDate($date)
	{
		$ObjClsDBInteraction = new class_dbconnector();
		
		$SQLSubPtype ="SELECT * FROM tbl_repair_works WHERE work_posted_on='".$date."' && work_status = 1 ORDER BY work_id DESC";
		if(isset($this->pageLimit) && $this->pageLimit != "" )
		{
			$SQLSubPtype .= $this->pageLimit;
		}
		$objRecordSubtype = $ObjClsDBInteraction -> select($SQLSubPtype) or die("Error=>".mysql_error());
			$works = array();
			while($row = mysql_fetch_array($objRecordSubtype))
			{	
				$works[] = $row;
			}
		return $works;
	}

Open in new window

Dave Baldwin

Since you have "ORDER BY work_id DESC" in your query, I don't understand what your question is.

webdork

ASKER

here is the repeat region: http://repairaplane.com/index.php

If you mouseover the job descriptions you can see that the order is not by work_id. The order is by "work_posted_on" which is a date field.

The record set does sort by this date field descending, but records on the same date don't respect the order by statement. I notice in the DB that the field "work_posted_on" records all have 0:00 for the hours minutes. Data type of this field in TIMESTAMP.

Dave Baldwin

A MySQL TIMESTAMP is the same as a Unix TIMESTAMP. It is an integer representing the number of seconds since Jan 1, 1970. There are 84,600 seconds in a day and even a 1 second difference is a different 'day'. If date is all you want, a DATE column that does not include the time would be better. It's not clear where you are getting $resDate from.

I suggest that you do a simple query to see what you are actually getting from the database. This should return the work_posted_on as a timestamp so you can see if they are actually different and the work_id's associated with them Change the LIMIT to whatever you need. If phpMyAdmin is available, it would be easy to try this there.

$Sql ="SELECT work_posted_on, work_id  FROM tbl_repair_works WHERE work_status = 1 ORDER BY work_posted_on DESC, work_id DESC LIMIT 25";

Open in new window

webdork

ASKER

Here are the results:

webdork

ASKER

"It's not clear where you are getting $resDate from."

$resDate = $objDyn->getDate();
	$i = 0;
	$dateList = array();
	
	foreach($resDate as $val )
	{	
		$workList = array();
		$resWork = $objDyn->getWorksByDate($val["work_posted_on"]);
		/*echo "<pre>";
			print_r($resWork);*/
		foreach($resWork as $data )
		{		
			$workList[] = $data;
			$i++;
			if($i == 10){
				break;
			}
		}
		$val['date_work'] = $workList;
		$dateList[] = $val;		
		if($i == 10){
			break;
		}
	}

Open in new window

Dave Baldwin

That shows exactly where your problem is. Your $resDate is not just a date but the date and time from a timestamp. Your code is actually working perfectly. To get just the date instead of date and time from a timestamp, it would be better to just use a DATE column instead of a TIMESTAMP. You still will have the problem of returning $resDate as unique dates instead of returning the same date over and over again. I guess the getdate() function is supposed to take care of that.

webdork

ASKER

OK... Not sure how to resolve. Are you suggesting changing the data type of the "work_posted_on" column? What are the ramifications?

Will this make the list order by work__id DESC?

ASKER CERTIFIED SOLUTION

Dave Baldwin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

webdork

ASKER

OK good tip.

Instead of a "short copy procedure" Can I just set the default of the new column to be GetDate() or whatever the correct MySQL syntax is?

webdork

ASKER

Great support. Which is typical of DaveBaldwin.

Dave Baldwin

You're welcome, glad to help.

webdork

ASKER

Dave: did you get a chance to look at my final question above?

Instead of a "short copy procedure" Can I just set the default of the new column to be GetDate() or whatever the correct MySQL syntax is?

Thanks,
WD

Dave Baldwin

The "short copy procedure" is to get the existing rows changed. You can use CURDATE() as the default for new entries.

webdork

ASKER

Ok I see thanks.