How can I create a MySQL table using form input?

[DigitalSky]

Okay so I'm relatively new to MySQL and while I'm learning fast I'm stuck on something.

First off here is my code...

<?
 // Connect to the MySQL database
$username="walkevent";
$password="#*$!#*$!";
$database="walkevent";

mysql_connect("localhost",$username,$password);
mysql_select_db($database) or die( "Unable to select database");

$name = mysql_real_escape_string(htmlentities($_POST['name'])); 
$email = mysql_real_escape_string(htmlentities($_POST['email'])); 
$phone = mysql_real_escape_string(htmlentities($_POST['phone']));
$teamName = mysql_real_escape_string(htmlentities($_POST['teamName']));

$query = "CREATE TABLE $teamName (id int(6) NOT NULL auto_increment,name varchar(60) NOT NULL,email varchar(50) NOT NULL,phone varchar(15) NOT NULL,INDEX (id))";

$query = "INSERT INTO $teamName VALUES ('','$name','$email','$phone')";

mysql_query($QueryOne); 
if(!mysql_error()) { 
// Confirmation 
} else { 
}

mysql_query($query); 

?>

Okay, so what this does basically is it takes input from a form I have where it asks for the persons name, email, phone number, and desired team name. What it then does is takes that info and creates a table inside the 'walkevent' database.

So most of it works great so far. When someone completes the form and submits it it sure as heck creates a table but here are my two problems...

1) When it creates the table it doesn't store the information in it (name, email, and phone). How can I correct my code to allow it to insert that persons information in the table when it's being created?

2) Right now when someone enters their team name (the 'teamName') variable, it uses that variable as the name for the table. Now obviously I know there can't be any spaces or punctuation in MySQL table names so I'm wondering what I can to to make sure that if someone enters a team name like 'Jason's Team' it will instead use something like 'JasonsTeam'.

[DigitalSky]

Okay I figured out the answer to my first question, now it's just question number two I need help on.

[Matthew1980]

Hi there DigitalSky,

It will be a mixture of preg_match() to check that the user hasn't put in any illegal chars, you could use ctype_alpha() ( check out:[uk3.php.net ]) to the same effect (would mean no messy regexp patterns too ;)) then trim() to get rid of white space. Just a few kneejurk thoughts there for you...

Oh, and for future reference, try to use the long tags when declaring php (<?php) as not all servers these days have the option set in the ini file for use of short tags (<?) And in the longrun this will make you code more portable & save headaches if you came to change servers in the future ;)

Have fun!

Cheers,
MRb

[Mahabub]

$teamName = preg_replace('/[\`\~\!\@\#\$\%\^\*\(\)\; \,\.\'\/\_\-]/i', '',$_POST['teamName']);


try this ..........

Thanks
Mahabub

[DigitalSky]

Mahabub,

That almost worked but not quite... For some reason the pre_replace function doesn't seem to work yet if I do this...

$teamTableName = ereg_replace('[^A-Za-z0-9]', '',$_POST['teamTableName']);

and use the ereg_replace function it works great. Now naturally I don't want to use the ereg_replace function since it's deprecated in newer versions of PHP. Can anyone tell me why it might be that the preg_replace function isn't working but ereg_replace is?

[edited by: DigitalSky at 8:54 am (utc) on Jun 1, 2010]

[DigitalSky]

I don't know what's going on but that code SHOULD work but it's not working. The ereg_replace function seems to work though so what gives...

This code works...

<?php
// Connect to the MySQL database
$username="walkevent";
$password="#*$!#*$!x";
$database="walkevent";

mysql_connect("localhost",$username,$password);
mysql_select_db($database) or die( "Unable to select database");

$name = mysql_real_escape_string(htmlentities($_POST['name']));
$email = mysql_real_escape_string(htmlentities($_POST['email']));
$phone = mysql_real_escape_string(htmlentities($_POST['phone']));
$teamName = mysql_real_escape_string(htmlentities($_POST['teamName']));
$teamTableName = ereg_replace('[^A-Za-z0-9]', '',$_POST['teamTableName']);

$queryOne = "CREATE TABLE $teamTableName (id int(6) NOT NULL auto_increment,name varchar(60) NOT NULL,email varchar(50) NOT NULL,phone varchar(15) NOT NULL,teamName varchar (100) NOT NULL,INDEX (id))";

