Help in query..

sweets · #1 (**permalink**) 22-03-2007, 05:03 PM

hi..
i got a little prob here.. as im still new to 'php world'.. i have question about mysql query..
okie.. i had to make a web form, and it has 4 tables which is Report, const_acc, const_location dan Position. const_acc,const_location and Position are tables with checkboxes..
all tables have the same primary key (ID)<--auto increment.

Report table has the all main information on the form.
the form need a 'Search' button for const_acc.

the question is: how im gonna query out const_acc along with all the information link to it in Report, const_location and position?? (do u guys even understand?? haha

.. my english sucks..

)

id learned only basic of php..
so, i knew that it would be something like this right?

PHP Code:


			
$variable = $_POST['search_acc'];

    $sql="SELECT * FROM Location WHERE const_acc like '%".$variable."%'";

but.. it'll only display values in const_acc..
i tried to search how to create the query in php manual.. but failed..

if anyone have link/source on how to do it..it will be helpful..

or you can just help me directly.. haha

kalu ade sape2 yg nk bls dlm bm pn boleh je.. lagi baik.. hehe

yipguseng · #2 (**permalink**) 23-03-2007, 10:37 AM

1st of all, i would like to know if it is necessary to use 4 tables for those coz if i m not wrong and if i happen to understand wat u wan, they wan u doin it doesnt seems to b right.

======================

i give an example of a basic form here for reference:
Form : Name, Tel, Email, Address.

for this i will create only 1 table which is let say: reg_table

now the reg_table will have followings element : reg_id, reg_name, reg_tel, reg_email, reg_add

now with this u can display all the infos as easy as pancake with just "SELECT * FROM reg_table" and if u wan u can add the condition to filter

=========================

the way u do it is still workable, but u need to have a foreign key to link all the 4 tables together which appears to be quite troublesome. so hope this does really help u

sweets · #3 (**permalink**) 23-03-2007, 10:58 AM

At first, i did only 1 table for those.. but it quite difficult coz i have checkboxes to select.. in order to insert multiple values from checkboxes into table, i have to separate them(checkboxes table) right??
for example.. this is the code for my checboxes insert.

PHP Code:


			
if(isset($_POST['Injured_person']))

{

    $values = implode(",", $_POST['Injured_person']);

    $query = "INSERT INTO position (Injured_person) VALUES('$values')";

    mysql_query($query) or die("Error inserting into position: ".mysql_error());

    

}

if(isset($_POST['Type']))

{

    $values = implode(",", $_POST['Type']);

    $query = "INSERT INTO const_acc (Type) VALUES('$values')";

    mysql_query($query) or die("Error inserting into const_acc: ".mysql_error());

    

}

{

if(isset($_POST['Location']))

{

    $values = implode(",", $_POST['Location']);

    $query = "INSERT INTO const_location (Location) VALUES('$values')";

    mysql_query($query) or die("Error inserting into const_location: ".mysql_error());

}

mysql_close($connect);

}

Or.. can i simply combine them all with Report table??
I still had problem with inserting data into Report table.. as i post the problem before... but no prob with checboxes inserting..
Really need help for this..

yipguseng · #4 (**permalink**) 24-03-2007, 10:11 AM

mayb it would be helpful if u could provide a screenshot or a testsite to see on how's ur form looks like

sweets · #5 (**permalink**) 26-03-2007, 08:49 AM

This is my form..
i cant post an attachment, so i just could post the form separately(the codes were too long to post)..
the 1st half of the form:

PHP Code:


			
<html>

<head>

<title>Untitled Document</title>

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">

</head>



<body>

<form name="form1" method="post" action="">

  <p align="center">CARE / PAIR</p>

  <p align="center">CREW/CONTRACTOR/PASSENGER ACCIDENT REPORT</p>

</form>

<form action="<?php echo $_SERVER['PHP_SELF'] ?>" method="post" name="form1" id="form1">

  <p><strong>Part A - General Information</strong></p>

  <table width="95%" border="0">

    <tr>

      <td width="19%">Name of Vessel:</td>

      <td width="19%" bordercolor="#000000"><input name="Name_of_Vessel" type="text" id="Name_of_Vessel"></td>

      <td width="6%">Time:</td>

      <td width="21%" bordercolor="#000000"><input name="Report_Time" type="text" id="Report_Time"></td>

      <td width="14%">Report No. :</td>

      <td width="21%" bordercolor="#000000"><input name="Report_No" type="text" id="Report_No"></td>

    </tr>

  </table>

  <p><strong>Part B - About the Accident</strong></p>

  <table width="73%" border="0">

    <tr> 

      <td width="25%">Date of Accident:</td>

      <td width="32%" bordercolor="#000000"> <input name="Date_of_Accident" type="text" id="Date_of_Accident"></td>

      <td width="11%">Time:</td>

      <td width="32%" bordercolor="#000000"> <input name="Time_of_Accident" type="text" id="Time_of_Accident"> </td>

    </tr>

  </table>

  <pre>                                                            <strong><font face="Times New Roman, Times, serif">Ship Activity

                                                                                                                                                                </font></strong><font face="Times New Roman, Times, serif">Sailing  <input name="Ship_Activity" type="checkbox" id="Ship_Activity" value="Sailing"></font> </pre>

  <p align="left"><strong>Injured Person </strong></p>

  <pre align="left"><strong><font face="Times New Roman, Times, serif">CREW</font> <input name="Injured_person[]" type="checkbox" id="Injured_person[]" value="Crew">         <font face="Times New Roman, Times, serif">CONTRACTOR</font> <input name="Injured_person[]" type="checkbox" id="Injured_person[]" value="Contractor">         <font face="Times New Roman, Times, serif">PASSENGER</font>  <input name="Injured_person[]" type="checkbox" id="Injured_person[]" value="Passenger"> 

