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');?> <!--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> <?php echo h($teacher['firstname']); ?></strong></a> - <?php echo h($teacher['language']); ?> teacher
</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>");}
?>
<?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>");}
?>
<?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>");}
?>
<?php
if($teacher['vid'] == "1")
{print ("<IMG SRC =public/img/icons/video1.gif>");}
?>
<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 <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 <i class="fa fa-thumbs-o-up" aria-hidden="true"></i></button>
</div>
</div>
</div>
<!-- card------------------------------------------------------------------------------------------------------------->
<?php } ?>
</div>
<?php //} ?>
</div>
</div><!-- container ends-->