Updates and Joins in MySQL

Now this is supremely hot. You can update a whole set of information from one table to another without passing any data from your PHP. Not sure if this works with MS SQL, I’ll check next time I’m around those machines.

Here is what I was after:

  1. Users on a website need the ability to customize their personal pages
  2. We want to make it easy for them by using themes (pre-selected styles, fonts, colors)
  3. Users must have the ability to adjust their theme if they so desire

Here is what I did:

  1. Created a theme table to MySQL that includes all the fancy style columns, like font styles, colors, etc
  2. Added columns to users page table to match those theme styles
  3. I created a PHP function to apply a theme based on theme selected, and user’s id

Awesomeness in this case comes to play when it’s time to change the theme. PHP is from a data object that has user id already set:

PHP Code:

public function setPageTheme($themeid){
    $sql = sprintf("
        UPDATE userpages U
        INNER JOIN themes T ON T.themeid = %d
        SET U.background=T.background, U.font=T.font, U.textcolor=T.textcolor
        WHERE U.userid = %d
    ",
        $this->esc($themeid), $this->userid
    );
    $result = mysql_query($sql, $this->_Link);
    if(!$result) $this->callError("Error: " . $sql);
}

So now you’ve got the theme transferred to users page, and then you just build a page where user can customize the theme further if they so desire. How cool is that? :) Hopefully this saves you some time.

Leave a Comment

Back to Top