Select percentage of rows from SQL table?

Question

I've got a site with a PHP script, this script has an SQL query inside returning data that is accessed by a JavaScript file. The data is a huge list of flight data, and I need to be able to select (let's say) a random 40% of the total flights for any given day specified. For arguments sake lets put it like this:

$query = "SELECT * FROM `Flight_Data` WHERE DepDateTimeUTC LIKE '%1/1/14%' ";

I understand that to get a random number of rows you simply use ORDER BY RAND() LIMIT 40' and ideally I want to say LIMIT 40% but that doesn't work.

EDIT:

$query = "SELECT * FROM `Flight_Data` WHERE DepDateTimeUTC LIKE '%1/1/14%' ";
$row = mysqli_fetch_row($result);
$total = $row[0];
$percent = $total * 0.40;
$query = "SELECT * FROM `Flight_Data` WHERE DepDateTimeUTC LIKE '%1/1/14%' LIMIT . $percent ";

Answer 1

You can COUNT all records and then calculate the % you need like this:

$query = "SELECT COUNT(*) FROM `Flight_Data` WHERE DepDateTimeUTC LIKE '%1/1/14%' ";
$result = mysqli_query($connection,$query);
$row = mysqli_fetch_row($result));

$total = $row[0];
$percent = intval($total * 0.40);

$query = "SELECT * FROM `Flight_Data` WHERE DepDateTimeUTC LIKE '%1/1/14%' LIMIT ". $percent;
//execute your query....

Answer 2

Since you are using a php script you should be able to achieve what you want. What you can do is, get the total no of rows in the table, which goes like:

SELECT count(*) AS Total FROM Flight_Data

With php you can calculate the 40% of that total.

Lets say $myPercent contains the calculated 40% of total. You can use the value of $myPercent in the limit as

$query = "SELECT * FROM `Flight_Data`  
WHERE DepDateTimeUTC LIKE '%1/1/14%'  
LIMIT ".$myPercent;

Here, we escape the php variable from the mysql query string which is a good practice.

In mysql, % is used as wildcard, so it cannot be used like you thought you could in limit.

Hope this has helped you.