mysql into an array
Hi,
Im wanting to get a query from mysql put it into an array and use the previous and next values of the array for my 'previous' and 'next' page numbers
what i have so far is being able to create the array:
<div id="nav_next">
<?
$sql = "SELECT user_id
FROM joined_users";
$res = mysql_query($sql);
while($row = mysql_fetch_array($res)){
$array1[]= $row[' user_id '];
}
print_r($array1); // prints out as Array ( [0] => 44 [1] => 46 [2] => 47 [3] => 48 [4] => 50 )
// now what i want to do is put this into the navigation so that the next brings up the next number in the array. where $current_id = (the user_id)
?>
<a href="index.php?Act=view_p
<a href="index.php?Act=view_p
</div>
Thanks
Im wanting to get a query from mysql put it into an array and use the previous and next values of the array for my 'previous' and 'next' page numbers
what i have so far is being able to create the array:
<div id="nav_next">
<?
$sql = "SELECT user_id
FROM joined_users";
$res = mysql_query($sql);
while($row = mysql_fetch_array($res)){
$array1[]= $row[' user_id '];
}
print_r($array1); // prints out as Array ( [0] => 44 [1] => 46 [2] => 47 [3] => 48 [4] => 50 )
// now what i want to do is put this into the navigation so that the next brings up the next number in the array. where $current_id = (the user_id)
?>
<a href="index.php?Act=view_p
<a href="index.php?Act=view_p
</div>
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Skip the array
<?
$sql = "SELECT user_id FROM joined_users";
$res = mysql_query($sql);
$crit = 0;
while($row = mysql_fetch_array($res) && $crit <=1){
if ($row['user_id'] == $_GET['id'] && $crit==0) {
$row_cur = $row['user_id'];
$crit++;
}
else if ($crit >= 1) {
$row_next=$row['user_id'];
$crit++;
}
if ($crit == 0) {
$row_prev = $row['user_id'];
}
}
if (isset($row_prev)) {
?>
<a href="index.php?Act=view_p
<?
}
if (isset($row_next)) {
?>
<a href="index.php?Act=view_p
<?
}
?>
<?
$sql = "SELECT user_id FROM joined_users";
$res = mysql_query($sql);
$crit = 0;
while($row = mysql_fetch_array($res) && $crit <=1){
if ($row['user_id'] == $_GET['id'] && $crit==0) {
$row_cur = $row['user_id'];
$crit++;
}
else if ($crit >= 1) {
$row_next=$row['user_id'];
$crit++;
}
if ($crit == 0) {
$row_prev = $row['user_id'];
}
}
if (isset($row_prev)) {
?>
<a href="index.php?Act=view_p
<?
}
if (isset($row_next)) {
?>
<a href="index.php?Act=view_p
<?
}
?>
The reason that I suggested that you run two queries is this way you are not buffering your complete table each time all you want is the next page. Depending on the size of your table this may take a very long time (putting unnecessary strain on your database) - If you run two queries that only only return one row - you will have a faster and more efficient page.
ASKER
running 2 queries each time the next page is accessed is better (performance wise) than putting them all into an array one time then skipping through the array?
michael
michael
it should be,
going through every record in a table involves the data to be sent to php - memory set aside for it from the db server and for php itself, then cycling through each record - not using the data is less efficient than relying on the db engine to do the work for you. There is only slight overhead for running two queries instead of one.
going through every record in a table involves the data to be sent to php - memory set aside for it from the db server and for php itself, then cycling through each record - not using the data is less efficient than relying on the db engine to do the work for you. There is only slight overhead for running two queries instead of one.
Wouldn't the 2 queries option rely on the user id's being in a particular order, though? I'm thinking from a perspective of how the user id's are generated. Are they just auto_incremented? If not, then it's possible that a new user id will be lower numerically than a pre-existing user id.
Of course, without knowing the actual schema of the database, it's all assumption. But, assuming there's no chance of a new user id ever supplanting the location of an existing user id, then 2 queries would be much faster and much less of a performance hit.
Of course, without knowing the actual schema of the database, it's all assumption. But, assuming there's no chance of a new user id ever supplanting the location of an existing user id, then 2 queries would be much faster and much less of a performance hit.
The two queries option does rely on the id's being generated in a particular order, you could change the queries to order by other variables rather than id if this was the case in terms of order.
if the order is not ordered by the id or any other field then there is a problem in that you would not be able to guarantee the order will be the same each time, mysql does not necessarily save the data in the order that it was input - there are many things that can disrupt this order and is never a good idea onlyl to rely on it returning everything in the same order each and every time.
if the order is not ordered by the id or any other field then there is a problem in that you would not be able to guarantee the order will be the same each time, mysql does not necessarily save the data in the order that it was input - there are many things that can disrupt this order and is never a good idea onlyl to rely on it returning everything in the same order each and every time.
ASKER
The id's on user_id are auto_increment.
If this is the case then you should be able to rely on the ordering with the two queries (provided you do not delete rows) if you delete rows there is the possibility that newer records will take an id that was previously deleted and your order will be out.
ASKER
The order of the user_id's is not super important as this is just the order in which they joined. As long as the pressing 'next' continuously will cycle through the entire list of user_id's and not jump/skip/miss anybody then things are great.
ASKER
do i need to make a while loop if i know there is only 1 number in the query results?
you do not need a while loop with these queries,
They will always return a row - as I have an ifnull statement.
You just need to run the query and pull the results from one into the previous page and one into the next page.
They will always return a row - as I have an ifnull statement.
You just need to run the query and pull the results from one into the previous page and one into the next page.
ASKER
Thanks very much for your help.
michael
michael
No Worries - glad to help
i ment
previous userid