homepage Welcome to WebmasterWorld Guest from 54.205.119.163
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe to WebmasterWorld

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
INSERT data from table-2 INTO table-1 WHERE VALUES are equal
Insert data from one database to another database in a specific row
capulet_x




msg:3292220
 12:24 am on Mar 25, 2007 (gmt 0)

This is my first try at this and I'm bombing miserably :-/

My query:

$query = "INSERT INTO table-1 (artist, client, clientemail, accesstime)?SELECT firstname+lastname,email,artist,time FROM table-2 WHERE artist= Ian";

My error:

Unknown column 'Ian' in 'where clause'

Scenerio:

I have a specific table (table1) which I cannot change because too many scripts reference it. What I am trying to do is take data from table-2 which has completely different names for its rows (cannot not change the names here for same reason as above) and INSERT them into table-1 WHERE artist = Ian (or whatever the name will be.) So to recap the only things that these two tables have in common besides residing in the same database is that they both have a row named artist and what I basically want to do is populate table-1 where

table-2.artist = table-1.artist
firstname+lastname = client
email = clientemail
time = accesstime

 

capulet_x




msg:3292221
 12:26 am on Mar 25, 2007 (gmt 0)

Pardon the double-negative.

capulet_x




msg:3292233
 1:15 am on Mar 25, 2007 (gmt 0)

I forgot to mention that I only want to INSERT the data from table-2 ONLY IF the fields client, clientemail, and accesstime in table-1 are EMPTY . If the fields are empty go to a specific URL and if they are NOT empty go to an entirely different URL.

Any help would be immensely appreciated...thanks for taking the time ti look at this.

capulet_x




msg:3292343
 6:33 am on Mar 25, 2007 (gmt 0)

Kind've talking to myself but still working at it....
This is a different approach. I haven't toched the redirect to different URLs yet. I'm just trying to tackle one element at a time.

