Newb needs help w/ UPDATE data in table with data from another table

bajadulce

Hi! 🙂 Newbie to forum AND php/sql.

This is great idea to set aside a dedicated forum for newbies (and their endless repeated questions I'm sure) so as to not disrupt the main forum!

my problem:
updating data from one table to another.

database has 2 tables: friends, cars
both tables have a common column: name
friends table contains: fav_color
cars table contains: car_color

I want to replace all car_color with fav_color
And this is where I appear to be stuck.

I've scrounged around internet sites digging thru elementary examples and snippets here and there to formulate the following:

// test to prime/change color
mysql_query("UPDATE cars SET car_color = 'ugly color'");

$result = mysql_query("SELECT friends.name, friends.fav_color, cars.car_color ".
	"FROM friends, cars ".
	"WHERE friends.name = cars.name") 
		or die(mysql_error());

 while($row = mysql_fetch_array($result))
	{
$rowid = $row['car_color'];
mysql_query("UPDATE cars SET car_color = fav_color WHERE car_color = $rowid");

// print out to confirm updates
	echo $row['name']. ": ". '</br>' .
	'fav color = ' . $row['fav_color'] . '</br>' .
	'car color = ' . $row['car_color'] . '<P><P><P>';
	}

Resulting output

Cathy:
fav color = Pink
car color = ugly color

Joe:
fav color = Blue
car color = ugly color

Mary:
fav color = Black
car color = ugly color

I've tried all sorts of things to get car_color to change in that WHERE loop, but just stumble. Help! 😕 Can't seem to get past this stage of learning process and am spinning wheels looking back @ the basics.

sneakyimp

Firstly, DO NOT USE ANY MYSQL_* FUNCTIONS. Use [man]mysqli[/man] or [man]pdo[/man] instead.

Second, you call mysql_query and then never bother to check its result. You should always check the result of a query to make sure it worked. I'd be willing to bet your sql is incorrect. You may find the mysql documentation on update queries helpful.

bajadulce

Not meaning to double post, but just read this in another thread:

dalecosp;11020645 wrote:
mysql_fetch_array returns a numerically-indexed array. If you want to refer to $row elements by name, you probably want to use [man]mysql_fetch_assoc/man.

So this prob pertains to my array as well?

Also... just seeing this too:

And, while we're on that subject, I'll refer you to the community's latest initiative: [thread=10387467]Stop-Using-the-MySQL-Extension![/thread] The mysql_* functions are deprecated and keeping the PHP community in the Dark Ages. We appreciate your time ... but please consider spending some of it to learn mysqli, PDO ... etc.

k will check out.

bajadulce

ooops! used the "reply" button to copy/paste to another thread! major fail for 2nd post.. I'm tired sorry.

bradgrafelman

First problem I see is that you don't surround $rowid in the second UPDATE query with quotes. Since car_color appears to be a string-type column, this suggests that you're putting a string value ($rowid) in a query without surround it with quotes (as you must do with all strings).

Second problem (well, not entirely a problem - maybe just a deficiency) I notice is that you really don't need to do a SELECT query followed by an UPDATE query. Why not just do the UPDATE query and let the SQL server take care of copying the values over?

EDIT: Third problem I see is that fav_color isn't the name of any known column in your second UPDATE query. If you're going to stick with the inefficient two query method, you probably meant to use the "friends.fav_color" column value contained in the $row array.

bajadulce

Thnx for such immediate responses. 🙂 I really appreciate the help and will now look at this offline and try to update/work out according to feedback.

Will also be sure to check out the link regarding outdated use of mysql.

ooops! used the "reply" button to copy/paste to another thread! major fail for 2nd post.. I'm tired sorry.

For some reason I thought I had by accident posted in the forum where I copied the quote from. guess wasn't expecting such a fast reply and was confused @ which thread was in. sorry for confusion.

K.. thnx again.

bajadulce

SOLVED: fixed

Found this in my searching:

It took me a few minutes to figure this out, but the syntax for UPDATING ONE TABLE ONLY using a relationship between two tables in MySQL 4.0 is actually quite simple:
update t1, t2 set t1.field = t2.value where t1.this = t2.that;

Translated to my own problem:

mysql_query("UPDATE cars SET car_color = 'ugly color'");

mysql_query("UPDATE cars, friends SET cars.car_color = friends.fav_color WHERE cars.name = friends.name")
	or die(mysql_error());


$result = mysql_query("SELECT friends.name, friends.fav_color, cars.car_color ".
	"FROM friends, cars ".
	"WHERE friends.name = cars.name") 
		or die(mysql_error());

 while($row = mysql_fetch_array($result)){

// print out to confirm updates
	echo $row['name']. ": ". '</br>' .
	'fav color = ' . $row['fav_color'] . '</br>' .
	'car color = ' . $row['car_color'] . '<P>';
	}

?>

With desired results:

Cathy:
fav color = Pink
car color = Pink

Joe:
fav color = Blue
car color = Blue

Mary:
fav color = Black
car color = Black

So that's that.

Hopefully other newbies who stumble w/ this won't stumble w/ finding an answer as much as I did.

Thnx will be sure to check out the link dealing w/ outdated use of mysql soon.

sneakyimp

Glad it worked out!

bajadulce;11022141 wrote:
Thnx will be sure to check out the link dealing w/ outdated use of mysql soon.

I would strongly recommend that you use a database abstraction layer -- meaning a class or set of functions of your own which acts as a wrapper around the PHP database functions you actually use. It might seem like a pain, but when the PHP developers decide to get rid of something (like the mysql_* functions) then you will probably only have to change your code in one place.