Solved

limit mysql insert to 22 players

Posted on 2010-08-17
10
362 Views
Last Modified: 2013-12-13
Hi,

I have been given a task to create an insert page where I can add football players into a database, I am using dreamweaver with php/mysql

I am not sure how I can do this without it being long winded.


I originally thought having 22 boxes on a page with textfields, but thats crazy.


Is there a way using dreamweaver I can disable the function of inserting players for that team.


My player table is as below





CREATE TABLE IF NOT EXISTS `player` (
  `player_id` int(8) NOT NULL auto_increment,
  `fname` varchar(100) NOT NULL,
  `sname` varchar(100) NOT NULL,
  `gender` varchar(10) default 'Male',
  `nationality` varchar(50) default NULL,
  `email` varchar(255) default NULL,
  `dob` date default NULL,
  `place_birth` varchar(255) default NULL,
  `height` varchar(20) default NULL,
  `weight` varchar(10) default NULL,
  `photo` varchar(255) default NULL,
  `comments` text,
  `position` varchar(255) NOT NULL,
  `team_id` int(8) NOT NULL,
  `Goals` int(5) default '0',
  `YC` int(5) default '0',
  `RC` int(5) default '0',
  `MOM` int(5) default '0',
  PRIMARY KEY  (`player_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

Open in new window

0
Comment
Question by:cataleptic_state
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
10 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33454772
add a team_player_num field int(2), with a check contraint of allowing values from 1 to 22, and make team_id + team_player_num a unique constraint.

that will ensure that you cannot have values other than 1 to 22 for that field, and with the team_id together ensure you can have max 22 players per team
0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33454983
how will the page interact with this tho?
0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33455093
What I mean is do I just create 1 form, and then do what kind of processing to check?

0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33455120
your insert will need to supply a team_player_num, for the new team member, that is not yet used...
so you could query for all values of the team already used, and not show those, aka use the first value that is not used ...
do you need help coding/doing this query first?
0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33455386
why do i need to have team_player_num, could I not use the player_id?

I think I will need help with the query :'(
I was thinking if the team is selected and the players selected and the total rows = 22 then do not allow insert and make the form disappear and a message come up.
0
 
LVL 70

Accepted Solution

by:
Jason C. Levine earned 500 total points
ID: 33456413
>> I was thinking if the team is selected and the players selected and the total rows = 22

Yes, that would be the easiest way.  Your form inserts a single player and a recordset on the same page exists to count the total number of rows for the team_id.  So long as count(player_id) WHERE team_id = x is less than 22, display the form.  As soon as it is not less than 22, do not display the form.

>> I originally thought having 22 boxes on a page with textfields, but thats crazy.

Not as crazy as you might think...You would have the textboxes exist as an array:

<input name="player[]" type="text" />

The user fills out the form and then you would take the array and step through it, inserting each value as a new row in the mysql table.  However, this starts to get you outside of the built-in capabilities of DW unless you buy a different set of extensions.  The first solution is easier to implement but has some issues for the end user (hard to remember who has already been entered unless you take additional steps to display existing player names on the page.
0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33473314
Thank you jason1178 that brilliant. Can you take a look at my other post regarding a results grid, it seems too complicated for me to put together
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33473321
just my 2 cents: that will work, but is not failsafe... open 2 forms when 21 players are in the team, you have 2 forms to submit another one.
only a constraint on the db level will ensure that the 2nd form will fail to insert...
0
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 33477169
>> that will work, but is not failsafe... open 2 forms when 21 players are in the team, you have 2 forms to submit
>> another one.

Absolutely.  I read the question as a single user who is creating a team and that there should not be multiple users logged in to the same team with creation privileges.  If the latter is indeed the case, then my solution is too simplistic for deployment.
0
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 33487318
>> Can you take a look at my other post regarding a results grid

I saw that one come through and it's fairly tough so I'll need to take a second look at it over the weekend.
0

Featured Post

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question