Ad:

PHP Discussion Thread
Unsolved
Views: 1652

You are currently viewing page 2 of this multi-page discussion thread; Jump to the first page

Permalink

Jul 7th, 2008

Re: insrting from one table to another

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

akshit

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

46 posts
since Jun 2008

Permalink

Jul 7th, 2008

Re: insrting from one table to another

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.

nav33n

Moderator

Featured Poster

Reputation Points: 524

Solved Threads: 356

Purple hazed!

Offline

3,878 posts
since Nov 2007

Permalink

Jul 8th, 2008

Re: insrting from one table to another

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:

PHP Syntax (Toggle Plain Text)
SELECT column1 from table1 where {condition} LIMIT 1
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:

PHP Syntax (Toggle Plain Text)
INSERT INTO table2 SET column1 = (SELECT column1 FROM table1 WHERE {condition} LIMIT1)
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...

digital-ether

Moderator

Reputation Points: 457

Solved Threads: 101

Nearly a Posting Virtuoso

Offline

1,250 posts
since Sep 2005

Permalink

Jul 8th, 2008

Re: insrting from one table to another

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

antwan1986

Reputation Points: 14

Solved Threads: 8

Junior Poster

Offline

110 posts
since May 2008

Permalink

Jul 8th, 2008

Re: insrting from one table to another

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

sql Syntax (Toggle Plain Text)
UPDATE table2 SET col2  = ( SELECT col1 FROM table1 WHERE col3  = 'value' ) WHERE ID = 5
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 ---

php Syntax (Toggle Plain Text)
mysql_query(" update table2 set col2  = ( Select col1 from table1 where col3  = '$_POST["Tests"]' ) where ID = '$_POST["ID"]' ")  or mysql_error()
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...

Last edited by peter_budo; Jul 9th, 2008 at 7:17 am. Reason: Keep It Organized - please use [code] tags

akshit

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

46 posts
since Jun 2008

Permalink

Jul 8th, 2008

Re: insrting from one table to another

Quote ...

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.

php Syntax (Toggle Plain Text)
mysql_query("update table2 set col2 = ( Select col1 from table1 where col3 = '".$_POST['Tests']."') where ID = '".$_POST['ID']."'") or mysql_error();
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.

nav33n

Moderator

Featured Poster

Reputation Points: 524

Solved Threads: 356

Purple hazed!

Offline

3,878 posts
since Nov 2007

Permalink

Jul 8th, 2008

Re: insrting from one table to another

Click to Expand / Collapse Quote originally posted by nav33n ...

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

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

46 posts
since Jun 2008

Permalink

Jul 8th, 2008

Re: insrting from one table to another

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

akshit

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

46 posts
since Jun 2008

Permalink

Jul 8th, 2008

Re: insrting from one table to another

Cool

nav33n

Moderator

Featured Poster

Reputation Points: 524

Solved Threads: 356

Purple hazed!

Offline

3,878 posts
since Nov 2007

Permalink

Jul 9th, 2008

Re: insrting from one table to another

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:

PHP Syntax (Toggle Plain Text)
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();
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.

PHP Syntax (Toggle Plain Text)
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();
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.

digital-ether

Moderator

Reputation Points: 457

Solved Threads: 101

Nearly a Posting Virtuoso

Offline

1,250 posts
since Sep 2005

Previous
1
Page 2

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in PHP Forum Timeline: Back Button problem

Next Thread in PHP Forum Timeline: Storing button clicks

DaniWeb Message

Cancel Changes

User Name
Password