mysql - 如果更新了MYSQL中的值，则其他值发生变化

Question

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

例如:

我有值为“RED”的 color_scheme 属性。如果 color_scheme 值更改为“BLUE”，则值:

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_interface 表(或颜色表)中的值。最基本的方法是像我所做的那样进行子查询，然后说 WHERE color_esquema = 'blue'。我添加了 set color_name 变量来简化条目。使用此语句，您开始声明一个名为 color_name 的变量，您只需声明一次并在整个查询过程中使用它。如果您想还原，可以插入您的 color_esquema 名称。

SQL FIDDLE DEMO

我首先将灰色添加到 user_interface 的最后一行。然后我使用更新查询将值更改为黑色的新颜色模式。将其分解为步骤。

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

然后更新相同的数据。顺便提一句。 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 -->