Jump to content

Easy mysql dynamic table name.


mamanybono

Recommended Posts

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)
)"); 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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}'

Link to comment
Share on other sites

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)
)");

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.