Mass Update (MySQL & PHP)

[SiGmA_X]

Okay, I just typed this out in a long message, but IE crashed, so you are getting the short version now!

I need to update 21 records in one batch, that updates the same field and same table. Right now, it is doing 21 loops and 21 queries, and that is slow. Here is the current query, if you can help at all, please do!!UPDATE pchm_affiliates SET impressions=$impressions WHERE id=$id

[filburt1]

I had this problem a while ago and the only solution I could get was a cheap hack: a huge succession of nested IFs in the SET clause.

[SiGmA_X]

I don't think I fully understand... Can you post an example?

[Xenon]

you have to post a bit more about the structure itself.

should everyfield be updated with different infos, or with the same...

also filburt is right, a long nested if clause helped him in a similar problem, search around MyChenQL questions ;)

[SiGmA_X]

Well, it needs to update all the records based on id, but only one field (impressions) needs updating. The data will be diffrent for each, most likely.

I'll search for the keyword 'MyChenQL' but what is it? :)

[SiGmA_X]

Okay, I looked at this thread (https://vborg.vbsupport.ru/showthread.php?s=&threadid=46676&highlight=MyChenQL) and tried out a IF in a query... Here's the query I tried it with:UPDATE pchm_affiliates SET impressions = IF(id=10,"840",IF(id=11,"732")) WHERE id IN (10,11)Guess what? It worked! Now I just have to modify my script to handle it :)

Thanks Xenon, thank you a lot!

One more thing tho, how about a multi-table COUNT()? I can do it if I used COUNT(*) but not if I used COUNT(id) or something like that. I need to count it on a single column, and then I can make it work with out a loop. So, how do I make this work?SELECT COUNT(id) WHERE id=1

[SiGmA_X]

Humm, having a little trouble with a loop for this because of the looping parans in the IF. The amount of affiliates could change, so it has to loops and cannot be set. How can I close the ')'s each time, because pf the IF...

[SiGmA_X]

Humm, okay.. I have been working on this, and got my loop to work, or so it seems. It returns a error when ran in phpMyAdmin however. Here is the MySQL QueryUPDATE pchm_affiliates SET impressions = IF (id=8, "908",
IF (id=3, "1993", IF (id=4, "927",
IF (id=7, "872", IF (id=5, "1728",
IF (id=10, "873", IF (id=1, "1907",
IF (id=6, "1894", IF (id=2, "1904",
IF (id=9, "871", IF (id=11, "765")))))))))))
WHERE id IN (8,3,4,7,5,10,1,6,2,9,11)If anyone can help.. Thanks!

[Xenon]

doublequotes are wrong, you have to use ' in sqlqueries

[SiGmA_X]

Okay, I'll change that. But it works with doublequotes in your example and in the short one that I wrote in post 394,761 (https://vborg.vbsupport.ru/showthread.php?postid=394761#post394761) a bit up the page :)

[SiGmA_X]

Okay, still is erroring..UPDATE pchm_affiliates SET impressions = IF (

id = 8,
'977',
IF (
id = 3,
'2062',
IF (
id = 4,
'996',
IF (
id = 7,
'941',
IF (
id = 5,
'1797',
IF (
id = 10,
'942',
IF (
id = 1,
'1976',
IF (
id = 6,
'1963',
IF (
id = 2,
'1973',
IF (
id = 9,
'940',
IF (
id = 11,
'834'
)
)
)
)
)
)
)
)
)
)
) WHERE id IN ( 8, 3, 4, 7, 5, 10, 1, 6, 2, 9, 11 ) Error:
MySQL said:

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 id IN ( 8, 3, 4, 7, 5, 10, 1, 6, 2,Help?

[filburt1]

Do you have the correct number of closing parens? Also you'll need a final dummy ELSE value.

[Xenon]

exactly a final , '' before the first closing ) is needed

[SiGmA_X]

Thank you Xenon, I see now. 1 sec.. Awsome! It now works :) Thank you all.

Oh, and about that count problem I was having.. I got it! Just needed a GROUP BY:SELECT news.id AS newsid, COUNT(*) AS count
FROM post AS post, pchm_news AS news
WHERE post.threadid = news.forumid
GROUP BY news.id LIMIT 0,30