Easy mysql dynamic table name.

[mamanybono]

Hi everybody,

I just want to learn this, I have $country_name variable in my code and if $country_name=turkey, I need to create a table in database as turkey_ip because of this I wrote below code and it works good. But I wonder is there a way to not create $_ip variable. I mean I want to use $country_name variable and _ip string in mysql query so ı do not need one extra variable.

 

$_ip=$country_name.'_ip';
        
        mysql_query("CREATE TABLE $_ip 
(
   id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
   p1 VARCHAR(50),
   p2 VARCHAR(50),
   p3 VARCHAR(50),
   p4 VARCHAR(50),
   p5 VARCHAR(50)
)"); 

[Pikachu2000]

If you're creating tables dynamically, chances are your database design needs to be rethought.

[mamanybono]

If you're creating tables dynamically, chances are your database design needs to be rethought.

 

Why, is there any disadvantages. Additionaly this is not a real project I am doing this for just learning. And this, creating table issue is not going to be busy because it is an admin panel, I am just trying to make admin panel more user friendly.

[Psycho]

If you're creating tables dynamically, chances are your database design needs to be rethought.

 

Why, is there any disadvantages. Additionaly this is not a real project I am doing this for just learning. And this, creating table issue is not going to be busy because it is an admin panel, I am just trying to make admin panel more user friendly.

 

If this is for learning purposes,then you should use it as an opportunity to learn the right way. Creating different tables to store the same type of data is inefficient and does not scale. Instead you just need to add another column to the table to identify the country. Here are a few example of why what you are doing is a bad idea:

 

1. If you need to pull records from multiple countries if only for gathering metrics or summary data, you would have to query each table individually or build up a massive query using UNION.

 

2. If you need to JOIN data between multiple tables you would have to put in variables for that table name. It makes the code harder to read and, more importantly, debug when there are issues.

 

So, instead of a query such as

SELECT *
FROM {$country_name}_ip
WHERE foo = 'bar'

 

You would instead write

SELECT *
FROM ip
WHERE foo = 'bar'
AND country = '{$country_name}'

[mamanybono]

Right psycho. I totally got you. I am going to change database design. Also I found the my original questions' answer in your reply. Curly brackets are the answer for my question. Thank you.

 

mysql_query("CREATE TABLE {$country_name}_ip 
(
   id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
   p1 VARCHAR(50),
   p2 VARCHAR(50),
   p3 VARCHAR(50),
   p4 VARCHAR(50),
   p5 VARCHAR(50)
)");