如果MYSQL中的值被更新，其他值也会发生变化

Question

我试图使当MYSQL属性的值发生变化时，其他属性的其他值​​也会发生变化。

例如：

我的color_scheme属性的值是"RED“。如果color_scheme值更改为“蓝色”，则：

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

有可能吗？

而且它只能更新一次值，因为用户必须能够单独更改特定属性(如menu_color、font_color、footer_color)。

就像这样：

​

​

​

​

Answer 1

试试这个：

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;

这是怎么回事？首先，你创建一个带有颜色的表格(例如'color_scheme')。然后得到值(例如'blue'，'#....'，...)从插入到您的'user_interface‘表中的这个表。顺便说一句，我假设你会把它用在用户界面上。无论如何，您可以采用它来满足您的需求。需要注意的是，您只能更新首先插入到数据库中的数据。因此，当添加用户时，您应该从默认颜色开始。我甚至建议您使用颜色创建一个单独的表，与user表分开。

接下来，您需要根据选定的配色方案更新默认颜色。假设你想使用“蓝色”。然后，您应该使用update查询来更新user_interface表(或colors表)中的值。最基本的方法是使用子查询，就像我对WHERE color_esquema = 'blue‘所做的那样。我添加了集合color_name变量来简化条目。通过这条语句，您开始声明一个名为color_name的变量，您只需要在整个查询中使用它一次就可以声明它。你可以，如果你想恢复，插入你的color_esquema名字。

SQL FIDDLE DEMO

我首先将灰色添加到user_interface中的最后一行。然后，我使用update查询将该值更改为新的黑色配色模式。把它分解成几个步骤。

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

然后更新相同的数据。B.T.W. fiddle不允许我在右侧面板中执行此操作。请参见顶部的最终结果。

Second step

PHP

更改配色方案的PHP脚本。

    <?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 **

这将仅加载配色方案名称：

   <?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 -->

Answer 2

为此，您可以使用CASE语句。这是一个非常简单的版本，但这应该会让你走上正轨。

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

Answer 3

您应该创建一个color_schemes表，并在其中存储每个配色方案的详细信息。