I have a postgres database with a table called workorders. In it is a column called date_out where the date is recorded like 2009-09-23. I want to sort this table data in a php page based on a users date range, ie, sort the table by date begin 2009-09-01 end 2009-09-31. Its for an accounts package I am creating for my company. Is there anyway I can do this. I can connect to my database but don't know how to sort the data. Also can I limit the number of views to say 15 lines, then onto a next page? Thanks for anyone that can help me out.
Add a comment
|
3 Answers
I'm not sure why you'd need PHP since this is a job which postgres can easily handle:
SELECT
visitdate
FROM
public.statistics
WHERE
visitdate
between date '2009-10-19' and date '2009-10-20'
ORDER BY
visitdate
OFFSET 2
LIMIT 3;
-
Thanks. I need it in php as my program is running through my apache server so i can access it anywhere i go. as i said, my knowledge on postgres is very small. how can i impliment your suggestion in php? thank yourussell– russell10/19/2009 20:13:52Commented Oct 19, 2009 at 20:13
-
I'm not a PHP programmer, but I think you might want to start with something like this: designmagick.com/article/10/Starting-Out/…p3t0r– p3t0r10/20/2009 05:22:04Commented Oct 20, 2009 at 5:22
I suggest to order the rows through SQL (ORDER BY clause). It will be the most efficient way, specially if the date_out column is indexed.
answered Oct 19, 2009 at 19:49
-
$date = $_POST['date']; $query = "SELECT * from workorders WHERE closed='Y' ORDER BY date_out '$date'";russell– russell10/19/2009 19:55:27Commented Oct 19, 2009 at 19:55
-
This is my sort code. I have another page which asks for a date and posts it to this page. When i run it, it says ###ERROR: non-integer constant in ORDER BY in /var/www/company/accounts/sales.php on line 31russell– russell10/19/2009 19:57:14Commented Oct 19, 2009 at 19:57
-
I am a complete newbie at postgres. Sorry ;)russell– russell10/19/2009 19:57:49Commented Oct 19, 2009 at 19:57
While you really should do this via SQL, php's strtotime should translate it to a timestamp you can use for integer based comparison.
answered Oct 20, 2009 at 19:55
