Page 1 of 1
Mysql & PHP problem
Posted: Mon Jul 20, 2009 10:53 am
by thkalam
Hello forum guys,
I m newbie to php/mysql so do not laugh with my problem...
So let me give you an example
I have a table with eg some schools. These schools have some facilities so i have created a table with facilities.
So i have created 2 tables one with schools and one with facilites.
My problem is that i need to add some of these facilities on each school. Maybe a school have 1 facility and maybe it has 100...
How i can add this info? and how i can retrieve it?
Re: Mysql & PHP problem
Posted: Mon Jul 20, 2009 10:59 am
by spider.nick
thkalam wrote:Hello forum guys,
I m newbie to php/mysql so do not laugh with my problem...
So let me give you an example
I have a table with eg some schools. These schools have some facilities so i have created a table with facilities.
So i have created 2 tables one with schools and one with facilites.
My problem is that i need to add some of these facilities on each school. Maybe a school have 1 facility and maybe it has 100...
How i can add this info? and how i can retrieve it?
Well, you have two tables, so all that you lack is a way to tie these two tables together.
Your reference table, perhaps called schoolfacility, would look like:
Your primary key
s would be both
school_id and
facility_id.
Then, when you go to query the database, you would join the three tables together, using
schoolfacility as the join 'glue'.
Nick
Re: Mysql & PHP problem
Posted: Mon Jul 20, 2009 11:15 am
by thkalam
you mean to add this info to a new table? this way seems easy...
Re: Mysql & PHP problem
Posted: Mon Jul 20, 2009 11:16 am
by spider.nick
thkalam wrote:you mean to add this info to a new table? this way seems easy...
Correct, add a new table. This new table works as cross reference, and allows 0 to infinity facilities per school.
Nick
Re: Mysql & PHP problem
Posted: Mon Jul 20, 2009 2:01 pm
by thkalam
It seems so hard for me.
lets say we have one school field and 10 facilities with check boxes...
If i had 1 field it would be easy cause for one id of school i d add one id of facility...
how is it possible to fill with same id of school and lot of ids of facilities different rows of a table?
Re: Mysql & PHP problem
Posted: Mon Jul 20, 2009 2:05 pm
by califdon
Fundamental part of relational databases: many-to-many relationships. Requires 3 tables. Read
http://www.tekstenuitleg.net/en/article ... -many.html or any tutorial on relational databases.
Re: Mysql & PHP problem
Posted: Tue Jul 21, 2009 10:07 am
by thkalam
i have created 3 tables 1 schools 1 facilities and 1 with 2 ids from the first 2 tables.
The problem is that i just know how to fill one row on mysql eg school1 and facility 1 where i can find code to fill school1 facility1 & school1 facility 2 with the same code?
Re: Mysql & PHP problem
Posted: Tue Jul 21, 2009 10:23 am
by spider.nick
thkalam wrote:i have created 3 tables 1 schools 1 facilities and 1 with 2 ids from the first 2 tables.
The problem is that i just know how to fill one row on mysql eg school1 and facility 1 where i can find code to fill school1 facility1 & school1 facility 2 with the same code?
I am not convinced that you have searched for, or tried to figure the problem out on your own, but I will succumb this time.
Code: Select all
$result = mysql_query('INSERT INTO school VALUES (*\'*\', *\''.$schoolName.'*\')');
$schoolID = mysql_insert_id();
$insertSQL = 'INSERT INTO facility VALUES ';
foreach( $schoolFacilityID as $facilityID )
$insertSQL .= '(*\'.$schoolID.'*\', *\''.$facilityID.'*\'), ';
$insertSQL = substr( $insertSQL, 0, (strlen($insertSQL) - 2) );
$insertRES = mysql_query($insertSQL);
There is a great start. Take it and run ... run Forrest, run!
NOTICE: Use only one \; this forum does not render the \, so I have to output \\ in order for you to see it.
UPDATED NOTICE: Replace * with \.
Nick
Re: Mysql & PHP problem
Posted: Tue Jul 21, 2009 10:33 am
by thkalam
it seems chinese to me...
i have to research again and again i think
Re: Mysql & PHP problem
Posted: Tue Jul 21, 2009 12:55 pm
by califdon
thkalam wrote:i have created 3 tables 1 schools 1 facilities and 1 with 2 ids from the first 2 tables.
The problem is that i just know how to fill one row on mysql eg school1 and facility 1 where i can find code to fill school1 facility1 & school1 facility 2 with the same code?
I can't really understand you. There are 3 tasks:
- Enter data for schools
- Enter data for facilities
- Enter linking data for the 3rd table
The first 2 tasks are the most basic tasks that absolutely every database project requires, so I assume that you know how to do that, or if not, you will learn that from a beginner's tutorial. How you accomplish the 3rd task depends on whether you just need to do it once, for a reasonably small number of schools/facilities, or whether this is an ongoing requirement and you need to have an interactive form-based method. If it is the latter, the simplest way is to have a form with two drop-down selection boxes, one for schools and one for facilities, and a submit button that can be clicked to form a relationship. The user selects a school, then selects a facility, then clicks on the submit button, which submits the form to a script that inserts a new record in the 3rd table, with the id values of the school and the facility, which would be the values in the <option> tags of the selected choices in the form.