Php number array from max, min, step size mysql data


 
Thread Tools Search this Thread
Top Forums Programming Php number array from max, min, step size mysql data
# 1  
Old 11-18-2014
Php number array from max, min, step size mysql data

I want to create a form with data values in a dropdown list. The values in the dropdown list need to be generated on the fly from max, min and increment values contained in a mysql database.

Hopefully this makes sense, I really have no idea where to start Smilie
Thanks
# 2  
Old 11-18-2014
Something like:
Code:
<select>
<?php
for ( $i=$min; $i <= $max; $i+=$step) {
  echo "<option value='$i'>$i</option>";
}
?>
</select>

# 3  
Old 11-18-2014
I was sort of hoping to include it as part of the sql query?
# 4  
Old 11-18-2014
Can you explain a little more about how to get the increment value? How is it stored in the MySQL table?
For the minimum and maximum value you would normally perform something like this:
Code:
SELECT MIN(x), MAX(x) FROM mytable GROUP BY x

and wrap that around PHP code, which is not provided here, because it is the next step.
# 5  
Old 11-18-2014
So the Min, Max and increment are all stored in the mysql table as integers, what I need to do is to great the array of values that will populate for the dropdown list. ie if Max=6, Min=0, increment=2 then I need the query to produce 0, 2, 4, 6
# 6  
Old 11-18-2014
There are countless examples of that on the internet, for example here:
PHP Select Data From MySQL
From the first example on that page you would change this part:
PHP Code:
while($row $result->fetch_assoc()) {
        echo 
"id: " $row["id"]. " - Name: " $row["firstname"]. " " $row["lastname"]. "<br>";
    } 
into something that looks more like what CarloM provided.
# 7  
Old 11-19-2014
Quote:
Originally Posted by barrydocks
...what I need to do is to great the array of values that will populate for the dropdown list. ie if Max=6, Min=0, increment=2 then I need the query to produce 0, 2, 4, 6
Just a quick note here - MySQL does not have features that would make creating such a query easy. It does not support recursive "with" subqueries (also called CTEs - Common Table Expressions). It does not support hierarchical queries. It does not provide pipelined functions or functions to generate a series of numbers.
Given all these limitations, your best bet for a database query would be to pre-populate a table of integers and use it to return the number series you want. And then again, there is the problem of how many rows the table should contain, if its load is static.

The hassle-free alternative is to fetch those three values (min, max, increment) in PHP and generate the numbers in there. I do not know PHP, but by looking at the "for" loops the others have posted here, it doesn't look that complicated.

---------- Post updated at 11:23 PM ---------- Previous update was at 10:52 PM ----------

Nevertheless, the approach mentioned above for the database query is posted here. Let's say you **know** beforehand that the min value will never be below 0 and the max value will never be above 10. Then you can populate an "iterator" table (called "numbers" in the example below) with values from 0 to 10.
The rest should be easy to comprehend:

