If value in MYSQL is updated, other values changes

3 Views

I’m trying to make that when the value of a MYSQL attribute is changed, other values ​​of other attributes also vary.

For example:

Read More

I have the color_scheme attribute with value “RED”.
If the color_scheme value changes to “BLUE” the value of: 

menu_color changes to #30618F
font_color changes to #FFFFFF
footer_color changes to #1A92CA

Is it possible?

And it must update the values only once, because users must be able to change specific atributes (like menu_color, font_color, footer_color) separately.

Just like this:

Update values depending on other

enter image description here

Post Views: 3

Related posts

Leave a Reply

4 comments

  1. Try this:

    CREATE TABLE color_scheme (
  id_color_sheme INT AUTO_INCREMENT PRIMARY KEY,
  color_esquema VARCHAR(30),
  color_front_banners VARCHAR(7),
  color_front_banners_bottom VARCHAR(7),
  color_front_botoes VARCHAR(7),
  color_front_links VARCHAR(7),
  color_front_menu VARCHAR(7),
  color_front_rodape VARCHAR(7),
  index(color_esquema) 
  );



CREATE TABLE user_interface (
  id INT AUTO_INCREMENT PRIMARY KEY,
  color_front_banners VARCHAR(7),
  color_front_banners_bottom VARCHAR(7),
  color_front_botoes VARCHAR(7),
  color_front_links VARCHAR(7),
  color_front_menu VARCHAR(7),
  color_front_rodape VARCHAR(7)


  );

-- SAMPLE DATA
INSERT INTO user_interface (
                          color_front_banners,
                          color_front_banners_bottom,
                         color_front_botoes,
                         color_front_links,
                         color_front_menu,
                         color_front_rodape)
VALUES ('#FFFFFF','#FFFFFF','#FFFFFF','#FFFFFF','#FFFFFF','#FFFFFF'),
('#EEEEEE','#EEEEEE','#EEEEEE','#EEEEEE','#EEEEEE','#EEEEEE') ; 



INSERT INTO color_scheme (color_esquema,
                          color_front_banners,
                          color_front_banners_bottom,
                         color_front_botoes,
                         color_front_links,
                         color_front_menu,
                         color_front_rodape)
VALUES ('black','#FFFFFF','#FFFFFF','#FFFFFF','#FFFFFF','#FFFFFF','#FFFFFF'),
('grey','#EEEEEE','#EEEEEE','#EEEEEE','#EEEEEE','#EEEEEE','#EEEEEE') ;

-- UPDATE based on color_esquema
SET @color_name := 'black'; 
UPDATE user_interface 
SET 
  color_front_banners = (
SELECT color_front_banners FROM color_scheme
WHERE color_esquema = @color_name ),


  color_front_banners_bottom = (
SELECT color_front_banners_bottom FROM color_scheme
WHERE color_esquema = @color_name ),

  color_front_botoes = (
SELECT color_front_botoes FROM color_scheme
WHERE color_esquema = @color_name ),

  color_front_links = (
SELECT color_front_links FROM color_scheme
WHERE color_esquema = @color_name ),

  color_front_menu = (
SELECT color_front_menu FROM color_scheme
WHERE color_esquema = @color_name ),

  color_front_rodape = (
SELECT color_front_rodape FROM color_scheme
WHERE color_esquema = @color_name )

WHERE id = 2;

-- If you want to change just one color, you can use this query.
UPDATE user_interface 
SET 
  color_front_botoes = '#DDDDDD'
