7 replies to this topic

[bajadulce]

Hiya! newbie to forums and sql/php

I've created this simplified version of a problem I can't seem to tackle by searching/reading online documents alone.

Basically I want to replace some field in a table, to that of a field in another table.

the 2 tables are called "friends" and "cars"
their common column is called "name"
table friends has a column "fav_color"
table cars has a column "car_color"

I want to update car_color w/ fav_color.

Can't seem to find the right syntax for the use of the Update function? Am I even using it correctly?

<?php
... connect to database

$query = "SELECT friends.name, friends.fav_color, cars.car_color ".
"FROM friends, cars ".
"WHERE friends.name = cars.name";

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

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

//UPDATE tablename SET (fieldname1=value1,fieldname2=value2,...) WHERE fldstudid=IdNumber <- syntax according to online documents?
"UPDATE cars SET ( $row['car_color'] = $row['fav_color'])";

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

?>

thnx for any help in advance

EDIT:
Sorry just read the stickied guideline for posting and prob didn't supply enough info:
* Not sure what version of php (am using a relatively new version of Easyphp for offline/local host)
* have tried various different ways to write that Update line, but seem to always get a syntax error.

hope that fits some of the quidelines for question.

Edited by bajadulce, 08 January 2013 - 04:44 PM.

[cpd]

Hello, welcome to the forums.

You can pull this off in a single query

UPDATE `cars` 

SET `car_color` = (

   SELECT `fav_color` 

   FROM `friends` 

   WHERE 

      `id` = :rowIdInFriends

)

WHERE `id` = :rowIdToUpdateInCars

Or if you have a common field, you said name, you can do

UPDATE `cars` 

SET `cars`.`car_color` = `friends`.`fav_color`

JOIN `friends` ON

   `friends`.`name` = `cars`.`name`

WHERE `name` = :someName

The second would update every row with the fav colour. Not sure if that's what you want or not. Be careful though as the second relies on a Many to One relationship from Cars to Friends respectively. I can't visualise the outcome of the opposite relationship at this tired hour but I think it'd be rather unwanted.

[bajadulce]

Thank you very much for the help. I imagine a site like this can get flooded w/ newbies such as myself w/ such elementary questions which prob is somewhat a distraction to those wanting to engage in more advanced discussion. Maybe the site could have a beginner's corner

So I really appreciate the help.

Unfortunately after fiddling w/ it some more for what seems like several hours, I still don't seem to grasp the protocol/syntax of the language (only my 2nd day) enough to seem to put together the proper code to make this work. I've spent quite a bit of time browsing online documents/tutorials etc. While they have been immensely helpful getting started, the examples seem to either be too basic or too advanced.

The second would update every row with the fav colour. Not sure if that's what you want or not

Yes, sorry didn't make that clear. In this particular application, I would like the entire "car_color" column to be updated/replaced with the associated "fav_color"

I've tried both:

mysql_query("UPDATE `cars`
SET `cars`.`car_color` = `friends`.`fav_color`
JOIN `friends` ON
`friends`.`name` = `cars`.`name`");

as well as:

msql_query("UPDATE `cars`
SET `car_color` = (
SELECT `fav_color`
FROM `friends`");

but neither of them seemed to change the car_color column. Not sure what am doing wrong. My results never seem to get updated when I try to bridge the 2 tables by updating car_color w/ fav_color:

Is it a problem that both tables contain a column with the same name? (i.e. cars.name and friends.name) I didn't realize this until now. prob doesn't make a difference just bad practice.

I keep going back to my original code in 1st post:
And this line appears to be what is holding me up as all the necessary information seems cached in the arrays.

"UPDATE cars SET $row['car_color'] = $row['fav_color']";

Parse error: syntax error, unexpected '' (T_ENCAPSED_AND_WHITESPACE),....

and some other syntax errors as I fumble w/ the language protocols. and that particular line.

EDIT:
Have cleaned up the first post code a bit and wanted to post it again. I've added the line mysql_query("UPDATE car_color SET car_color = 'ugly color'"); line so as to have some reassurance that the UPDATE function was being used properly.

mysql_query("UPDATE car_color 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)){
//   mysql_query("UPDATE cars SET $row['car_color'] = $row['fav_color']");

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

?>

Outputs as:
Cathy:
fav color = Pink
car color = ugly color

John:
fav color = Blue
car color = ugly color

Marie:
fav color = Black
car color = ugly color

Edited by bajadulce, 09 January 2013 - 04:36 AM.

[bajadulce]

Sorry for "double posting". Doesn't look like you can edit more than once?

I spotted this:

"UPDATE cars SET $row['car_color'] = $row['fav_color']";

where "cars" is a table name not a column.

which prob should be:

"UPDATE $row['car_color'] SET $row['car_color'] = $row['fav_color']";

didn't seem to change the results tho and sytax is reported as wrong still:
syntax error, unexpected '' (T_ENCAPSED_AND_WHITESPACE), expecting id... etc.

[Barand]

UPDATE `cars`
SET `cars`.`car_color` = `friends`.`fav_color`
JOIN `friends` ON
`friends`.`name` = `cars`.`name`
WHERE `name` = :someName

That one was close but should be

UPDATE `cars` 
  JOIN `friends` ON
   `friends`.`name` = `cars`.`name`
SET `cars`.`car_color` = `friends`.`fav_color`

[cpd]

Sorry, that was my fault. I should have spotted that!

[DavidAM]

I keep going back to my original code in 1st post:
And this line appears to be what is holding me up as all the necessary information seems cached in the arrays.

"UPDATE cars SET $row['car_color'] = $row['fav_color']";

Parse error: syntax error, unexpected '' (T_ENCAPSED_AND_WHITESPACE),....

Barand's solution is the quickest most efficient way to handle this. But, I thought I'd explain this problem. The correct syntax is:

UPDATE tableName SET columnName = newValue

Your query, when the variables are replaced with their values would be (something like):

UPDATE cars SET blue = red

which is obviously not correct.

Also, without a WHERE clause in there, your statement would update every row in the table every time it is called. Furthermore, you need to put quotes around the string values. So, you need something like:

$sql = "UPDATE cars SET car_color = '{$row[fav_color]}' 
  WHERE name = '{$row[name]}'";

However, as was said above, doing the update in a loop like that is not necessary, and terribly inefficient.

[fenway]

Updating in a loop is usually reserved for transaction contention -- but I don't see any evidence of that in this case.