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:

Code: Select all

 
school_id, facility_id
 
Your primary keys 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.