Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2005
    Posts
    24

    Unanswered: Constants or user defined variables - not sure what they are or exactly how they work

    Hi can anyone help - I am trying to use constants or user defined variables (or whatever they are called - you tell me please) within a script as per below. "Set" seems to work fine with columns as per @taxonomy but can it be used to replace table names? the way I have it below is failing.

    Additionally could I do something similar in a loop, concatenating table prefix: wp_2 increments, with the tail. iterating to the number of tables similaraly named on the database, that is there will be wp_2, wp_3, wp_x,....wp_400_terms etc

    Fot your interest this is a wordpress multisite style scehma.

    All the best

    Andy Seabrook


    Code:
    Set @terms_target_tbl='wp_2_terms';
    Set @taxonomy_target_table='wp_2_term_taxonomy';
    
    ALTER TABLE @terms_target_tbl ADD temp_flag BOOLEAN;
    
    Set @taxonomy='activities';
    
    INSERT INTO `@terms_table`( `name`, `slug`, temp_flag ) 
    SELECT `name`, slug, true FROM wp_terms JOIN wp_term_taxonomy ON wp_terms.term_id =wp_term_taxonomy.term_id WHERE wp_term_taxonomy.taxonomy = @taxonomy;
    
    INSERT INTO @taxonomy_target_table (`term_id`, `taxonomy`, `parent`, `description`) 
    	SELECT a.term_id AS 'Actual term' , @taxonomy,
    	IFNULL((SELECT e.term_id FROM @terms_target_tbl e WHERE e.slug IN (SELECT slug FROM wp_terms where term_id = c.parent)) , 0)'AS Actual Parent term_id',
    	''
    	FROM @terms_target_tbl a JOIN wp_terms b ON a.slug = b.slug
    	JOIN wp_term_taxonomy c ON b.term_id = c.term_id 
    	WHERE c.taxonomy = @taxonomy;
    	
    ALTER TABLE @terms_target_tbl DROP COLUMN temp_flag;

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    In this case here it will not work. Have a look at PREPARE, EXECUTE and DEALLOCATE PREPARE on how to dynamically create your SQL statements. I have created a stored procedure which does this to return a random row from a table (which is passed in as a parameter). This will give you an idea of how this works.

    MySQL – Stored Procedure that returns random rows from a table IT Integrated Business Solutions
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Jan 2005
    Posts
    24
    Quote Originally Posted by it-iss.com View Post
    In this case here it will not work. Have a look at PREPARE, EXECUTE and DEALLOCATE PREPARE on how to dynamically create your SQL statements. I have created a stored procedure which does this to return a random row from a table (which is passed in as a parameter). This will give you an idea of how this works.

    MySQL Stored Procedure that returns random rows from a table IT Integrated Business Solutions
    Thanks for this Ronan. As is so often the case almost as soon as I asked the question I am being pulled away as other priorities surface. Will look into this suggestion as soon as I get the chance, but to be frank not sure how far off that will be. Appreciate your response anyhow.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •