Jump to content


Photo

Newbie needs help w/ sample code regarding Update function


  • Please log in to reply
7 replies to this topic

#1 bajadulce

bajadulce

    Newbie

  • New Members
  • Pip
  • 3 posts

Posted 08 January 2013 - 04:35 PM

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.


#2 cpd

cpd

    ¬_¬

  • Members
  • PipPipPip
  • 891 posts
  • LocationLondon, UK

Posted 08 January 2013 - 06:39 PM

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.
"Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it."

"One of my most productive days was throwing away 1000 lines of code."

#3 bajadulce

bajadulce

    Newbie

  • New Members
  • Pip
  • 3 posts

Posted 09 January 2013 - 04:30 AM

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

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.


#4 bajadulce

bajadulce

    Newbie

  • New Members
  • Pip
  • 3 posts

Posted 09 January 2013 - 05:00 AM

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.

#5 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,184 posts
  • LocationCheshire, UK

Posted 09 January 2013 - 08:07 PM

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`


|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#6 cpd

cpd

    ¬_¬

  • Members
  • PipPipPip
  • 891 posts
  • LocationLondon, UK

Posted 10 January 2013 - 03:39 AM

Sorry, that was my fault. I should have spotted that!
"Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it."

"One of my most productive days was throwing away 1000 lines of code."

#7 DavidAM

DavidAM

    Advanced Member

  • Gurus
  • 1,972 posts
  • LocationSpring, TX USA

Posted 13 January 2013 - 05:07 PM

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.
-- I haven't lost my mind, it's backed up on tape ... somewhere!

#8 fenway

fenway

    MySQL Si-Fu / PHP Resident Alien

  • Moderators
  • 16,193 posts
  • LocationToronto, ON

Posted 13 January 2013 - 08:33 PM

Updating in a loop is usually reserved for transaction contention -- but I don't see any evidence of that in this case.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com