Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 2 of 2 FirstFirst 12
Results 16 to 19 of 19
  1. #16
    New Coder
    Join Date
    Aug 2014
    Posts
    10
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I gotcha. The total is still being updated daily. It will be a lot. I would estimate 500,000+. Check this code out that is currently working for the rough version of the form.

    Code:
    <?php
    require_once ('db.php');
    
    if(isset($_POST['init']) && $_POST['init']==true)
    {
    	$data = '';
    	$result = mysql_query("SELECT Trap_Manufacturer FROM stmTrps GROUP BY Trap_Manufacturer ORDER BY Trap_Manufacturer ASC") or die(mysql_error());
    	while($row = mysql_fetch_array( $result )) {
    		   $data .= '<option value="'.$row['Trap_Manufacturer'].'">'.$row['Trap_Manufacturer'].'</option>';
    	}
    
    	$response['data'] = $data;
        echo json_encode($response);
    }
    
    if(isset($_POST['search']) && $_POST['search']==true)
    {
    	$id = $_POST['id'];
    	if(isset($_POST['manufacturer'])) { $v1 = $_POST['manufacturer']; }
    	if(isset($_POST['model'])) { $v2 = $_POST['model']; }
    	if(isset($_POST['size'])) { $v3 = $_POST['size']; }
    	if(isset($_POST['pressure'])) { $v4 = $_POST['pressure']; }
    
    	$data = '';
    	if($id == 'manufacturer') {
    		$result = mysql_query("SELECT DISTINCT Trap_Model FROM stmTrps WHERE Trap_Manufacturer='".$v1."' ORDER BY Trap_Model")	or die(mysql_error());
    		while($row = mysql_fetch_array( $result )) {
    			   $data .= '<option value="'.$row['Trap_Model'].'">'.$row['Trap_Model'].'</option>';
    		}
    		$response['affect'] = 'model';
    	}
    	elseif($id == 'model')
    	{
    		$result = mysql_query("SELECT DISTINCT size FROM stmTrps WHERE Trap_Manufacturer='".$v1."' AND Trap_Model='".$v2."'") or die(mysql_error());
    		while($row = mysql_fetch_array( $result )) {
    			   $data .= '<option value="'.$row['size'].'">'.$row['size'].'</option>';
    		}
    		$response['affect'] = 'size';
    	}
    	elseif($id == 'size')
    	{
    	    $result = mysql_query("SELECT pressure FROM stmTrps WHERE Trap_Manufacturer='".$v1."' AND Trap_Model='".$v2."' AND size='".$v3."'") or die(mysql_error());
    		while($row = mysql_fetch_array( $result )) {
    			   $data .= '<option value="'.$row['pressure'].'">'.$row['pressure'].'</option>';
    		}
    		$response['affect'] = 'pressure';
    	}
    
    	$response['data'] = $data;
        echo json_encode($response);
    }
    Does this look like it would work for a larger database

  2. #17
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,650
    Thanks
    80
    Thanked 4,638 Times in 4,600 Posts
    500,000 is not a "larger database." Anyway, assuming that the appropriate fields in that stmTrps table have been indexed it should be perfect.

    I'd probably suggest
    Code:
    CREATE INDEX stmTrps_key ON stmTrps(Trap_Manufacturer,Trap_Model);
    if it's not already similarly indexed.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  3. Users who have thanked Old Pedant for this post:

    bobbyshirley (08-07-2014)

  4. #18
    New Coder
    Join Date
    Aug 2014
    Posts
    10
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I added that.

    Also, here's a crude walkthrough of the form.



    Am I missing anything in the walkthrough?

  5. #19
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,650
    Thanks
    80
    Thanked 4,638 Times in 4,600 Posts
    Yes. Remember what I said. You *MUST* create the base section 1 survey info in the DB, in order to get the surveyID (INT AUTOINCREMENT PRIMARY KEY) which you then use when you store EACH of the section 2 sets of data. Again, you could use a surveyComplete flag, set to false until the survey truly is complete.

    Here's what your DB should REALLY look like (some data types might be changed; I can't tell by just looking at the field names):
    Code:
    CREATE TABLE Trap_Model (
        modelid INT AUTOINCREMENT PRIMARY KEY,
        Trap_Manufacturer VARCHAR(xxx),
        Trap_Model VARCHAR(xxx),
        Trap_Size VARCHAR(xxx),
        Trap_Pressure FLOAT 
    ) ENGINE InnoDB;
    
    CREATE TABLE Plants (
        plantid INT AUTOINCREMENT PRIMARY KEY,
        plantName VARCHAR(xxx),
        plantLocation VARCHAR(xxx),
        contact VARCHAR(xxx)
    ) ENGINE InnoDB;
    
    CREATE TABLE Surveys (
        surveyid INT AUTOINCREMENT PRIMARY KEY,
        surveyDate DATE,
        plantid INT,
        CONSTRAINT FOREIGN KEY (plantid) REFERENCES Plants(plantid)
    ) ENGINE InnoDB;
    
    CREATE TABLE SurveyDetails (
       detailid INT AUTOINCREMENT PRIMARY KEY,
       surveyid INT,
       Direction	VARCHAR(xxx),
       Location TEXT,
       FloorLevel INT,
       Elevation FLOAT,
       TagNumber INT,
       Service VARCHAR(xxx),
       TrapConditions VARCHAR(xxx),
       modelid INT,
       CONSTRAINT FOREIGN KEY (surveyid) REFERENCES Surveys(surveyid),
       CONSTRAINT FOREIGN KEY (modelid) REFERENCES Trap_Model(modelid)
    ) ENGINE InnoDB;
    This is called NORMALIZATION. Look it up.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  6. Users who have thanked Old Pedant for this post:

    bobbyshirley (08-07-2014)


 
Page 2 of 2 FirstFirst 12

Tags for this Thread

Posting Permissions

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