drop down menu list update *help*

[wizardry]

i can query the list fine using:

select list.list, many_table.list_id_fk from list left join many_table
on list.id = many_table.list_id_fk and many_table.userid = 'UserId';
 

that populates the list per user id.

now when the user selects a new item from the list
it does not update

here is the update query:

$updateSQL = sprintf("UPDATE list_many SET list_many_id_fk=%s, UserIdFk='1', `Date`=now() WHERE Id=%s",
GetSQLValueString($_POST['list_many_idfk'], "int"),
GetSQLValueString($_POST['UserIdFk'], "int"),
GetSQLValueString($_POST['Date'], "date"),
GetSQLValueString($_POST['Id'], "int"))
 

thanks in advance for your help!

theo

[TheServant]

Can I just ask why you are using that method to update your tables? Why haven't you used something like:

$list_many_idfk = $_POST['list_many_idfk'];
$userIdFk  $_POST['UserIdFk'];
$date  $_POST['Date'];
$id = $_POST['id'];
 
$result = mysql_query("UPDATE list_many SET list_many_id_fk=$list_many_id_fk, UserIdFk='1', `Date`=now() WHERE Id=$id");

I think that's much easier to read/debug etc... As I say, I don't know much about this method, but are you sure you can set default values for UserIDFk and Date?

[Dormilich]

in sprintf() there are only the indicated values (%s, …) replaced, you have 2x %s but 4 values, obviously, the last two are therefore not used.

[wizardry]

no problem neither did i, until i started programming it and using it. that method ( GetSQLValueString) calls a function which checks for sql injection, etc... you can log it if it fails, suspects, etc...

I prefer this method. less issues in the long run.

yes you can changed the %s to include a default record to set that should be in the where i switched them around. not worried about id. just the users record that is being updated.

************************************************** *********

ok -

this is what i'm trying to do.

pull data from a list table, for the fk in the many table and allow the user to update by using the drop down menu.

i have the drop down menu pulling the records and defaults on the record stored in the many table. its just the update is failing.

any help is appreciated.

ive tried doing a join update which works in mysql but php doesnt like it.

[Dormilich]

@wizardry
this should be no concern of PHP since it is only a string. if PHP doesn’t like it, there should be at least an notice/warning/error message.

[TheServant]

@wizardry
Interesting, thanks. Well because I don't know the function myself, and for the next 8 hrs I will not be able to test it out, I recommend you do this:
Make a simple MySQL query (using GetSQLValueString) and bit by bit add small components to change it in your code until it doesn't work - that will be your problem, or atleast one fo them. It's the long way round, but I think you will get a result quicker.

Failing that, wait until another expert comes along to this post and spots it without reading twice :P

[wizardry]

this has been resolved!

i needed to limit what the form was updating, just the list fk id and date that the new update was entered.

$updateSQL = sprintf("update ASBodyType as a
              left join BSType as b
        on b.Id = a.BodyType
        set a.BodyType=%s, a.Date=now()
             where a.UIdFk='1'",
           GetSQLValueString($_POST['BodyType'], "int"));

thanks again for your help!

[TheServant]

Your code looks a little different with the BodyType, so still unclear what the problem was, but glad you got it solved.