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

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