Search code examples
phpmysqlsqllimit

Select percentage of rows from SQL table?

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 ";
Solution

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