<font face="Times New Roman, Times, serif">

</font></strong></pre>

  <table width="80%" border="0" bordercolor="#999999">

    <tr> 

      <td width="17%">Given Name:</td>

      <td width="26%" bordercolor="#000000"><input name="IP_Name" type="text" id="IP_Name"></td>

      <td width="23%">Surname:</td>

      <td width="34%" bordercolor="#000000"><input name="IP_Surname" type="text" id="IP_Surname"></td>

    </tr>

    <tr> 

      <td height="26">D.O.B:</td>

      <td bordercolor="#000000"><input name="IP_DOB" type="text" id="IP_DOB"></td>

      <td>Passport/SAP No.:</td>

      <td bordercolor="#000000"><input name="IP_Passport" type="text" id="IP_Passport"></td>

    </tr>

    <tr> 

      <td>Nationality:</td>

      <td bordercolor="#000000"><input name="IP_Nationality" type="text" id="IP_Nationality"></td>

      <td colspan="2" rowspan="2">&nbsp;</td>

    </tr>

    <tr> 

      <td height="26">Sex:</td>

      <td bordercolor="#000000"><input name="IP_Sex" type="text" id="IP_Sex"></td>

    </tr>

  </table>

  <table width="43%" height="132" border="0">

    <tr>

      <td width="32%">Address:</td>

      <td width="68%" bordercolor="#000000"><textarea name="IP_Address" cols="30" rows="5" id="IP_Address"></textarea></td>

    </tr>

  </table>

  <p>&nbsp;</p>

sweets · #6 (**permalink**) 26-03-2007, 08:51 AM

continue from the first code(above)..

PHP Code:


			
 <p><strong>Part C - Description of Event:</strong></p>

  <pre><font face="Times New Roman, Times, serif">(Give a summary of the event in your own words)                                                     Only ONE event can be reported per report form.

Give as much details as you can:

         a. The operation or activity in progress                                              d. The name and type of equipment / machine involved

         b. The environmental conditions, lighting, weather, etc.                  e. The event that led to the accident

         c. Name of any substance or tools involved                                       f. The part played by any person</font></pre>

  <table width="83%" height="153" border="0">

    <tr> 

      <td> <div align="left"> 

          <textarea name="Desc_of_Event" cols="100" rows="10" id="Desc_of_Event"></textarea>

        </div></td>

    </tr>

  </table>

  <p><strong>Type of Accident</strong></p>

  <pre><strong> <input name="Type[]" type="checkbox" id="Type[]" value="Fall at same level"> </strong><font face="Times New Roman, Times, serif">Fall at same level</font>       <input name="Type[]" type="checkbox" id="Type[]" value="Contact with hot water"> <font face="Times New Roman, Times, serif">Contact with hot water</font>         <input name="Type[]" type="checkbox" id="Type[]" value="Illness"> <font face="Times New Roman, Times, serif">Illness                                     <input name="Type[]" type="checkbox" id="Type[]" value="Fire door">  Fire door

   <input name="Type[]" type="checkbox" id="Type[]" value="Fall from staircase/ladder ">  Fall from staircase/ladder     <input name="Type[]" type="checkbox" id="Type[]" value="Contact with hot substance">  Contact with hot substance                <input name="Type[]" type="checkbox" id="Type[]" value="Fainting">  Fainting                                   <input name="Type[]" type="checkbox" id="Type[]" value="Cabin door">  Cabin door

   <input name="Type[]" type="checkbox" id="Type[]" value="Fall from bed">  Fall from bed                          <input name="Type[]" type="checkbox" id="Type[]" value="Contact with chemical">  Contact with chemical                          <input name="Type[]" type="checkbox" id="Type[]" value="Food poisoning">  Food poisoning                      <input name="Type[]" type="checkbox" id="Type[]" value="Watertight door">  Watertight door

   <input name="Type[]" type="checkbox" id="Type[]" value="Fall from height">  Fall from height                     <input name="Type[]" type="checkbox" id="Type[]" value="Contact with electricity">  Contact with electricity                        <input name="Type[]" type="checkbox" id="Type[]" value="Choking">  Choking                                    <input name="Type[]" type="checkbox" id="Type[]" value="Other doors">  Other doors

   <input name="Type[]" type="checkbox" id="Type[]" value="Sport/Games related accident">  Sport/Games related             <input name="Type[]" type="checkbox" id="Type[]" value="Self inflicted injury">  Self inflicted injury                               <input name="Type[]" type="checkbox" id="Type[]" value="Cut">  Cut                                             <input name="Type[]" type="checkbox" id="Type[]" value="Tripped">  Tripped

            accident                                 <input name="Type[]" type="checkbox" id="Type[]" value="Hazard to safety">  Hazard to safety                                    <input name="Type_of_Accident[]" type="checkbox" id="Type[]" value="Death">  Death                                         <input name="Type[]" type="checkbox" id="Type[]" value="Others">  <strong>Others

   <input name="Type[]" type="checkbox" id="Type[]" value="Slipped">  </strong>Slipped                                   <input name="Type[]" type="checkbox" id="Type[]" value="Related to fire">  Related to fire                                        <input name="Type[]" type="checkbox" id="Type[]" value="Accident related to use of equipment/tool">  Accident related to use           <input name="If_Others" type="text" id="If_Others">

   <input name="Type[]" type="checkbox" id="Type[]" value="Hit by object">  Hit by object                         <input name="Type[]" type="checkbox" id="Type[]" value="Smouldering/Smoking">  Smouldering/Smoke                                      of equipment/tool

   <input name="Type[]" type="checkbox" id="Type[]" value="Penetration by foreign object">  Penetration by foreign        <input name="Type[]" type="checkbox" id="Type[]" value="Toxic gas">  Toxic gas                                                 <input name="Type[]" type="checkbox" id="Type[]" value="Accident related to lifting/carrying">  Accident related to lifting/

           object                                                                                                                        carrying





