Jump to content

Access SQL query problems


ryanh_106

Recommended Posts

Hi

I am trying to manipulate a microsoft access database (with DSN) using ODBC funcitons in PHP. I can connect, close and query fine but it will not let me run this update command. Can anyone tell me why not I have been searching the internet for hours


[code]$sql = "SELECT * FROM Customers WHERE 1";
$myresults = db_query($sql);  // Wraps odbc_exec

print '<br /><br />I have '.no_of_results($myresults).' Customers, They Are:<br />';
print_results($myresults); // Wraps odbc_result_all

while ($row = next_row($myresults)) { // next_row = odbc_fetch_array
    $sql = "UPDATE Customers c SET c.Password = \"".md5($row['Password'])."\" WHERE c.CustID = '{$row['CustID']}'";
    db_update($sql); // Wraps odbc_exec
}

[/code]


Error is:

Could not execute update, Error message:
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.!
Query was:
UPDATE Customers c SET c.Password = "a029d0df84eb5549c641e04a9ef389e5" WHERE c.CustID = '1'

(its a nice error I defined to include the original query thats not straight from ODBC obviously)
Tried with all types of quotes, the field names are definately right, only thing i can poss think may be causing it is CustID is auto number??? could that be it?

Please help

Cheers
Link to comment
Share on other sites

A few things....i think. For starters, the [i]c[/i] bit, I dont understand it.

Password is more than likely a reserved word in access so i would change that filed name.

Access does not like INTEGER values being surrounded by quotes, and all other values should be surrounded by single quotes.

Try...
[code]
$sql = "UPDATE Customers SET newPassword = '".md5($row['Password'])."' WHERE CustID = {$row['CustID']}";
[/code]
Dont forget to change the Password field name.
Link to comment
Share on other sites

Thanks for that ace advice,

Its funny as soon as you mention it I have had trouble using the word Password in a MySQL database before

Anyway I will look at that again later thank you

and the c is a reference name for the customers name. In SQL writing
FROM Customers c
means later on you can refer to fields as c.username etc. Mainly for if you are doing are doing a join query with two tables that contain fields with the same name, it was just something i tried, heres an example

SELECT CustName, OrderNumber
FROM Customers c, Odrers o
WHERE (CustName = 'Me' AND c.CustID = o.CustID)

Anyway, there you have it

Thanks for the help
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.