PHP connect DBF file

DBF is very old format databse type. About 10 years ago, I use dBase which use DBF format.
Now I will show you how to connect the DBF file from PHP script.
1) Add a system DSN in ODBC Data Source Administrator
I have a source named dd.


system_dsn

2) Make a file named odbc.php, the content is:

 <?php
 $odbc = odbc_connect ('dd', '', '') or die('Could Not Connect to ODBC Database!');
 ?>

3) In the PHP file, which needs to connect this database, I use the following script.

require_once('odbc.php');
$strsql= 'SELECT * FROM database.dbf';
$query = odbc_exec($odbc, $strsql) or die (odbc_errormsg());
while($row = odbc_fetch_array($query))
{
echo 'Client Name: '.$row['name'].'&lt;br /&gt;';
echo 'Client Phone Number: '.$row['phone'].'&lt;br /&gt;';
echo '&lt;hr /&gt;';
}
odbc_close($odbc);

Use this way, PHP can connect the old database file, like dBase, Foxpro.

32 Comments


  1. hello david, can you post again something similar like this, a way on how to connect dbf files in php? because this post was 8 years old already and the way on how it is donw i believe has already changed. Thanks in advance!

  2. how to connect a php file with fox pro?

  3. How can I do inserting data into dbf?

  4. How can I do inserting data into dbf?

  5. Is the database.dbf file in the same location as the php file? where should it be?

  6. Is the database.dbf file in the same location as the php file? where should it be?

  7. Hi David,
    thanks for sharing, the script running great. But I’m still facing problem how to reindex the dbf files. Could you help me David ?
    Thanks.

  8. Hi Hanh,
    ODBC for dbf files is based on folder. Your example has two folders, so you have to setup two ODBC connection for them.

  9. Hi David
    I have free tables in the structure like this:
    Folder\file1.dbf
    Folder\file2.dbf
    Folder\SubFolder\file3.dbf
    How can I create one ODBC and how to call out the file3.dbf for use in PHP?
    And I want to run a query which joins file1 with file3 – Can I do that with this structure?
    Thank you.

  10. ============================================
    Warning: odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][ODBC dBase Driver] External table is not in the expected format., SQL state S1000 in SQLExecDirect in C:\AppServ\www\crm.php on line 4
    [Microsoft][ODBC dBase Driver] External table is not in the expected format.
    =============================================
    I facing uxpected format problem
    What is the problem?
    odbc_exec()

  11. 1. The connection works fine (I receive no “die” )message
    2. The dbase file is found ok
    3. The querry string is somehow OK but didn’t echo/display any result.

  12. how to filter date using sql query?

  13. Hello,
    Do you know how to count the number of records in the dbf table?
    Thanks!

  14. Thanks for your tutorial!
    I cannot access the data from a .dbf file,
    but I have another question,
    can I join 2 tables together(inner join, full join…etc)?
    I tried:
    $cost_92808a = “cost_92808a.dbf”;
    $TF_SOR_92808_B = “TF_SOR_92808_B.dbf”;
    $strsql= ‘SELECT C.COST, C.DESC, D.item_no FROM ‘.$cost_92808a.’ as C JOIN ‘.$TF_SOR_92808_B.’ as D ON (C.ITEM_NO = D.ITEM_NO)’;
    and I got such error:
    Warning: odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][Pilote ODBC dBase] Syntax error in clause FROM., SQL state 37000 in SQLExecDirect in …
    [Microsoft][Pilote ODBC dBase] Syntax error in FROM clause.
    So, I would like to ask if dbf tables can be joining together?

  15. Hi David. I can also recommend trying the recover mssql program that automatically eliminates dbf errors and save the output data in desired format.

  16. Dear David,
    id like to say thanks for your help. you are right about my sql script. there is a variable that .dbf table cant accept.
    thank you very much for your help.
    Best Regards,
    Anif

  17. When construct your sql script, the variable should be formatted well.
    Here is an example.
    strsql = ‘insert INTO d_spmind.dbf(nospm, tgspm) values (“abc”,”‘.$some_variable.'”);

  18. Hi david i have new news about the error…
    when i use values without variable in my query it succeed. this is the query without variable
    {code removed}
    thx in advanced and hope you can help me.
    Regards,
    Anif

  19. I think the sql script you use to insert data has some invalid keywords.
    show me the sql script.

  20. Dear David,
    first i would like to thanks for the answer for my last question. Now I have a new problem. i’ve try to insert at some table and it’s succeed but for the last table its failed. it show this error:
    Message: odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][ODBC Visual FoxPro Driver]Command contains unrecognized phrase/keyword., SQL state 37000 in SQLExecDirect
    Filename: controllers/smarti_spm.php
    Line Number: 395
    [Microsoft][ODBC Visual FoxPro Driver]Command contains unrecognized phrase/keyword.
    i dont know what’s wrong with it. i’ve try the query using sql editor and it’s work but it cant work in my PHP program. is there any reason because the table has 140 column? i hope you can help me with this problem.
    thanks in advanced,
    Anif

  21. dear david,
    i’ve try your way in selecting from .dbf. but how do i inserting into .dbf using odbc?
    thx in advance for your answer,
    Anif

  22. The dbf files are saved in any folder. Say, I have three dbf files in c:\data-dbf\.
    When I add system DSN and select Microsoft dBase drive, it will let me choose the folder.
    Any dbf file in this folder can be accessed by this way.

  23. Dear David,
    where are your databases (dbf)? I don’t know that where should i put it?
    Pls advise me.
    Many thanks,
    Jack

  24. Hi David.
    I am trying to connect to a .dbf file via php.
    I tried your to follow your instructions.
    1. Created a “dd” connection via my ODBC admin
    2. Created both .php files
    3. Created a small dbf file with three name/phone records.
    When I run the main php script, I receive the following error message:
    Warning: odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][Pilote ODBC dBase] Syntax error in clause FROM., SQL state 37000 in SQLExecDirect in c:\Web\essai\testdbase.php on line 5
    [Microsoft][Pilote ODBC dBase] Syntax error in FROM clause.

    The files are currently on my test server. You can test them at:
    http://www.pconno.gotdns.com/essai/testdbase.php
    Seems that:
    1. The connection works fine (I receive no “die” )message
    2. The dbase file is found ok
    3. The querry string is somehow defective.
    Would you have any suggestion?
    Thanks a lot,
    Pierre

  25. I have get back to you by email. I think it should be OK now.

Leave a Reply

Your email address will not be published. Required fields are marked *