CODEWITHSUNDEEP
phpjquerysqlajaxjson
Bob
Bob

Reputation: 1375

How to generate a JSON based on form selections to build SQL statement by using a jQuery AJAX call to a PHP script

I have an HTML form(2 list-boxes and a check-box) which I want to use for filtering results on the page.

How can I make it so when I press the Submit button(.onclick(function(){})) it would execute a jQuery AJAX call to a PHP script on my local server that will, firstly, check if anything was selected in the list-boxes and if the check-box was checked and then based on that build a SQL statement to query my database and retrieve the result as JSON.

How would you do it, theoretically. I already have the PHP script to simply grab everything from my table and save it as JSON:

<?php
// databse connection info
require("dbinfo.inc.php");

// try to open a connection to a MySQL server
$connection=mysql_connect($host, $username, $password);
if (!$connection) {
  die('Not connected : ' . mysql_error());
}

// select the active MySQL database to work with
$db_selected = mysql_select_db($database, $connection);
if (!$db_selected) {
  die ('Can\'t use db : ' . mysql_error());
}

// grab data about every campus and which instituion they belong to
$query = 'select statement to grab all the data from a table';
$result = mysql_query($query);

// check if $results has anything
if (!$result) {
  die('Invalid query: ' . mysql_error());
}

// time to start generating our huge XML
while ($row = @mysql_fetch_assoc($result)){
    $finalarray[] = $row;
    $main_arr['products'] = $finalarray;
}
//  close connection to the database
mysql_close($connection);

//  echo, save and write the file
// print json_encode($main_arr);

$fp = fopen('results.json', 'w');
fwrite($fp, json_encode($main_arr));
fclose($fp);


?>

Upvotes: 1

Views: 483

Answers (2)

Ed Fryed
Ed Fryed

Reputation: 2198

Here is an example of posting to a php file and grabbing the data with ajax. The result will get logged into the console. I assume you know how to process the json with js once it has been retrieved?

//the php
<?php

    //bla bla connect to database etc

    //if form has been submitted with ajax
    if(isset($_POST["ajax_request"])){

        //build query
        $id    = $_POST["id"];
        $fruit = $_POST["fruit"];
        $query = "SELECT * FROM fruits WHERE id=$id AND fruit='$fruit'";

        //fetch from db
        $result = mysql_query($query);

        //send out json
        die(json_encode($result));

    }

?>

//the jquery
<script type="text/javascript">

$(document).ready(function(){

    //on submit
    $("#ajaxForm").submit(function(e){

        //stop default page refresh
        e.preventDefault();

        //define the form
        var form = $(this);

        //get the form data
        var data = form.serialize();

        //send it via ajax
        $.ajax({
           type      : "post",
           dataType  : "json",
           url       : "url to your php here",
           data      : data+"&ajax_request=1",
           success   : function(json){
               console.log(json);
           },
           error     : function(a,b,c){
               console.log(a,b,c);
           }
        });

    });

});

</script>

//the html
<form id="ajaxForm" method="post" action="">
    <input type="text" name="id" required="required" placeholder="enter id"/>
    <input type="text" name="fruit" required="required" placeholder="enter fruit"/>
    <input type="submit" name="submit"/>
</form>

Upvotes: 1

adeneo
adeneo

Reputation: 318192

First send the data from the form with ajax:

$('form').on('submit', function(e) {
    e.preventDefault();
    $.ajax({
        type: 'POST',
        url : 'myPHPfile.php',
        data: {form : $('form').serialize()}
    }).done(function(data) {
         var myJSONresult = data;
    });
});

In your PHP file you'll get the form with

$form = $_POST['form'];

and there's no need to save to a file, just send it back by echoing

echo json_encode($main_arr);

Upvotes: 1

Related Questions