Changing variable in MySql

Question

I have this code.

$db_update_syntax = array(
        "INSERT INTO posts (SELECT NULL,'TEMP','".$data->cid."',title,img,redirect,redirect_full,site_name,description,longurl,shorturl,publishdate,publishtime,platform,post_type,youth,volunteers,biomed,img_idea,client,category,sme,approved,featured,announce,dynamic_content,trash,archive,NULL FROM posts WHERE cid='".$orig_cid."' AND trash != 1)",

        "SET @subdomain = (SELECT SUBSTRING_INDEX(guid, '-', 1) FROM posts WHERE cid ='".$orig_cid."' limit 1)",
        "SET @html = (SELECT LEFT(longurl, LENGTH(longurl) - LOCATE('/', REVERSE(longurl))+1) FROM posts WHERE cid ='".$orig_cid."' limit 1)",
        "SET @postid = (SELECT id FROM posts WHERE cid ='".$orig_cid."' limit 1)",
        "SET @newpostid = (SELECT id FROM posts WHERE cid ='".$data->cid."' limit 1)",

        "UPDATE posts SET guid=CONCAT(@subdomain,'-',id) WHERE cid='".$data->cid."'",
        "UPDATE posts SET longurl=CONCAT(@html,id,'.html') WHERE cid='".$data->cid."'",

        "INSERT INTO platform_xrf (SELECT NULL,0,platformid FROM platform_xrf WHERE postid=@postid)",
        "UPDATE platform_xrf SET postid=@newpostid WHERE postid=0"
        );

I have three tables: post table, platform table and platform_xrf table.

The platforms associated with each post are stored in the platform_xrf table.

When a post gets duplicated I need to also duplicate the associated rows in the platform_xrf table and then update these new rows with the new post id.

The problem I am having is my SET @postid and SET @newpostid.

If I take out the LIMIT 1 the queries fail but if I keep it in and there are two or more post getting duplicated it will only duplicate one.

[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)***. Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — Jay Blanchard, Oct 25 '16 at 16:12
You cannot SET a variable to a query resultset that has more than one field, or more than one result; also, your `INSERT...SELECT` syntax is incorrect; — Uueerdo, Oct 25 '16 at 16:22
`INSERT...SELECT` doesn't need parenthesis is all; actually, now that I think about it, I am not 100% sure they would cause issues, but they end up looking very weird. As far as the `SET`s there is no simple correction; what you are trying just isn't supported. An approach using temp tables to hold the plural, intermediate results is what you need. — Uueerdo, Oct 25 '16 at 16:28
@Uueerdo Which of his `SET` statements has a query with more than one field? — Barmar, Oct 25 '16 at 16:40
It seems like much of this could be done better using `UPDATE posts AS p1 JOIN posts AS p2 ...` — Barmar, Oct 25 '16 at 16:41
'postid and newpostid. If 1 posts is getting duplicated this all works as expected. If 2 or more posts are getting duplicated it only gives one id — Jason, Oct 25 '16 at 16:42
@Barmar His current queries do not. However, I assuming that is what he is trying to do judging by the last statement in the question, "_If I take out the LIMIT 1 the queries fail..."_ — Uueerdo, Oct 25 '16 at 16:55
@Jason It would help if you could explain clearly what you're trying to accomplish, including sample input data and the desired result. — Barmar, Oct 25 '16 at 18:05
@Barmar sure, the system create Campaigns. Each campaign can have multiple posts. Each post can have either Facebook or Twitter or both platforms associated with it. The code I posted is to duplicate a campaign and all it's posts. The problem is when I try to update the platform table with the new posts. Sorry I am not at my computer right now so I can not post any code — Jason, Oct 25 '16 at 18:15
@Jason Put it in the question, including sample data to make it clear. — Barmar, Oct 25 '16 at 18:17
It looks like the `UPDATE` queries are changing columns in the rows that it just inserted. Why don't you just put the correct values in the `INSERT` query? — Barmar, Oct 25 '16 at 18:26

score 0 · Accepted Answer · answered Oct 25 '16 at 18:33

0

Change your last INSERT so that it uses the original query to get all the rows to duplicate, rather than setting a variable.

INSERT INTO platform_xrf
SELECT NULL, 0, platformid
FROM platform_xrf AS x
JOIN posts AS p ON p.id = x.postid
WHERE p.cid = $orig_cid

UPDATE platform_xrf AS x
CROSS JOIN posts AS p
SET x.postid = p.id
WHERE x.postid = 0
AND p.cid = $data->cid

I'm not sure what you expect to happen if the query for @newpostid returns multiple IDs. You can only store one of them into all the platform_xrf rows where postid = 0.

answered Oct 25 '16 at 18:33

Barmar

741,623
53
500
612

This almost works. It is now adding the appropriate amount of entries into the platform_xrf table but they all have the same postid. Example: post ID1 has 2 platforms associated with it 1, 2. post ID2 has 1 platform associated with it. the current code adds three entires into platform_xrf but all three have ID2 when instead it should be two entries have ID1 and the third entry has ID2 – Jason Oct 25 '16 at 19:16
This is why I wanted to see some sample data in the question. It's hard to understand the data flow when the `INSERT` statements don't have column names saying what fields it's inserting into. – Barmar Oct 25 '16 at 19:20
I am not sure what data you need? Question, when these queries run do they run in a loop or does the first query run first thus generating three entires and then the second query runs? – Jason Oct 25 '16 at 19:25
I think my approach may be flawed but we are very close. I might need to do everything in the first statement instead of two statements – Jason Oct 25 '16 at 19:26
table platform xrf has three columns. id (auto increment), postid, platformid. I am using the id, and cid columns from the post table – Jason Oct 25 '16 at 19:28
foreach post duplicated it needs to also duplicate the associated platform_xrf rows but the new platform_xrf rows need to have the newly created post's ID in their postid field. I was duplicating these by setting the postid to 0 and then going back and updating those rows with the new post id but that is where I am hitting a wall – Jason Oct 25 '16 at 19:32
If you stop describing and show real examples, I'll get it much more easily. – Barmar Oct 25 '16 at 19:43
I am not sure what more examples I can give – Jason Oct 25 '16 at 19:44
After you do an `INSERT`, you can use `LAST_INSERT_ID()` to get its ID. But if you insert multiple rows, there's no way to get all of them. – Barmar Oct 25 '16 at 19:44
You haven't given any examples. Show some original table contents, and then the desired result after the procedure is done. – Barmar Oct 25 '16 at 19:45
You need a procedure where you duplicate one at a time. Then you can get its ID for insertion in the relation table. – Barmar Oct 25 '16 at 19:46
Ah yes, ok, I am not familiar enough with MySQL. can you provide some code of how to do a procedure? – Jason Oct 25 '16 at 19:47
You're using PHP, do the loop there. – Barmar Oct 25 '16 at 19:56
I took your advice and went back to php. Not sure why I didn't just do that from the start. I guess I got caught up with trying to make SQL work. Thanks for you help. I am accepting your answer – Jason Oct 26 '16 at 13:09

Changing variable in MySql

1 Answers1