php mysql based on user input form

Question

I am new to PHP and AJAX so bare with me on this, I am having trouble using a checkbox to select one or multiple fields of data for PHP/AJAX to process and display. I have the PHP/AJAX working great on my <select>s but as soon as I try setting up the checkbox all hell breaks lose.

I also am very unsure on how to further prevent SQL injection on the site so if anyone could fill me in a little more about this I would GREATLY appreciate it! I read the link I was provided and just don't understand how bid_param or PDO works exactly.

The ajax script: (I can't seem to insert the ajax/js so I'll leave a link to the live site)

Link to Agent search page

My php page that displays the 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 pulls the info and places it into 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 and all help on this topic! Any time I add the checkbox values to my php file it ends up displaying everyone 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 the user can input text into?

EDIT As of today I gave a try with using jQuery to activate the checkboxes and then call some AJAX. Here is the script I wrote and it is pulling an agent, just not everyone that has that "expertise".

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

Before you write any more SQL code, you must read up on proper SQL escaping and how to use bind_param to properly insert values from $_GET into your queries without creating severe SQL injection bugs. — tadman, Aug 1 at 15:21
Yeah defiantly agree with that, you don't want people to gain unauthorized access from gaping holes like that. — DarkMantis, Aug 1 at 15:30
Okay I am reading up on this page but like I said I am totally new to sql and even using a database in general. Mind giving me a slightly broken down explanation about bind_param? — Josh Powell, Aug 1 at 15:32
RE: PHP, some bad practive in there as well. If you dont have anything to do in an else dont code one. Also if yuo have nothing to do in an IF but you do have something to do in an else then change the test in the IF so you dont have empty IF statements with an ELSE. It will make your code easier to read and understand and therefore easier to maintain in 6 months when YOU have to try and understand it again. — RiggsFolly, Aug 1 at 15:39
Hmm Okay I see what you mean with the else statements but when I was using only an if statement and the field was empty it would include an empty row in the results. That's why I was using an empty if statement with the else statement to echo the result. — Josh Powell, Aug 1 at 15:44

Brian · Answer 1 · 2013-08-01 21:43:29Z

Here's a quick example of something I recently worked on in which I needed to loop through multiple checkboxes and pass those values into a SQL statement. Although this example happens on a button click, hopefully its something along the lines of what you are trying to accomplish, or at least at start... :)

<?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 one variable, try setting a variable for each select control and putting your SQL statement in a foreach loop. I just tested this with some dummy data and didn't have any issues 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...
?>

Thanks for the reply! Sadly I have been trying this out and it ends up doing nothing and not even giving me any errors so I'm not to sure what to do. I don't know why this one is so difficult heh, using the select fields was so easy and did not make me want to pull my hair out. — Josh Powell, Aug 1 at 19:49
Did you try to echo the results to see if anything is coming back? — Brian, Aug 1 at 20:13
Yeah I did and I was expecting it to at least show something or error. Here is a link to the live site that I am working on, link. The first four select fields work just fine but as soon as I trying to fiddle with the Agent Expertise area the entire script goes crazy. Bare with me as the structure design is not by me but everything else is :} — Josh Powell, Aug 1 at 20:21
How are you detecting when a checkbox is checked/unchecked? It looks like something is happening because if I change any of the 4 selects with Ancillary checked, then uncheck Ancillary, the page updates. If I try the same steps with any of the other checkboxes the page doesn't update...You need to verify that every checkbox is being detected when checked/unchecked. I see you are doing an onChange for the selects, but are you using jQuery and making an ajax call to detect the checkboxes? — Brian, Aug 1 at 20:42
See my update. I think you just need to assign a variable for each select and put the SQL in a loop so you're passing the correct values in each time. — Brian, Aug 1 at 21:30

Josh Powell · Accepted Answer · 2013-08-02 16:00:54Z

up vote 1 down vote accepted

I DID IT!! Wohoo! I ended up just making a separate php page called expertise.php to process the checkboxs using jquery/ajax.

The jQuery that achieved this: (Thank god I went onto the jQuery website to look up 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 except for the sql:

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

If anyone would enlighten me more on making this better protected against sql injections, feel free to!

Agent Search Page

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

Now in the sql I can use AND or OR, with AND it pulls only agents that have everyone of those expertise and with OR it seems to pull everyone. Any ideas?

edited Aug 2 at 16:00

answered Aug 2 at 15:48

Josh Powell
1,082318

Great job! That's close to what I was just about to update my answer with (going with an ajax approach). Was doing some final testing before I updated it but looks like you beat me to it! – Brian Aug 2 at 16:19

@Brian Thanks a lot! I am super proud from the results for my first time using php. I still have a minor problem with the results I am getting though. Using AND in the sql gives me only the agents who has all of those fields and using OR gives me all agents, any ideas? – Josh Powell Aug 2 at 16:24

1

Well using AND, only results that meet every criteria will come back. Using OR allows for a much broader search, therefore more results. I'm guessing you are wondering which to use when for the checkbox values? If so (and I'm understanding it correctly), my original suggestion about separating them with commas should work but you would being using SQL's IN instead of =. Something like..."Ancillary IN ('Largegroup', 'Smallgroup') ORDER BY Last_Name ASC. Please note that I am in NO WAY a SQL expert and this is only a suggestion based on the information shared here. – Brian Aug 2 at 16:49

Since you have figured out the answer to the main question here, you might want to start a new question regarding the SQL stuff as it would probably have more hits being a new question and you can add some SQL tags to it as well...just a thought though. :) – Brian Aug 2 at 16:52

I will give your suggestion a try and see if that works. If it does not I'll make a new question based on my current state. :p Thank you so much for all your help mate! It really has helped me out a ton! – Josh Powell Aug 2 at 17:34

show 2 more comments

asked	3 months ago
viewed	398 times
active	3 months ago

php mysql based on user input form

2 Answers

Your Answer

Not the answer you're looking for? Browse other questions tagged php mysql ajax or ask your own question.

php mysql based on user input form

2 Answers

Your Answer

Sign up or login

Post as a guest

Not the answer you're looking for? Browse other questions tagged php mysql ajax or ask your own question.

Related