Results 1 to 11 of 11

Thread: Help in query..

  1. #1
    Join Date
    Mar 2007
    Location
    Port Klang, Malaysia
    Posts
    20
    Rep Power
    0

    Help in query..

    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

  2. #2
    Join Date
    Jan 2007
    Location
    Petaling Jaya
    Posts
    867
    Rep Power
    167
    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

  3. #3
    Join Date
    Mar 2007
    Location
    Port Klang, Malaysia
    Posts
    20
    Rep Power
    0
    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..

  4. #4
    Join Date
    Jan 2007
    Location
    Petaling Jaya
    Posts
    867
    Rep Power
    167
    mayb it would be helpful if u could provide a screenshot or a testsite to see on how's ur form looks like

  5. #5
    Join Date
    Mar 2007
    Location
    Port Klang, Malaysia
    Posts
    20
    Rep Power
    0
    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>

  6. #6
    Join Date
    Mar 2007
    Location
    Port Klang, Malaysia
    Posts
    20
    Rep Power
    0
    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:
             
    aThe operation or activity in progress                                              dThe name and type of equipment machine involved
             b
    The environmental conditionslightingweatheretc.                  eThe event that led to the accident
             c
    Name of any substance or tools involved                                       fThe 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="checkbox" id="Location[]" value="checkbox">  </strong>Others: <strong>Specify</strong><input type="text" name="textfield3">

    </
    font></pre>
      <
    table width="9%" border="0" align="center">
        <
    tr
          <
    td><div align="center"
              <
    input type="submit" name="Submit" value="Submit">
            </
    div></td>
        </
    tr>
      </
    table>
      <
    p align="center"><a href="View_Report.php">[View]</a></p>
      <
    p align="center"
        <
    input name="MM_insert" type="hidden" id="MM_insert" value="Hidden Field">
      </
    p>
      </
    form>

    <
    p>&nbsp;</p>
    <
    p align="left">&nbsp;</p>
    <
    p align="left">&nbsp;</p>
    <
    p align="left">&nbsp;</p>
    <
    p align="left">&nbsp;</p>

      </
    body>
    </
    html

  7. #7
    Join Date
    Jan 2007
    Location
    Petaling Jaya
    Posts
    867
    Rep Power
    167
    ok, i give a simple example on this.
    referring to ur html form.
    i give 2 tables which is "general information" and "type of accident checkbox"
    refer to the image below.
    [img=http://img408.imageshack.us/img408/2591/sqlexpbh0.th.jpg]

    both table have 1 primary key which is the [pre]_id, and u will notice the checkbox table has an attributes - info_id which is the same as the primary key of general information table, this will act as the foreign key for checkbox table.

    Now, after setting up the tables, all will falls on how u play wif ur sql. U have to use Join Sql in order to link these 2 tables together using the foreign key. For more information, u can refer to here http://www.w3schools.com/sql/sql_join.asp

    is easy to learn on the join sql, hope this helps u

  8. #8
    Join Date
    Mar 2007
    Location
    Port Klang, Malaysia
    Posts
    20
    Rep Power
    0
    Thanks.. i'll take a good look and try it first..

  9. #9
    Join Date
    Mar 2007
    Location
    Port Klang, Malaysia
    Posts
    20
    Rep Power
    0
    Im using this sql..
    PHP Code:
    $sql="SELECT * FROM Report, const_acc, const_location, Position 
          WHERE Report.ID = const_acc.ID = const_location.ID = Position.ID
          AND const_acc.Type LIKE '%"
    .$search_acc."%'"
    $result=mysql_query($sql)or die( "There was an error running '$result' " mysql_error());
    $rows2 mysql_fetch_assoc($result); 
    Hmm.. display nothing..
    Last edited by sweets; 27-03-2007 at 09:16 AM.

  10. #10
    Join Date
    Jan 2007
    Location
    Petaling Jaya
    Posts
    867
    Rep Power
    167
    ur foreign key for all is only "ID" ?

    WHERE Employees.Employee_ID=Orders.Employee_ID
    WHERE Report.ID = const_acc.ID = const_location.ID = Position.ID

    the ID is actually 1 of ur attributes in ur table which is the foregin key.

    and did u try to combine 2 tables first instead of 4 tables straight away?
    try do it with 2 tables and when the desire result achieve, only go with combining 4 tables

  11. #11
    Join Date
    Mar 2007
    Location
    Port Klang, Malaysia
    Posts
    20
    Rep Power
    0
    okay.. now im using this sql to view all my data from the table.. i tried with two tables first.. I had Report_No as the primary key for Report table. in const_acc table, Report_No acts as foreign key.

    PHP Code:
    $sql="SELECT * FROM Report,const_acc 
          WHERE Report.Report_No = const_acc.Report_No "
    ;       
          
    $result=mysql_query($sql)or die( "There was an error running '$result' " mysql_error()); 
    but it came out
    There was an error running '' You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' const_acc.Report_No' at line 2
    im using wamp5. what would be the correct syntax??
    Last edited by sweets; 27-03-2007 at 01:34 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Query Vs Array
    By semutpower in forum Website Programming
    Replies: 2
    Last Post: 30-01-2009, 02:44 PM
  2. Need help : send & query image in php
    By mimosa in forum Website Programming
    Replies: 11
    Last Post: 04-11-2007, 11:53 PM
  3. Why my Query Not Working??
    By marukochan in forum Website Programming
    Replies: 3
    Last Post: 05-04-2007, 02:10 PM
  4. Sql query untuk A+B-C ...
    By nixx in forum Website Programming
    Replies: 5
    Last Post: 06-10-2004, 04:51 PM
  5. how to secured query string
    By vash in forum Website Programming
    Replies: 3
    Last Post: 28-04-2004, 02:47 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Search Engine Optimization by vBSEO 3.5.0 RC1 PL1

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37