WHERE id = 1;

    How does this work? First you create a table with the colors (e.g. ‘color_scheme’). Then you get the values (e.g. ‘blue’,’#….’,…) from this table inserted into your ‘user_interface’ table. BTW I made the assumption that you will use this for a user interface. Anyway you can adopted it for your needs. Important to note is that you can only update data that is first inserted into a database. So, you should start with default colors when a user is added. I would even recommend that you create a separate table with the colors, separate from the user table.

    Next you want to update the default colors based on the selected color scheme. Lets say you want to use ‘blue’. Then you should use an update query to update the values in the user_interface table (or colors table). The most basic way would be to do subqueries like I did with then says WHERE color_esquema = ‘blue’. I added the set color_name variable to ease the entries. With this statement you started to declare a variable called color_name and you just need to declare it once a use it throughout you query. You can if you want to revert back insert your color_esquema name.

    SQL FIDDLE DEMO

    I first add grey colors to the last row in the user_interface. Then I use the update query to change the value into a new color schema which is black. To break it down into steps. 

    CREATE TABLE color_scheme (
  id_color_sheme INT AUTO_INCREMENT PRIMARY KEY,
  color_esquema VARCHAR(30),
  color_front_banners VARCHAR(7),
  color_front_banners_bottom VARCHAR(7),
  color_front_botoes VARCHAR(7),
  color_front_links VARCHAR(7),
  color_front_menu VARCHAR(7),
  color_front_rodape VARCHAR(7),
  index(color_esquema) 
  );



CREATE TABLE user_interface (
  id INT AUTO_INCREMENT PRIMARY KEY,
  color_front_banners VARCHAR(7),
  color_front_banners_bottom VARCHAR(7),
  color_front_botoes VARCHAR(7),
  color_front_links VARCHAR(7),
  color_front_menu VARCHAR(7),
  color_front_rodape VARCHAR(7)


  );

-- SAMPLE DATA
INSERT INTO user_interface (
                          color_front_banners,
                          color_front_banners_bottom,
                         color_front_botoes,
                         color_front_links,
                         color_front_menu,
                         color_front_rodape)
VALUES ('#FFFFFF','#FFFFFF','#FFFFFF','#FFFFFF','#FFFFFF','#FFFFFF'),
('#EEEEEE','#EEEEEE','#EEEEEE','#EEEEEE','#EEEEEE','#EEEEEE') ; 



INSERT INTO color_scheme (color_esquema,
                          color_front_banners,
                          color_front_banners_bottom,
                         color_front_botoes,
                         color_front_links,
                         color_front_menu,
                         color_front_rodape)
VALUES ('black','#FFFFFF','#FFFFFF','#FFFFFF','#FFFFFF','#FFFFFF','#FFFFFF'),
('grey','#EEEEEE','#EEEEEE','#EEEEEE','#EEEEEE','#EEEEEE','#EEEEEE') ;

    First step

    Then the same data with the update. B.T.W. fiddle doesn’t allow me to do this in right
    panel. See final at the top.

    Second step

    PHP

    A PHP script to change the color scheme. 

        <?php

    $color_name = 'black';

    $host = 'host';
    $user = 'user';
    $password = 'password';
    $database = 'database';

    $link = mysqli_connect($host, $user, $password, $database);
    IF (!$link) {
        echo ('Unable to connect to database');
    }
    ELSE{
        $query = "
            SET @color_name := '".$color_name."'; 
    UPDATE user_interface 
    SET 
      color_front_banners = (
    SELECT color_front_banners FROM color_scheme
    WHERE color_esquema = @color_name ),


      color_front_banners_bottom = (
    SELECT color_front_banners_bottom FROM color_scheme
    WHERE color_esquema = @color_name ),

      color_front_botoes = (
    SELECT color_front_botoes FROM color_scheme
    WHERE color_esquema = @color_name ),

      color_front_links = (
    SELECT color_front_links FROM color_scheme
    WHERE color_esquema = @color_name ),

      color_front_menu = (
    SELECT color_front_menu FROM color_scheme
    WHERE color_esquema = @color_name ),

      color_front_rodape = (
    SELECT color_front_rodape FROM color_scheme
    WHERE color_esquema = @color_name )

    WHERE id = 2;
            ";
        mysqli_query($link, $query);
    }
    mysqli_close($link);
    ?>

**Edit WordPress udpate **

    This will only load the color scheme names: 

       <?php
// insert database connection to get the colors from the color scheme 
   function retrieve_color_scheme() {
       $host = 'host';
       $user = 'user';
       $password = 'password';
       $database = 'database';

       $link = mysqli_connect($host, $user, $password, $database);
       IF (!$link) {
           echo ('Unable to connect to database');
       } ELSE {
           $query = "SELECT color_esquema FROM color_scheme";
           $result = mysqli_query($link, $query);
           $choices = mysql_fetch_array($result);
           return $choices;
       }
       mysqli_close($link);
   }


                    $this->add_control( 'color_esquema', array(
                            'label'   => 'Esquema de cores:',
                            'section' => 'custom_colors',
                            'type'    => 'select',
                            'priority'=> 9,
                            'choices'    => retrieve_color_scheme()
                    ) );                

                    $this->add_setting( 'color_menu', array(
                            'default'        => '#65696E',
                            'type'           => 'option',
                            'capability'     => 'edit_theme_options',
                            'transport'      => 'postMessage',
                            'priority'       => 10
                    ) );

                    $this->add_control( new WP_Customize_Color_Control( $this, 'color_menu', array(
                            'label'   => __( 'Fundo do Menu'),
                            'section' => 'custom_colors',
                            'settings'   => 'color_menu',
                            'priority'       => 10
                    ) ) );

                    $this->add_setting( 'color_titulo_menu', array(
                            'default'        => '#FFFFFF',
                            'type'           => 'option',
                            'capability'     => 'edit_theme_options',
                            'transport'      => 'postMessage',
                            'priority'       => 15
                    ) );

                    $this->add_control( new WP_Customize_Color_Control( $this, 'color_titulo_menu', array(
                            'label'   => __( 'T&iacute;tulo'),
                            'section' => 'custom_colors',
                            'settings'   => 'color_titulo_menu',
                            'priority'       => 15
                    ) ) );                
?>                   
    <!-- Page -->
    <?php $color_menu = get_option('color_menu');  ?>
    <?php $color_titulo_menu = get_option('color_titulo_menu');?>

        #container{border-top:82px solid <?php if(empty($color_menu)){echo "#65696E";} else { echo "$color_menu";}; ?>}
        #logo a span{color:<?php if(empty($color_titulo_menu)){echo "#FFFFFF";} else { echo "$color_titulo_menu";}; ?>}

    <!-- My try - In Page -->

  2. You can use a CASE statement for this. This is a pretty simplified version, but this should get you on the right track.

    UPDATE mytable
SET menu_color = CASE WHEN color_scheme = 'Red' THEN '#30618F' WHEN color_scheme = 'Blue' THEN  '#FFFFFF' ELSE '' END

  4. Radical,

    I’m Artur’s friend, I tried a different aproach for this same problem yesterday, and I don’t know if it’s easier or what… Please take a look at my other question

    We are using WordPress, and we are not very good at coding PHP, we can adapt scripts and stuff, but we are not very good at creating our own.

    Thank you for your efforts, we are almost there!