how to update date in a bridge table

guxin1999

In a bridge table, 4 keys:
userid, groupid, lastvisittime, totalvisit.
userid and groupid are foreign keys.

I can't update the lastvisittime and totalvisit.

My SQL is like:

$sql = "update usergroupline01 set lastvisittime='$now'where userid='$userid' and groupid='$groupid';";

Thanks for your help.

[deleted]

And what is the error message? What does the script do?

guxin1999

Error message is:
Warning: SQL error: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression., SQL state 22005 in SQLExecDirect in H:\xxxx\xxxx\xxxx\xxxx\msginfopage.php on line 40
Unable to prepare statement: update.

My script:
$sql = "update usergroupline01 set lastvisittime='$now'
where userid='$userid' and groupid='$groupid';";

I want to update the date when user last visit the group.

Thanks Vincent!

Anon

I'm going to guess that your userid and groupid are INT type and you are putting them in as STRING type so, you need to eliminate the single quotes around those two columns, i.e.:

$sql = "update usergroupline01 set lastvisittime='$now' where userid=$userid and groupid=$groupid;";

the other possibility would be that the lastvisittime is a DATE type and the $now variable contains a different/unsupported format or unusable data.

Anon

When I inserted data to this bridge table, I used the same data type, it was fine. And the data can be inserted into the table.

upadte "userid, groupid, lastvisittime" are all the same datatype as when I insert them.

So I am confused what did I do wrong?

Thanks for your help.

Anon

look carefully at the difference between the two sql statements below:

$sql = "update usergroupline01 set lastvisittime='$now' where userid='$userid' and groupid='$groupid';";

$sql = "update usergroupline01 set lastvisittime='$now' where userid=$userid and groupid=$groupid;";

the second one (the one I'm suggesting you try), does not have single quotes around the $userid and $groupid variables. By enclosing them in quotes, you are attempting to insert them as a string type instead of as a INT type (wihch I assume the table is expecting).

gio2k

May be. MySQL usually converts quoted values to the appropriate data type. But I've seen cases where it just doesn't.
I find this 'feature' very disturbing, because it confuses the programmer.

Anyway, I haven't worked with ODBC and PHP before, so maybe I'm going to make some dumb suggestions :-p

Delete the ";" at the end of your SQL code, and see if it works. In MySQL you don't have to add the extra semicolon, maybe it's the same for ODBC.
DEBUG:

Start by typing a simple: echo $sql; before you execute the code, to see how your SQL is being sent.

Check for the error message:
$res = odbc_exec($sql);
if(! $res)
{
echo odbc_errormsg();
}

Finally, if you don't see anything wrong with your query, and the error message displayed by ODBC doesn't give you a hint, just copy the SQL statement generated by your echo $sql; statement and execute it in the SQL tool that comes with your DBMS. Native SQL Query tools provide almost always with a better description of the error.

Hope that will help you. :-)

-Gio-