vb.org Archive

vb.org Archive (https://vborg.vbsupport.ru/index.php)
-   vB3 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=15)
-   -   Mass Update (MySQL & PHP) (https://vborg.vbsupport.ru/showthread.php?t=52869)

SiGmA_X 05-12-2003 10:49 PM

Mass Update (MySQL & PHP)
 
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!![sql]UPDATE pchm_affiliates SET impressions=$impressions WHERE id=$id[/sql]

filburt1 05-12-2003 10:52 PM

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 05-12-2003 10:58 PM

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

Xenon 05-13-2003 04:47 PM

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 05-13-2003 09:44 PM

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 05-13-2003 10:12 PM

Okay, I looked at this thread and tried out a IF in a query... Here's the query I tried it with:[sql]UPDATE pchm_affiliates SET impressions = IF(id=10,"840",IF(id=11,"732")) WHERE id IN (10,11)[/sql]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?[sql]SELECT COUNT(id) WHERE id=1[/sql]

SiGmA_X 05-13-2003 10:23 PM

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 05-13-2003 11:49 PM

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 Query[sql]UPDATE 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)[/sql]If anyone can help.. Thanks!

Xenon 05-14-2003 11:34 AM

doublequotes are wrong, you have to use ' in sqlqueries

SiGmA_X 05-14-2003 03:38 PM

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 a bit up the page :)

SiGmA_X 05-14-2003 03:48 PM

Okay, still is erroring..[sql]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 ) [/sql]
Code:

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 05-14-2003 04:15 PM

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

Xenon 05-14-2003 04:26 PM

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

SiGmA_X 05-15-2003 12:25 AM

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:[sql]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[/sql]


All times are GMT. The time now is 03:51 AM.

Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2025, vBulletin Solutions Inc.

X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.01186 seconds
  • Memory Usage 1,742KB
  • Queries Executed 10 (?)
More Information
Template Usage:
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_navbar_below
  • (1)bbcode_code_printable
  • (1)footer
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (6)option
  • (1)post_thanks_navbar_search
  • (1)printthread
  • (14)printthreadbit
  • (1)spacer_close
  • (1)spacer_open 

Phrase Groups Available:
  • global
  • postbit
  • showthread
Included Files:
  • ./printthread.php
  • ./global.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/class_hook.php
  • ./includes/modsystem_functions.php
  • ./includes/class_bbcode_alt.php
  • ./includes/class_bbcode.php
  • ./includes/functions_bigthree.php 

Hooks Called:
  • init_startup
  • init_startup_session_setup_start
  • init_startup_session_setup_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • global_setup_complete
  • printthread_start
  • bbcode_fetch_tags
  • bbcode_create
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • printthread_post
  • printthread_complete