Code:
mysql>
mysql> create table dropdown (max int, min int, incr int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into dropdown (max, min, incr) values (6, 0, 2);
Query OK, 1 row affected (0.01 sec)

mysql>
mysql> create table numbers (value int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into numbers (value)
    -> select  0 union all
    -> select  1 union all
    -> select  2 union all
    -> select  3 union all
    -> select  4 union all
    -> select  5 union all
    -> select  6 union all
    -> select  7 union all
    -> select  8 union all
    -> select  9 union all
    -> select 10;
Query OK, 11 rows affected (0.00 sec)
Records: 11  Duplicates: 0  Warnings: 0

mysql>
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>
mysql> select * from dropdown;
+------+------+------+
| max  | min  | incr |
+------+------+------+
|    6 |    0 |    2 |
+------+------+------+
1 row in set (0.00 sec)

mysql>
mysql> select * from numbers;
+-------+
| value |
+-------+
|     0 |
|     1 |
|     2 |
|     3 |
|     4 |
|     5 |
|     6 |
|     7 |
|     8 |
|     9 |
|    10 |
+-------+
11 rows in set (0.00 sec)

mysql>
mysql> -- Query to generate the series (0,2,4,6) given the values
mysql> -- min=0, max=6, incr=2
mysql>
mysql> select n.value
    ->   from numbers n, dropdown d
    ->  where n.value between d.min and d.max
    ->    and mod(n.value - d.min, d.incr) = 0;
+-------+
| value |
+-------+
|     0 |
|     2 |
|     4 |
|     6 |
+-------+
4 rows in set (0.00 sec)

mysql>
mysql>

The limitation of this method should be easy to see. If you update the max value in the dropdown table to 1000, then the final query will return incorrect result unless you load numbers at least till 1000 in the iterator table.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Print root number between min and max ranges

Hi to all, Please help on the following problem, I'm not where to begin, if awk or shell script. I have pairs of ranges of numbers and I need to find the root or roots of ranges based on min Range and Max ranges Example #1: If min range is 120000 and max ranges 124999, it means that are... (5 Replies)
Discussion started by: Ophiuchus
5 Replies

2. Shell Programming and Scripting

Get min and max value in column

Gents, I have a big file file like this. 5100010002 5100010004 5100010006 5100010008 5100010010 5100010012 5102010002 5102010004 5102010006 5102010008 5102010010 5102010012 The file is sorted and I would like to find the min and max value, taking in the consideration key1... (3 Replies)
Discussion started by: jiam912
3 Replies

3. UNIX for Dummies Questions & Answers

Integrate MIN and MAX in a string

I need to use awk for this task ! input (fields are separated by ";"): 1%2%3%4%;AA 5%6%7%8%9;AA 1%2%3%4%5%6;BB 7%8%9%10%11%12;BBIn the 1st field there are patterns composed of numbers separated by "%". The 2nd field define groups (here two different groups called "AA" and "BB"). Records... (8 Replies)
Discussion started by: beca123456
8 Replies

4. Shell Programming and Scripting

Number of elements, average value, min & max from a list of numbers using awk

Hi all, I have a list of numbers. I need an awk command to find out the numbers of elements (number of numbers, sort to speak), the average value the min and max value. Reading the list only once, with awk. Any ideas? Thanks! (5 Replies)
Discussion started by: black_fender
5 Replies

5. Shell Programming and Scripting

to find min and max value for each column!

Hello Experts, I have got a txt files which has multiple columns, I want to get the max, min and diff (max-min) for each column in the same txt file. Example: cat file.txt a 1 4 b 2 5 c 3 6 I want ouput like: cat file.txt a 1 4 b 2 5 c 3 6 Max 3 6 Min 1 4 Diff 2 2 awk 'min=="" ||... (4 Replies)
Discussion started by: dixits
4 Replies

6. Shell Programming and Scripting

Data stream between min and max

Hi, I have a text file containing numbers. There are up to 6 numbers per row and I need to read them, check if they are 0 and if they are not zero check if they are within a given interval (min,max). If they exceed the max or min they should be set to max or min respectively, if they are in the... (4 Replies)
Discussion started by: f_o_555
4 Replies

7. Shell Programming and Scripting

get min, max and average value

hi! i have a file like the attachement. I'd like to get for each line the min, max and average values. (there is 255 values for each line) how can i get that ? i try this, is it right? BEGIN {FS = ","; OFS = ";";max=0;min=0;moy=0;total=0;freq=890} $0 !~ /Trace1:/ { ... (1 Reply)
Discussion started by: riderman
1 Replies

8. UNIX for Dummies Questions & Answers

Iterate a min/max awk script over time-series temperature data

I'm trying to iterate a UNIX awk script that returns min/max temperature data for each day from a monthly weather data file (01_weath.dat). The temperature data is held in $5. The temps are reported each minute so each day contains 1440 temperature enteries. The below code has gotten me as far as... (5 Replies)
Discussion started by: jgourley
5 Replies

9. Shell Programming and Scripting

min and max value of process id

We are running a AIX 5.2 OS. Would anyone happen to know what the max value for a process id could be? Thanks jerardfjay :) (0 Replies)
Discussion started by: jerardfjay
0 Replies

10. UNIX for Advanced & Expert Users

MAX SIZE ARRAY Can Hold it

Hi, Do anyone know what's the max size of array (in awk) can be store before hit any memory issue. Regards (3 Replies)
Discussion started by: epall
3 Replies
Login or Register to Ask a Question