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
INGRES_RESULT_SEEK(3)							 1						     INGRES_RESULT_SEEK(3)

ingres_result_seek - Set the row position before fetching data

SYNOPSIS
bool ingres_result_seek (resource $result, int $position) DESCRIPTION
This function is used to position the cursor associated with the result resource before issuing a fetch. If ingres.array_index_start is set to 0 then the first row is 0 else it is 1. ingres_result_seek(3) can be used only with queries that make use of scrollable cursors. It cannot be used with ingres_unbuffered_query(3). Note Related Configurations See also the ingres.scrollable and ingres.array_index_start directives in Runtime Configuration. PARAMETERS
o $result - The result identifier for a query o $position - The row to position the cursor on. If ingres.array_index_start is set to 0, then the first row is 0, else it is 1 RETURN VALUES
Returns TRUE on success or FALSE on failure. EXAMPLES
Example #1 Position the cursor on the 3rd row <?php $result=ingres_query($link, "select * from airport where ap_ccode = 'ES' order by ap_place asc"); /* goto row 3 */ if (!ingres_result_seek($result, 3)) { echo ingres_errno() . " - " . ingres_error . " "; die("i died"); } else { $airport = ingres_fetch_object ($result); { echo $airport->ap_iatacode . " - " . $airport->ap_name . " "; } } ingres_commit($link); ?> SEE ALSO
ingres_query(3), ingres_fetch_array(3), ingres_fetch_assoc(3), ingres_fetch_object(3), ingres_fetch_row(3). PHP Documentation Group INGRES_RESULT_SEEK(3)
All times are GMT -4. The time now is 08:48 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy