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 :)


All times are GMT. The time now is 01:41 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.01026 seconds
  • Memory Usage 1,728KB
  • 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)footer
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (6)option
  • (1)pagenav
  • (1)pagenav_curpage
  • (1)pagenav_pagelink
  • (1)post_thanks_navbar_search
  • (1)printthread
  • (10)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
  • pagenav_page
  • pagenav_complete
  • bbcode_fetch_tags
  • bbcode_create
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • printthread_post
  • printthread_complete