CODEWITHSUNDEEP
phpmysql
Simon Staton
Simon Staton

Reputation: 4505

Update value where value equals

Am touching up on MYSQL and PHP at the moment and still very begineer. Can anyone point out what I am doing wrong here:

UPDATE mage_catalog_product_entity_text 
SET value=" . $row['value2'] . " 
WHERE value = " . $row['value'];

I am trying to set value to another variable where value equals its current value. So there value2 is what I am updating with and value is just "test"

The error I get is: 

Unknown column 'test' in 'where clause'

Edit: echoing this entire query returns the same error so I have decided to post my entire code where not "test" isnt being mentioned at all:

<?php // instead of <?

mysql_connect ("localhost","cpsdev_mage1","D******"); 
mysql_select_db ("cpsdev_mage1"); 

$sql = "select value from mage_catalog_product_entity_text WHERE Attribute_id = 64"; 
    $result = mysql_query ($sql) or die($myQuery."<br/><br/>".mysql_error()); 

while($row = mysql_fetch_array($result,MYSQL_ASSOC))
{
$lines = file('/media/dev/' . $row['value']);
echo "UPDATE mage_catalog_product_entity_text SET value=" . $lines . "WHERE value = '"     . $row['value']; . "'";
}

?>

The rows I have in my database are: Attribute_id Value 64 Data 109 some other data

Edit: SOLVED - was my mistake was ftping into the wrong location on the suggested edits

Upvotes: 0

Views: 173

Answers (6)

smassey
smassey

Reputation: 5931

Please, look into prepared queries (mysqli/pdo). What you're trying to achieve could be disastrous if the contents of either of your "values" contained SQL code and would have been executed as normal SQL. Thinking especially about user submitted values.. Otherwise, be sure to use mysql_real_escape in conjunction with properly quoting the values.

Upvotes: 0

Jeremy Smyth
Jeremy Smyth

Reputation: 23493

In SQL, string values are delimited with single quote characters.

To effect this sort of thing in SQL:

    UPDATE mage_catalog_product_entity_text 
    SET value= 'new'
    WHERE value = 'old'

...and in PHP:

$sql = "UPDATE mage_catalog_product_entity_text " .
       " SET value= '" . $row['value2'] . 
       "' WHERE value = '" . $row['value'] . "'";

Note the addition of the two extra single-quotes in each clause.

Upvotes: 1

Pete Hamilton
Pete Hamilton

Reputation: 7900

Check you're quoting correctly. Assuming your query is within double quotes:

$query = "UPDATE mage_catalog_product_entity_text" .
         "SET value='" . $row['value'] . "'" .
         "WHERE value = '" . $row['value2'] . "';";

(the dots just concatenate the strings)

For your 'echo' in your edit:

echo "UPDATE mage_catalog_product_entity_text SET value='$lines' WHERE value = '" . $row['value'] . "'";

That would be the correct format, since you use double quotes, the standard variable $lines doesn't need to be outside.

Upvotes: 0

Olaf Dietsche
Olaf Dietsche

Reputation: 74018

I guess, your column value is a text column. Then you must enclose your where value into quotes:

UPDATE mage_catalog_product_entity_text 
SET value='" . $row['value2'] . "'
WHERE value = '" . $row['value'] . "'";

Upvotes: 0

Waleed Khan
Waleed Khan

Reputation: 11467

No doubt you're trying to check if the value is test. Enquote your string to do so:

SET value='" . $row['value2'] . "'
WHERE value = '" . $row['value']; . "'

But you should really be parametrizing your queries, and if not, escaping your data.

Upvotes: 0

Marcin Orlowski
Marcin Orlowski

Reputation: 75629

Error message clearly indicates that table you are trying to work with does not have any column named test. I'd suggest to echo() the query to see what's it really looks like. Additionally, your query uses incorrect syntax as string values have to be quoted:

WHERE value='" . $row['value'] . "'";

or even better:

$query = sprintf(  "UPDATE `mage_catalog_product_entity_text` "
                 . "SET `value`='%s' WHERE `value`='%s'",
                   $row['value2'], $row['value']);

Upvotes: 0

Related Questions