up vote 0 down vote favorite

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.

link|flag
60% accept rate

3 Answers

up vote 2 down vote accepted

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;
link|flag
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 you – russell Oct 19 '09 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 Oct 20 '09 at 5:22
up vote 0 down vote

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.

link|flag
$date = $_POST['date']; $query = "SELECT * from workorders WHERE closed='Y' ORDER BY date_out '$date'"; – russell Oct 19 '09 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 31 – russell Oct 19 '09 at 19:57
I am a complete newbie at postgres. Sorry ;) – russell Oct 19 '09 at 19:57
up vote 0 down vote

While you really should do this via SQL, php's strtotime should translate it to a timestamp you can use for integer based comparison.

link|flag

Your Answer

 
or
never shown

Not the answer you're looking for? Browse other questions tagged or ask your own question.