vb.org Archive - MySQL query please

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- vB3 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=15)

- - MySQL query please (https://vborg.vbsupport.ru/showthread.php?t=228856)

pentium

11-25-2009 01:28 PM

MySQL query please

Hi all!
i need to attach a paragraph above the text already is in pagetext (table "post"):
e.g. :
text to add: "some text to add"
pagetext: "some text already in pagetext"
result in pagetext: "some text to add some text already in pagetext"
I already created a table "addings" with 3 fields (postid, threadid, addtext).
Could someone show to me wich query I need to execute pls?
Thanks.

Lynne

11-25-2009 01:41 PM

Do you need to actually change what is in the database (which means the user can edit it) or can you just put the text in the postbit template?

pentium

11-25-2009 01:49 PM

I need to change what is in the database

pentium

11-26-2009 11:09 PM

noone can help?

kh99	11-27-2009 12:02 PM

So just to make sure I understand, do you want a query so that the database starts out as:

Code:

table 'posts':

1, 2, "This is a post"

2, 5, "This is another post"

3, 3, "This is a third post"



table 'addings':

1, 2, "Add text-"

3, 3, "More added text-"

then you run the query once and the database is

Code:

table 'posts':

1, 2, "Add text-This is a post"

2, 5, "This is another post"

3, 3, "More added text-This is a third post"



table 'addings':

1, 2, "Add text-"

3, 3, "More added text-"

pentium

11-27-2009 12:24 PM

Yes kh99 that's exactly what I whant to do :)
could you help?
Thanks

kh99	11-27-2009 12:43 PM

Well, I'm not a SQL expert (just learning actually), but I ran a little test and this seems to work (use at your own risk!):

Code:

UPDATE vb_post AS post 

  LEFT JOIN vb_addings as addings 

  ON (post.postid=addings.postid AND post.threadid=addings.threadid) 

  SET post.pagetext=CONCAT(addings.addtext,  post.pagetext)   

  WHERE addings.addtext IS NOT NULL

Notes:
1. This seems very dangerous! Are you sure you want to try this? Back up your database first.
2. The 'vb_' in red should be changed to be your own prefix.
3. Did you add a prefix to your 'addings' table? Change or remove the 'vb_' in blue accordingly
4. I test both postid and thread id, although I believe postid is unique
5. If you want to put something between the addtext and pagetext, you can add it to the CONCAT function (in green), such as CONCAT(addings.addtext, ' ', post.pagetext) to add a space.
6. Are you sure? Maybe run a test with an addings table that has only one or two rows.

pentium

11-27-2009 02:26 PM

Really a great Thank you kh99 :)
Yessss it worked very fine, 409 lines sucessfully updated!
Thanks again for yor time, you are a very kind person.

All times are GMT. The time now is 06:32 PM.

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01246 seconds Memory Usage 1,723KB 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 (3)bbcode_code_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (8)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
Messages: