List:General Discussion« Previous MessageNext Message »
From:Mark Date:March 27 2006 2:45pm
Subject:JOINs-- need some expertise on this one
View as plain text  
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
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