Jump to content

Archived

This topic is now archived and is closed to further replies.

ryanh_106

Access SQL query problems

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

Share this post


Link to post
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.

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

×

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.