Page 1 of 1

can i get table name from FORM?

Posted: Tue Apr 12, 2011 7:58 am
by muzammil
<html>
<head>
</head>
<body>
<form name="register" action="create.php" method="post">
username:<input type="text" name="user" />
e-mail:<input type="text" name="email" />
<input type="submit" name="submit" value="register" />
</form>

</body>
</html>

I am tryna to create a table and the name of the table should be the one that is user name. I m tryin following code. Its not working for me. Please help. Or is it even possible.

Code: Select all

<?php  //create.php 
$u=$_POST['user']; 
$con = mysql_connect("localhost","peter","abc123"); 
if (!$con) 
  { 
  die('Could not connect: ' . mysql_error()); 
  } 

if (mysql_query("CREATE TABLE '$u'",$con)) 
  { 
  echo "Database created"; 
  } 
else 
  { 
  echo "Error creating database: " . mysql_error(); 
  } 

mysql_close($con); 
?>

Re: can i get table name from FORM?

Posted: Tue Apr 12, 2011 1:10 pm
by McInfo
You should see an error that says something like,
Error creating database: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''example'' at line 1
In this case, the user/table name is example. The error tells you that there is a problem with the syntax of your query near the table name. The problem happens to be that apostrophes cannot be used to quote object identifiers such as table names. Instead, use backticks (formally known as U+0060 GRAVE ACCENT).

After you fix the first problem, you should see another error message that says,
Error creating database: No database selected
That should be easy to interpret. A table cannot be created without a database. Because there is no call to mysql_select_db() and otherwise no database referenced in the query, the table cannot be created.

After you fix that problem, you should see yet another error message that says,
Error creating database: A table must have at least 1 column
That means the query is incomplete. Read the MySQL Documentation page on CREATE TABLE Syntax and add at least one create_definition to the query.

Now, most importantly, there is a vulnerability in your script that will not reveal itself to you through an error message. Instead, it will wait silently for someone to take advantage of it. The vulnerability is known as SQL Injection, and is a threat until you filter and validate user-submitted data or at least take a few seconds to type 26 simple characters: mysql_real_escape_string().

Re: can i get table name from FORM?

Posted: Tue Apr 12, 2011 10:36 pm
by califdon
In addition to what McInfo has explained, it is almost certain that you do not need a table for each user! What you surely want is one table ("users") with a row for each user, containing whatever data is appropriate for each user.

Re: can i get table name from FORM?

Posted: Wed Apr 13, 2011 2:09 am
by muzammil
Thanks a lot guys you guys are awsome. Thank you verymuch for taking time to read my post. Yup my bad I didnot select the databse first.
And in my original code i have validated all the inputs.

So basically what happens in my original code is. The users signup in the table name 'Employes' and when all the input user name,password and other details is Ok I want another table created in the database 'Rosters'. And the name of the table would be the one that is entered by user in one of the fields while they are signing up. Thats all.

Re: can i get table name from FORM?

Posted: Wed Apr 13, 2011 2:17 am
by muzammil
So my original codes will look like this. is it good? I am newbie and just started to learn php. Please letme know if there are mistakes or blunders in my code. I would really appreciate it. Thanks

Code: Select all

