mysql update table if column exists (foreach)

Question

I've been playing with this :

foreach($textnode as $key => $value) {

$value = stripslashes($value);
 $value = mysql_real_escape_string($value, $con);

 mysql_query("INSERT INTO paragraphs (paragraphs, url)
 VALUES ('$value', '$url')");



}

I've been trying to update the column "paragraphs" for where the url already exists

This doesn't seem to work well as it just replaces each row in paragraphs with the 1st paragraph. (repeats it over and over again)

mysql_query("UPDATE paragraphs SET paragraphs = '$value'
 WHERE url = '$url'");

You have a column with the same name as the table? What does your DBA have to say about this? — Ignacio Vazquez-Abrams
– Ignacio Vazquez-Abrams, Commented Jul 6, 2011 at 21:13
Are you against making a basic query against the database to see if a valid row exists and update if so? — Grambot
– Grambot, Commented Jul 6, 2011 at 21:17

ypercubeᵀᴹ · Accepted Answer · 2011-07-06 21:37:36Z

1

Is the url field unique?

If not, add a UNIQUE constraint on it and use INSERT INTO . . . ON DUPLICATE KEY UPDATE with something like this:

 mysql_query("INSERT INTO paragraphs (paragraphs, url)
                VALUES ('$value', '$url')
              ON DUPLICATE KEY
                UPDATE paragraphs = '$value'
             ");

or (do you want to "append" the new '$value' when url already exists?):

 mysql_query("INSERT INTO paragraphs (paragraphs, url)
                VALUES ('$value', '$url')
              ON DUPLICATE KEY
                UPDATE paragraphs = CONCAT(paragraphs, '$value')
             ");

edited Jul 6, 2011 at 21:37

answered Jul 6, 2011 at 21:26

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

re1man Over a year ago

ya it is ...how would I do that using foreach though

ypercubeᵀᴹ Over a year ago

@Pranet: No, I think there needs to exist a UNIQUE constraint for the ON DUPLICATE KEY to work. If url is the primary key, thats's fine too.

re1man Over a year ago

Would finding to see if the url exists and replacing each row with the url with the new values be possible...I think thats what I'm looking for

ypercubeᵀᴹ Over a year ago

You can use IF EXISTS ... syntax but I think it needs you to create a stored procedure and it's not the best option for many other reasons. Look here anyway: stackoverflow.com/questions/6168822/…

Thomas Jones-Low · Accepted Answer · 2011-07-06 21:31:45Z

0

Try running the query

SELECT paragraphs, url
FROM   paragraphs;

to see what your table really contains. It may be that you've put in all the same URLs into your table. Which would explain the update behavior.

answered Jul 6, 2011 at 21:31

Thomas Jones-Low

7,1812 gold badges35 silver badges37 bronze badges

Collectives™ on Stack Overflow

mysql update table if column exists (foreach)

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related