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 ;
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']);
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.