Ajax, php, mysql ,json Query issues

microchipmatt · June 25, 2017, 10:12pm

Hello everyone. I have a form dropdown, that will query my mysql database via ajax, php and sql. I am using ajax, so that a front end of NW.js and possibly cordova can query the DB without any extra security measures, and no fuss since ajax can query a remote url over port 80 and pull json formatted data. My main page calls the ajax api code. Everything works, except for some reason my posted values, are not being stored and passed to the ajax api. I know the ajax code is working because if I pass it some values, it queries what it should. Im sure I have posting mistakes. Can an extra set of eyes look over my code and see any errors? I have been staring at this while to no avail…

    <script type="text/javascript">
		
               $(document).ready(function(){
                    $('button').on('click', function(){
                    
    		    var GaaSOptiSpeed = $("#GaaSOptiSpeed").val();
    			var GaaSOptiLatency = $("#GaaSOptiLatency").val();
    			


    $.ajax({
      type: "post",
      url: "http://myserverip/processGaaSOpti.php",
      data: "GaaSOptiSpeed=" + GaaSOptiSpeed + "&GaaSOptiLatency=" + GaaSOptiLatency,
      success: function(data) {
        $("#info").html(data);
        //window.location.href = "index.html";
        
        
          $.ajax({                                      
      url: 'http://myserverip/processGaaSOpti.php',                  //the script to call to get data          
      data: "GaaSOptiSpeed=" + GaaSOptiSpeed + "&GaaSOptiLatency=" + GaaSOptiLatency,                      //you can insert url argumnets here to pass to api.php
                                       //for example "id=5&parent=6"
      dataType: 'json',                //data format      
      success: function(data)          //on recieve of reply
      {
        var id = data[0];              //get id
        var vname = data[1];           //get name
        //--------------------------------------------------------------------
        // 3) Update html content
        //--------------------------------------------------------------------
	
        for(var i = 0; i < data.length; i++) {
     $('#output').append("<tr><td width=20%><b>"+data[i][1]+"</b></td><td width=20%><b>"+data[i][8]+"</b></td><td width=20%><b>"+data[i][2]+"</b></td><td class=statusClass width=20%><font color=white><b>"+data[i][3]+"</b></font></td><td width=20%><b>"+data[i][4]+"</b></td></tr>");      
	}
       //Code to color cells based on if the bus is running or cancelled
	$("#output td:contains(SPEED)").attr("style","background-color:green");
	$("#output td:contains(LATENCY)").attr("style","background-color:red");



      } 
    });
        
        
        
        
        
      } 
    });
  });
});


 
				

               
       </script>
	<table bgcolor="#0099FF" width="100%">
  <form name="GaaSCollect1" method="post" action="">
    
    
   
    
    <tr>
      <td><font color="white">Enter your speed based on Assessment Tests : </font>
        <select name="GaaSOptiSpeed" id="GaaSOptiSpeed">
        <option value="1 Mbps">1 Mbps</option>
    	<option value="2 Mbps">2 Mbps</option>
    	<option value="3 Mbps">3 Mbps</option>
    	<option value="4 Mbps">4 Mbps</option>
    	<option value="5 Mbps">5 Mbps</option>
    	<option value="6 Mbps">6 Mbps</option>
    	<option value="7 Mbps">7 Mbps</option>
    	<option value="8 Mbps">8 Mbps</option>
    	<option value="9 Mbps">9 Mbps</option>
    	<option value="10 Mbps">10 Mbps</option>
    	<option value="15 Mbps">15 Mbps</option>
    	<option value="20 Mbps">20 Mbps</option>
    	<option value="25 Mbps">25 Mbps</option>
    	<option value="30 Mbps">30 Mbps</option>
    	<option value="35 Mbps">35 Mbps</option>
    	<option value="40 Mbps">40 Mbps</option>
    	<option value="45 Mbps">45 Mbps</option>
    	<option value="50 Mbps">50 Mbps</option>
    	<option value="55 Mbps">55 Mbps</option>
    	<option value="60 Mbps">60 Mbps</option>
    	<option value="65 Mbps">65 Mbps</option>
    	<option value="70 Mbps">70 Mbps</option>
    	<option value="75 Mbps">75 Mbps</option>
    	<option value="80 Mbps">80 Mbps</option>
    	<option value="85 Mbps">85 Mbps</option>
    	<option value="90 Mbps">90 Mbps</option>
    	<option value="95 Mbps">95 Mbps</option>
    	<option value="100 Mbps">100 Mbps</option>
    	<option value="150 Mbps">150 Mbps</option>
    	<option value="200 Mbps">200 Mbps</option>
    	<option value="250 Mbps">250 Mbps</option>
    	<option value="300 Mbps">300 Mbps</option>
    	<option value="350 Mbps">350 Mbps</option>
    	<option value="400 Mbps">400 Mbps</option>
    	<option value="450 Mbps">450 Mbps</option>
    	<option value="500 Mbps">500 Mbps</option>
    	<option value="550 Mbps">550 Mbps</option>
    	<option value="600 Mbps">600 Mbps</option>
    	<option value="650 Mbps">650 Mbps</option>
    	<option value="700 Mbps">700 Mbps</option>
    	<option value="750 Mbps">750 Mbps</option>
    	<option value="800 Mbps">800 Mbps</option>
    	<option value="850 Mbps">850 Mbps</option>
    	<option value="900 Mbps">900 Mbps</option>
    	<option value="950 Mbps">950 Mbps</option>
    	<option value="1000 Mbps">1000 Mbps</option>
    	</select>
      </td>
    </tr>
 
     <tr>
      <td><font color="white">Enter Your latency based on Assessment Tests : </font>
        <select name="GaaSOptiLatency" id="GaaSOptiLatency">
        <option value="1 ms">1 ms</option>
        <option value="2 MS">2 MS</option>
        <option value="3 MS">3 MS</option>
        <option value="4 ms">4 ms</option>
        <option value="5 MS">5 MS</option>
        <option value="6 MS">6 MS</option>
        <option value="7 MS">7 MS</option>
        <option value="8 MS">8 MS</option>
        <option value="9 MS">9 MS</option>
        <option value="10 MS">10 MS</option>
        <option value="11 MS">11 MS</option>
        <option value="12 MS">12 MS</option>
        <option value="13 MS">13 MS</option>
        <option value="14 MS">14 MS</option>
        <option value="15 MS">15 MS</option>
        <option value="16 MS">16 MS</option>
        <option value="17 MS">17 MS</option>
        <option value="18 MS">18 MS</option>
        <option value="19 MS">19 MS</option>
        <option value="20 MS">20 MS</option>
        <option value="21 MS">21 MS</option>
        <option value="22 MS">22 MS</option>
        <option value="23 MS">23 MS</option>
        <option value="24 MS">24 MS</option>
        <option value="25 MS">25 MS</option>
        <option value="26 MS">26 MS</option>
        <option value="27 MS">27 MS</option>
        <option value="28 MS">28 MS</option>
        <option value="29 MS">29 MS</option>
        <option value="30 MS">30 MS</option>
        <option value="31 MS">31 MS</option>
        <option value="32 MS">32 MS</option>
        <option value="33 MS">33 MS</option>
        <option value="34 MS">34 MS</option>
        <option value="35 MS">35 MS</option>
        <option value="36 MS">36 MS</option>
        <option value="37 MS">37 MS</option>
        <option value="38 MS">38 MS</option>
        <option value="39 MS">39 MS</option>
        <option value="40 MS">40 MS</option>
        <option value="41 MS">41 MS</option>
        <option value="42 MS">42 MS</option>
        <option value="43 MS">43 MS</option>
        <option value="44 MS">44 MS</option>
        <option value="45 MS">45 MS</option>
        <option value="46 MS">46 MS</option>
        <option value="47 MS">47 MS</option>
        <option value="48 MS">48 MS</option>
        <option value="49 MS">49 MS</option>
        <option value="50 MS">50 MS</option>
        <option value="60 MS">60 MS</option>
        <option value="70 MS">70 MS</option>
        <option value="80 MS">80 MS</option>
        <option value="90 MS">90 MS</option>
        <option value="100 MS">100 MS</option>
        <option value="150 MS">150 MS</option>
        <option value="200 MS">200 MS</option>
        <option value="250 MS">250 MS</option>
        <option value="300 MS">300 MS</option>
        <option value="300+ MS">300+ MS</option>
        </select>
      </td>
    </tr>

    
    <tr>
      <td>
        <button>Submit</button>

