Jump to content

MySQL/PHP Order and Group Numeric List


thunderousity
 Share

Recommended Posts

I would like to group a list of mysql results in groups of 10 like so via php: >= 260 266265265265264262261260 >= 250 255251etc... Hopefully the same code could be replicated to another list on my site which will contain decimals. >= 2.0 2.92.82.62.62.0 >=1.0 1.91.81.71.0 A solution something like the one here would be fantastic: http://w3schools.invisionzone.com/index.php?showtopic=25967&hl=%26%23036%3bfirst_letter&fromsearch=1

Link to comment
Share on other sites

One that will return all results. I attempted a version of the one below which is from the thread mentioned earlier - I thought it may work even though it is using strings. It worked for the hundreds as I added a zero to the header for the group and grouped it based on assessing the first 2 numbers (strlen). However, when it got to those with 2 rather than 3 numbers it added a zero to the 50 so the heading looked like 500 for example.

<?php include 'connect.php'; $q = mysql_query("SELECT name FROM I3 ORDER BY name"); $first_letter = ''; while($row = mysql_fetch_assoc($q)) { // Check if the first letter is different than in previous rows if( $first_letter != strtoupper(substr($row['name'],0,1)) ) { // Only close a list if one is already open if( strlen($first_letter) ) echo '</ul>'; // Print a new title for the letter and open a new list echo "<h4>{$first_letter}</h4>"; echo '<ul>'; $first_letter = strtoupper(substr($row['name'],0,1)); } // Print the row value and whatever else you want echo "<li>{$row['name']}</li>"; } // Only close a list if one is already open. if( strlen($first_letter) ) echo '</ul>'; ?>
Link to comment
Share on other sites

I would treat these as numbers instead of strings. You should have one variable to keep track of the "max", the upper bound for the group you're currently printing, and another to hold the increment to make the script easy to change later. The max would start at 0. The increment would be 10 if you want to group numbers in groups of 10, or it would be 1 if you want to group decimal numbers like your second example. When you're looping through the results, compare the number against the max. If it is less than the max then it's part of the current group and you just print it. If it's not less than the max then you close the previous group, add the increment to the max, and start a new group with the new max. This assumes you're going from low to high, your examples show high to low so the algorithm would be a little different in that case (you would decrement instead of increment, and start the min (instead of max) based on what the first number is).

Link to comment
Share on other sites

The above sounds like a good solution - are there any scraps of code you could provide as an example. I understand how to increment using the ++ operator but I'm not sure what function to use to test for a max. From a novices point of view there seems to be a lot going on to try and put it all together without a little more direction any help appreciated

Link to comment
Share on other sites

You don't need to use a function, you can just use a comparison operator (less than, greater than, etc) in an if statement. A lot of programming problems sound complex but you can break them down into individual pieces that are easy to solve. The first step is to define your variables, define the increment variable with the value you want (like 10), and start off the max variable with a value of 0. Then, create the loop that will loop through all of the numbers. The first thing the loop should do is check if the current number is greater than max, and if it is then you update max. The end of the loop has you printing out the number, after you've compared it against max and done whatever else. Start writing that, just take it one line at a time. If you get stuck post what you currently have.

Link to comment
Share on other sites

I've not had chance to test this until I get home, but would something along these lines work using a 'while' statement rather than an 'if' $min = 0$max = 10$increment = 10$number = $row_result while (($number >= $min) && ($number <= $max)){echo "<h1>Between ".$min." and ".$max."</h1>";echo "<p>".$row_result['Result']."</p>";$min + $increment;$max + $increment;)

Edited by thunderousity
Link to comment
Share on other sites

A while loop wouldn't work there because it's going to quit the loop as soon as the condition is false. You don't want to stop at that point, you just want to change the max and continue with the next number. The while loop should be looping over all of the numbers from the database, inside the while loop is an if statement that only needs to check if the number is less than max. If the number is not less than max then you print a new header. e.g.:

$min = 0;$increment = 10;$max = $min + $increment; while ($row = mysql_fetch_assoc($result)){  if ($row['number'] > $max)  {    while ($max < $row['number']) $max += $increment;    $min = $max - $increment;    echo "<h1>Between ".$min." and ".$max."</h1>";  }  echo $row['number'] . '<br>';}

Link to comment
Share on other sites

This works a treat - I've managed to tweak it slightly so it works for my descending mysql list by (amongst other things) changing the $min from 0 to 290 as I know that the highest value will be less than 280. I've also managed to incorporate my css alternating row color for the results. However, the results always miss the very first row from the resultset. The highest value was 273 but this was omitted, I added the value of 275 to the database table and the 273 now appears but not 275!?

<?php$min = 290;$increment = 10;$max = $min - $increment;$rowclass = 0; while ($row_result = mysql_fetch_assoc($result)){  if ($row_result['number'] < $max)  {    while ($max > $row_result['number']) $max -= $increment;    $min = $max + $increment;echo "<br>";    echo "<h3 class='listrow'>Between ".$min." and ".$max."</h3>";}  echo "<div class= row" . $rowclass . "><div class='alignleft'><a title=" . $row_result['number'] . "text href=page.php?ID=" . $row_result['ID'] . ">" .$row_result['number'] . "</a></div><div class='alignright'>more text - <b>" . $row_result['number'] . "</b></div><div style='clear: both;'></div></div>";$rowclass = 1 - $rowclass;}?>

Link to comment
Share on other sites

It sounds like you have another call to mysql_fetch_assoc at some point between when you run the query and when you run the loop. Using that function advances the result pointer, so the next time you use it it will return the next record.

Link to comment
Share on other sites

It sounds like you have another call to mysql_fetch_assoc at some point between when you run the query and when you run the loop. Using that function advances the result pointer, so the next time you use it it will return the next record.
Yes you're correct - deleted the extra mysql_fetch_assoc and it works brilliantly - fantastic - thanks for the help :-)
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...