List:General Discussion« Previous MessageNext Message »
From:SGreen Date:March 28 2006 3:31am
Subject:Re: JOINs-- need some expertise on this one
View as plain text  
"Mark" <neretlis@stripped> wrote on 03/27/2006 09:45:57 AM:

> Hi i am emiling you hoping you can help me urgently,
> I have a football tipping script which works fine now except i want 
> to update a field from one table to another.
> 
> At the moment there is a table (leaderboard) which is updated weekly
> via the updateleaderboard.php
> It shows  USERNAME ,SCORE ,and COMPETITON NAME (eg: George | 12 
> points | harrys comp.) What i want to do is ad an avatar next to 
> each username.
> 
> I have already worked out the form for users to choose avatar and 
> place the name of the image in an AVATAR field in the USERNAME table
> and echo it.
> 
> My problem is that each week the LEADERBOARD usernames change and so
> do thier scores, comp name and of course their avatar.. i have made 
> an AVATAR field in the LEADERBOARD table as well, now i just need 
> some script which i will use as an i-nclude -on the 
> updateleaderboard script to take the avatar field data and carry it 
> over to the AVATAR field in the LEADERBOARD table as well.
> 
> here is my script that doesnt work (also there is no session 
> involved, makes it harder)
> below is my schema
> 
> <title>update_avatars</title><?php
> include("header.php");
> include("connect.php");
> 
> 
> //insert avatar into leaderboard
> 
> 
> $sql = mysql_query SELECT users.avatar, leaderboard.username FROM 
> users, leaderboard
>        WHERE users.comp_id=leaderboard.comp_id;
> 
>   $result = @mysql_query($sql);
> $avatars = "avatar"; 
> $username = "username"; 
> 
> $query = mysql_query("UPDATE leaderboard SET avatar = '$avatars' 
> WHERE username = '$username'");
> $query = mysql_query($sql); 
> 
> 
> ?>
> 
> <?php
> include("footer.html");
> ?>
> 
> 
> 
> 
> 
> *************************************************
> 
> #
> # Table structure for table `comps`
> #
> 
> CREATE TABLE `comps` (
>   `id` int(11) NOT NULL auto_increment,
>   `name` varchar(255) NOT NULL default '',
>   `username` varchar(255) NOT NULL default '',
>   `password` varchar(255) NOT NULL default '',
>   `emailtipsuser` char(1) NOT NULL default '0',
>   `emailtipsall` char(1) NOT NULL default '0',
>   `latetips` text NOT NULL,
>   `winpoints` int(11) NOT NULL default '0',
>   `drawpoints` int(11) NOT NULL default '0',
>   `joinfee` float NOT NULL default '0',
>   `perfect8point` char(1) NOT NULL default '0',
>   `perfect8amt` float NOT NULL default '0',
>   `ranking` text NOT NULL,
>   `email` varchar(255) NOT NULL default '',
>   `signup_date` datetime NOT NULL default '0000-00-00 00:00:00',
>   `rules` text NOT NULL,
>   `status` text NOT NULL,
>   `pool` int(11) NOT NULL default '0',
>   PRIMARY KEY  (`id`)
> ) TYPE=MyISAM ;
> 
> 
> # Table structure for table `leaderboard`
> #
> 
> CREATE TABLE `leaderboard` (
>   `id` int(11) NOT NULL auto_increment,
>   `user_id` int(11) NOT NULL default '0',
>   `comp_id` int(11) NOT NULL default '0',
>   `username` text NOT NULL,
>   `points` int(11) NOT NULL default '0',
>   `amt` float NOT NULL default '0',
>   `margin` int(11) NOT NULL default '0',
>   `acc_margin` int(11) NOT NULL default '0',
>   `avatar` varchar(50) NOT NULL default '',
>   PRIMARY KEY  (`id`)
> ) TYPE=MyISAM ;
> 
> 
> CREATE TABLE `tips` (
>   `id` int(11) NOT NULL auto_increment,
>   `user_id` int(11) NOT NULL default '0',
>   `points` int(11) NOT NULL default '0',
>   `round` varchar(2) NOT NULL default '0',
>   `game` int(11) NOT NULL default '0',
>   `winner` text NOT NULL,
>   `comp_id` int(11) NOT NULL default '0',
>   `margin` int(11) NOT NULL default '0',
>   PRIMARY KEY  (`id`)
> ) TYPE=MyISAM ;
> 
> #
> # Table structure for table `users`
> #
> 
> CREATE TABLE `users` (
>   `id` int(11) NOT NULL auto_increment,
>   `comp_id` int(11) NOT NULL default '0',
>   `username` varchar(255) NOT NULL default '', 
>   `password` varchar(255) NOT NULL default '',
>   `phone` varchar(255) NOT NULL default '',
>   `email` varchar(255) NOT NULL default '',
>   `signup_date` datetime NOT NULL default '0000-00-00 00:00:00',
>   `last_login` datetime NOT NULL default '0000-00-00 00:00:00',
>   `activated` char(1) NOT NULL default '',
>   `first_name` varchar(50) NOT NULL default '',
>   `last_name` varchar(50) NOT NULL default '',
>   `avatar` varchar(50) NOT NULL default '',
>   PRIMARY KEY  (`id`)
> ) TYPE=MyISAM ;
> 
> }
> 
> ?>
> 
> 
> **************************************************
> 
> Regards MArk

Multi-table updates work just like multi-table selects. All you need to do 
is to swap things around a little.

UPDATE leaderboard l
INNER JOIN users u
        on u.comp_id = l.comp_id
SET l.avatar = u.avatar;

Because we are using an INNER JOIN, you will only be updating those 
records in leaderboard (because that what we said to do in our SET clause) 
with a valuefrom users (also from the SET clause) based on whether 
leaderboard.comp_id = users.comp_id (please look at the ON clause).

Please RTFineM for more details:
http://dev.mysql.com/doc/refman/4.1/en/update.html

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Thread
JOINs-- need some expertise on this oneMark27 Mar
  • Re: JOINs-- need some expertise on this onePeter Brawley27 Mar
  • Re: JOINs-- need some expertise on this oneSGreen28 Mar