paginate this data

[styxlawyer]

Sorry, I don't have time to look at this now and probably not again before Thursday at the earliest. However, it looks as though the PHP code is now right and it's just your SQL queries which aren't working. I think you need to go back and re-read this post.

[ianhaney]

That's ok, I will have a look in the mean time and see if I can get it working and post a update if I manage to get it working

[ianhaney]

I think I have have a temp fix for now, it was what you said in a earlier reply about the date format in the db table, it was yyyy-mm-dd and the datepicker was in format dd-mm-yyyy so for now I have just altered the date format to yyyy-mm-dd and is working perfect now

for now will leave it like that until can do it in dd-mm-yyyy or until Thursday but main thing it is working, I'll do some googling in the mean time and see if can get it working under date format dd-mm-yyyy

[styxlawyer]

The whole point of the ISO 8601 format is that dates are always sequential. Use your existing date picker and manipulate the string from it so it matches that required by the database before you run the query. You'll need to use the PHP "explode()" function.

I might be back on Wednesday evening.

[ianhaney]

Ahh ok, that went bit over my head regarding the explode function, think I got the bit about manipulate the string so it matches before running the query but as not 100%

if can't solve it, if ok to wait until your back Wednesday/Thursday

[styxlawyer]

There are several options for making the two date strings match. Firstly, we have to know which of the following is correct and then we can progress.

.
the column called sales_month stores the date in the format of YYYY/MM/DD
.

.
it was yyyy-mm-dd and the datepicker was in format dd-mm-yyyy
.

I'll assume that the date format required for the database query is YYYY-MM-DD (the standard ISO 8601 format) but, if that's wrong, you'll need to change the code below.

The simplest way to fix this is to get the date-picker to produce a date string in the YYYY-MM-DD format. However, if that's not possible (or appropriate for your users), then the date-picker string needs to be broken down into it's constituent parts and reassembled in the correct format. It's important to remember that the format of the dates in the URI must always be the same whether the call to search-data.php comes from the date-picker page or is a recursive call from the search-data page itself. This narrows the choice down to two possible places where the translation can occur.

The first is in the date-picker page and, as this page is running on the browser, this will require some JavaScript. A simple function can translate the date codes from the date-picker into ISO 8601 format before they are inserted into the URI passed to search-data.php. That way no further translation is needed.

The second method is to translate the date-picker output immediately before the SQL query is assembled and, as this is server-side code, PHP will be required. Here's how to do that.

Firstly copy the function below and paste near the top of your page:

function translateDate($date) {
  $dateArray = explode("-", $date);   /* This creates an array of the three parts of the string in $date. */
  return $dateArray[2].'-'.$dateArray[1].'-'.$dateArray[0];  /* Now stitch the parts back together in the reverse order. */
} 

Now add a couple of calls to the function before assembling the query string (you'll have to do this in two places, once for query which establishes the size of the data and the second one for the retrieval of the data):

        $start_date = translateDate($d1); 
        $end_date = translateDate($d2); 
        $query = "SELECT * FROM purchased_software WHERE sales_month BETWEEN $start_date AND $end_date ORDER BY id LIMIT $start_from, $per_page"; 

If that doesn't fix it, you'll have to post the source of the page which is currently working.