I have referredd to the links ou indicated, and this is the code i have come up with. But this doesnt seem to work either. I guess the error is based on the usage of ("") , ('') and (``) at different points in the query.
Somebody please clarify... with the code...
$res = select * from table1 where column1 = 'value';
mysql_query("Update table2 set col2 = '$res[`1`]' ");
// retreiving data from table 1
$r1 = mysql_query("Select * from table1 where Column1 = '$test' ") or die(mysql_error());
// updating the other table
while($row = mysql_fetch_array($r1))
$value2 = $row['Rate'];
$query = mysql_query("update table2 set amt = '$value2' ");
If you run the same select query from navicat, it works fine, but, if you run it from your script, it doesn't return any records ? I can say this for sure. Something is wrong with your script. You are doing something wrong while building dynamic query.
When you place user input into an SQL query, always make sure you escape it for SQL injections (used hack into your database). For Strings you can use mysql_real_escape_string() or mysql_escape_string() depending on support for it. If its an integer, then just use intval().
mysql_query("update table2 set col2 = ( Select col1 from table1 where col3 = '".mysql_real_escape_string($_POST['Tests'])."') where ID = '".intval($_POST['ID'])."'") or mysql_error();
In mysql, the Integer fields can be treated as strings, but for portability its good to not have qoutes around field values that are integers as it would most likely not work on other SQL databases.
Also, its good to optimize the queries. A simple optimization is to use the LIMIT keyword.
mysql_query("update table2 set col2 = ( Select col1 from table1 where col3 = '".mysql_real_escape_string($_POST['Tests'])."' LIMIT 1) where ID = ".intval($_POST['ID'])." LIMIT 1") or mysql_error();
On large datasets LIMIT can really speed up the query. What happens is the database does not have to go through its whole index, but instead stop on the first result that matches the WHERE portion of the query.