[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 661
  • Last Modified:

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

0
webdork
Asked:
webdork
  • 8
  • 6
1 Solution
 
Dave BaldwinFixer of ProblemsCommented:
Since you have "ORDER BY work_id DESC" in your query, I don't understand what your question is.
0
 
webdorkAuthor Commented:
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.
0
 
Dave BaldwinFixer of ProblemsCommented:
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

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
webdorkAuthor Commented:
Here are the results:

DB snap shot image
0
 
webdorkAuthor Commented:
"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

0
 
Dave BaldwinFixer of ProblemsCommented:
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.
0
 
webdorkAuthor Commented:
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?
0
 
Dave BaldwinFixer of ProblemsCommented:
It is already ordering by work__id DESC, the code is working Perfectly.  However, if you look at the response you posted from phpMyAdmin above, you will see that it is returning by Date And Time, not just date.  That is where your problem is.

Make a new column that is just a DATE type.  Write a short procedure to copy the date from the "work_posted_on" column to the new column that is just the date.  Then use the new column for the date in all of your routines instead of the "work_posted_on" column.
0
 
webdorkAuthor Commented:
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?
0
 
webdorkAuthor Commented:
Great support. Which is typical of DaveBaldwin.
0
 
Dave BaldwinFixer of ProblemsCommented:
You're welcome, glad to help.
0
 
webdorkAuthor Commented:
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
0
 
Dave BaldwinFixer of ProblemsCommented:
The "short copy procedure" is to get the existing rows changed.  You can use CURDATE() as the default for new entries.
0
 
webdorkAuthor Commented:
Ok I see thanks.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now