Lookup a column value in recordset based on the value of another column

Question

I have a MySQL SELECT query (using wpdb_custom->get_results()) that returns two UNSIGNED TINYINT columns Col_1 and Col_2 for an arbitrary number of rows. What is the least costly way in PHP to lookup the value of Col_2 from the recordset given a value of Col_1?

Emp_ID  Col_1    Col_2
120         3       11
120         5       17
120         6        8
120         8       13
120        11       10

So for example, the pseudo-code lookup function lookup(6, $recordset) would yield 8.

could you print a portion of your recordset ? So we can more easily see how your returned array/object looks like — Paolo_Mulder, Apr 12 '12 at 12:39

score 0 · Answer 1 · answered Apr 12 '12 at 12:19

0

Depends on what are you planing to do and more important how often.

You can do it with MySQL-JOIN on the same table. Or you create a hash and compare the datasets. Or you use array_search().

answered Apr 12 '12 at 12:19

Kescha Skywalker

479
4
9

thanks, @Kescha , but another query is not an option, since, as the question states, i need to perform the lookup on the already-returned recordset. hm.. how do you suggest i go about array_search() on a recordset? as to how often, this routine will have to be executed 7x24=168 times per page call. any thoughts? – Ana Ban Apr 12 '12 at 12:31
If you have to execute this routine 168 per page call on the same dataset, than I would suggest to use the answer from Paolo_NL_FR. You pay a high cost for creating a hash, but than you have really low-cost access. – Kescha Skywalker May 03 '12 at 10:29

score 0 · Accepted Answer · answered Apr 12 '12 at 13:58

Let's assume your recordset to array will look something like this :

Array
(
    [0] => Array
        (
            [Emp_ID] => 120
            [Col_1] => 3
            [Col_2] => 11
        )

    [1] => Array
        (
            [Emp_ID] => 120
            [Col_1] => 5
            [Col_2] => 17
        )

    [2] => Array
        (
            [Emp_ID] => 120
            [Col_1] => 6
            [Col_2] => 8
        )
    ....
)

Now create ONCE per page a search array.

$search_array =array();
foreach( $recordSet as $key=>$data ){

        $search_array[$data['Col_1']]=$data['Col_2'];   
}

Then each time you need to find the value of Col_2 from Col_1 just use :

echo $search_array[6]; // will return 8

For the other way around with the same search array use array_search()

echo  array_search("8",$search_array); // will return 6

aaiyt, will fine-tune this to glove-fit my application. thanks, man! much app'd. — Ana Ban, Apr 12 '12 at 15:00

Lookup a column value in recordset based on the value of another column

2 Answers2