<?php
$page_title = 'Register';
if(isset($_POST['submit']))
{
	require_once('mysql_connect.php');
	// connect to the db
	
	//Create a function for escaping the data.
	function escape_data($data)
	{
		global $con; // need connection
		if(ini_get('magic_quotes_gpc'))
		{
			$data = stripslashes($data);
		}
		return mysql_real_escape_string($data, $con);
	}
	$message = NULL;
	if(empty($_POST['firstName']))
	{
		$fn = FALSE;
		$message .= '<p>you forgot to enter your first name!</p>';
	}
	else
	{
		$fn = escape_data($_POST['firstName']);
	}
	
	if(empty($_POST['lastName']))
	{
		$ln = FALSE;
		$message .= '<p>You forgot to enter your last name!</p>';
	}
	else
	{
		$ln = escape_data($_POST['lastName']);
	}
	if(empty($_POST['licenceId']))
	{
		$li = FALSE;
		$message .='<p>You Forgot enter your Security Officer Licence Number!</p>';
	}
	else
	{
		$li = escape_data($_POST['licenceId']);
	}
	if(empty($_POST['crowdLicenceNo']))
	{
		$cln = FALSE;
		$message .='<p>You Forgot to enter your Crowd Controller Licence Number!</p>';
	}
	else
	{
		$cln = escape_data($_POST['crowdLicenceNo']);
	}
	if(empty($_POST['driverLicenceNo']))
	{
		$dln = FALSE;
		$message .='<p>You forgot to enter your Driving Licence Number!</p>';
	}
	else
	{
		$dln = escape_data($_POST['driverLicenceNo']);
	}
	if(empty($_POST['password']))
	{
		$p = FALSE;
		$message .='<p>You forgot to enter your password!</p>';
	}
	else
	{
			if($_POST['password'] == $_POST['password2'])
				{
					$p = escape_data($_POST['password']);
				}
			else
				{
					$p = FALSE;
					$message .='<p>Your password did not match the confirmed password</p>';
				}
	}
	if($fn && $ln && $li && $cln && $dln && $p)
	{
				
		$query = "SELECT ID FROM Employes WHERE SecurityLicence='$li'";
		$result = @mysql_query($query);
		if(mysql_num_rows($result) == 0)
		{ 
		
		
		$query = "INSERT INTO Employes (FirstName, LastName, SecurityLicence, CrowdLicence, Driverslicence, Password)
		VALUES ('$fn', '$ln', '$li', '$cln', '$dln', PASSWORD('$p'))";
		$result = @mysql_query($query);
			if($result)
				{
					echo '<p>You have been registered</p>';
						$firstquery = "CREATE TABLE `$li`
					(
					JobId int NOT NULL AUTO_INCREMENT,
					JobDate varchar(15),
					JobStart varchar(15),
					JobFinish varchar(15),
					JobLocation varchar(255),
					RequestedBy varchar(15),
					PRIMARY KEY (JobId)
					)";
					$firstresult = @mysql_query($firstquery);
						if(!$firstresult)
							{
								$message = '<p>2nd table not created.</p><p>' . mysql_error(). '</p>';
							}
		
					
				}
			else
			{
				$message = '<p>We apologise there is a system error.</p><p>' . mysql_error(). '</p>';
			}
		} 
		else 
		{
			$message = '<p>That Security Licence is already registered</p>';
		}
			mysql_close();
	}
	else
	{
		$message .='<p>Please try again</p>';
	}
}

//print the message if there is one
if (isset($message))
{
	echo '<font color="red">', $message, '</font>';
}

?>

Re: can i get table name from FORM?

Posted: Wed Apr 13, 2011 1:57 pm
by califdon
muzammil wrote:So basically what happens in my original code is. The users signup in the table name 'Employes' and when all the input user name,password and other details is Ok I want another table created in the database 'Rosters'. And the name of the table would be the one that is entered by user in one of the fields while they are signing up. Thats all.
That's where you're getting yourself into trouble. Each user should not have a separate table! That would create excessive overhead in the database, and make it nearly impossible to join tables in relationships. And what will you do if an employee is assigned to more than one job? No, you need only TWO tables here: Employees and Jobs. The Jobs table will have a foreign key to match the primary key of the Employees table, allowing an employee to be assigned to more than one job, perhaps at different times. This is the classic one-to-many relationship. The ER (Entity-Relationship) diagram would look something like this:

Code: Select all

+------------+       +------------+
| Employees  |       | Jobs       |
+------------+       +------------+
| EmplID (PK)|<--+   | JobID (PK) |
| FirstName  |   +---| EmplID (FK)|
| LastName   |       | JobDate    |
| SecurityLic|       | JobStart   |
| CrowdLic   |       | JobFinish  |
| DriversLic |       | JobLocation|
| Password   |       | RequestedBy|
+------------+       +------------+
You surely should make all those date fields real date/time datatypes, too. You will never forgive yourself later if you put dates and/or times into varchar fields, believe me!

I recommend that you read some tutorials on relational database design. Here's one I wrote a few years ago: http://tinyurl.com/RDBMSprinciples. Although it's written about Microsoft Access, the principles apply to every relational database.