<?php
$result = mysql_query("SELECT firstname, email, artist, time FROM table-2 WHERE haspaid = yes");
while (list($client, $clientemail, $artist, $access) = mysql_fetch_row($result)) {
echo "Name : $client " .
"email : $clientemail " .
"artist : $artist ".
"access : $access <br>";
$query=("INSERT INTO table-1 (client, clientemail, access)
Values ('$client','$clientemail','$access') WHERE artist = $artist");
mysql_query($query) or die('Error, insert query failed');
}
?>

The error I get with this one is pretty vague:

Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource...

dreamcatcher




msg:3292397
 9:17 am on Mar 25, 2007 (gmt 0)

Hi capulet_x,

The error basically means your query is failing. Check your query to make sure its correct. A good way to debug is to use mysql_error():

mysql_query("SELECT firstname, email, artist, time FROM table-2 WHERE haspaid = yes") or die(mysql_error());

dc

dramstore




msg:3292401
 9:25 am on Mar 25, 2007 (gmt 0)

I could be missing the point here, but it looks like you need quotes for a string:

WHERE artist= 'Ian'
or
artist = '$artist'

otherwise it assumes that ian is a field in a table

capulet_x




msg:3292508
 12:33 pm on Mar 25, 2007 (gmt 0)

Thanks for trying to help me with this...I do appreciate every little bit...

Below is the entire thing. but I still get an error. I applied your advice, DC, but now I get:
No database selected
and I know that isn't true because I know the database exist, that I'm spelling it correctly, etc. I've even tested simpler queries beneath the same connection and gotten results.
I know I must be overlooking something.

<?php

$hostname = "#*$!#*$!xx";

$database = "aaaaaa";

$username = "bbbbbb";

$password = "ccccccc";

$db1 = mysql_pconnect($hostname, $username, $password) or die(mysql_error());

$query = "SELECT firstname, email, artist, time FROM payers WHERE haspaid = 'yes'";

$result = mysql_query($query)or die(mysql_error());

while (list($client, $clientemail, $artist, $access) = mysql_fetch_row($result)) {

echo "Name : $client " .

"email : $clientemail " .

"artist : $artist ".

"access : $access <br>";

$query=("INSERT INTO `db-1` (client, clientemail, access)

Values ('$client','$clientemail','$access') WHERE artist = '$artist'");

mysql_query($query) or die('Error, insert query failed');

}

?>

jatar_k




msg:3292531
 12:55 pm on Mar 25, 2007 (gmt 0)

you should get that error, in the code bove you haven't selected a db. It should be between these 2 lines

$db1 = mysql_pconnect($hostname, $username, $password) or die(mysql_error());

mysql_select_db($database);

$query = "SELECT firstname, email, artist, time FROM payers WHERE haspaid = 'yes'";

capulet_x




msg:3292565
 1:46 pm on Mar 25, 2007 (gmt 0)

great scott! Your absolutely right...totally overlooked...I've rearranged this thing so many times now. Okay so now the first portion echos back what it should but the error I get now is:

Error, insert query failed

Of the two tables I'm working with "artist" is the only column they have in common. So I want to insert the values that were echoed for the first query, '$client','$clientemail','$access' and '$artist to be inserted into `db-1` where the value artist match but only if there is not already something populating those fields if something is I want to redirect to another URL or echo back that the fields are occupied.

<?php

$hostname = "#*$!#*$!xx";

$database = "aaaaaa";

$username = "bbbbbb";

$password = "ccccccc";

$db1 = mysql_pconnect($hostname, $username, $password) or die(mysql_error());

mysql_select_db($database, $db1);

$query = "SELECT firstname, email, artist, time FROM payers WHERE haspaid = 'yes'";

$result = mysql_query($query)or die(mysql_error());

while (list($client, $clientemail, $artist, $access) = mysql_fetch_row($result)) {

echo "Name : $client " .

"email : $clientemail " .

"artist : $artist ".

"access : $access <br>";

$query=("INSERT INTO `db-1` (client, clientemail, access)

Values ('$client','$clientemail','$access') WHERE artist = '$artist'");

mysql_query($query) or die('Error, insert query failed');

}

?>

jatar_k




msg:3292566
 1:48 pm on Mar 25, 2007 (gmt 0)

for that last line you should change it from

mysql_query($query) or die('Error, insert query failed');

to

mysql_query($query) or die('Error, insert query failed: ' . mysql_error());

to get the actual error, you know it failed, what you need to know is why

capulet_x




msg:3292576
 1:59 pm on Mar 25, 2007 (gmt 0)

Okay, so I guess it doesn't like INSERT...WHERE...
Any idea how to get more specific with an insert query...I'm checking the mySQL.com site now. BTW thank you all so much for looking at this with me.

Error, insert query failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE artist = 'Ian'' at line 2

jatar_k




msg:3292581
 2:09 pm on Mar 25, 2007 (gmt 0)

because it isn't allowed

[dev.mysql.com...]

an insert just adds a record

if you want to change an existing record then you need to use update

[dev.mysql.com...]

capulet_x




msg:3292600
 2:39 pm on Mar 25, 2007 (gmt 0)

I've got a new error which I put below the code. I'm reading up on the UPDATE syntax because I obviously missed something.

<?php

$hostname = "#*$!#*$!xx";

$database = "aaaaaa";

$username = "bbbbbb";

$password = "ccccccc";

$db1 = mysql_pconnect($hostname, $username, $password) or die(mysql_error());

mysql_select_db($database, $db1);

$query = "SELECT fifirstname,lastname, email, artist, time FROM payers WHERE haspaid = 'yes'";

$result = mysql_query($query)or die(mysql_error());

while (list($client1,$client2, $clientemail, $artist, $access) = mysql_fetch_row($result)) {

echo "FirstName : $client1 " .

"LastName : $client1 " .

"email : $clientemail " .

"artist : $artist ".

"access : $access <br>";

$client = "$client1, $client2";

$query=("UPDATE `db-1`SET client=$client, clientemail=$clientemail, access= $access WHERE artist = '$artist'");

mysql_query($query) or die('Error, insert query failed');

}

?>

New error:

Error, insert query failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Bob, clientemail=Bob@example.com, access= 1174 WHERE artist = 'Ian'' at line 1

[edited by: eelixduppy at 9:00 pm (utc) on Mar. 25, 2007]
[edit reason] exemplified error [/edit]

jatar_k




msg:3292601
 2:42 pm on Mar 25, 2007 (gmt 0)

is db-1 the tablename?

you also need quotes around your values

UPDATE `db-1`SET client='$client', clientemail='$clientemail', access='$access' WHERE artist = '$artist'

capulet_x




msg:3292602
 2:44 pm on Mar 25, 2007 (gmt 0)

Yes, db-1 is the table name.

capulet_x




msg:3292612
 3:01 pm on Mar 25, 2007 (gmt 0)

Hey, the quotes did it, jatar k, thankyou!

Now I just need to echo a message if
client, clientemail, access have data in them.
You see, I don't want them to be overwritten I only want to populate those fields IF they're empty(I will use a different query to empty them when I don't need them) Is there a way to specify the UPDATE only if the fields (client, clientemail, access) are empty
and also if the UPDATE is successful because the fields in db-1 were empty can I then simulataneously delete the record from the payers table (I have redundant data dublicated elsewhere) to avoid the same info from populating again on a different occassion?

jatar_k




msg:3292615
 3:03 pm on Mar 25, 2007 (gmt 0)

select the data first to check it

only execute your update if you find that no data was selected

yes, once your update is successful you can delete from the other table. Keep in mind that data that is deleted is ogne. A better option might be to set a flag to show they were updated in the other table. This would allow you not to select them if needed by using that field.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved