<?
$rows_per_page=6;
$lastpage = ceil($numrows/$rows_per_page);//<-- came from a previous working query
$f=0;
for($ii=0;$ii<$lastpage;$ii++){
$pagei=$ii+1;
if($pagei==1){
$limit_left = 0;
}else{
$limit_left = $f+$rows_per_page;
}
$fnqs = "SELECT users.fn FROM users WHERE users.cc='abc' ORDER BY users.fn ASC LIMIT $limit_left,$rows_per_page";
$fnquery = mysql_query($fnqs,$conn);
while($ln=mysql_fetch_array($fnquery)){
if($f==0){
$from_ln = "$ln[fn]";
$f++;
}elseif($f<=$rows_per_page){
$to_ln = "$ln[fn]";
$f++;
}else{
$from_ln = "$ln[fn]";
$f=0;
}
}
echo "<option value=\\"$pagei\\">$limit_left $from_ln — $f $to_ln</option>\
";
}
?>
For some reason the above code results with this:
<option value="1">0 Ace - 6 Bleh</option>
<option value="2">12 Packer - 0 Olsen</option>
<option value="3">6 Davis - 6 Loblaw</option>
What I want to happen is this:
<option value="1">0 Ace - 6 Bleh</option>
<option value="2">7 Davis - 13 Loblaw</option>
<option value="3">14 Packer - 20 Olsen</option>
The point is I would like the list to generate alphabetically. I can’t figure out why they start in order but then are out of order. (The numbers in front of the names are for debugging purposes only.)
I’m sure the answer is right in front of my face, but I’m an idiot.
Thanks in advance for any help offered!
There are a few problems with the way you’re going about things - if your user base grows large, then this is going to be a performance drag, as it has to loop through ALL users in the table - and it’s doing so over multiple queries!
You could just select all the users in the first place and then in the loop check if the count has reach the ‘per page’ limit, then echo the option.
I think you’d be better off trying to perform this in more simple query, for example getting finding out how many rows there are in the table then performing a query which selects on the xth and x+nth user names.
Ok, so I created the SQL required to do this in one go, however it turned out a little harder than I had expected. I’m sure there could possibly be a better solution than this, but at least this one works. I’ve used a per page value of 4, so you will need to replace all 4’s with $rows_per_page and all 3’s with $rows_per_page-1.
select
u1.name,
u2.name,
(
select
count(name)
from
users
where
name < u1.name
order by
name
) as position
from
users u1,
users u2
where
(u2.name =
(
(
select
u3.name
from
users u3
having
((
select
count(name)
from
users
where
name >= u3.name
AND name > u1.name
order by
name
limit 1
) % 4) = 3
order by
name
limit 1)
)
OR
(
(select
count(name)
from
users
where
name > u1.name
order by
name
limit 1
) < 4)
AND
u2.id = (
select id from users order by name desc limit 1
)
)
having
(position % 4) = 0
order by
u1.name
Simplified a little, now you only need to change the perrow value once at the top.
select
4 as perpage,
u1.name,
u2.name,
u2.id,
(
select
count(name)
from
users
where
name < u1.name
order by
name
) as position
from
users u1,
users u2
having
(position % perpage) = 0
and
(u2.name =
(
(
select
u3.name
from
users u3
having
((
select
count(name)
from
users
where
name >= u3.name
AND name > u1.name
order by
name
limit 1
) % perpage) = (perpage-1)
order by
name
limit 1)
)
OR
(
((select count(*) from users)-position) < perpage)
AND
u2.id = (
select id from users order by name desc limit 1
)
)
order by
u1.name
Wow. My head hurts now. :injured:
I’ll give it a shot and let you know. Thanks!
What’s the relationship between $from_ln and $to_ln supposed to be?
$from_ln = “From this Last Name…”
$to_ln = “…To this Last Name”
(in alphabetical order).
Is that what you meant?
I’m still a bit confused about the actual relationship (at the DB level) between $to_ln and $from_ln. I assume that if you had a User table that looked like this:
id username
--------------------
1 A
2 B
3 C
4 D
5 E
6 F
7 G
8 H
Then you’re looking for a set of <option>'s that look like this?
<option>A - B</option>
<option>C - D</option>
<option>E - F</option>
<option>G - H</option>
In which case there’s no distinct relation in the DB between A and B etc, other than the fact they are 1 row apart from each other when sorted alphabetically. (You may want to re-think your schema so you can query it correctly).
Thus (ignoring anything except the core problem here):
$sql = "SELECT name FROM users ORDER BY name ASC";
$result = mysql_query($sql, $conn);
while ($firstRow = mysql_fetch_assoc($result))
{
if (!$secondRow = mysql_fetch_assoc($result))
{
break; // Not enough data to construct a new <option>
}
echo '<option>' . $firstRow['name'] . ' - ' . $secondRow['name'] . '</option>;
}
Chippie, my SQL sorts the whole thing out - I was just wondering if there’s a simpler version of my SQL statement
ericksonstudio, can you post a “Create Table” for the users table?
Let’s get it straight as to what your trying to do, your trying to display a list of users, but with a certain number of users listed per page, correct?
$perrow = 4; // Change this to the number of names per page you want
$sql = "SELECT
$perrow AS perpage,
u1.name as fname,
u2.name as lname,
u2.id,
(
SELECT
count(name)
FROM
users
WHERE
name < u1.name
ORDER BY
name
) AS position
FROM
users u1,
users u2
HAVING
(position % perpage) = 0
AND
(u2.name =
(
(
SELECT
u3.name
FROM
users u3
HAVING
((
SELECT
count(name)
FROM
users
WHERE
name >= u3.name
AND name > u1.name
ORDER BY
name
LIMIT 1
) % perpage) = (perpage-1)
ORDER BY
name
LIMIT 1)
)
OR
(
((SELECT count(*) FROM users)-position) < perpage)
AND
u2.id = (
SELECT id FROM users ORDER BY name DESC LIMIT 1
)
)
ORDER BY
u1.name";
$result = mysql_query($sql, $conn);
while ($row = mysql_fetch_assoc($result))
{
echo '<option>' . $row['fname'] . ' - ' . $row['lname'] . '</option>;
}
This uses my sql statement, and will also be the most optimised as it performs only a single query - so that when the database is huge with your 1,000,000 users it won’t be dogged down with multiple queries.
Note: You’ll definitely want to put in INDEX on the name column
Often I consider post-processing a result set to be better than writing twisty SQL There’s a slight tradeoff between performance and legibility of the code. In this case, I don’t really think the performance hit using code like mine would be all that significant (you just need to apply the limits x 2 to account for the relationship between the rows). I didn’t delve too far into your SQL, but given the relatively simple nature of the problem it probably can use a relatively simple solution
PS: Sub-selects are expensive when used like this (they’ll evaluate for each row in the outer query)… ideally you’d incorporate the sub-select into a derived table for optimal performance.
EDIT : I wasn’t intending my small query to be used inside a for loop like the OP’s code… I’m not too sure what that loop was there for but the only requirement I was aiming for was to produce the <option> group. Hence, my code is the entire code.
This thread has totally confused me now. :injured:
I can’t post the table because of dependancies on 2 other tables, and it’s proprietary. I am very grateful for all your help.