insrting from one table to another

[akshit]

hi guys...

i wanna select a single cell (not full row) from one MySQL table and insert the value into another table. The datatype of the (value being extracted) and the (column of insertion) are the same.

somebody pls help...

[akshit]

well i guess the problem is that when i use the command

$res = select * from table where column = 'value';

then AN ENTIRE RECORD is returned into $res.

Then, when i try to assign the value of $res into the other table, i am trying to assign a RECORD into a COLUMN, which is probably causing he error....

in case this is correct, how do i get over this??

[nav33n]

This is how you fetch the record and this is how you insert it.

[akshit]

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...

MY CODE

$res = select * from table1 where column1 = 'value';
mysql_query("Update table2 set col2 = '$res[`1`]' ");

thx...

[nav33n]

Okay. This is how you do it.

$res = mysql_query("select * from table1 where column1='value'"); 
while($row = mysql_fetch_array($res)) {
$value2 = $row['column2']; //assign column2 value to $value2
$query = mysql_query("update table2 set col2='$value2'"); //update col2 in table2
}
?>

[akshit]

i tried your solution... it does not produce an error, but no UPDATE occurs...

[nav33n]

Post your code. Lets see where you have gone wrong.

[akshit]

The CODE

// 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' ");
}

plz help...

thx...

[nav33n]

Before while have this.
print mysql_num_rows($r1);
I believe, your query doesn't return any records. :)
The above print statement, will print the number of records found for the select query.

[akshit]

Before while have this. print mysql_num_rows($r1); I believe, your query doesn't return any records. :) The above print statement, will print the number of records found for the select query.

Hey nav33n.

You were right. IO tried that command an it showed 0, indicating that no rows were returned by SQL.

Now pls suggest what i shud do...

i shall be highly grateful fr ur co-operation.

thx a lot...

[akshit]

PS. I tried running the SELECT query from the SQL GUI (naviCAT), and i gave me the perfect result....

[nav33n]

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.

[digital-ether]

If you want to select just one column, then make sure you ask for just one column. If you only need one row, then limit the results to one row...

eg:

SELECT column1 from table1 where {condition} LIMIT 1

The insert you want can be done with the result of the first query, or you can do the select as a subquery:

INSERT INTO table2 SET column1 = (SELECT column1 FROM table1 WHERE {condition} LIMIT1)

Of course {condition} should be replaced with your WHERE condition.

Subqueries should work on MySQL 4.1 and later I believe...

[antwan1986]

Silly question I'm sure but have you got both your connection and select database strings? i.e. mysql_conn and mysql_select_db ?

Anthony

[akshit]

i tried this in the GUI (navicat), and it worked just perfect, but when i run it from my script, it doesnt work.

In the GUI, i tried--

update table2 set col2  = ( Select col1 from table1 where col3  = 'value' ) where ID = 5

(i chose a static vaue of 'id' and 'value' here, but in actual, have to use dynamic value for both, depending on user input coming from previous page, which is a form)

in the script(PHP), i wrote ---

mysql_query(" update table2 set col2  = ( Select col1 from table1 where col3  = '$_POST["Tests"]' ) where ID = '$_POST["ID"]' ")  or mysql_error()

PS-- "Tests" and "ID" are 2 fields entered by the user in the form on the previous page.

When i run this command from my PHP script, it dos not return any error, bu no updating occurs....

wat may be wrong?

pls suggest...

THX...

[nav33n]

mysql_query(" update table2 set col2 = ( Select col1 from table1 where col3 = '$_POST["Tests"]' ) where ID = '$_POST["ID"]' ") or mysql_error()

Are you sure this didn't give any error ? I am 100% sure this will generate an error (because of the mixup of' and "). Try this.

mysql_query("update table2 set col2 = ( Select col1 from table1 where col3 = '".$_POST['Tests']."') where ID = '".$_POST['ID']."'") or mysql_error();

P.S. Also check if $_POST['ID'] and $_POST['Tests'] is empty.

[akshit]

P.S. Also check if $_POST['ID'] and $_POST['Tests'] is empty.

Thx a lot buddy.... u just got it right... i checked up, and as u suggested, the "$_POST['Tests']" variable was blank.... the reason being (OK, now pls dont beat me up)....

i the previous page(the form where all data is collected, i used 'T'ests), whereas in the script, i was using $_POST["tests"].....

thx 4 pointing that out.... i wud never hav managed without u pointing it out...

thx a lot....

thx...

[akshit]

P.S. There was no error due he the ('),(`) or the (") .....

[nav33n]

Cool :)

[digital-ether]

Just a note on this:

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().

eg:

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.