SQL and Arrays

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
jsky
Forum Newbie
Posts: 8
Joined: Wed Feb 13, 2008 12:23 am

SQL and Arrays

Post 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!
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: SQL and Arrays

Post 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);
 
User avatar
jsky
Forum Newbie
Posts: 8
Joined: Wed Feb 13, 2008 12:23 am

Re: SQL and Arrays

Post 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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: SQL and Arrays

Post 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.
User avatar
jsky
Forum Newbie
Posts: 8
Joined: Wed Feb 13, 2008 12:23 am

Re: SQL and Arrays

Post by jsky »

Thanks a lot! :oops:
User avatar
jsky
Forum Newbie
Posts: 8
Joined: Wed Feb 13, 2008 12:23 am

Re: SQL and Arrays

Post 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];
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: SQL and Arrays

Post 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
Post Reply