Php mysql based on user input form

Question

I am new to PHP and AJAX, so naked with me on this, I am having trouble using a checkbox to select one or more data fields for PHP / AJAX to process and display. I have PHP / AJAX working fine on my <select> , but as soon as I try to configure the checkbox, all the hellish misses will lose.

I am also very unsure of how to further prevent SQL injection on the site, so if anyone can fill me up a bit more about it, I would GREAT rate it! I read the link that was provided to me and I just don’t understand how bid_param or PDO works.

ajax script: (I cannot insert ajax / js, so I will leave a link to the site live)

Link to agent search page

My php page displaying data:

 <div id="bodyA"> <h1>Find a Local OAHU Agent.</h1> <!-- This is where the data is placed. --> </div> <div id="sideB"> <div class="sideHeader"> <em>Advanced Search</em> </div> <form class="formC"> <label for="last">Last Name</label><br /> <select id="last" name="Last_Name" onChange="showUser(this.value)"> <?php include 'datalogin.php'; $result = mysqli_query($con, "SELECT DISTINCT Last_Name FROM `roster` ORDER BY Last_Name ASC;"); echo '<option value="">' . 'Select an Agent' .'</option>'; while ($row = mysqli_fetch_array($result)) { echo '<option value="'.$row['Last_Name'].'">'.$row['Last_Name'].'</option>'; } ?> </select> <label for="company">Company</label><br /> <select id="company" name="users" onChange="showUser(this.value)"> <?php include 'datalogin.php'; $result = mysqli_query($con, "SELECT DISTINCT Company FROM `roster` ORDER BY Company ASC;"); echo '<option value="">' . 'Select a Company' .'</option>'; while ($row = mysqli_fetch_array($result)) { if ($row['Company'] == NULL) { } else { echo '<option value="'.$row['Company'].'">'.$row['Company'].'</option>'; } } ?> </select> <label for="WorkCity">City</label><br /> <select id="WorkCity" name="WorkCity" onChange="showUser(this.value)" value="city"> <?php include 'datalogin.php'; $result = mysqli_query($con, "SELECT DISTINCT WorkCity FROM `roster` ORDER BY WorkCity ASC;"); echo '<option value="">' . 'Select a City' .'</option>'; while ($row = mysqli_fetch_array($result)) { echo '<option value="'.$row['WorkCity'].'">'.$row['WorkCity'].'</option>'; } ?> </select> <label for="WorkZipCode">Zip Code</label><br /> <select id="WorkZipCode" name="WorkZipCode" onChange="showUser(this.value)"> <?php include 'datalogin.php'; $result = mysqli_query($con, "SELECT DISTINCT WorkZipCode FROM `roster` ORDER BY WorkZipCode + 0 ASC;"); echo '<option value="">' . 'Select a Zip Code' .'</option>'; while ($row = mysqli_fetch_array($result)) { echo '<option value="'.$row['WorkZipCode'].'">'.$row['WorkZipCode'].'</option>'; } ?> </select> <label for="agent">Agent Expertise</label><br /> <label for="ancillary"><input type="checkbox" value="Ancillary" name="Ancillary[]" id="ancillary" />Ancillary</label><br /> <label for="smallgroup"><input type="checkbox" value="Smallgroup" name="Smallgroup[]" id="smallgroup" />Small Group</label><br /> <label for="largegroup"><input type="checkbox" value="LargeGroup" name="LargeGroup[]" id="largegroup" />Large Group</label><br /> <label for="medicare"><input type="checkbox" value="Medicare" name="Medicare[]" id="medicare" />Medicare</label><br /> <label for="longterm"><input type="checkbox" value="LongTerm" name="LongTerm[]" id="longterm" />Long Term Care</label><br /> <label for="individual"><input type="checkbox" value="Individual" name="Individual[]" id="individual" />Individual Plan</label><br /> <label for="tpa"><input type="checkbox" value="TPASelfInsured" name="TPASelfInsured[]" id="tpa" />TPA Self Insured</label><br /> <label for="ppaca"><input type="checkbox" value="CertifiedForPPACA" name="CertifiedForPPACA[]" id="ppaca" />Certified for PPACA</label><br /> </form> </div> 

