Needing help with MySQL

[thomas.mckean]

Good morning guys and gals hopefully you are all sticking to your New Years resolutions and having a great start to 2007 :)

I've been a phpfreaks member since the beginning of 2004 and just decided to join the forums, looks a great community, keep up the good work.

I am after a little help to start me own my PHP / MySQL journeys. My friend has designed a DB schema for himself to keep track of his favourite football (soccer as it may be referred to here) team. Now I also want to do this but I want to use a PHP front end to update it and also use it as a front end.

But first of I need to add records, but with all the information flying around I am unable to understand how to do it. I have read/watched many a mysql/php tutorials but when it comes to practise I always need a helping hand. So could someone (not asking to be spoonfed or nothing) help me out on how I would be able to INSERT data into this DB as it has quite a few tables and to be honest I don't understand much of it. I have included a copy of the schema below for anyone who decides to have a look.

Thanks for looking,

Thomas.

**edit** that sounds as if I am sponging for information. So if even if someone can point me in the correct direction for me to solve my problem I'd also be grateful.


[code]
-- MySQL dump 10.10
--
-- Host: localhost    Database: football
-- ------------------------------------------------------
-- Server version 5.0.24a-Debian_9-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `competition`
--

DROP TABLE IF EXISTS `competition`;
CREATE TABLE `competition` (
  `guid` int(4) NOT NULL auto_increment,
  `competition_name` varchar(64) NOT NULL default '',
  PRIMARY KEY  (`guid`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

--
-- Table structure for table `game`
--

DROP TABLE IF EXISTS `game`;
CREATE TABLE `game` (
  `guid` int(6) NOT NULL auto_increment,
  `opponent_uid` int(4) NOT NULL default '0',
  `venue_uid` int(4) NOT NULL default '0',
  `competition_uid` int(2) NOT NULL default '0',
  `round` varchar(64) default NULL,
  `date` date default NULL,
  `han` enum('h','a','n') default NULL,
  `goals_for` int(2) default NULL,
  `goals_against` int(2) default NULL,
  `attendance` int(6) default NULL,
  `notes` text,
  PRIMARY KEY  (`guid`)
) ENGINE=MyISAM AUTO_INCREMENT=4767 DEFAULT CHARSET=latin1;

--
-- Table structure for table `opponent`
--

DROP TABLE IF EXISTS `opponent`;
CREATE TABLE `opponent` (
  `guid` int(4) NOT NULL auto_increment,
  `opponent_name` varchar(64) NOT NULL default '',
  `nation_id` int(3) default NULL,
  PRIMARY KEY  (`guid`)
) ENGINE=MyISAM AUTO_INCREMENT=167 DEFAULT CHARSET=latin1;

--
-- Table structure for table `opponent_nation`
--

DROP TABLE IF EXISTS `opponent_nation`;
CREATE TABLE `opponent_nation` (
  `guid` int(4) NOT NULL auto_increment,
  `nation` varchar(64) NOT NULL default '',
  PRIMARY KEY  (`guid`)
) ENGINE=MyISAM AUTO_INCREMENT=33 DEFAULT CHARSET=latin1;

--
-- Table structure for table `player`
--

DROP TABLE IF EXISTS `player`;
CREATE TABLE `player` (
  `guid` int(4) NOT NULL auto_increment,
  `surname` varchar(32) default NULL,
  `firstname` varchar(32) default NULL,
  `commonname` varchar(32) default NULL,
  `DOB` date default NULL,
  `died` date default NULL,
  `notes` text,
  PRIMARY KEY  (`guid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Table structure for table `scorers`
--

DROP TABLE IF EXISTS `scorers`;
CREATE TABLE `scorers` (
  `guid` int(4) NOT NULL auto_increment,
  `player_uid` int(6) NOT NULL default '0',
  `game_uid` int(6) NOT NULL default '0',
  `goals` int(2) default NULL,
  PRIMARY KEY  (`guid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Table structure for table `signing`
--

DROP TABLE IF EXISTS `signing`;
CREATE TABLE `signing` (
  `guid` int(4) NOT NULL auto_increment,
  `player_uid` int(6) NOT NULL default '0',
  `signing_date` date default NULL,
  `leaving_date` date default NULL,
  PRIMARY KEY  (`guid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Table structure for table `team`
--

DROP TABLE IF EXISTS `team`;
CREATE TABLE `team` (
  `guid` int(4) NOT NULL auto_increment,
  `game_uid` int(6) NOT NULL default '0',
  `player_uid` int(6) NOT NULL default '0',
  PRIMARY KEY  (`guid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Table structure for table `tiebreak`
--

DROP TABLE IF EXISTS `tiebreak`;
CREATE TABLE `tiebreak` (
  `guid` int(4) NOT NULL auto_increment,
  `game_uid` int(6) NOT NULL default '0',
  `ffor` int(6) default NULL,
  `against` int(6) default NULL,
  `break_method_uid` int(2) NOT NULL default '0',
  PRIMARY KEY  (`guid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Table structure for table `tiebreak_method`
--

DROP TABLE IF EXISTS `tiebreak_method`;
CREATE TABLE `tiebreak_method` (
  `guid` int(2) NOT NULL auto_increment,
  `tiebreak_method_name` varchar(64) NOT NULL default '',
  PRIMARY KEY  (`guid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Table structure for table `venue`
--

DROP TABLE IF EXISTS `venue`;
CREATE TABLE `venue` (
  `guid` int(4) NOT NULL auto_increment,
  `venue_name` varchar(64) NOT NULL default '',
  PRIMARY KEY  (`guid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

[/code]

[hvle]

CREATE TABLE `signing` (
  `guid` int(4) NOT NULL auto_increment,  `player_uid` int(6) NOT NULL default '0',
  `signing_date` date default NULL,
  `leaving_date` date default NULL,
  PRIMARY KEY  (`guid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

let take a look at above table.
// guid is a field of type integer with 4bytes lenght, it is not allowed to be null and will be automaticly increment
so an appropriate data type for this is 5,100,3525423, but because it is auto_increment, it doesn't need to present.
//signing_dat is of type date or a string like this: YYYY-MM-DD, if this is not present, mysql will set it to NULL
// 'leaving_date' is same as signing_date
// PRIMARY KEY ('guid') will place guid as a key to distinguish row by row, ie. no 2 rows shall have a same guid value

so, to insert, you have to first have the datatype for each field correctly

$query = "insert into signing (`signing_date`, `leaving_date`) values ('2006-12-3', '2007-12-2')";
mysql_query($query);

this will insert a new row with signing_date = '2006-12-3' and leaving_date = '2007-12-2'
this also set a value of guid, which depend on the next available auto_increment value.  to be more specific, if your table initialy has 0 row, then the guid of the row you have just inserted is 1.  if you do another
mysql_query($query);
you would have 2 identical row with a different guid, the second one will be 2.

I hope this example show you the logic of insertion into database and you will learn to apply with other tables.

[thomas.mckean]

Hey hvle,

Great reply man thanks! I understand it which is great and shall help me on my travels to start with...but, how would I update say the other tables? Just do that as you says and maybe if I get enough data I should format and do a mysqlimprt on the data aslong as I can be sure that its going to insert correctly?

You have brought a lot of insight to what I didn't know but also raised another query as above.

I'll take a further look into things and post back with more details if I need more help but thanks for the help you have given me thus far!

Best Regards,

Thomas.

[hvle]

If you got stuck, feel free to post.