<strong><font size="3">General Location of the Accident</font></strong>



<input name="Location[]" type="checkbox" id="Location[]" value="Alleyway">  Alleyway                                   <input name="Location[]" type="checkbox" id="Location[]" value="Open Deck">  Open Deck                                           <input name="Location[]" type="checkbox" id="Location[]" value="Gallery">  Gallery                                       <input name="Location[]" type="checkbox" id="Location[]" value="Staircase">  Staircase

<input name="Location[]" type="checkbox" id="Location[]" value="Tenderboat">  Tender Boat                              <input name="Location[]" type="checkbox" id="Location[]" value="Pantry">  Pantry                                                   <input name="Location[]" type="checkbox" id="Location[]" value="Shower/Toilet">  Shower/Toilet                          <input name="Location[]" type="checkbox" id="Location[]" value="Elevators/Lifts">  Elevators/Lifts

<input name="Location[]" type="checkbox" id="Location[]" value="Tender platforms">  Tender platforms                      <input name="Location[]" type="checkbox" id="Location[]" value="Mess Room">  Mess Room                                         <input name="Location[]" type="checkbox" id="Location[]" value="Balcony">  Balcony                                     <input name="Location[]" type="checkbox" id="Location[]" value="Swimming Pool">  Swimming Pool

<input name="Location[]" type="checkbox" id="Location[]" value="Survival Crafts">  Survival Crafts                          <input name="Location[]" type="checkbox" id="Location[]" value="Store Room">  Store Room                                          <input name="Location[]" type="checkbox" id="Location[]" value="Mooring Station">  Mooring Station                      <input name="Location[]" type="checkbox" id="Location[]" value="Gymnasium">  Gymnasium

<input name="Location[]" type="checkbox" id="Location[]" value="Side Door/Pilot Door ">  Side Door/Pilot Door               <input name="Location[]" type="checkbox" id="Location[]" value="Laundry">  Laundry                                                <input name="Location[]" type="checkbox" id="Location[]" value="Other Public Area">  Other Public Area                    <input name="Location[]" type="checkbox" id="Location[]" value="Theatre/Galaxy of Stars">  Theatre/Galaxy of Stars

<input name="Location[]" type="checkbox" id="Location[]" value="Gangway/Link Way">  Gangway/Link Way                 <input name="Location[]" type="checkbox" id="Location[]" value="Lobby">  Lobby                                                   <input name="Location[]" type="checkbox" id="Location[]" value="Club">  Club                                           <input name="Location[]" type="checkbox" id="Location[]" value="Child Care">  Child Care

<input name="Location[]" type="checkbox" id="Location[]" value="Sauna">  Sauna                                         <input name="Location[]" type="checkbox" id="Location[]" value="Video Arcade">  Video Arcade                                       <input name="Location[]" type="checkbox" id="Location[]" value="Retail Shop">  Retail Shop                               <input name="Location[]" type="checkbox" id="Location[]" value="Library">  Library

<input name="Location[]" type="checkbox" id="Location[]" value="Restaurant">  Restaurant : <strong>Specify</strong>  <input type="text" name="textfield">                                       <input name="Location[]" type="checkbox" id="Location[]" value="Engine Spaces">  Engine Spaces: <strong>Please Indicate <input type="text" name="textfield2">

<input name="Location[]" type=