SQL help with update versus replace into

Quote

Post by Cue4cheap » Sat Aug 02, 2014 8:55 am

I guess I don't have enough experience in sql syntax so I am looking for help....

This code works perfectly IF and only IF the product (represented by $query->row['p_id']) already is in the product_special table.

Code: Select all

$this->db->query("UPDATE `" . DB_PREFIX . "product_special` SET price = ' " . $specialdbprice  . " ' WHERE product_id = ' " . $query->row['p_id'] . "'");
But if the product doesn't have a special already I would like to insert it. So I tried "replace into"

Code: Select all

$this->db->query("REPLACE INTO `" . DB_PREFIX . "product_special` SET price = ' " . $specialdbprice  . " ' WHERE product_id = ' " . $query->row['p_id'] . "'");
But that just gives me an error for what seems like the WHERE clause....

Could someone help me understand how I could do this?

Thank you,
Mike

cue4cheap not cheap quality

Cue4cheap
Expert Member
Posts
2030
Joined
Fri Sep 20, 2013 4:45 am
Top

Re: SQL help with update versus replace into

Quote

Post by Qphoria » Sat Aug 02, 2014 12:01 pm

How about this

Code: Select all

$this->db->query("INSERT INTO `" . DB_PREFIX . "product_special` SET price = ' " . $specialdbprice  . " ', product_id = ' " . $query->row['p_id'] . "' ON DUPLICATE KEY product_id=product_id");

Image

User avatar
Qphoria
Administrator
Posts
20059
Joined
Tue Jul 22, 2008 3:02 am
Top

Re: SQL help with update versus replace into

Quote

Post by Cue4cheap » Sat Aug 02, 2014 12:26 pm

Of course I figure it out after posting but this one took me a bit since 1: the syntax is different and 2: replace into seemed to duplicate records...

replace into needs to in a format like:
(`product_id`,`price`) VALUES ($proid,$specialdbprice)

Onward to my next issue with this script! :)

Mike

cue4cheap not cheap quality

Cue4cheap
Expert Member
Posts
2030
Joined
Fri Sep 20, 2013 4:45 am
Top

Re: SQL help with update versus replace into

Quote

Post by Cue4cheap » Sat Aug 02, 2014 9:04 pm

Qphoria wrote:How about this

Code: Select all

$this->db->query("INSERT INTO `" . DB_PREFIX . "product_special` SET price = ' " . $specialdbprice  . " ', product_id = ' " . $query->row['p_id'] . "' ON DUPLICATE KEY product_id=product_id");
Qphoria,

So sorry I didn't see your reply as I was working on it and replied blindly (didn't refresh the page to see your post - BUT I see it this morning!). I'll try yours this morning it already looks better then mine.

The ON DUPLICATE KEY is the piece I need to research and is the piece I just might be missing but in my hack I did a delete first since it was duplicating (or adding another) record in the table.

That is what I get for working too late into my sleeping time.

I must say thank you very much for the code and I'll give it a shot!

Mike

cue4cheap not cheap quality

Cue4cheap
Expert Member
Posts
2030
Joined
Fri Sep 20, 2013 4:45 am
Top

Re: SQL help with update versus replace into

Quote

Post by Cue4cheap » Sat Aug 02, 2014 9:37 pm

I did get an error:

2014-08-02 7:24:50 - PHP Notice: 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 'product_id=product_id' at line 1<br />Error No: 1064<br />INSERT INTO `product_special` SET price = ' 11.02 ', product_id = ' 203' ON DUPLICATE KEY product_id=product_id in /store/system/database/mysql.php on line 50

So I am researching (trying to learn) the ON DUPLICATE KEY to check syntax.

Mike

cue4cheap not cheap quality

Cue4cheap
Expert Member
Posts
2030
Joined
Fri Sep 20, 2013 4:45 am
Top

Re: SQL help with update versus replace into

Quote

Post by Cue4cheap » Sat Aug 02, 2014 9:56 pm

I updated it to:

Code: Select all

$this->db->query("INSERT INTO `" . DB_PREFIX . "product_special` SET price = ' " . $specialdbprice  . " ', product_id = ' " . $query->row['p_id'] . "' ON DUPLICATE KEY UPDATE product_id = ' " . $query->row['p_id'] . "' ");
Which doesn't kick an error but it does add records for that product in the product_specials table. So after running it 3 times I have 4 records in the product_specials table for that one product.

I don't know why it doesn't work as reading looks like it should (and shouldn't duplicate) but as for this morning I am going back to my code of delete then insert, and I'll research ON DUPLICATE KEY a bit more.

Mike

cue4cheap not cheap quality

Cue4cheap
Expert Member
Posts
2030
Joined
Fri Sep 20, 2013 4:45 am
Top
Post Reply
Return to “Off-Topic”
Who is online

Users browsing this forum: No registered users and 188 guests