get sql query displayed rather than query result using AJAX

Question

Hello I have been trying to figure out why my code aimed at listing a query result in a table does not work. I took code found on the web and tired to adapt it. My data is stored in pgsql. The html page has a drop down menu that allows selecting an institution name. When I click the submit button to know who belongs to this institution in my database, the php page is loaded and displays the SQL query I want to send to pgsql. I should get the result of the query displayed in a table displayed on the html page instead. The drop down menu works correctly so I do not provide the php code for this one (listinstitutions.php)

A person told me I should use ajaxsubmit() but I do not know where to put this function. There may be an error in the php file also since the query is displayed rather than being sent to pgsql. Is the json correctly sent?

Your guidance would be very much appreciated.

Thank you.

The html side:

<html>
<head>

<link rel="stylesheet" href="http://code.jquery.com/ui/1.10.1/themes/base/jquery-ui.css" />

<script src="http://code.jquery.com/jquery-1.9.1.min.js"></script>
<script src="http://code.jquery.com/ui/1.10.1/jquery-ui.js"></script>
<script>
$(document).ready(function(){


  //////////////////////////////////////
  // Displays insitution names in Drop Down Menu

        //Getting the selector and running the code when clicked
        $('#selinstit').click(function(e){
                 //Getting the JSON object, after it arrives the code inside
               //function(dataI) is run, dataI is the recieved object
               $.getJSON('http://localhost/listinstitutions.php',function(dataI){
                            //loop row by row in the json, key is an index and val the row
                            var items = [];  //array
                          $.each(dataI, function(key, val) {
                            //add institution name to <option>
                            items.push('<option>' + val['Iname'] + '</option>');
                        });//end each
                        //concatenate all html
                        htmlStr=items.join('');
                        console.log(htmlStr);
                        //append code
                        $('option#in').after(htmlStr);
                });//end getJSON
        });//end cluck



    ///////////////////////////////
   // Displays persons form an institution in a table

     $( "$subinst" ).button().click(function( event ) {
     console.log($(this)); // for Firebug
     $.getJSON('http://localhost/SelectPersonsBasedOnInstitution.php',function(data){   // I make an AJAX call here
     console.log($(this)[0].url); // for Firebug   check what url I get here
                            //loop row by row in the json, key is an index and val the row
                            var items = [];  //array
                          $.each(data, function(key, val) {

                        //add table rows
                            items.push('<tr border=1><td>' + val['Pfirstname'] + '</td><td>' + val['Plastname'] + '</td><td><a mailto:=" ' + val['Pemail'] + ' " >' + val['Pemail'] + '</a></td></tr>');
                        });//end each
                        //concatenate all html
                    htmlStr=items.join('');

                        //append code
                        $('#instito').after(htmlStr);
                });//end getJSON
      event.preventDefault();
      });

}); //end ready

</script>

</head>   

<body>
<form id="myForm" action="SelectPersonsBasedOnInstitution.php" method="post">
Select persons from an institution:
<br>                                            
<tr>
 <td>
   <select id="selinstit" name="instit">
   <option id="in">Select</option>                       
   </select>
 </td>
 <td>
   <input type="submit" id="subinst" value="Submit" /> 
 </td>
</tr>

</form>

   <table frame="border" id="instito">
   </table>
</body>
</html>

Here is the php code for SelectPersonsBasedOnInstitution.php

<?php


//////////
// part 1: get information from the html form
ini_set('display_errors', 1);                                      
ini_set('display_startup_errors', 1);

foreach ($_REQUEST as $key => $value){
 $$key=$value;  
}

// part2: prepare SQL query from input
$sqlquery= sprintf('SELECT "Pfirstname", "Plastname", "Pemail" FROM "PERSON"
LEFT JOIN "INSTITUTION" ON
"PERSON"."Pinstitution"="INSTITUTION"."Iinstitution"
WHERE "Iname" = \'%s\'',$instit);
echo $sqlquery;


/////////
// part3: send query
$dbh = pg_connect("host=localhost dbname=mydb user=**** password=*****");
$sql=  $sqlquery;
$result = pg_query($dbh,$sql);
$myarray = pg_fetch_all($result);

$jsontext = json_encode($myarray);
echo($jsontext);

?>

Mike Dinescu · Accepted Answer · 2013-03-27 17:11:41Z

1

The following line is likely to be the problem (it shouldn't be there):

echo $sqlquery;

Rewrite your code without that line and it should work.

$sqlquery= sprintf('SELECT "Pfirstname", "Plastname", "Pemail" FROM "PERSON" LEFT JOIN "INSTITUTION" ON "PERSON"."Pinstitution"="INSTITUTION"."Iinstitution" WHERE "Iname" = \'%s\'', $instit);

$dbh = pg_connect("host=localhost dbname=mydb user=**** password=*****");
$result = pg_query($dbh, $sqlquery);
$myarray = pg_fetch_all($result);
$jsontext = json_encode($myarray);
echo($jsontext);

answered Mar 27, 2013 at 17:11

Mike Dinescu

56k17 gold badges124 silver badges153 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

bmo Over a year ago

this specific issue is fixed, thank you. Now the php page is loaded and nothing is displayed. I should get the table (id="instito") with the results displayed in the html page instead. Any hint using ajaxsubmit() to fix this?

bmo Over a year ago

Problem should be in the html page I guess now.

bmo Over a year ago

I added this code in the</script> section:$("#subinst").click(function() { var url = "localhost/SelectPersonsBasedOnInstitution.php"; // the script where you handle the form input. $.ajax({ type: "POST", url: url, data: $("#myForm").serialize(), // serializes the form's elements. success: function(data) { alert(data); // show response from the php script. } }); return false; // avoid to execute the actual submit of the form. });

Mike Dinescu Over a year ago

do you get back the JSon encoded text that you expect now? (from SelectPersonsBasedOnInstitution.php)

bmo Over a year ago

Sorry about bad formatting above. Added code below and got query result displayed on the php page. How to display the result in the table?. [code] $("#subinst").click(function() { var url = "localhost/SelectPersonsBasedOnInstitution.php"; $.ajax({ type: "POST", url: url, data: $("#myForm").serialize(), success: function(data) { alert(data); } }); return false; // avoid to execute the actual submit of the form. });

|

Collectives™ on Stack Overflow

get sql query displayed rather than query result using AJAX

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related