Create a table from a variable name


#1

Hello,
Please forgive me if this has been asked before, but I am trying to create a table from from a variable name that is concatenated and then passed. The issue is my table is never created.

<?PHP
$Winning_Player1=$_POST['Winning_Player_1'];
$Winning_Player2=$_POST['Winning_Player_2'];
$Loosing_Player1=$_POST['Loosing_Player_2'];
$Loosing_Player2=$_POST['Loosing_Player_2'];


// this just makes it easy to modify the connection details site wide if details change
$host    ="localhost";
$user    ="root";
$pwd    ="";
$db        ="tennis_information";

$mysqli    = mysqli_connect($host, $user, $pwd, $db);
if (!$mysqli)
{
	die('Could not connect: ' . mysql_error());
}
//mysqli_select_db($db, $mysqli);
$WinningPlayer1 = mysqli_query($mysqli, "select * from players where players.Player_Number = '$Winning_Player1'");
while ($row = $WinningPlayer1->fetch_assoc()) 
{ 
   $FirstNameWinningPlayer1 = $row["First_Name"]; 
   $LastNameWinningPlayer1 = $row["Last_Name"]; 
} 
/*
$WinningPlayer2 = mysqli_query($mysqli, "select * from players where players.Player_Number = '$Winning_Player1'");
while ($row = $WinningPlayer2->fetch_assoc()) 
{ 
   $FirstNameWinningPlayer2 = $row["First_Name"]; 
   $LastNameWinningPlayer2 = $row["Last_Name"]; 
} 

$LoosingPlayer1 = mysqli_query($mysqli, "select * from players where players.Player_Number = '$Winning_Player1'");
while ($row = $LoosingPlayer1->fetch_assoc()) 
{ 
   $FirstNameLoosingPlayer1 = $row["First_Name"]; 
   $LastNameLoosingPlayer1 = $row["Last_Name"]; 
} 

$LoosingPlayer2 = mysqli_query($mysqli, "select * from players where players.Player_Number = '$Winning_Player1'");
while ($row = $LoosingPlayer2->fetch_assoc()) 
{ 
   $FirstNameLoosingPlayer2 = $row["First_Name"]; 
   $LastNameLoosingPlayer2 = $row["Last_Name"]; 
} 

*/

	
// Concatenation Of String
$combinedNamesWinningPlayer1 = $FirstNameWinningPlayer1."_".$LastNameWinningPlayer1;
  
//This checks to see if the table exsist and if not will create the table
$winningPlay1Name = "select * from ".$combinedNamesWinningPlayer1;

$winPlay1Name = mysqli_query($mysqli, $winningPlay1Name);

//if table is empty then create it
if(empty($winPlay1Name))
{
$sql = "CREATE TABLE " .$combinedNamesWinningPlayer1. "(
	id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
	firstname VARCHAR(30) NOT NULL,
	lastname VARCHAR(30) NOT NULL,
	email VARCHAR(50),
	reg_date TIMESTAMP)";
 echo "talbe Created";

}
else
{
echo "created already";
}

		if (mysqli_query($winPlay1Name, $sql)) 
			{
				echo "Table $combinedNames was created successfully";
			} 
		else 
			{
				echo "Error creating table: " . mysqli_error($sql);
			}
		}
		else 
		echo "The table for $FirstNameWinningPlayer1 $LastNameWinningPlayer1  already exsist";
?>

EDITED By Benanamen - Added Code Tags


#2

OP, creating the DB tables is a one time process. What is the point of doing code for it?


#3

This smells of very bad db design. I strongly suggest you do not make a new table for every winner. Your firstname_lastname table layout suggests it’s a users table, if you want to be able to query it somehow to select only winners or only spesific winners then there are way better methods to achieve that but we need more info on what you’re trying to do to suggest something


#4

I will second, third, and forth, that dynamically creating a table for this is a bad design.

What are you actually trying to store?


#5

Here is the logistics of what I am doing.

I have a form with 4 drop down boxes that are dynamically filled from a player db with a submit button. When the submit button is pressed it takes you to a page that has the code above. From here it will see tables exist according to what was selected in the list. If the table exist it doesn’t do anything, if it doesn’t then it creates the table. I am doing it this way so I can keep the wins losses and calculate dynamically items in the specific table for the player. I have this part working now, but i am running into issues on the input statement.


#6

But it’s totally uneccessary to have one table for each user. Just throw all users data in the same table and for queries/calculations just select where user_id = ? the user id you want to calculate/fetch data for. one table = one type of data, the moment you have multiple tables with the same columns you know you are doing something wrong


#7

I have a similar system I built for a client that is completely dynamic. You are over complicating what you need to do.


#8

If putting it into a user data table, which I already contain how would you keep past data for all of the wins and losses against each player? BTW each table of each user has 20 columns, if that matters? The example I gave above was for testing purposes.


#9

You need to normalize your data (you can research on the web what that means.)

The unique/one-time user information (first name, last name, email, …) would be stored in the user table. Different meaning data, related/belonging to the user, such as game result data, would be stored in a (one) separate table designed to hold the game result data, with a separate row for each piece of result data. The game result data would be related back to the user through the user’s id (auto-increment integer column in the user table.) You would not duplicate information between the two tables (only the user id is common between the tables) and to retrieve the user information and related game result data, you would JOIN the tables in a query.