droopsnoot · June 26, 2017, 9:30am

I’m confused as to why you do the Ajax call twice, the second time from inside the success function of the first call, with slightly different specs but the same data each time. Or is that a typo?

That aside, first question is does it get the correct values from the inputs, that is, if you alert the two variables at the start of the JS function just after you’ve retrieved their values, do they show as they should?

I’m also confused about the loop in the second success function, specifically how you treat data[0] and data[1] as single array elements, but then loop through the data[0] then data[1] (and possibly more) elements as arrays in themselves. But if you say that bit is working if you hard-code the values, then that must be a lack of JS knowledge on my part - I haven’t done much in that language.

microchipmatt · June 27, 2017, 12:43am

I was wondering if the second ajax call is unessecery too. When I docuwrite the values, it is catching them, and it can spit them out…however, in my apoinion, for some reason it does not seem to be making it to the processGaaSOpti.php, I am not sure why at this point, since the data: “GaaSOptiSpeed=” + GaaSOptiSpeed + “&GaaSOptiLatency=” + GaaSOptiLatency, looks right to me.

droopsnoot · June 27, 2017, 9:37am

Can you show the code to the PHP script as well?

microchipmatt · June 27, 2017, 10:11pm

Yes I can Here it is That’s not my real user and pass of course.

     <?php

  mysqli_connect('localhost','myconnectuser','somecrazypassword', 'myGaaSDB');
  $link=mysqli_connect('localhost','myconnectuser','somecrazypassword', 'myGaaSDB');
  
 

  	
  	$GaaSOptiSpeed = mysqli_real_escape_string($link, $_POST['GaaSOptiSpeed']);
  	$GaaSOptiLatency = mysqli_real_escape_string($link, $_POST['GaaSOptiLatency']);

	//$GaaSOptiSpeed = "20 Mbps";
  	//$GaaSOptiLatency = "4 ms";

  

 
  $result=mysqli_query($link, "SELECT * FROM `GaaSOptiSettings` WHERE BINARY `GaaSOptiSpeed`='$GaaSOptiSpeed' AND BINARY `GaaSOptiLatency`='$GaaSOptiLatency'");
  