My php page that retrieves the information and puts it in a container on the page:

  $q = (isset($_GET['q'])) ? $_GET['q'] : false; // Returns results from user input include 'datalogin.php'; // PHP File to login credentials $sql="SELECT * FROM `roster` WHERE Company = '".$q."' OR Last_Name = '".$q."' OR WorkCity = '".$q."' OR WorkZipCode = '".$q."' ORDER BY Last_Name ASC"; $result = mysqli_query($con,$sql) // Connects to database or die("Error: ".mysqli_error($con)); echo "<h1>" . "Find a Local OAHU Agent." . "</h1>"; while ($row = mysqli_fetch_array($result)) { // Gets results from the database echo "<div class='agentcon'>" . "<span class='agentn'>" . "<strong>".$row['First_Name'] . "&nbsp;" .$row['Last_Name'] . "</strong>" . "</span>" . "<a href=mailto:".$row['Email'] . ">" . "<span class='email'>".$row['Email'] . "</span>" . "</a>" ."<div class='floathr'></div>"; if ($row['Company'] == NULL) { echo "<p>"; } else { echo "<p>" . "<strong>" .$row['Company'] . "</strong>" . "<br>"; } echo $row['WorkAddress1'] . "&nbsp;" .$row['WorkCity'] . "," . "&nbsp;" .$row['WorkStateProvince'] . "&nbsp;" .$row['WorkZipCode'] . "<br>"; if ($row['Work_Phone'] !== NULL) { echo "<strong>" . "Work" . "&nbsp;" . "</strong>" .$row['Work_Phone'] . "<br>"; } if ($row['Fax'] !== NULL) { echo "<strong>" . "Fax" . "&nbsp;" . "</strong>" .$row['Fax'] . "<br>"; } echo "<strong>" . "Agent Expertise:" . "</strong>"; if ($row['Ancillary'] == 1) { echo "&nbsp;" . "Ancillary" . "/"; } if ($row['SmallGroup'] == 1) { echo "&nbsp;" . "Small Group" . "/"; } if ($row['IndividualPlans'] == 1) { echo "&nbsp;" . "Individual Plans" . "/"; } if ($row['LongTermCare'] == 1) { echo "&nbsp;" . "Long Term Care" . "/"; } if ($row['Medicare'] == 1) { echo "&nbsp;" . "Medicare" . "/"; } if ($row['LargeGroup'] == 1) { echo "&nbsp;" . "LargeGroup" . "/"; } if ($row['TPASelfInsured'] == 1) { echo "&nbsp;" . "TPA Self Insured" . "/"; } if ($row['CertifiedForPPACA'] == 1) { echo "&nbsp;" . "Certified For PPACA"; } echo "</p>" . "</div>"; } mysqli_close($con); ?> 

I appreciate any help on this topic! Each time I add checkbox values ​​to my php file, it finishes displaying everything in the database for all fields in the form.

I am also trying to prevent SQL injection on this, but how can a user do this if I don't have a field that the user can enter into the text?

EDIT So far, I have tried using jQuery to activate checkboxes and then invoke some AJAX. Here is the script I wrote, and it pulls the agent, but not everyone who has this "experience".

 $('input').click(function() { $.ajax({ url: "process.php", data: { value: 1}, success: function (data) { $('#bodyA').html(data); } }); }); 

Answer 1

I DID IT !! Wohoo! In the end, I just created a separate php page called expert.php to handle checkboxes using jquery / ajax.

jQuery that accomplished this: (Thank god I went to the jQuery site to search for functions!)

 $('input').click(function() { $.ajax({ url: "expertise.php", data: { value: 1}, success: function (data) { $('#bodyA').html(data); } }); }); 

The PHP page is the same as my process.php page, with the exception of sql:

 $sql="SELECT * FROM `roster` WHERE Ancillary = '1' AND SmallGroup = '1' AND CertifiedForPPACA = '1' ORDER BY Last_Name ASC"; 

If someone enlightened me more to make it better protected against sql injection, feel free!

Agent Search Page

Well, at least I got both parts of the search, but a new problem arose: p

Now in sql I can use AND or OR, with AND, and it pulls only agents that have all this knowledge, and with OR, it seems, pulls everyone. Any ideas?

Answer 2

Here is a brief example of what I recently worked in, in which I needed to go through several checkboxes and pass these values ​​to an SQL statement. Although this example happens when a button is clicked, I hope something similar to what you are trying to execute, or at least at startup ... :)

 <?php $array = array(); if (isset($_POST['medicare'])) { foreach ($_POST['medicare'] as $value) { array_push($array, $value); } } // this will return the value of each selected checkbox, separating each with a comma $result = implode(",", $array); // if you want to loop through each individually (for example pass each into a SQL statement) foreach ($_POST['medicare'] as $value) { // Do your SQL here // $value will be the value of each selected checkbox (Smallgroup, Largegroup, etc.) $sql = "insert into tablename(fieldname) values ('$value')"; // just an example } ?> <input type="checkbox" name="medicare[]" id="smallgroup" value="Smallgroup" /> <label for="smallgroup">Small Group</label> <br /> <input type="checkbox" name="medicare[]" id="largegroup" value="Largegroup" /> <label for="largegroup">Large Group</label> <br /> <input type="checkbox" name="medicare[]" id="medicare" value="Medicare" /> <label for="medicare">Medicare</label> <br /> <input type="checkbox" name="medicare[]" id="individualplan" value="IndividualPlan" /> <label for="individualplan">Individual Plan</label> <br /> <input type="submit" value="Submit" id="btnSubmit" name="btnSubmit" /> 

UPDATE

Instead of setting a single variable, try setting a variable for each select control and putting the SQL statement in the foreach . I just checked this with some dummy data and had no problems with it.

 <?php $lastname = (isset($_GET['Last_Name'])) ? $_GET['Last_Name'] : false; $users = (isset($_GET['users'])) ? $_GET['users'] : false; $workCity = (isset($_GET['WorkCity'])) ? $_GET['WorkCity'] : false; $WorkZipCode = (isset($_GET['WorkZipCode'])) ? $_GET['WorkZipCode'] : false; foreach ($_GET['medicare'] as $value) { //echo $value; $sql="SELECT * FROM roster WHERE Company = '$users' OR Last_Name = '$lastname' OR WorkCity = '$workCity' OR WorkZipCode = '$WorkZipCode' OR Ancillary = '$value' ORDER BY Last_Name ASC"; } ...continue as you were... ?>