"Mark" <[EMAIL PROTECTED]> 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
