[SOLVED] update more than one table via form

[dflow]

i would like to acieve an update table

 

i have a form with ProposalID as primary and it is set with

RequestID as another key i would like to update the proposals_tbl

and the Requests_tbl at the same time from the same submit form button.

 

i would like to update the both Requests_tbl and proposals_tbl with StatusID

for example

[Bjom]

UPDATE proposals_tbl JOIN Requests_tbl ON proposals_tbl.RequestID = Requests_tbl.RequestID SET proposals_tbl.StatusID = 'newValue', Requests_tbl = 'newValue';

[dflow]

UPDATE proposals_tbl JOIN Requests_tbl ON proposals_tbl.RequestID = Requests_tbl.RequestID SET proposals_tbl.StatusID = 'newValue', Requests_tbl = 'newValue';

 

ok i tried:

entered this as a variable and as the action in the form

<?php
$update_tables="UPDATE proposals_tbl JOIN Requests_tbl ON proposals_tbl.RequestID = Requests_tbl.RequestID SET proposals_tbl.StatusID ='$StatusID', Requests_tbl = '$StatusID'";

?>
<form action="<?php echo $update_tables;?>" method="POST" name="form1" id="form1">
  
            <input name="RequestID" type="hidden" id="RequestID" value="<?php echo $_GET['RID']; ?>" />
            <input name="StatusID" type="hidden" id="StatusID" value="4" />
            <?php $StatusID='4';?>

 

what  am i doing wrong?

 

 

[Bjom]

I suggest that in the form action you put the name of a script like processsql.php

In that new script you need to:

a) open a connection to a database

b) execute that query

 

Have a good read here about database access

 

Bjom

[dflow]

I suggest that in the form action you put the name of a script like processsql.php

In that new script you need to:

a) open a connection to a database

b) execute that query

 

Have a good read here about database access

 

Bjom

 

ok i tried this new approach

i still need to update all the fields in the proposals table

and update at the end the contact_form.StatusID

<?php
$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("international", $con);

mysql_query("UPDATE proposals, contact_form
     SET contact_form.StatusID = '$StatusID' AND proposals.StatusID= '$StatusID'
        WHERE proposals.RequestID = contact_form.RequestID
        AND proposals.ProposalID = '3'");





mysql_close($con);
?> 

 

what am i dong wrong?

[Bjom]

Looks good...what is the issue?

 

I strongly suggest to never use "die" user trigger_error instead - it can be controlled via error reporting levels.

 

this might help:

var_dump($StatusID);
mysql_query("UPDATE proposals, contact_form
     SET contact_form.StatusID = '$StatusID' AND proposals.StatusID= '$StatusID'
        WHERE proposals.RequestID = contact_form.RequestID
        AND proposals.ProposalID = '3'") or trigger_error(mysql_error(),E_USER_ERROR);

[dflow]

actually it just isn't updating the StatusID fields in both tables

 

i set a value for StatusID but nothing in the DB

[Bjom]

did you try my code? what does the var_dump give you?

[dflow]

did you try my code? what does the var_dump give you?

I set StatusID=43

result echoed:

 

string(2) "43"

[Bjom]

ok. so that variable has a value, as it should.

 

oops just seen a thing. You altered that query not only to comma join but also added a "AND" which is syntactically wrong. Change it like this:

 

("UPDATE proposals, contact_form
     SET contact_form.StatusID = '$StatusID', proposals.StatusID= '$StatusID'
        WHERE proposals.RequestID = contact_form.RequestID
        AND proposals.ProposalID = '3'") or trigger_error(mysql_error(),E_USER_ERROR);

 

P.S: Rather: It is not syntactically wrong - that's why you don't get an error. It is nonsensical.

[dflow]

ok. so that variable has a value, as it should.

 

oops just seen a thing. You altered that query not only to comma join but also added a "AND" which is syntactically wrong. Change it like this:

 

("UPDATE proposals, contact_form
     SET contact_form.StatusID = '$StatusID', proposals.StatusID= '$StatusID'
        WHERE proposals.RequestID = contact_form.RequestID
        AND proposals.ProposalID = '3'") or trigger_error(mysql_error(),E_USER_ERROR);

 

P.S: Rather: It is not syntactically wrong - that's why you don't get an error. It is nonsensical.

 

changed the nonsensical typo

 

but still no result  in the tables

[Bjom]

no error?

 

replace the variable with a value for testing purposes and also run this select to see if you get anything out of the DB with those settings:

 

SELECT * FROM proposals, contact_form WHERE proposals.RequestID = contact_form.RequestID AND proposals.ProposalID = '3'

 

 

 

[Bjom]

is the proposalID an integer? remove the '' around it.

 

Dude and try stuff!

[dflow]

no error?

 

replace the variable with a value for testing purposes and also run this select to see if you get anything out of the DB with those settings:

 

SELECT * FROM proposals, contact_form WHERE proposals.RequestID = contact_form.RequestID AND proposals.ProposalID = '3'

 

 

 

 

ok it works now

sql testing in the phpmyadmin works

 

both the update and select return no errors

 

the script works as well

 

thank you