MySql query to a multidimensional array

capetonian

Hi Everyone.

I'm an 'advanced' newbie to PHP and I find a lot of things pretty straightforeward. Where I always go wrong are arrays. Either they are really complex or I'm really thick, ... I'll opt for the latter, ...

Anyway, my problem is this:

I have a database with images, each record has 8 fields,

A query is done on a particular aircraft, using the construction number as the key for the query. The results are then put into an array. No problem.

   //get the info from the DB
    $SQL_p166="SELECT * FROM p166com_img WHERE ac_cons=".$cons;
    $p166img_set=mysql_query($SQL_p166);

that works fine.

Now I can dump the data to the screen with this:


while($row=mysql_fetch_array($p166img_set, MYSQL_ASSOC)){


       $tmb = $row['img_fil'];
       echo "Construction :{$row['ac_cons']} <br>";
       echo "<img src=\"tmb/$tmb\"> <br>";
       echo "File name : {$row['img_fil']} <br>";
       echo "Comments : {$row['img_cmts']} <br>";
       echo "Location : {$row['img_loc']} <br>";
       echo "Photographer : {$row['img_phot']} <br>";
       echo "Date : {$row['img_date']} <br>";
       echo "Views : {$row['img_hit']} <br><br>";
                 }

The result of that would then look like this ....
Construction :452
<image here>
File name : 452-1.jpg
Comments :
Location :
Photographer :
Date :
Views : 0

Fine, so I know that the query works, I know how many images there are of that particular construction number. I am dumping it to the screen just to test, eventually the idea is to display one image, with the rest of the available images for this construction number at thumbs below the main image.

I figure that I need to make an array with a 'sub-array for each field that is returned from the db. So lets say that construction number 417 has 3 pictures I would have to have something like this:

main array(
sub array 1 (cons, image, comments, photographer, date, views, location)
sub array 2 (cons, image, comments, photographer, date, views, location)
sub array 3 (cons, image, comments, photographer, date, views, location)
)

How the heck do I do that in PHP?

maybe I am missing the boat totally, but any help will be appreciated, I'm a bit of a die hard so a nudge in the right direction is more than welcome.

Thanks,

Andrew

paulnaj

Hi Andrew,

Your description ...

main array( 
    sub array 1 (cons, image, comments, photographer, date, views, location) 
    sub array 2 (cons, image, comments, photographer, date, views, location) 
    sub array 3 (cons, image, comments, photographer, date, views, location) 
)

... is really easy to convert to PHP. But, you can choose between associative arrays or index arrays ...

// Declare your array
$aircraft_assoc = array();
$aircraft_index = array();
while($row=mysql_fetch_array($p166img_set)){
    // I'm guessing the unique identifier for the aircraft
    $aircraft_assoc[$row['aircraft_id']] = $row;

// Syntax to add the $row to the end of the array
$aircraft_index[] = $row;
}

// Later ... 

// Assoc
foreach($aircraft_assoc as $key => $value){

       $row = $value;

       $tmb = $row['img_fil'];
       echo "Construction :{$row['ac_cons']} <br>";
       echo "<img src=\"tmb/$tmb\"> <br>";
       echo "File name : {$row['img_fil']} <br>";
       echo "Comments : {$row['img_cmts']} <br>";
       echo "Location : {$row['img_loc']} <br>";
       echo "Photographer : {$row['img_phot']} <br>";
       echo "Date : {$row['img_date']} <br>";
       echo "Views : {$row['img_hit']} <br><br>";

}

// Index

for($i = 0; $i < count($aircraft_index); $i++){

      $row = $aircraft_index[$i];

      $tmb = $row['img_fil'];
       echo "Construction :{$row['ac_cons']} <br>";
       echo "<img src=\"tmb/$tmb\"> <br>";
       echo "File name : {$row['img_fil']} <br>";
       echo "Comments : {$row['img_cmts']} <br>";
       echo "Location : {$row['img_loc']} <br>";
       echo "Photographer : {$row['img_phot']} <br>";
       echo "Date : {$row['img_date']} <br>";
       echo "Views : {$row['img_hit']} <br><br>";
}

Hope that helps.

Paul 🙂

PS: I haven't de-bugged so could be typo's

capetonian

Hi Paul,

I'm going to get stuck into that code now as I believe that my solution is there, I just need to read between the lines, reading my post again it seems that I was not as clear as I should have been (at least that's the way I read it).

My DB fields are:
img_id an id, unique, auto incremented. not related to the ac.
ac_cons Aircraft construction number
img_fil File name, ie 417-1.jpg (always this format, con-xx.jpg)
img_loc Location where pic was taken
img_cmts Comments
img_date Date of pic
img_phot Photographers name.
img_hit Nr of views.

My code: (with extra comments)

    /* 
    The unique aircraft identifier is it's construction number, a 3 digit number. 
    The variable for the construction number in this case is passed through from a database page.
    For the sake of testing I use :localhost/gallery/main/showpic.php?cons=417 
    As a link containing a construction number (417).
   */



   //get the info from the DB
    $SQL_p166="SELECT * FROM p166com_img WHERE ac_cons=".$cons;  //  in this case 417
    $p166img_set=mysql_query($SQL_p166);

while($row=mysql_fetch_array($p166img_set, MYSQL_ASSOC)){

   // Here I'll insert the array code. 
   // The echo's are just to dump to the screen so that I can see that the correct number  

   //of records are returned and that  all the information is there.


       $tmb = $row['img_fil'];
       echo "Construction :{$row['ac_cons']} <br>";
       echo "<img src=\"tmb/$tmb\"> <br>";
       echo "File name : {$row['img_fil']} <br>";
       echo "Comments : {$row['img_cmts']} <br>";
       echo "Location : {$row['img_loc']} <br>";
       echo "Photographer : {$row['img_phot']} <br>";
       echo "Date : {$row['img_date']} <br>";
       echo "Views : {$row['img_hit']} <br><br>";
                 }

in this line of code :

while($row=mysql_fetch_array($p166img_set, MYSQL_ASSOC)){

$p166img_set is the returned query, as far as I understand arrays this is one massive array with everything inside it already, initially I thought I would be able to read the data per image out of this array without doing anything else, I was wrong.

I figured that I could then 'create' my first image by plucking it out of the first 'sub' array created from the main array,..

Pfff - I need more coffee, ...

Thanks for the help so far, ... I'm gonna get stuck into it again.

Andrew