//OLD JASON RESULT CODE
//$array = array();
  //$row = ($result);
  //while($row) {
   // $array[] = $row;
   // $row = mysqli_fetch_row($result);
//}


//UPDATED JSON RESULTS CODE
while($row = mysqli_fetch_row($result))
    $array[] = $row; 




echo json_encode($array);
 
 ?>

droopsnoot · June 28, 2017, 9:50am

OK, I did a basic test. Obviously I don’t have your database so I had to curtail the PHP code, but I don’t think the database query is anything to do with the problem. I’ve removed the second Ajax call, because it didn’t seem to do anything. Adding a div with id=info displays the data I get back from the PHP, so you can do what you need after that.

This is the html code, and I’ve put ** on lines I’ve changed:

<html>
<body>
<script src="https://code.jquery.com/jquery-3.2.1.min.js"></script>
  <script type="text/javascript">
		
  $(document).ready(function(){
    $('#subDemo').click(function(event){   ** // added button id
      event.preventDefault(); ** // added this
      var GaaSOptiSpeed = $("#GaaSOptiSpeed").val();
      var GaaSOptiLatency = $("#GaaSOptiLatency").val();
    		
      $.ajax({
        type: "post",
        url: "http://localhost/gaastest.php",
        data: "GaaSOptiSpeed=" + GaaSOptiSpeed + "&GaaSOptiLatency=" + GaaSOptiLatency,
        success: function(data) {
          $("#info").html(data);
          } 
    });
  });
});

       </script>
	<table bgcolor="#0099FF" width="100%">
  <form name="GaaSCollect1" method="post" action="">
    
    <tr>
      <td><font color="white">Enter your speed based on Assessment Tests : </font>
        <select name="GaaSOptiSpeed" id="GaaSOptiSpeed">
        <option value="1 Mbps">1 Mbps</option>
    	<option value="2 Mbps">2 Mbps</option>
    	<option value="3 Mbps">3 Mbps</option>
  	</select>
      </td>
    </tr>
 
     <tr>
      <td><font color="white">Enter Your latency based on Assessment Tests : </font>
        <select name="GaaSOptiLatency" id="GaaSOptiLatency">
        <option value="1 ms">1 ms</option>
        <option value="2 MS">2 MS</option>
        <option value="3 MS">3 MS</option>
        <option value="4 ms">4 ms</option>
        </select>
      </td>
    </tr>
  
    <tr>
   <td>
        <button id="subDemo">Submit</button>  ** // added an id to the button
		</td>
		</tr>
		</table>
		</form>