$queryTwo = "INSERT INTO $teamTableName VALUES ('','$name','$email','$phone','$teamName')";


mysql_query($queryOne);
if(!mysql_error()) {
mysql_query($queryTwo);
if(!mysql_error()) {
// Confirmation
} else {
}
} else {
}

mysql_query($query);

?>


This code doesn't...

<?php
// Connect to the MySQL database
$username="walkevent";
$password="#*$!#*$!x";
$database="walkevent";

mysql_connect("localhost",$username,$password);
mysql_select_db($database) or die( "Unable to select database");

$name = mysql_real_escape_string(htmlentities($_POST['name']));
$email = mysql_real_escape_string(htmlentities($_POST['email']));
$phone = mysql_real_escape_string(htmlentities($_POST['phone']));
$teamName = mysql_real_escape_string(htmlentities($_POST['teamName']));
$teamTableName = preg_replace('/[\`\~\!\@\#\$\%\^\*\(\)\; \,\.\'\/\_\-]/i', '',$_POST['teamTableName']);

$queryOne = "CREATE TABLE $teamTableName (id int(6) NOT NULL auto_increment,name varchar(60) NOT NULL,email varchar(50) NOT NULL,phone varchar(15) NOT NULL,teamName varchar (100) NOT NULL,INDEX (id))";

$queryTwo = "INSERT INTO $teamTableName VALUES ('','$name','$email','$phone','$teamName')";


mysql_query($queryOne);
if(!mysql_error()) {
mysql_query($queryTwo);
if(!mysql_error()) {
// Confirmation
} else {
}
} else {
}

mysql_query($query);

?>

[Matthew1980]

Hi there DigitalSky,

Just a heads up on the ereg function - it's depricated, so you may watch to change to a preg_replace instead, similar pattern though.

preg_replace('/^[\`\~\!\@\#\$\%\^\*\(\)\; \,\.\'\/\_\-]$/i', '',$_POST['teamTableName']);

I think that should be ok should be along the lines of '/^what_pattern_you_need$/i i means case insensitive ;)

Cheers,
MRb

[rocknbil]

I doubt that it's the difference between preg_relace and eregi . . . that's showing the difference, you are using two different patterns in the two. And yes, eregi is scheduled to become extinct in v6 . . .

what I can to to make sure that if someone enters a team name like 'Jason's Team' it will instead use something like 'JasonsTeam'.

The first preg pattern **might** be fine, but it's probably neither efficient or accurate. What is does is removes specific characters, and anything you **didn't think about** and include in that list will still "Get through."

Your original solution

[^A-Za-z0-9]

is probably closer to what you want. The leading ^ within the character class means "anything NOT these" which is simple and probably more accurate. But let's make it even more terse:

A-Za-z is not necessary with the case insensitive modifier, i. \w is often a good substitute for "word characters", but there are other characters that fall under \w than a-z.

\d is any digit.

The underscore **is** a legal table name character, take it in, leave it out, your choice.

$teamTableName = preg_replace('/[^a-z\d\_]+/i', '',$_POST['teamTableName']);

Note that when you "remove anything NOT these characters" the beginning and ending string delimiters are not necessary:

'/^[^a-z\d\_]+$/i'

as it doesn't matter **where** they are, they will be removed. (the + is probably not necessary either, it's a habit of mine, it means "one or more of the preceding." Remove if it works without it.)

All things considered, I would be very reluctant to allow your database user (i.e., your public script) to create new tables. This can make your DB very huge in a very short time, and complicates your error trapping - you will, of course, have to insure the name is not taken and it doesn't already exist. It may require some stronger security considerations as well.

For whatever you're trying to do, there is likely a safer way than creating a table for each member. Joins on relational tables, for example. You might want to re-think this part. :-)

[DigitalSky]

Bill,

Wow, thank you so much! Your response was very thorough and I learned a lot from it so thank you again. That code you wrote worked great actually.

Anyhow the reason I'm allowing the user to create is a table is because I have this event coming up in December that is a memorial walk even for anyone who has lost a child. Basically what I want people to be able to do is to come to my website and create their own team (in memory of someone they lost). When they create their team it will create a table for their team with the rows id, name, email, phone, amount_raised.

What will then happen is that other users (or friends of theirs) will be able to come to the site and opt to join any team of their choice (from a drop down list in a registration form).

