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]

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]

Pardon the double-negative.

[capulet_x]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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=[e-mail], 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]

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]

Yes, db-1 is the table name.

[capulet_x]

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]

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.