Sponsored Content
Top Forums Web Development MySQL: Random offers for every airport Post 302312668 by worchyld on Saturday 2nd of May 2009 02:47:38 PM
Old 05-02-2009
MySQL: Random offers for every airport

I need to write a MySQL query where it loops through every airport and generates 10 random offers (my offers table is 990,000+ rows)

The code I have below works for 1 airport, but as soon as it gets bigger it slows RIGHT down.

This is using PHP/MySQL

At the moment it takes well over a minute to run which is way too long -- I need it much, much quicker.

Can anyone help?

Code:
/**
 * This code takes over a minute to run.  How do I make it quicker?
 */
// Get airports
$sql0    = 'SELECT airportcode FROM airports ORDER BY airportcode ASC';
$query    = $this->db->query($sql0);

// Loop through airports
foreach ($query->result() as $row)
{
    // Get a random offer so long as the airport matches
    $sql1 = 'SELECT T.id, T.DepAirportCode
            FROM offers T
            JOIN (
                SELECT FLOOR( MAX( id ) * RAND( ) ) AS id
                FROM offers
                ) AS x ON T.id >= x.id
            WHERE 
                T.DepAirportCode="'.$row->airportcode.'" 
            LIMIT 
                10';
    
    // Loop through random offers
    $query1    = $this->db->query($sql1);
    foreach ($query1->result() as $row)
    {
        print $row->id;
        print $row->DepAirportCode;
    } // next
} // next

 

6 More Discussions You Might Find Interesting

1. Solaris

Connecting Solaris to Airport network

Hi, I've been trying to find out for the past two days how to set my Solaris 10 to join wireless network (Airport base station with ACL and wep or wpa on). Using ethernet is a bit out of question as I don't have an ethernet cable that would reach the other side of the house. Solaris 10... (1 Reply)
Discussion started by: giax
1 Replies

2. Programming

Airport using semaphores and shared memo

Hi just doin' this here for the naval school, back here in Pportugal, and needed some help, especially with the shared memo i want to use for the 10 airport gate, and the maximum of 4 planes preparing to leave; canīt figure out how the gate can be id by the same PID. WELL, if someone wants to... (1 Reply)
Discussion started by: Turbo
1 Replies

3. Homework & Coursework Questions

Airport using semaphores and shared memo

Hi just doin' this here for the naval school, back here in Pportugal, and needed some help, especially with the shared memo i want to use for the 10 airport gate, and the maximum of 4 planes preparing to leave; canīt figure out how the gate can be id by the same PID. WELL, if someone wants to... (2 Replies)
Discussion started by: Turbo
2 Replies

4. Ubuntu

expect script for random password and random commands

Hi I am new to expect. Please if any one can help on my issue its really appreciable. here is my issue: I want expect script for random passwords and random commands generation. please can anyone help me? Many Thanks in advance (0 Replies)
Discussion started by: vanid
0 Replies

5. OS X (Apple)

Airport ID instead of machine name in Terminal

Hi. When I open a Terminal window instead of getting the machine's name (i.e. Joe-Bloggs-laptop) I get `unknown-00-1x-5x-cx-ex-ex:~ bloggs$' which is the Mac's Airport ID (ID munged). I'm on a wireless LAN with one other laptop & three iPhones (Macs running 10.6.8 & iPhones all up to date). ... (9 Replies)
Discussion started by: phildobbin
9 Replies

6. Shell Programming and Scripting

Need to generate a file with random data. /dev/[u]random doesn't exist.

Need to use dd to generate a large file from a sample file of random data. This is because I don't have /dev/urandom. I create a named pipe then: dd if=mynamed.fifo do=myfile.fifo bs=1024 count=1024 but when I cat a file to the fifo that's 1024 random bytes: cat randomfile.txt >... (7 Replies)
Discussion started by: Devyn
7 Replies
MYSQL_FETCH_ROW(3)							 1							MYSQL_FETCH_ROW(3)

mysql_fetch_row - Get a result row as an enumerated array

SYNOPSIS
Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include: omysqli_fetch_row(3) o PDOStatement::fetch array mysql_fetch_row (resource $result) DESCRIPTION
Returns a numerical array that corresponds to the fetched row and moves the internal data pointer ahead. o $ result -The result resource that is being evaluated. This result comes from a call to mysql_query(3). Returns an numerical array of strings that corresponds to the fetched row, or FALSE if there are no more rows. mysql_fetch_row(3) fetches one row of data from the result associated with the specified result identifier. The row is returned as an array. Each result column is stored in an array offset, starting at offset 0. Example #1 Fetching one row with mysql_fetch_row(3) <?php $result = mysql_query("SELECT id,email FROM people WHERE id = '42'"); if (!$result) { echo 'Could not run query: ' . mysql_error(); exit; } $row = mysql_fetch_row($result); echo $row[0]; // 42 echo $row[1]; // the email value ?> Note This function sets NULL fields to the PHP NULL value. mysql_fetch_array(3), mysql_fetch_assoc(3), mysql_fetch_object(3), mysql_data_seek(3), mysql_fetch_lengths(3), mysql_result(3). PHP Documentation Group MYSQL_FETCH_ROW(3)
All times are GMT -4. The time now is 12:03 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy