Access- ODBC/PHP error

Hi,

Im currently working on linking my web page with an access database using PHP and and ODBC. I have the following code to input a new record in a table and display the old and new data. I know its fairly basic but im just trying to learn how the odbc connection works, ive done some work with php/mysql but never with access/odbc. The code:

<?php

function HTML_Head() {
    echo "
    <HTML><HEAD>
    <TITLE>Processing Form</TITLE>
    </HEAD>
    <BODY BGCOLOR=\"#D5D5AB\">";
}

function HTML_Foot() {
    echo "</body></html>";
}

function Database_Entries($msg) {
    echo $msg;
}

function Output_Entries() {
    /*
        Make the connection to the database. The syntax is
        odbc_connect( 'SYSTEM_DSN' , 'USER', 'PASSWORD' );
        $cnx will hold the
        pconnect is used to establish a persistent database
        connection to the Database until the procedure is completed.
    */

    $cnx = odbc_connect( 'WebTute' , 'root', '' );
    if (!$cnx) {
        Error_handler( "Error in odbc_connect" , $cnx );
    }

    // send a simple odbc query . returns an odbc cursor
    $cur= odbc_exec( $cnx, "select Index,FirstName,LastName,PhoneNumber from People" );
    if (!$cur) {
        Error_handler( "Error in odbc_exec( no cursor returned ) " , $cnx );
    }
    echo "<table border=1><tr><th>Index</th><th>First Name</th>".
        "<th>Last Name</th><th>Phone Number</th></tr>\n";
    $nbrow=0;   //Local variable to count number of rows

    // fetch the succesive result rows
    while( odbc_fetch_row( $cur ) ) {
        $nbrow++;
        $Index= odbc_result( $cur, 1 ); // get the field "Index"
        $FirstName= odbc_result( $cur, 2 ); // get the field "FirstName"
        $LastName= odbc_result( $cur, 3 ); // get the field "LastName"
        $PhoneNumber= odbc_result( $cur, 4 ); // get the field "PhoneNumber"

        echo "<tr><td>$Index</td><td>$FirstName</td>".
            "<td>$LastName</td><td>$PhoneNumber</td></tr>\n";
    }

    echo "<tr><td colspan=2>$nbrow entries </td></tr></table>";

    // close the connection. important if persistent connection are "On"
    odbc_close( $cnx);
}

function Error_Handler( $msg, $cnx ) {
    echo "$msg \n";
    odbc_close( $cnx);
    exit();
}

function Enter_New_Entry($FirstName,$LastName,$PhoneNumber) {

    /*
        First, we create a connection to our ODBC source. This is done by creating
        a connection. Once this is done, we are returned an ODBC connection number.
        We use this number to use the ODBC functions within PHP.
    */

    $cnx = odbc_connect( 'WebTute' , 'root', '' );
    if (!$cnx) {
        Error_handler( "Error in odbc_connect" , $cnx );
    }
    $SQL_Exec_String = "Insert Into People (FirstName, LastName, PhoneNumber)
            Values ('$FirstName', '$LastName', '$PhoneNumber')";

    $cur= odbc_exec( $cnx, $SQL_Exec_String );
    if (!$cur) {
        Error_handler( "Error in odbc_exec( no cursor returned ) " , $cnx );
    }

    odbc_close( $cnx);
}

$strOldEntries = "Previous Entries in database";
$strNewEntries = "Updated version of databse (after entries)";

HTML_Head();
Database_Entries($strOldEntries);
Output_Entries();
Enter_New_Entry($FirstName,$LastName,$PhoneNumber);
Database_Entries($strNewEntries);
Output_Entries();
HTML_Foot();

?>

This generates a connection as i can see the current data in the db but it does not input new data from my form, it brings an error msg referring to odbc_exec section:

'Warning: odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression., SQL state 22005 in SQLExecDirect in C:\Program Files\Apache Group\Apache2\htdocs\webpages\mygym\DataAccess.php on line 81
Error in odbc_exec( no cursor returned )'

This in turn refers to the $SQL_Exec_String, where the data is inserted. The table has another column that is just an auto number so is not referenced in the insert statement, this is the only think i can think of that is causing the problem.

Any Ideas?

Cheers
LVL 2
generaliAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

flavoCommented:
<Wild Guess>

Do any of the Names contain a '  (ie O'Brien?)

Dave
0
flavoCommented:
ahh.. Shouldn't have hit submit.

If so, then you need to replace the single ' with double '

Not sure of the PHP syntax, but it would be like this in VBA

"Insert Into People (FirstName, LastName, PhoneNumber) Values ('" & Replace(FirstName, "'", "''") & "', '" & Replace(lasttName, "'", "''") & "', '$PhoneNumber')";

Dave

0
flavoCommented:
>Data type mismatch in criteria expression.,

Given that I can't read PHP, the following maybe a problem

Text fields need to be wraped in ' or "
Date fields need to be wraped in #
Numeric fields don't need any wrappings (They don't like Christams :P)

So, your SQL needs to be interperated as:

"Insert Into People (FirstName, LastName, PhoneNumber)
            Values ('FirstNameValue', 'LastNameValue', '555-100-100')"
           
Assuming all fields are text.  
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

generaliAuthor Commented:
Thanks for the comments

Just had another play around with the wrappings etc. I removed the input of the PhoneNumber as this is formatted to number in the DB and the input was successful, except for the fact that it did'nt enter the text for firstname and last name, just the auto number for index......great!!

....but there was no error with the data type mismatch so its the number value that it doesnt like. ive changed the format of the number to text as i would normally format a phone number as text (been working from someone elses example), and also re wrote the form and action page which now works fine.

so im ok with any inserts that are text, but will become unstuck if i need to insert a number, ive tried no quotes, double quotes and it doesnt like either, any more suggestions?

0
flavoCommented:
Let's say your phonenumber was still Numeric, then I would have assumed this would have worked:


    $SQL_Exec_String = "Insert Into People (FirstName, LastName, PhoneNumber)
            Values ('$FirstName', '$LastName', $PhoneNumber)";

If not, can you post the example that does work, I'll see what I can work out.

Dave
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
generaliAuthor Commented:
Thats what i thought but it returns an error with regards to the sql syntax when i tried that.

It works like this:

$SQL_Exec_String = "Insert Into People (FirstName, LastName, PhoneNumber)
            Values ('$FirstName', '$LastName', '$PhoneNumber')";

but only if the field PhoneNumber is set to text.
0
flavoCommented:
hmm.. According to this post : http:Q_20958626.html#10857389  I thought I would have got it right :(

I can ask some PHP Experts to look at this if you want?

Dave
0
generaliAuthor Commented:
Feel like a bit of an idiot now......

just checked with the new scripts i created and it works fine without the '' around the variable holding the phonenumber, so the problem arose from the original example script i was working from.

Many Thanks for the help
0
flavoCommented:
Glad to help :^)

Dave
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.