2 Replies - 1426 Views - Last Post: 30 October 2011 - 04:24 PM Rate Topic: -----

#1 itdoell  Icon User is offline

  • D.I.C Head

Reputation: 8
  • View blog
  • Posts: 239
  • Joined: 13-January 11

Limit SQL query based on Pagination Script

Posted 30 October 2011 - 02:21 PM

So I have two files I need to figure out how to make work together. One is a pre-made pagination script, and the other is simply a script.php file where I run queries. My problem though is that I do not know how to limit the new queries to just the posts being displayed via this pagination script.

This is how the pagination script grabs results (based on variables entered earlier.

//Fetch the required result set
		$rs = @mysql_query($this->sql . " LIMIT {$this->offset}, {$this->rows_per_page}" );
		if (! $rs) {
			if ($this->debug)
				echo "Pagination query failed. Check your query.<br /><br />Error Returned: " . mysql_error();
			return false;
		}
		return $rs;


Could someone tell me how I can take this and apply it to my script.php file...?

I just grab queries the normal way in my script.php file like this...

$sql = mysql_query("SELECT * FROM posts WHERE type= 'image'");
while($row = mysql_fetch_assoc($sql)) {
$id = $row['id'];
echo "
<script>
alert('$id');
</script>
";
}


But obviously this isn't practical because I'll be alerting the ID's of all image posts on the site regardless of whether or not they're on the page. I've been stuck on this for a couple days, so if anyone can help me out here I'd greatly appreciate it.

Is This A Good Question/Topic? 0
  • +

Replies To: Limit SQL query based on Pagination Script

#2 Ace26  Icon User is offline

  • D.I.C Head

Reputation: 40
  • View blog
  • Posts: 183
  • Joined: 10-August 08

Re: Limit SQL query based on Pagination Script

Posted 30 October 2011 - 03:20 PM

I think the link you pointed us to explains it all well. But if I understand your question thoroughly, then I'd suggest you do this in your script.php:

replace
$sql = mysql_query("SELECT * FROM posts WHERE type= 'image'");



in script.php with

/Include the PS_Pagination class
include('ps_pagination.php');

//Connect to mysql db
$conn = mysql_connect('localhost', 'username', 'password');
mysql_select_db('testdb',$conn);

//Query to pass to pagination class
$sql = "SELECT * FROM posts WHERE type= 'image'";

//Create a PS_Pagination object where 8 is the number of rows to be displayed per page
//and 3 is the number of navigation links per page
$pager = new PS_Pagination($conn, $sql, 8, 3);

//The paginate() function returns a mysql result set for the current page
$rs = $pager->paginate();

//Loop through the result set just as you would loop
//through your normal mysql result set
while($row = mysql_fetch_assoc($rs)) {
	echo $row['id'];
...
//then all your alerting logic here...
}



This way you will be alerting only 8 id's per page. Does that solve your problem?
Was This Post Helpful? 1
  • +
  • -

#3 itdoell  Icon User is offline

  • D.I.C Head

Reputation: 8
  • View blog
  • Posts: 239
  • Joined: 13-January 11

Re: Limit SQL query based on Pagination Script

Posted 30 October 2011 - 04:24 PM

View PostAce26, on 30 October 2011 - 03:20 PM, said:

I think the link you pointed us to explains it all well. But if I understand your question thoroughly, then I'd suggest you do this in your script.php:

replace
$sql = mysql_query("SELECT * FROM posts WHERE type= 'image'");



in script.php with

/Include the PS_Pagination class
include('ps_pagination.php');

//Connect to mysql db
$conn = mysql_connect('localhost', 'username', 'password');
mysql_select_db('testdb',$conn);

//Query to pass to pagination class
$sql = "SELECT * FROM posts WHERE type= 'image'";

//Create a PS_Pagination object where 8 is the number of rows to be displayed per page
//and 3 is the number of navigation links per page
$pager = new PS_Pagination($conn, $sql, 8, 3);

//The paginate() function returns a mysql result set for the current page
$rs = $pager->paginate();

//Loop through the result set just as you would loop
//through your normal mysql result set
while($row = mysql_fetch_assoc($rs)) {
	echo $row['id'];
...
//then all your alerting logic here...
}



This way you will be alerting only 8 id's per page. Does that solve your problem?



Well actually no, because I already call the pagination earlier to display posts.... the thing is I want to be able to call it again. So essentially I have it display the posts, then I want to perform commands afterwards from script.php. Now I know I could just stick the javascript in the loop when it's called earlier, but I'd prefer to just keep all the javascript/jquery/ajax in the script.php file. So do you know how I would be able to call it again?

The pagination is working fine, it's just my inability to figure out how I can run another query for only the displayed posts after it's already been called.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1