php-mysql getting data from one table and populating another

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
jamejo
Forum Newbie
Posts: 2
Joined: Thu Jul 22, 2010 2:29 am

php-mysql getting data from one table and populating another

Post by jamejo »

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.

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
DaiLaughing
Forum Commoner
Posts: 76
Joined: Thu Jul 16, 2009 8:03 am

Re: php-mysql getting data from one table and populating ano

Post by DaiLaughing »

I may not be alone in not understanding your question. Can you give us a clearer idea of what data you need and why?

Certainly putting the same data into a table six times is usually a sign of problems.
jamejo
Forum Newbie
Posts: 2
Joined: Thu Jul 22, 2010 2:29 am

Re: php-mysql getting data from one table and populating ano

Post by jamejo »

Hey, DaiLaughing...

Thank you for your response, i am not sure how to best explain this however let me try to explain once again hopin that i can be clearer this time.

1. A form where user's enter a message in the textbox named "message"
2. On the same form a Checkbox option to select Category for which the value is fetched from the Table "Category"

Note: I have a Table named "Category_Profile_mapping" which holds the primary key "ID" of entries from table "Category" & "Profile", which allows me to link each profile to a category or more.

As the User Submit's This Information, i want to populate the table "OUT".
a. Where based on the category selected from the form ( I need to get all the entries in the table "profile" and retrieve only its column named "MSISDN")
b. And if under Category selected there are 6 entries, available in "profile" table. we make six unique entries with the same message from the textbox however with unique MSISDN's which are retrieved from the table profile.

I am sure i am bad at explaining however thats basically my understanding of the DB_SChema i have designed.

For a better explaination, i would say. i want to have a table with profile of users which can be added into multiple categories.
And during submission of a form based on the selected category, i want to populate another table by retrieving all the MSISDN's from the profile table for the entries that falls under the category selected in the form.
My Concern is if there is more than 1000 Rows, this could all slow down the process hence is there a better way of doing it?

Kind Regards,
James Joseph :) Thanks once again buddy
DaiLaughing
Forum Commoner
Posts: 76
Joined: Thu Jul 16, 2009 8:03 am

Re: php-mysql getting data from one table and populating ano

Post by DaiLaughing »

Every time I read this I feel I am almost understanding but then I just can't quite get the whole thing!

Are you saying that a user might send a message and that the categories are where the message will go? Can you give me some examples of the what the categories are?

The profiles seems to be profiles of the user. Does each user have one profile?

Do users also belong to a number of categories? So that if one user sends a message it goes to all of the people in the categories he selects?

I've probably got all that wrong!
Post Reply