Choosing a table to save to based on drop down choice

Question

I have 12 locations that I'm trying to figure out if I should create 12 tables (1 for each community) to save to or 1 table, add the location as a column, and throw everything in it and just get the data I need based on the location row?

CREATE TABLE `location1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `newsTitle` varchar(50) NOT NULL,
  `introParagraph` varchar(500) NOT NULL,
  `newsLink` varchar(100) NOT NULL,
  `downloadLink` varchar(100) NOT NULL,
  `file` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=15 ;

It probably really depends on your use cases. Do you think each community will always need the same exact columns of data? — Mike Brant, Jun 10 '14 at 06:22
You can use this for reference http://stackoverflow.com/questions/9774715/mysql-multiple-tables-or-one-table-with-many-columns based on your design. — v2solutions.com, Jun 10 '14 at 06:27
@MikeBrant the table will hold data for a newsletter and will be the same type of data each month. — Bonyboy, Jun 10 '14 at 06:48

Muhammad Raheel · Accepted Answer · 2014-06-10T06:36:29.467

You can use a single table for your requirement. When you need to save the data for locations which is different you can overcome this situation by using php serialize() and unserialize(). When you get data you can do whatever you want after unserializing. You can use the field as text.

CREATE TABLE `location` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `location` varchar(50) NOT NULL,
  `location_data` text(32565) NOT NULL
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=15 ;

Here is how you can save data in table

$location       =   'Location A';
$location_data  =   array('langitude' => 32.123 , 'longitude' => 87.3123 , 'status' => 'active');

$serialized =   serialize($location_data);
$query  =   "INSERT INTO location (location , location_data) VALUES ('$location','$serialized')";
mysqli_query($query);

$location       =   'Location B';
$location_data  =   array('test1' => 123 , 'test2' => 321); 

$serialized =   serialize($location_data);
$query  =   "INSERT INTO location (location , location_data) VALUES ('$location','$serialized')";
mysqli_query($query);

And when you get result

$query= "SELECT * FROM location WHERE id=1";
$rs = mysqli_query($query);
$row = mysqli_fetch_assoc($rs);
$location_data = unserialize($row['location_data']);

score 0 · Answer 2 · answered Jun 10 '14 at 06:27

0

1 table. If the only difference is which community the row belongs to, then definitely 1 table. If there will be other differences, then depends on use as Mike said.

answered Jun 10 '14 at 06:27

user3724715

1
1

Choosing a table to save to based on drop down choice

2 Answers2