php-mysql getting data from one table and populating another
Posted: Thu Jul 22, 2010 3:30 am
Dear All,
I am New on this website, and have got here with a question in my mind. i am fairly new to php programming however do manage to get things up and running. my querie concerned is mentioned below. along with the mysql table details.
I have Attached here with the database schema.
I have a a form which has
1. Textbox = message
2. checkbox = category
3. Submit Button
On Submission of the above details, i want to populate the "Out" Table with the information as mentioned below.
1. MSISDN = will come from the table "profile"
2. Message = From the Text Box.
My Concern here is. in the case that, during submission someone selects category id = "4"
Under the table "Category_profile_mapping" i have 6 Unqiue entries, with categoryID = 4, from which then i can select the profile id corresponding to each row and retrieve the MSISDN from the "profile" table. and along with the message from the text box, update the "out" table with 6 new records with same message and 6 unique msisdn's.
Please advice of any better way to do this? which can help me cut down on the system resource the above process can consume.
Also my concern is, as the database grows to six to eight digit, the whole process will slow down. i would highly appreciate any kinda of advice... which will help me in acheiving the above target in a better way.
Note : I have added only the required tables / and columns. please advice... and as attachement is not working for me, please find below the schema.
Regards,
James
I am New on this website, and have got here with a question in my mind. i am fairly new to php programming however do manage to get things up and running. my querie concerned is mentioned below. along with the mysql table details.
I have Attached here with the database schema.
I have a a form which has
1. Textbox = message
2. checkbox = category
3. Submit Button
On Submission of the above details, i want to populate the "Out" Table with the information as mentioned below.
1. MSISDN = will come from the table "profile"
2. Message = From the Text Box.
My Concern here is. in the case that, during submission someone selects category id = "4"
Under the table "Category_profile_mapping" i have 6 Unqiue entries, with categoryID = 4, from which then i can select the profile id corresponding to each row and retrieve the MSISDN from the "profile" table. and along with the message from the text box, update the "out" table with 6 new records with same message and 6 unique msisdn's.
Please advice of any better way to do this? which can help me cut down on the system resource the above process can consume.
Also my concern is, as the database grows to six to eight digit, the whole process will slow down. i would highly appreciate any kinda of advice... which will help me in acheiving the above target in a better way.
Note : I have added only the required tables / and columns. please advice... and as attachement is not working for me, please find below the schema.
Code: Select all
--
-- Database: `msg`
--
-- --------------------------------------------------------
--
-- Table structure for table `category`
--
CREATE TABLE IF NOT EXISTS `category` (
`categorid` int(11) NOT NULL AUTO_INCREMENT,
`Name` varchar(100) NOT NULL DEFAULT '',
PRIMARY KEY (`categorid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;
--
-- Dumping data for table `category`
--
INSERT INTO `category` (`categorid`, `Name`) VALUES
(1, 'Parents'),
(2, 'Teachers'),
(3, 'Students'),
(4, 'SJC_School');
-- --------------------------------------------------------
--
-- Table structure for table `category_profile_mapping`
--
CREATE TABLE IF NOT EXISTS `category_profile_mapping` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`categoryid` int(11) NOT NULL,
`profileid` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;
--
-- Dumping data for table `category_profile_mapping`
--
INSERT INTO `category_profile_mapping` (`id`, `categoryid`, `profileid`) VALUES
(1, 1, 1),
(2, 1, 2),
(3, 2, 3),
(4, 2, 4),
(5, 3, 5),
(6, 3, 6),
(7, 4, 1),
(8, 4, 2),
(9, 4, 3),
(10, 4, 4),
(11, 4, 5),
(12, 4, 6);
-- --------------------------------------------------------
--
-- Table structure for table `out`
--
CREATE TABLE IF NOT EXISTS `out` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`msisdn` int(11) NOT NULL,
`message` varchar(500) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
--
-- Dumping data for table `out`
--
-- --------------------------------------------------------
--
-- Table structure for table `profile`
--
CREATE TABLE IF NOT EXISTS `profile` (
`profileid` int(11) NOT NULL AUTO_INCREMENT,
`msisdn` int(11) NOT NULL DEFAULT '0',
`name` varchar(100) NOT NULL,
PRIMARY KEY (`profileid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;
--
-- Dumping data for table `profile`
--
INSERT INTO `profile` (`profileid`, `msisdn`, `name`) VALUES
(1, 9819333, 'Sam'),
(2, 9819334, 'Anita'),
(3, 9819335, 'Joe'),
(4, 9819336, 'Flow'),
(5, 9819337, 'Beck'),
(6, 9819338, 'James');
Regards,
James