0

I'm a noob, and I'm following a guide to setup counts, page numbers, and links for the additional pages that follow a SQL LIMIT query.

http://evolt.org/node/19340/

So far, everything works. I have total counts, total page numbers, and linkable pages. It looks like this and appears to be accurate:

enter image description here

The problem lies with actually linking to the next pages. The links fail to make the page go to the next page and I either get errors, or simply the default query reloads page 1. I can never get to page 2.

The section of his page I'm stuck at is titled "Linking To Other Pages". He uses these 3 different lines to make the pages link:

<a href=\"$PHP_SELF?query=$query&page=$back_page&limit=$limit\">back</a> 
<a href=\"$PHP_SELF?query=$query&page=$ppage&limit=$limit\">$i</a>
<a href=\"$PHP_SELF?query=$query&page=$next_page&limit=$limit\">next</a>

Regardless of what I try, I can't get that code to actually link the page. $query is not defined in my code, and I can't really see where it's defined in his, so I don't know how to properly formulate my structure. I could really use some help.

More Info About My Code:

Some variables from my code (the query is an example as it's actually much longer):

$database = new mysqli($hostname, $username, $password, $databasename);

$sqlquery = "SELECT DISTINCT";  

$sql = "
event.event_id as 'Event ID',
event.event_group_id as 'Group ID',
abs_path.abs_path as 'Path\Filename'
FROM 
event.event_group
LEFT JOIN event.event using (event_group_id)
LEFT JOIN event.package_config using (event_id)
LEFT JOIN file_info.abs_path using (abs_path_id)
WHERE event.timestamp >= DATE_SUB(CURDATE(),INTERVAL $time)
LIMIT 50";

$result = $database->query($sqlquery.$sql);

If I try to replace his ?query=$query variable with variations of my $sqlquery$sql variables like this:

<a href=\"$PHP_SELF?query=$sqlquery$sql&page=$back_page&limit=$limit\">back</a>

Then it causes this to happen in the browser (of course I don't get proper results with this):

enter image description here

The only other difference I see between his code and mine is his code shows this:

while ($data = mysql_fetch_array($results))

Whereas I do this:

while($row = $result->fetch_row())

I'm not calling 'array'. But from my research, I'm not sure this is my issue (it's just the only other thing that is different, so I figured I'd mention it just in case)

I just can't get the query to _POST and I have no idea what I'm doing wrong. I'm really frustrated. Can anyone possible help out? I can provide more info if needed.

Thanks.

Community
  • 1
  • 1
Ron Paulfan
  • 31
  • 1
  • 1
  • 8
  • I think that the tutorial that you're using is wrong. the $query is not defined there too. try to remove the $query or define it with "" – carmel May 24 '16 at 05:24
  • As I showed in the example, I've tried all sorts of variations. I've removed ?query=$query, I've removed it and replaced it with my own variables. I've tried at least 2 dozen different variations. I have no idea how to properly form that $PHP_SELF query – Ron Paulfan May 24 '16 at 05:27
  • I would use a different tutorial as well. For me any paging tutorial that does a `select *` and then mysql_num_rows() to count the total number of lines is useless. – Shadow May 24 '16 at 05:30
  • I'm actually not using that bit of code. I grab counts by running a separate query string that uses the COUNT function: $sqlcount = "SELECT DISTINCT count(event.event_id) as 'rowcount',"; And then I just append it as $sqlcount.$sql --- So I run 2 queries. One to pull the counts, and one to pull the results. I just change $sqlquery to $sqlcount when I need to switch. – Ron Paulfan May 24 '16 at 05:35
  • Good man. Actualky, you do not need the $query parameter at all, since you are not filtering your query. You must not pass the entire sql query in the parameter. – Shadow May 24 '16 at 05:37
  • And I hate to use a new tutorial. It's SOOO close to working. Everything works except going to page #2 etc. I would hate to change it all at this point. The math is working, the counts are working -- everything is working except for actually going to the next page. – Ron Paulfan May 24 '16 at 05:37
  • What do you mean that I don't need the query parameter? Are you saying it should look like this? **back** – Ron Paulfan May 24 '16 at 05:39
  • Sorry, you have $time as a oarameter, so that's the one you need to pass. The limit clause should be dynamic, that decides what data to show. – Shadow May 24 '16 at 05:39
  • Yes, $time is defined.. but I don't understand what you mean by that comment. – Ron Paulfan May 24 '16 at 05:40
  • And you should not use php_self either. It can be used to attack your code. Plus the code relies on the old and deprecated register_globals directive to be set. Pls dump this tutorial and find a good one instead. – Shadow May 24 '16 at 05:45
  • This is for an internal, non-public facing project. Is there anything that can be done to salvage this code? Can you provide an example of the proper structure as I really have no clue what it should look like. Thank so much for your assistance. – Ron Paulfan May 24 '16 at 05:50
  • Insiders pose at least the same if not more risk than external hackers, so this is a poor excuse to write insecure code. I'm sorry, but I will not go through this old and poor tutorial line by line to check for errors. Go and find a more up-to-date and better quality tutorial on paging. There are dozens out there. I do not think that you have to rewrite everything. – Shadow May 24 '16 at 23:55

1 Answers1

0

This is more question related to how to write mysql query

MySQL Data - Best way to implement paging?

SELECT * FROM table_name WHERE conditions LIMIT offset, rows_count

In your query, you have only offset

so if you enter offset 50, it will rows from 50 to last_row

but if you have 10 rows per page, you should use

LIMTI 1, 10 -- for first page
LIMIT 11, 10 -- for 2nd page
...
LIMIT 101, 10 -- for 10th page
-- etc.
Community
  • 1
  • 1
Richard
  • 1,045
  • 7
  • 11