asked on

SQL statement is no longer displaying parameters set up in previous search box due to added Ajax call (I think?)

Hi, I've edited this question as it was previously too long winded. Breaking my issue down, using Ajax, I have declared values for offset and limit

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

Open in new window

Later on in my code I try to assign those values to variables:

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

Open in new window

And then try to apply the following SQL select function:

$data_sql = "SELECT * FROM teachers_table LIMIT {$limit} OFFSET {$offset}";

However, I'm getting this error message:

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

Is there an issue with how I've defined $limit and $offset or perhaps my sql query?

Many thanks.

Raja Jegan R

>> SELECT * FROM teachers_table LIMIT limit OFFSET offset

Ideally limit and offset should be a numerical value instead of a string as you have shown above..
Or basically, your values aren't replaced by the code which you have above and hence getting an error.
I'm not comfortable with AJAX and hence found out some article to your for AJAX variable passing..
https://forum.freecodecamp.org/t/assigning-ajax-response-to-variable/84991

Adam

ASKER

Thanks Raja Jegan R,

You are right in that when I replaced the strings with numbers (9) I didn't get the error messages any more but my loop just kept displaying the first 9 items in the table over and over again. I guess I want the value to increment in multiples of 9. Should I perhaps be looking to do the looping increment outwith AJAX?

Thanks,

Adam

Adam

ASKER

I tried to increment the offset value using php:

<?php

					for($offset = 0; $offset < 10000; $offset += 9){	
					
					//$data_sql = " SELECT * FROM teachers_table WHERE ". join(' AND ', $whereClause) ."  ";  //works on its own
					$data_sql = "SELECT * FROM teachers_table LIMIT 9 OFFSET {$offset}";  //isn't working
					}
					
					$data = mysqli_query($db_connection, $data_sql);
					
				?>

Open in new window

But this just resulted in no results from the database table being displayed...:(

Raja Jegan R

Kindly let us know how many records your teachers table has so that we can confirm whether it should return records or not..

Adam

ASKER

Hi Raja. Apologies, I just saw the php I posted was blank. I will amend this when I return home in about 7 hours (currently at work). As for the records in teacher table, there are several thousand.
Thanks.

ASKER CERTIFIED SOLUTION

Raja Jegan R

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Adam

ASKER

Just updating this post as I got a reminder from Admin to keep things going or award points. I'm still working on the issue (but hope to get there sometimes) and will update this comment when I make a bit more progress.

Thanks,

Adam

Adam

ASKER

Closing this post - Struggled to complete so I've decided on a different approach.