Range in php mysql

Querying a MySQL range using LIKE

I have a ref field in my mysql table that holds values that look like ‘0-0-at-3267-201411041356’. The first part (0-0-at-3267-) varies in length and values and the second part (201411041356) is a date/time that references a creation date/time. Everything that this code is used for is checked to see if it falls within a certain date/time period, such as between 201409010000 and 201508312359. Normally I can simply explode the data and then measure but for this instance it would make it too clunky. So what I want to do is use the LIKE function in my query like so LIKE ‘%201411041356’ but I want to use it with the > and < symbols, so the full query looking something like SELECT * FROM my_table WHERE ref LIKE >‘%201409010000’ AND ref LIKE < '%201508312359' Any ideas would be most welcome! BTW, this has always been the way this data has been stored and there's lots of it so changing it is not an option.

4 Answers 4

Could you use between on a substring, so something like:

SELECT * FROM my_table WHERE SUBSTRING(ref,-12) BETWEEN '201409010000' AND '201508312359' 

SELECT * FROM my_table WHERE SUBSTRING(ref,-12) BETWEEN '201409010000' AND '201508312359'` 

would work better for substracting last 12 characters.

Because You wrote that first part may have different lenght so You have strip substring starting from the end.

So Your quwery may looks like:

SELECT * FROM table WHERE SUBSTRING('ref', -12) > $from AND SUBSTRING('ref', -12) < $to 

If you just want to measure by date use:

select * from my_table where left(right(ref, 12), 8) BETWEEN '20141104' AND '20141108' sqlfiddle.com/#!2/8b8154

This question is in a collective: a subcommunity defined by tags with relevant content and experts.

Источник

MySQL: How to select range in mysql?

Solution 1: To select a specific range, you have to use so in your case, you'd select the first 1000 rows like: The second one would be: and the third would be The first number in the limit is the row that it starts at, and the second number is the number of rows after and including that row that will be selected Solution 2: Solution 3: For example: Solution 1: You can separate the range Table Query Create table Solution 2: The second value (the max-points) might be superfluous if a) the ranges are continuous and b) you will select exactly 1 (or 0) outcome id Solution 3: You not need to change your table structure.

Select a range of rows from a table

To select a specific range, you have to use limit [start number], [# to select] so in your case, you'd select the first 1000 rows like:

select [your select stuff] limit 0, 1000 

select [your select stuff] limit 1000, 1000 

select [your select stuff] limit 2000, 1000 

The first number in the limit is the row that it starts at, and the second number is the number of rows after and including that row that will be selected

You can use limit in the query to retrieve a specific number of rows . something like $start=0; $end=1000; $sql = 'SELECT a1.post_id, a1.kw_id, a2.kw_name ' + 'FROM ' . $q_pages . ' as a1 ' + 'LEFT JOIN ' . $q_kws . ' as a2 ON (a1.kw_id = a2.id)' + 'WHERE a1.draft_id = 4 limit ".$start.",".$end."'; 

select * from table where rownum < 1000 

Php - Select a range of rows from a table, Sorted by: 1. To select a specific range, you have to use limit [start number], [# to select] so in your case, you'd select the first 1000 rows like: select [your select stuff] limit 0, 1000. The second one would be: select [your select stuff] limit 1000, 1000. and the third would be. select [your select stuff] limit 2000, 1000.

MySQL: How to select range in mysql?

You can separate the range

outcome_id | min_outcome | max_outcome 1 | 2 | 5 2 | 6 | 10 3 | 11 | 15 

SELECT * FROM table_name WHERE min_outcome = 3 

CREATE TABLE IF NOT EXISTS `table_name` ( `outcome_id` int(11) NOT NULL AUTO_INCREMENT, `min_outcome` int(10) NOT NULL, `max_outcome` int(10) NOT NULL, PRIMARY KEY (`outcome_id`) ) ; INSERT INTO `table_name` (`outcome_id`, `min_outcome`, `max_outcome`) VALUES (1, 2, 5), (2, 6, 10), (3, 11, 15); 

The second value (the max-points) might be superfluous if
a) the ranges are continuous and
b) you will select exactly 1 (or 0) outcome id

CREATE TABLE outcomes ( outcome_id int, outcome_min int, unique index(outcome_min) ) INSERT INTO outcomes (outcome_id,outcome_min) VALUES (1,2), (2,6), (3,11) SELECT outcome_id FROM outcomes WHERE outcome_min>=3 ORDER BY outcome_min ASC LIMIT 1 

You not need to change your table structure.

just use SUBSTRING_INDEX mysql function to find the values as min and max value.

SELECT `outcome_id`, `outcome_range` FROM `test` HAVING SUBSTRING_INDEX(`outcome_range`, ',', 1) = 3 

This Pretty thing also done by using the WHERE clause. The same result come out.

SELECT `outcome_id`, `outcome_range` FROM `test` WHERE SUBSTRING_INDEX(`outcome_range`, ',', 1) = 3 

Here i use 3 as static value. you can use the dynamic one. This is a user input.

outcome_id outcome_range 1 2,5 

Parsing JSON array with PHP foreach, Sending data using ajax json and phrasing in php. 0. PHP Multiple foreach loops in one? 0. How to create a blob in MongoDB using laravel. 0. Parsing JSON object foreach. 0. Get result out of nested Json with php. 0. How to parse JSON from a URL with PHP. Related. 9037. Can comments be used in JSON? …

Validate a numeric value range from html form textbox. PHP or Javascript

It is important to mention that your $volume, $gain, $treble, $middle and $bass will never actually be null as you have assigned a string to them in addition to the $_POST value. In addition you should always check if the $_POST values exist before trying to use them (or you will get an undefined notice message).

Here is an example for a PHP version based on the code you had (untested, but should work fine).

 $high || $value < $low ) < // return null (not a valid value) return null; >// otherwise the value is valid so return it return $value; > // make sure the $name var is safe to use $name = ( isset($_POST['ampMod']) ) ? htmlentities($_POST['ampMod'],ENT_QUOTES,'UTF-8') : null; $volume = ( isset($_POST['volume']) ) ? isValidRange($_POST['volume']) : null; $gain = ( isset($_POST['gain']) ) ? isValidRange($_POST['gain']) : null; $treble = ( isset($_POST['treble']) ) ? isValidRange($_POST['treble']) : null; $middle = ( isset($_POST['middle']) ) ? isValidRange($_POST['middle']) : null; $bass = ( isset($_POST['bass']) ) ? isValidRange($_POST['bass']) : null; if( isset($volume) && isset($gain) && isset($treble) && isset($middle) && isset($bass) ) < echo "
$name
 "; echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; echo "
Volume = $volume
Gain = $gain
Treble = $treble
Middle = $middle
Bass = $bass
"; > else < echo ("Please try again. Values must be between 0-65535. 0=Off 65535=Full On 10
Click here to try again!");> ?> 

Lastly I would not recommend just relying on JavaScript to actually check if your values are safe to use (i.e. echo them out), but using js as a pre-warning to users and then properly validating with PHP is the best way to go.

Just do something like this? Don't know why you would want to go between 0 and 65535. I doubt you want them to go that high. If you do just change 10 to 65535

This makes sure the value is between 10 and 0

In situations like this, I often prefer to silently clean the form input. You've got client-side validation in place already. If the value is higher than allowed, just set the value to the maximum allowed instead of showing an error message.

// Clean the posted data and prevent notices if not set $volume = (isset($_POST['volume'])) ? (int) $_POST['volume'] : 0; // Make sure the value is within a certain range $min = 0; $max = 10; $volume = min($max, max($min, $volume)); 

Источник

Get value range from MYSQL using PHP

I'm trying to get all the ages (let's say 18-21) after searching ages 18-21 from the form, but the form is displaying all the data from the database. The username search works though. Any help appreciated. Here's the form:

    - 18-20 20-23 20 21 22   - Female Male  '; ?> 

= $min_length) < // if query length is more or equal minimum length then $username = htmlspecialchars($username); // changes characters used in html to their equivalents, for example: < to > $username = mysql_real_escape_string($username); // makes sure nobody uses SQL injection // Username query does not need a like, you know what the username will be precisely $raw_results = mysql_query("SELECT * FROM user WHERE (`username` = '".$username."')"); // You need to filter out an age range $ages = ""; switch($age) < case 0: case 20: case 21: case 22: $ages = $age; break; case "18-20": $ages = "18,19,20"; break; case "20-23": $ages = "20,21,22,23"; break; >// combine with ages to get all relevant results //$raw_results = mysql_query("SELECT * FROM user WHERE (`age` in (" . $ages . ") AND gender = '" . $gender . "')"); if(mysql_num_rows($raw_results) > 0) < // if one or more rows are returned do following while($results = mysql_fetch_array($raw_results))< // $results = mysql_fetch_array($raw_results) puts data from database into array, while it's valid it does the loop echo ""; < echo ""; echo ""; echo ""; echo ""; echo ""; > echo "
 
username
 
gender
 
age
 
" . $results['username'] . "
" . $results['gender'] . "
" . $results['age'] . "
"; > > else < // if there is no matching rows do following echo "No results"; >> else < // if query length is less than minimum echo "Minimum length is ".$min_length; >?> 

CREATE TABLE IF NOT EXISTS `user` ( `user_id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(64) NOT NULL, `email` varchar(64) NOT NULL, `password` varchar(32) NOT NULL, `age` int(11) NOT NULL, `gender` varchar(7) NOT NULL, `image` blob NOT NULL, PRIMARY KEY (`user_id`), UNIQUE KEY `email` (`email`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=24 ; 

Источник

PHP mysql select date range

I have a invoice type form submission and every submission has a date so I have created a varcha date field in mysql database to save the time.Time format is like '2011-06-26'.Now I need to select a range based on the date from my database.I tried following options but they don't display any result just blank page is displayed,even no errors. Should I follow some special techniques on saving date to my database.If so please give me some explain about that because I am new to PHP and MYSQL development.

SELECT * FROM table_name WHERE 'date' BETWEEN UNIX_TIMESTAMP('2011-06-02') AND UNIX_TIMESTAMP('2011-06-25') SELECT * FROM my_table WHERE 'date' BETWEEN CAST('2011-06-02' AS DATE) AND CAST('2011-06-25' AS DATE)"; 

$result=mysql_query($query); while($row=mysql_fetch_array($result))< echo $row['tax']."
"; > 

3 Answers 3

If you use DATETIME type to store your date the trick is simple. DATETIME allows you to store the date and the timestamp, however if only the date is specifed, timestamp will be stored as 00:00:00

I'll show you a simple example.

mysql> CREATE TABLE test_table( id INT UNSIGNED AUTO_INCREMENT, test_date DATETIME, PRIMARY KEY (id)); mysql> INSERT INTO test_table(test_date) VALUES('2011-06-26'), ('2011-05-14'), ('2011-05-02'); mysql> SELECT * FROM test_table; +----+---------------------+ | id | test_date | +----+---------------------+ | 1 | 2011-06-26 00:00:00 | +----+---------------------+ | 2 | 2011-05-14 00:00:00 | +----+---------------------+ | 3 | 2011-05-02 00:00:00 | +----+---------------------+ mysql> SELECT * FROM test_table WHERE test_date BETWEEN '2011-05-01' AND '2011-05-31'; +----+---------------------+ | id | test_date | +----+---------------------+ | 2 | 2011-05-14 00:00:00 | +----+---------------------+ | 3 | 2011-05-02 00:00:00 | +----+---------------------+ 

Источник

Filter Range Of Date With MySQL using PHP

In this tutorial, we will create a Filter Range Of Date With MySQLi using PHP. This code can filter a range of table row from MySQLi when the user provides two dates from inputs. The code use MySQLi SELECT query to filter a variety of data in the MySQL row by providing two dates in the WHERE clause by adding a parameter BETWEEN . This a user-friendly program feel free to modify and use it to your system.

We will be using PHP as a scripting language and interpreter that is mainly used on any web server, including xamp, wamp, etc. It is being applied to any popular websites because of the modern approach as its today.

Getting Started:

First, you have to download & install XAMPP or any local server that run PHP scripts. Here’s the link for XAMPP server https://www.apachefriends.org/index.html .

And, this is the link for the bootstrap that I used for the layout design https://getbootstrap.com/ .

Creating Database

Open your database web server then create a database name in it db_range , after that click Import then locates the database file inside the folder of the application then click ok.

Creating the database connection

Open your any kind of text editor(notepad++, etc..). Then just copy/paste the code below then name it conn.php.

Creating The Interface

This is where we will create a simple form for our application. To create the forms simply copy and write it into your text editor, then save it as index.php.

        
PHP - Filter Range Of Date With MySQLi
 
 Date: To  
 
 
   Firstname Lastname Project Date Submit   
 
  

Creating the Main Function

This code contains the main function of the application. This code will filter a range of data when the button is clicked. To do that just copy and write this block of codes inside the text editor, then save it as range.php.

0) < while($fetch=mysqli_fetch_array($query))< ?>     >else < echo' Record Not Found '; > >else < $query=mysqli_query($conn, "SELECT * FROM `member`") or die(mysqli_error()); while($fetch=mysqli_fetch_array($query))< ?>     > ?>

There you have it we successfully created Filter Range Of Date With MySQLi using PHP. I hope that this simple tutorial help you to what you are looking for. For more updates and tutorials just kindly visit this site. Enjoy Coding!

Источник