Jump to content


Photo

Access SQL query problems


  • Please log in to reply
2 replies to this topic

#1 ryanh_106

ryanh_106
  • Members
  • PipPipPip
  • Advanced Member
  • 48 posts

Posted 13 June 2006 - 03:41 PM

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


$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
}



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

#2 trq

trq
  • Staff Alumni
  • Advanced Member
  • 31,041 posts

Posted 13 June 2006 - 03:48 PM

A few things....i think. For starters, the c 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...
$sql = "UPDATE Customers SET newPassword = '".md5($row['Password'])."' WHERE CustID = {$row['CustID']}";
Dont forget to change the Password field name.

#3 ryanh_106

ryanh_106
  • Members
  • PipPipPip
  • Advanced Member
  • 48 posts

Posted 14 June 2006 - 09:41 AM

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





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users