Issue with multiple arrays and select

[czseby]

Hi there.
I'm quite new to php programming and I'm stumbling upon this issue.

using this select:

SELECT * from postmeta WHERE post_id = $postid && meta_key LIKE 'movie-link%'

will get me sets of links for current post. These sets are defined with 'movie-link%' keys and are always ending with number. So I have set of few links with 'movie-link1' keys, one link with 'movie-link2' key and so on.

Using the 'movie-link%' will make it one-call-for-all, thats why I'm using it - db load.

Using the next code, I fetch data and create array with indexes => links correctly associated (I hope?)

while ( $row = mysql_fetch_array( $request ) ) {
$index = str_replace("movie-link", "", $row[2]);
$array = array($index => array($row[3]));
}

The problem is, that I get one array, containing all the links. Something like this, I think:

Array ( 
[0] => Array ( [1] => http://localhost/mosofa/1 ) 
[1] => Array ( [1] => http://localhost/mosofa/2 ) 
[2] => Array ( [1] => http://localhost/mosofa/3 ) 
[3] => Array ( [2] => http://localhost/mosofa/22 ) 
[4] => Array ( [3] => http://localhost/mosofa/22 ) 
[5] => Array ( [3] => http://localhost/mosofa/223 ) 
[6] => Array ( [3] => http://localhost/mosofa/444 ) 
) 

I guess, I need to create separate arrays for each set and then print them as separate ul lists.

By now, with my experience, I'm only able to print them as one <ul> list and can't find a clue how to make this come true. Any ideas please?

[Elad_Nava]

Why are you creating another array when $row already contains everything you need, just not sorted into index => link? Have it as an associative array with column => value:
while ( $row = mysql_fetch_array( $request, MYSQL_ASSOC ) ) {

And then you can access the index and link of each row inside the while.

[Weedpacket]

I think that would increase database load because there are a whole bunch of optimisations that the DBMS wouldn't be able to make any more. On top of that there is all the extra work you're having to do with the data once you've retrieved it.

At least separating the "meta-key" into two columns - one for the type and one for the number - would make a big difference; not just for the database but your application code as well. I don't know enough about your structure but it may be worth going further and storing them in a separate table as well.

Another thing that would help would be to not use "SELECT *", but instead select the columns you actually want.