Now I thought about putting this all in one table but then every time someone creates a team it would all be part of one table. That being said, when someone wanted to join a team it would add them to that one table. The problem there is that then I don't know how I would extract the information from the database per team.

For instance, on each teams page I would want to list all of their members names and amounts of money they've raised. That might be a lot harder if it's pulling it from one huge table as an individual table per team.

Realistically speaking I don't think I'll have hundreds of teams. I doubt it will exceed 50 to be honest.

[rocknbil]

You only need two tables, and you join on the originating member's unique id. The example below only needs name, email, etc. if you want the name and email of the opt-in team members. Of course you wouldn't use obvious names like members and teams, but . . . .

table members
id|email|fname|lname|team_name[etc]

The exception to the previous is if a member has multiple teams. Then you'd put team_name in the teams table instead (and you'd also add an order by teams.team_name to the query).

table for their team

table teams
id|member_id|t_email|t_fname|t_lname|amt_raised[etc]

Get John Doe's totals, say, by id. Store the sum of the team's totals in a variable. You could use any field or combo of fields to search by, but a clickble link could be

<a href="totals.php?m=1234">John Doe</a>

$id=(isset($_GET['m']) and is_numeric($_GET['m']) and ($_GET['m']>0))?$_GET['m']:null; 
if (! $id) { do_error_trap_here(); } 
// 
// set these to null, we'll only need them once and it's going to return multiple rows 
$member_name=$teamname=$total=$results_rows=$out=null; 
// 
// Of course, use classes instead . . . 
$lt = ' style="background:#fff"'; 
$dk = ' style="background:#f5f5f5"'; 
$hd = 'text-align:center;color:#fff;font-weight:bold;background:#000;'; 
// 
$query = "select sum(amt_raised) as tot, members.fname,members.lname, 
members.team_name,teams.t_fname,teams.t_lname,teams.amt_raised 
from members,teams where members.id=teams.member_id and 
members.id=$id order by teams.amt_raised desc, teams.t_lname asc, 
teams.t_fname asc group by teams.member_id"; 
$result = mysql_query($query) or die(mysql_error()); 
while ($row = mysql_fetch_array($result)) { 
 // 
 // alternate BG colors . . .  
 $bg = ($bg == $lt)?$dk:$lt; 
 if (! $member) { $member = $row['fname'] . ' ' . $row['lname']; } 
 if (! $teamname) { $teamname = $row['team_name']; } 
 if (! $total) { $total = $row['tot']; } 
 // 
 $results_rows .= ' 
 <tr><td ' . $bg . ' nowrap>' . $row['t_fname'] . ' ' . $row['t_lname'] . '</td> 
 <td ' . $bg . ' nowrap> $' . $row['amt_raised'] . '</td></tr> 
 '; 
} 
// 
if ($results_rows) { 
 $out = '<table style="width: 400px; margin:auto;"> 
 <tr><td colspan="2">Earnings for ' .  
 "$member of team: $teamname" .  
 '</td></tr> 
 <tr><td style="' . $hd . '">TEAM MEMBER</td> 
 <td style="' . $hd . '">EARNINGS</td></tr>' . 
 $results_rows . 
 '</table> 
 '; 
} 
else {  
 // If there's no results, you'll have to do a new query here 
 // to get their name for output. 
 $out = '<p>Member ' . $member . ' has not yet assembled a team.</p>';  
} 
// 
echo $out; 

What will then happen is that other users (or friends of theirs) will be able to come to the site and opt to join any team of their choice (from a drop down list in a registration form).

$team_list = get_team_list('team_name'); // now just put $team_list in your form 
// 
function get_team_list($name) { 
 if (preg_match('/^[^a-z\_\-]$/i',$name)) { die("Invalid name passed to get team list"); } 
 // 
 $list = '<select name="' . $name . '" id="' . $name . '"> 
 <option value="">Select Team</option> 
 '; 
 // 
 $query = "select id,team_name,fname,lname from members order by  
 lname asc,fname asc, team_name asc"; 
 $result = mysql_query($query) or die(mysql_error()); 
 while ($row = mysql_fetch_array($result)) { 
 $list .='<option value="' . $row['id']; 
 if (isset($_POST[$name]) and ($_POST[$name]==$row['id'])) { 
 // selected="selected" for XHTML types 
 $list .= ' selected'; 
 } 
 $list .= '">' . $row['team_name'] . ' (' . 
 $row['fname'] . ' ' . $row['lname'] . ')</option>'; 
 //you get: <option value="123">Flaming Tigers (John Doe)</option> 
 } 
 $list .= '</select>'; 
 return $list; 
} 

