Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Php number array from max, min, step size mysql data
Top Forums Programming Php number array from max, min, step size mysql data Post 302925709 by durden_tyler on Tuesday 18th of November 2014 11:23:43 PM
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.
 

10 More Discussions You Might Find Interesting

1. 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

2. 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

3. 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

4. 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

5. 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

6. 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

7. 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

8. 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

9. 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

10. 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

LEARN ABOUT SUSE

mysqldumpslow

MYSQLDUMPSLOW(1)					       MySQL Database System						  MYSQLDUMPSLOW(1)

NAME

       mysqldumpslow - Summarize slow query log files

SYNOPSIS

       mysqldumpslow [options] [log_file ...]

DESCRIPTION

       The MySQL slow query log contains information about queries that take a long time to execute (see Section 5.2.5, "The Slow Query Log").
       mysqldumpslow parses MySQL slow query log files and prints a summary of their contents.

       Normally, mysqldumpslow groups queries that are similar except for the particular values of number and string data values. It "abstracts"
       these values to N and 'S' when displaying summary output. The -a and -n options can be used to modify value abstracting behavior.

       Invoke mysqldumpslow like this:

	   shell> mysqldumpslow [options] [log_file ...]

       mysqldumpslow supports the following options.

       o   --help

	   Display a help message and exit.

       o   -a

	   Do not abstract all numbers to N and strings to 'S'.

       o   --debug, -d

	   Run in debug mode.

       o   -g pattern

	   Consider only queries that match the (grep-style) pattern.

       o   -h host_name

	   Host name of MySQL server for *-slow.log file name. The value can contain a wildcare. The default is * (match all).

       o   -i name

	   Name of server instance (if using mysql.server startup script).

       o   -l

	   Do not subtract lock time from total time.

       o   -n N

	   Abstract numbers with at least N digits within names.

       o   -r

	   Reverse the sort order.

       o   -s sort_type

	   How to sort the output. The value of sort_type should be chosen from the following list:

	   o   t, at: Sort by query time or average query time

	   o   l, al: Sort by lock time or average lock time

	   o   s, as: Sort by rows sent or average rows sent

	   o   c: Sort by count

       o   -t N

	   Display only the first N queries in the output.

       o   --verbose, -v

	   Verbose mode. Print more information about what the program does.

       Example of usage:

	   shell> mysqldumpslow
	   Reading mysql slow query log from /usr/local/mysql/data/mysqld51-apple-slow.log
	   Count: 1  Time=4.32s (4s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
	    insert into t2 select * from t1
	   Count: 3  Time=2.53s (7s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
	    insert into t2 select * from t1 limit N
	   Count: 3  Time=2.13s (6s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
	    insert into t1 select * from t1

COPYRIGHT

       Copyright 2007-2008 MySQL AB, 2008-2010 Sun Microsystems, Inc.

       This documentation is free software; you can redistribute it and/or modify it only under the terms of the GNU General Public License as
       published by the Free Software Foundation; version 2 of the License.

       This documentation is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of
       MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

       You should have received a copy of the GNU General Public License along with the program; if not, write to the Free Software Foundation,
       Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA or see http://www.gnu.org/licenses/.

SEE ALSO

       For more information, please refer to the MySQL Reference Manual, which may already be installed locally and which is also available online
       at http://dev.mysql.com/doc/.

AUTHOR

       Sun Microsystems, Inc. (http://www.mysql.com/).

MySQL 5.1							    04/06/2010							  MYSQLDUMPSLOW(1)
All times are GMT -4. The time now is 01:12 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy