Page 1 of 1

SQL and Arrays

Posted: Mon Feb 18, 2008 1:05 am
by jsky
I have an array of id's(array1) each with corresponding hits(array2).
I'm using an imploded array1 to select ids from a table.

like so:
select * from table where id in $array1;

Now i want to add the corresponding hits(array2) of each id in the query result.

The problem is that the result is automatically ordered by id ASC. so when i add my hits to the result, they do not match with the id's. Is there a way to make this work? Thanks!

Re: SQL and Arrays

Posted: Mon Feb 18, 2008 1:09 am
by Benjamin
Yes there is.

You can use asort to sort the array1 in ascending order. Add "order_by id asc" to your query and you'll be good to go.

Code: Select all

 
asort($array1);
 

Re: SQL and Arrays

Posted: Mon Feb 18, 2008 1:27 am
by jsky
Thanks. Yes but even if even if i sort array1, how about array2? They are separate btw.

array1(id)
4
2
1
3
7

array2(hits)
23
14
16
24
45

id 4 has 23 hits.

Re: SQL and Arrays

Posted: Mon Feb 18, 2008 1:34 am
by Benjamin
Assuming both array indexes start at 0, asort will maintain the index association, so to get the hits you can:

Code: Select all

 
foreach($array1 as $index => $id)
{
    // hits for the current $id
    $hits = $array2[$index];
}
 
If I was writing this I would have a single array, using the id as the array index and the value would store the hits.

Re: SQL and Arrays

Posted: Mon Feb 18, 2008 1:37 am
by jsky
Thanks a lot! :oops:

Re: SQL and Arrays

Posted: Mon Feb 18, 2008 3:40 am
by jsky
I used your code, but I forgot to add that i'm using paging:
select * from table where id in $array1 $LIMIT;
$LIMIT = $LIMIT($a,$b);

I made a loop but it only works without paging.

Code: Select all

$data_array[$i]['numhits'] = $hits[$i];

Re: SQL and Arrays

Posted: Mon Feb 18, 2008 3:59 am
by Benjamin
You can use array slice to get the chunk of records for the current page. So if there are 10 records per page and your on the second page, $a would be 11 for the 11th record and $b would be 10 for the number of elements to get. The fourth parameter (true) maintains the index association. The fourth parameter is only availabe on php versions >= 5.0.2.

Code: Select all

 
$hits = array_slice($hits, $a, $b, true);
 
http://us3.php.net/manual/en/function.array-slice.php