May contain errors . . . needs security considerations, left out for simplicity . . . but that's the idea anyway . . . .

[DigitalSky]

Oh damn... I'm in WAY over my head.

[DigitalSky]

Okay so I did manage to figure all of this out by using some of your code Bill so thank you very much.

Now the only thing I seem to be stuck on is how to echo the entire contents of a table row.

For instance... I want to echo the contents of table rows 'fname' and 'lname' so I can display a list of each persons name who has signed up for the event.

I tried this code but all it does is output the most recent row in that column.

$query="SELECT * FROM members";
$result=mysql_query($query);

$num=mysql_numrows($result);

$i=0;
while ($i < $num) {

$firstName=mysql_result($result,$i,"fname");
$lastName=mysql_result($result,$i,"lname");

$i++;
}

Oh and obviously I'm outputting the data in my HTML using this line...

<?php echo $firstName; ?> <?php echo $lastName; ?>

[rocknbil]

OK well . . . what is your table structure? Are you still trying to create tables? I ask because you're saying you want to output who's joined a team, but I only see you querying the member's table.

A couple comments, take them or leave them . . .

I tried this code but all it does is output the most recent row in that column.... Oh and obviously I'm outputting the data in my HTML using this line...

Put that line INSIDE the while loop. Understand what is happening.

$num=mysql_numrows($result);

You get a count of results, then use an $i counter to step through them using $num as the limit. So if your echo comes AFTER the while loop, the variables only contain the last value.

I see this a lot, and there may be a reason for this approach, but I don't "get it." I have a simplified version for you below, basically, the end result is the same, it just doesn't use a counter and an extra function (mysql_numrows) or a counter loop to step through.

$query="SELECT * FROM members"; 
$result=mysql_query($query); 
while ($row=mysql_fetch_array($result)) { 
 $firstName=$row['fname']; // see note below 
 $lastName=$row['lname']; 
 echo "$firstName $lastName <br>"; 
} 

See how the echo is inside the while loop?

A note on mysql_fetch_array: This stores **both** an indexed array and an associative array in $result. So these are equivalent:

$row[0] == [ whatever your auto increment field is, making an assumption here ] OR $row['id']

$row[1] == $row['fname'] [ Assuming it's the second field in the table ]

$row[2] == $row['lname'] [ Assuming it's the third field in the table ]

Hope that doesn't throw you off, but it's an important thing to know when you decide to anonymously step through results without knowing the field name.

[DigitalSky]

Bill,

What I'm trying to do is a few things...

On one of the pages I'm listing the members of each individual team. On another page I'm listing ALL of the names of the people who signed up (regardless of what team they're on).

My table structure is as follows...

table members
id|fname|lname|email|phone|team_name

table teams
id|member_id|t_fname|t_lname|t_email|t_phone|amt_raised

The code you showed me worked good for the most part.

Basically the only issue I'm having from it now is that obviously I want it to pull the first name and last name from BOTH tables since there are still members who get added to the 'teams' table which are the members who created the team (the team owner).

The other thing is that I have your code in a file called 'output.php'

output.php is being referenced on the page (which is called index.php) that I want to list the participants on like so...

require_once('includes/output.php');

Now I notice that it DOES echo all the first name and last names from the members table BUT it does so in the upper corner of the screen.

What I'd like to do is echo the contents of all the rows in a variable like so...

<?php echo $firstName, $lastName; ?>

The only issue with that is that when I do it it only echos the most recent fname and lname row from the table.

I guess what I'm saying is that to clean my code up I'm trying to find a way to echo those variables OUTSIDE of the loop in order to echo them on a different page and make everything cleaner.

[rocknbil]

On one of the pages I'm listing the members of each individual team. On another page I'm listing ALL of the names of the people who signed up (regardless of what team they're on).

You mean like

member - team
- team member data
- team member data
- team member data
member - team
- team member data
- team member data
- team member data
member - team
- team member data
- team member data
- team member data

Then you need a while loop on the first query with the second inside, or just "switch" the headers when the member name changes. Is that correct? Then you can use the code on the previous page to figure out the outer loop.

BUT it does so in the upper corner of the screen.

Shouldn't . . . you should have a table in the center of the screen. Ah, may have errors, it's untested.