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

[mamanybono]

I found the answer .

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

[NogDog]

Generally speaking, I probably would not recommend creating a separate table. Instead, use one table, and include in it a column for 'country' in this case, or probably to be even better normalized, a key to a row in a separate table that is just a country list. Then if you create an index on that column, it's a snap to get all entries for a given country from the single table (as opposed to ending up with dozens, maybe hundreds of separate tables, each with the same basic purpose.

country
===============
id  name
--  ----------------
1   turkey
2   greece
3   italy
(etc....)

ip_table
================
id  country_id  p1  p2  p3  p4  p5
--  ----------  --  --  --  --  --
1   1           ?   ?   ?   ?   ?
2   1           ?   ?   ?   ?   ?
3   3           ?   ?   ?   ?   ?
4   2           ?   ?   ?   ?   ?
5   3           ?   ?   ?   ?   ?
6   2           ?   ?   ?   ?   ?
(etc....)

Then in this example, to get all records from ip_table for Turkey:

SELECT * FROM ip_table
INNER JOIN country ON ip_table.country_id = country.id
WHERE country.name = 'turkey'

[mamanybono]

yes, you are right NogDog. I realized this and redisign my database.