Archive for the 'SQL' Category
Sub archives: RegEx
I'm surprised I have not come across this earlier.
Often when writing scripts for paging between sets of results or lists of products I have processed two queries. The first runs the required query with no limit clause and COUNT(*) and the second with a set of column names and a LIMIT clause.
NO MORE!
SQL_CALC_FOUND_ROWS (released in MySQL 4.0.0 onwards) tells MySQL to calculate how many rows would be returned if the limit clause was missing. If you want to get the result you can do Select FOUND_ROWS().
Check out the following code example if you dont believe me!
<?php
//Running the query here. Lets assume the products table has 100+ entries
$result = mysql_query(“SELECT SQL_CALC_FOUND_ROWS * FROM `products` LIMIT 20”);
//Lets get the number of results actually returned (20)
$iRows = mysql_num_rows($result);
$totalResult = mysql_query(“Select FOUND_ROWS()”);
$totalRow = mysql_fetch_assoc($totalResult);
//The following will return the number of rows that our first query returned if the LIMIT clause has been missing
$iTotalRows = $totalRow[“FOUND_ROWS()”];
?>
//Running the query here. Lets assume the products table has 100+ entries
$result = mysql_query(“SELECT SQL_CALC_FOUND_ROWS * FROM `products` LIMIT 20”);
//Lets get the number of results actually returned (20)
$iRows = mysql_num_rows($result);
$totalResult = mysql_query(“Select FOUND_ROWS()”);
$totalRow = mysql_fetch_assoc($totalResult);
//The following will return the number of rows that our first query returned if the LIMIT clause has been missing
$iTotalRows = $totalRow[“FOUND_ROWS()”];
?>
This was actually introduced to MySQL to help with paging and solve the two query problem.
I know some people still prefer using two queries, but if you didn't know this existed. Then use it as you wish...
Posted by epic at 22:24pm Permalink
This is actually a neat little peice of MYSQL code. It enables you to select only rows that start with the given regular expression. In this case, any names that start with the letter 'M' (not case sensitive).
mysql> select * from People;
+----------+-------+------+------------+
| Name | Color | Sex | DOB |
+----------+-------+------+------------+
| Boris | Blue | m | 1980-03-22 |
| Barry | Red | m | 1919-04-09 |
| Belinda | Green | f | 1988-01-04 |
| Paul | Red | m | 1988-02-18 |
| Mike | Green | m | 1979-05-25 |
| Morris | White | f | 1971-01-12 |
+----------+-------+------+------------+
6 rows in set (0.01 sec)
mysql> /*Select names beginning with 'm'. Uses '^' to match the beginning of the name*/
mysql> SELECT * FROM People WHERE name REGEXP '^M';
+----------+-------+------+------------+
| Name | Color | Sex | DOB |
+----------+-------+------+------------+
| Mike | Green | m | 1979-05-25 |
| Morris | White | f | 1971-01-12 |
+----------+-------+------+------------+
2 row in set (0.02 sec)
See, simple.
+----------+-------+------+------------+
| Name | Color | Sex | DOB |
+----------+-------+------+------------+
| Boris | Blue | m | 1980-03-22 |
| Barry | Red | m | 1919-04-09 |
| Belinda | Green | f | 1988-01-04 |
| Paul | Red | m | 1988-02-18 |
| Mike | Green | m | 1979-05-25 |
| Morris | White | f | 1971-01-12 |
+----------+-------+------+------------+
6 rows in set (0.01 sec)
mysql> /*Select names beginning with 'm'. Uses '^' to match the beginning of the name*/
mysql> SELECT * FROM People WHERE name REGEXP '^M';
+----------+-------+------+------------+
| Name | Color | Sex | DOB |
+----------+-------+------+------------+
| Mike | Green | m | 1979-05-25 |
| Morris | White | f | 1971-01-12 |
+----------+-------+------+------------+
2 row in set (0.02 sec)
Posted by OLLIE at 21:45pm Permalink
Ok, this is something thats been bugging me for a while.
In the past ive built for loops and generated random numbers and when found the location that corrosponds to that number returned the row... that is actually stupidly wasteful.
Especially when if i hadnt been so lazy and just searched the web for an answer i could have made things easier.
Anyways, enough babbling, here is a simple MySQL implementation that will return a random row.
<?php
$query = "SELECT * FROM tablename ORDER BY RAND()";
?>
YES! Thats it, its that easy. Just use the RAND() function and your laughing...
$query = "SELECT * FROM tablename ORDER BY RAND()";
?>
Posted by OLLIE at 21:55pm Permalink
