Problems with SQL query not returning error - Maybe Ajax issue?


#1

Hi all,

I am struggling to combine 2 working webpages I have into one. One webpage takes search parameters and displays the results from a search box. The other automatically loads information from the database when I scroll to the bottom of the page.

I have used ajax to create two variables:

$.ajax({ type: "GET", url: "mini_profiles.php", data: { 'offset':0, 'limit':9 }, success:function(data){ $('body').append(data); flag += 9; }

Further down in my code I assign those values to strings:

$limit = 'limit'; $offset = 'offset';

I then try to call in an sql statement
[php]$data_sql = “SELECT * FROM teachers_table LIMIT {$limit} OFFSET {$offset}”;[/php]

but I get the following error:

Query: SELECT * FROM teachers_table LIMIT limit OFFSET offset
Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘limit OFFSET offset’ at line 1

This suggests the statement might not be correct but I’m not sure why?

The entire code is pasted below - I have commented out some of the SQL commends as I am trying to work out what is causing the problems, but my final sql command will be something like this -

[php]$data_sql = "SELECT * FROM teachers_table LIMIT {$limit} OFFSET {$offset} WHERE “. join(’ AND ', $whereClause) .” ";[/php]

or maybe this?
[php]$data_sql = "SELECT * FROM teachers_table WHERE “. join(’ AND ', $whereClause) .” LIMIT {$limit} OFFSET {$offset} ";[/php]

Many thanks for reading this…Full code posted below…

[php]

<?php require_once('private/initialize.php'); ?> <?php include('header.php');?> <?php $page_title = 'Orango Japan Teacher'; ?> <?php include('navbar.php');?>
Load More
 <!--make the ajax call when page loads-->
$(document).ready(function()
{
	 var flag = 0;
	 
	 <!--pass the two parameters, offset and limit-->	
	 $.ajax({
		 
			type: "GET",
			url: "mini_profiles.php",
			data: {
				'offset':0,
				'limit':9
				  },
			success:function(data){
				$('body').append(data);
				flag += 9;
			}
		 
			});
			//Every time when we scroll we check the current value of scrollbar 
			//and if it has reached the bottom of the page
			$(window).scroll(function(){
				if($(window).scrollTop()>= $(document).height() - $(window).height()){
			//this is what happens at the bottom - same ajax function but we now want to offset by+=3 everytime
			//so above we create a variable and increase by three whenver the ajax call is successful		
					
					 $.ajax({
		 
					type: "GET",
					url: "mini_profiles.php", //this is the ajax function calling the get_data.php
					data: {
						'offset':flag,
						'limit':9
						  },
					success:function(data){
						$('body').append(data);
						flag += 9;
					}
				 
					});
					
					
				}
			});
});

</script>
</head>
<body>


<div class="container">

				



			 <?php
			  
			  $language   = isset($_POST['language'  ]) ? $_POST['language'  ] : null; //These are the values sent through from the simple search box on the homepage
			  $prefecture = isset($_POST['prefecture']) ? $_POST['prefecture'] : null;
			  $vid        = isset($_POST['vid'       ]) ? $_POST['vid'       ] : null;
			  $photo      = isset($_POST['photo'     ]) ? $_POST['photo'     ] : null;
			  
			  
			  $whereClause = []; //we are creating an empty array and calling it 'where clause'
			  
			  //The 'mysqli_real_escape_string()' function escapes special characters in a string for use in an SQL statement.
			  //It provides a level of protection against SQL Injection
			  
			  if ($language) {  
				 $whereClause[] = 'language = "'. mysqli_real_escape_string($db_connection, $language) .'"';  //if a value for language has been passed in then add it to the array
			 }
			 
			 if ($prefecture) {
				 $whereClause[] = 'prefecture = "'. mysqli_real_escape_string($db_connection, $prefecture) .'"';
			 }
			 
			 if ($photo) {
				 $whereClause[] = 'photo != ""';
			 }
			  
			  if ($vid) {
				 $whereClause[] = 'vid != ""';
			 }
			 
			 if (empty($whereClause)) {
				 $whereClause[] = '1';
			 }
			 
			 ?>
	 
	 
	 
	 

			<?php

			//if(isset($_GET['offset']) && isset($_GET['limit']))
			//{
				
				$limit = 'limit';
				$offset = 'offset';
						
				//creating an sql
				//$data_sql = " SELECT * FROM teachers_table WHERE ". join(' AND ', $whereClause) ."  ";  //works on its own
				$data_sql = "SELECT * FROM teachers_table LIMIT '{$limit}' OFFSET '{$offset}'";  //isn't working
				
				//$data_sql = "SELECT * FROM teachers_table LIMIT {$limit} OFFSET {$offset} WHERE ". join(' AND ', $whereClause) ."  ";
				//$data_sql = "SELECT * FROM teachers_table  WHERE ". join(' AND ', $whereClause) ." LIMIT {$limit} OFFSET {$offset}  ";
				//running sql to get dataset
				$data = mysqli_query($db_connection, $data_sql);
				
			?>
				
			
			<?php
			//Testing for errors
			if($data === false)
			{
			  echo "<b>Query failed!!</b><br />\n<b>Query:</b> {$data_sql}<br />\n<b>Error:</b> " . mysqli_error($db_connection);
			exit();
			}
			
			//
			?> 
				
				
				
				
				<div class="row">		
			
						<?php while($teacher = mysqli_fetch_array($data))
						{?> 
					
					
					
					
						
					
									<!-- card--------------------------------------------------------------------------------------------------------->				
									 <div class="col-sm-6 col-md-4 buffer">
											<div class="card" style="width: 100%;">
														<div class="card-header">
														<img src="public/img/flags/<?php echo h ($teacher['nationality']) ?>flag.gif" alt="teacher nationality" />
														<a href = "<?php echo 'Profile.php?id=' . $teacher['id'];?>"><strong>&nbsp;&nbsp;&nbsp;<?php echo h($teacher['firstname']); ?></strong></a>&nbsp; - &nbsp;<?php echo h($teacher['language']); ?> teacher&nbsp;&nbsp;
														
														</div>
												
														<div class="card-body">
															 <div align="center"> 
															 <a href = "<?php echo 'Profile.php?id=' . $teacher['id'];?>"><img src="http://orangutanenglish.com/upload/<?php echo h($teacher['photoid']); ?>" class="ojborder " width="176" height="156" alt="" title="" /></a></div>
																	  <div class="col-sm text-center">
																	  <br />
																		<?php
																		if($teacher['teflconfirm'] == "yest")
																		{print ("<IMG SRC =public/img/icons/yes_t_icon.gif>");}
																		else  
																		{print ("<IMG SRC =public/img/icons/no_t_icon.gif>");}
																	   
																		?>&nbsp;&nbsp;
																		<?php
																		if($teacher['degreeconfirm'] == "yesd")
																		{print ("<IMG SRC =public/img/icons/yes_d_icon.gif>");}
																		elseif ($teacher['degreeconfirm'] == "studying")  
																		{print ("<IMG SRC =public/img/icons/studying_icon.gif>");}
																		else  
																		{print ("<IMG SRC =public/img/icons/no_d_icon.gif>");}
																		?>&nbsp;&nbsp;
																		<?php
																		if($teacher['expconfirm'] == "expconfirm")
																		{print ("<IMG SRC =public/img/icons/yes_exp_icon.gif>");}
																		elseif ($teacher['expconfirm'] == "someexpconfirm")  
																		{print ("<IMG SRC =public/img/icons/some_exp_icon.gif>");}
																		else  
																		{print ("<IMG SRC =public/img/icons/no_d_icon.gif>");}
																		?>&nbsp;&nbsp;
																		<?php
																		if($teacher['vid'] == "1")
																		{print ("<IMG SRC =public/img/icons/video1.gif>");}
																		
																		?>&nbsp;&nbsp;
																		<br><br>
																		<h6 class="card-subtitle mb-2 text-muted"><div align="center">profile added: May 2017</div></h6>
																		<p class="card-text"><div align="center">Price per lesson: <?php echo h($teacher['private_price']); ?><br>Teaching Locations: <?php echo h($teacher['prefecture']); ?></div></p>
																	  </div>
																	
																	<button type="button" data-toggle="popover" class="btn btn-success btn-sm" data-placement="top" data-content="Sorry - only members can contact teachers" data-original-title="Members Only" data-trigger="focus">Ask about availabilty&nbsp;&nbsp;<i class="fa fa-comment-o" aria-hidden="true"></i></button> 
																	<button type="button" data-toggle="popover"  class="btn btn-info btn-sm"  data-placement="bottom"  data-content="Sorry - only members can add favourites" data-original-title="Members Only" data-trigger="focus">Favourite&nbsp;&nbsp;<i class="fa fa-thumbs-o-up" aria-hidden="true"></i></button> 

															</div>
													  
											
										  </div>
									</div>	
							 <!-- card-------------------------------------------------------------------------------------------------------------> 
				  <?php } ?> 
							 
				</div>				 
					
				
				
			 <?php //} ?> 




</div>	






</div><!-- container ends--> 		  		 
[/php]

#2

It doesn’t appear that you understand what ajax is or that php code is executed on the server when a page gets requested and javascript/jquery/ajax code is executed in the browser when the page gets rendered. You also need to produce a valid html document.

The ajax code makes a http request to the url: parameter, receives the response back from that http request, and appends that to the appropriate place in the current html document being displayed in the browser. The ajax code you have makes an initial ajax request to get the 1st ‘page’ of data when the main page is requested. This however causes the page to ‘flicker’ when it is altered to display this data. It is customary to have the 1st ‘page’ of data be output when the main page is requested. The ajax url should be to a separate .php file that just does the processing necessary to get the requested data and output it. You can make the url be the same as your main page, but you would then need to have php logic to detect a non-ajax request and an ajax request and output the correct content at the correct time. Just keep it simple and have a separate .php file to handle the ajax request.

The .php processing code needs to validate the inputs, produce and execute the sql query, retrieve the data, and output the result back to the ajax code. The inputs will be in $_GET variables (the javascript variables don’t exist in the php code and you cannot just assign them to php variables. The current assignment is assigning literal strings to the php variables, which results in invalid sql syntax.)

As to your existing search form. You should be using a method=‘get’ form for these. This will cause the search parameters to exist in the query string part of the url when the main page is requested. The ajax code should read the existing get parameters and include them in the data: parameter that is sent in the ajax request. The .php processing code will then receive both the search get parameter and the pagination/scroll get parameters.


#3

[php] $limit = ‘limit’;
$offset = ‘offset’;

$data_sql = “SELECT * FROM teachers_table LIMIT {$limit} OFFSET {$offset}”;[/php]

Equates to,
[php]
$data_sql = “SELECT * FROM teachers_table LIMIT limit OFFSET offset”;[/php]


#4

Hi phdr, astonecipher,

Thanks for getting back to me with this and apologies for the delay in getting back.

phdr - you are correct in that my understanding of the proper working of ajax vs php is shaky at best. Many thanks for the guidance. I’ll get back to the drawing board with these and as you suggested, use a separate php page to handle the ajax request and use GET requests for my form to allow the ajax page to read them.

Many thanks