How to optimize research on .dbf files with dbase in PHP?

Question

I would like to know if you have some ideas/suggestions about the use of dbase in PHP.

Here's my problem, i have to "translate" a Foxpro script in PHP, and i'm using dbase since I have to deal with .DBF files. The problem is the time it takes for PHP to execute the script, because i didn't find a better way to translate the select queries than iterating through the entire file to select the data I need ...

And obviously, I reached the point where it takes too much time for PHP to render the script to the user. I know I can give the server more time to execute the PHP by modifying the php.ini file etc. but that's not a solution since this script is later going to be accessible to almost every user of the site i'm developping, so if it could go as fast as possible to render that would be really great ...

So, to sum up, what can I do to get a faster execution time with PHP on .dbf files queries ? How would you translate Foxpro's SELECT queries in PHP ? Do you recommend me to make this script in an other language than PHP ?

I give you an example of what i have to translate, it's just a small part of what i have to translate, and .dbf files are containing at least 50 000 lines with at least 10 columns each :/

FUNCTION 
PARAMETERS n_pc_id, l_pc_euro, n_sf_id, l_contrat
PRIVATE tb_res, mwhere
DIMENSION tb_res[1]
STORE 0 TO tb_res

IF n_sf_id = 0
IF l_contrat
    mwhere = "lig_mo.lo_gar = 'T' AND mo.mo_sf_id != m.pa_sfd_id"
ELSE
    mwhere = "lig_mo.lo_gar != 'T' AND mo.mo_sf_id != m.pa_sfd_id"
ENDIF
ELSE
IF l_contrat
    mwhere = "lig_mo.lo_gar = 'T' AND mo.mo_sf_id = n_sf_id"
ELSE
    mwhere = "lig_mo.lo_gar != 'T' AND mo.mo_sf_id = n_sf_id"
ENDIF
ENDIF

SELECT SUM(lig_mo.lo_qte);
FROM mo, lig_mo;
WHERE mo.mo_id = lig_mo.lo_mo_id;
AND (lig_mo.lo_pc_id = n_pc_id;
AND &mwhere;
AND lig_mo.lo_mo_id != 99999999);
AND lig_mo.lo_type IN ('F','V','B');
INTO ARRAY tb_res
RETURN tb_res[1]

Alan B Alan B · Accepted Answer · 2015-05-27T15:04:18

You definitely don't want to be doing it on a row basis. I would look at this discussion of accessing DBF files from PHP, then you can pass the (presumably Rushmore-optimised) query above straight into that, after changing it to use query parameters.

How to optimize research on .dbf files with dbase in PHP?

1 Answers