<div id="info"></div>
</body>
</html>

and this is the php for reference, but as you see it’s just for testing purposes.

<?php

$GaaSOptiSpeed = $_POST['GaaSOptiSpeed'];
$GaaSOptiLatency = $_POST['GaaSOptiLatency'];

$array = array($GaaSOptiSpeed, $GaaSOptiLatency);

echo json_encode($array);
?>

The main thing I did was to give the button an id and assign the click handler to that button, but (and which made all the difference) add the reference to event and called preventDefault() so the form doesn’t submit in the normal way. Now, if I select a couple of options and hit submit, they’re passed in to the PHP code (which in my case just sends them back) and the return data is inserted into the info div.

(I deleted a load of the options just to make the code a bit easier to read, but that won’t matter - I had them in place while testing. )

microchipmatt · June 29, 2017, 1:54pm

Thank-you so much. I will test this today and get back to you, I appreciate all the help. Talk to you soon.

microchipmatt · June 30, 2017, 8:42pm

Still testing, just getting to it now, will respond soon.

microchipmatt · June 30, 2017, 11:48pm

IT WORKS!!! Thank you. I had to add back some of my code to get it to encode jason data to the data type on feedback. Thank-you for your help!!

microchipmatt · July 2, 2017, 7:24pm

Hi Droopsnoot, I did get this working…I have one more situation I am trying to resolve. So based othese exmaple, I now have code where I can query a table. I have code wheere I can serach or query specific values in a db and get a result. I am scowering the internet trying to find an example I can integrate into this code AJAX, Jquery, html php code where I could have a like sialike system. I would need to be able to:

A. Query reacords client side, and only process on server side with JSON, html, javascript, jquery and AJAX like I am now.
B. The like dislike would need to show up per record queried
C. The Like, Dislike buttons would need to show up for the query and apply per record.
D. Once a certain number of like or dislikes occured the data base record entry would become certified or uncertified…

Do you have any ideas for how I can acheieve this…any input would be greatly appreciated.

droopsnoot · July 3, 2017, 9:41am

Well, that’s basically the same as you’re doing above, but instead of reading the values from drop-down lists and sending them to a query, you’re reading the values from a button and sending that to a query. The difference is that it’s an update query.

So your code would just be to read the button, call the query to record whether it’s a like or a dislike, and then, if there are sufficient of either, mark the certification status. The button would be drawn for each record, and contain a means of identifying which record it refers to.

The only complexity is that you’ll need to know who your user is, so that you can check whether they’ve voted before and update things accordingly. So if the user “liked” and now “dislikes”, as well as storing the “dislike”, you also need to remove the “like”. Or if you prefer, don’t give them the chance to vote again, don’t draw the buttons if they already have a vote recorded.

microchipmatt · July 3, 2017, 5:15pm

Okay, I’ll try it…hopefully I can make this all work. I know this might sound rediculous, but the part I am having trouble wrapping my head around is, when they initially quiry the record in a search, how do I, “Draw the button” for the record? In my eyes the order of operations would be this, unless I’m completly out to lunch:

A serach Query is made (Like we have done already, in the revioud code)
Oncce the query is made, somehow it will have a like a dislike button associstaed with it (This is the part I stumble on for some reason). It would be displayed for each record, and somehow I would more than likely use the record’s ID, to relate to that specific database entry or row. I have updated on record ID in the past, so I imagine it will be similar to that.
The record Like or Dislike would be updated based on the button they press. Some kind of count of the records would occur, to enumerate the total likes or dislikes for certification.

I will do my best to try any come up with some working code…I may stumble…But I’ll do my best…you may hear from me again…

system · October 3, 2017, 12:15am

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.

Topic		Replies	Views
If (isset($_POST['name'])) { JavaScript equivalent JavaScript	32	51948	December 3, 2013
Run a database query in a JS file JavaScript	30	89292	April 19, 2011
PHP with AJAX Scripting PHP	12	3069	June 26, 2013
AJAX not passing variable to PHP JavaScript	7	4455	May 30, 2013
JQuery JSON updating sql JavaScript jquery	31	2230	November 15, 2021

Ajax, php, mysql ,json Query issues

Related topics