replace into .. values ... where???

[sabret00the]

apparently i can't use replace into blah blah with where, what's the alternative?

[Marco van Herwaarden]

Could you elaborate.

[sabret00the]

i'm trying to do this $DB_site->query("
REPLACE INTO prs_users(favourites)
VALUES(if(favourites='', $p, concat_ws(' ', favourites, $p)))
WHERE userid = $bbuserinfo[userid]
");

but it gives me this error mysql error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE userid = 10' at line 3

mysql error number: 1064

not sure if i'm doing a good job of explaining this so let me know if not and i'll try harder.

[sabret00the]

*bump*

[Andreas]

WHERE with REPLACE doesn't work.
REPLACE = INSERT, except that it does delete existing records with the same key first.
So you can insert without having to check if the record does already exist.

If you want to update a record (which you know does exist) you should use UPDATE.

[sabret00the]

problem is that i don't know if it exists or not that's why i made it a replace so that if it didn't exist it would it would create the record, can update do that?

i.e.


$DB_site->query("
UPDATE prs_users(favourites)
VALUES(if(favourites='', $p, concat_ws(' ', favourites, $p)))
WHERE userid = $bbuserinfo[userid]
");

[Andreas]

No, UPDATE can just update existing records.

What's the Schema of prs_users?

[sabret00the]

| prs_user |
=================================================
| recordid | userid | subscriptions | favourites | responseupdate |
---------------------------------------------------------------


basically as soon a user either tries to subscribe, mark something as a favourite are ask for a response update it creates a record for the user.

more info on the schema

CREATE TABLE `prs_users` (
`recordid` int(10) unsigned NOT NULL auto_increment,
`userid` int(10) NOT NULL default '0',
`subscriptions` mediumtext collate latin1_general_ci NOT NULL,
`favourites` mediumtext collate latin1_general_ci NOT NULL,
`responseupdate` enum('1','0') collate latin1_general_ci NOT NULL default '1',
PRIMARY KEY (`recordid`),
KEY `userid` (`userid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=2 ;

[sabret00the]

*bump*