[RESOLVED] Store multiple results

[pedroteixeira07]

Hello I have this query :

	$query="SELECT SP.ServicePic, D.Date, K.Keyword, C.Comment FROM Service_Pic SP, Position P, Date D, SPKeywords SPK, SPComments SPC, Keyword K, Comment C WHERE SP.Position_ID = P.Position_ID AND SP.Date_ID = D.Date_ID AND SP.ServicePic_ID = SPK.ServicePic_ID AND SPK.Keyword_ID = K.Keyword_ID AND SP.ServicePic_ID=SPC.ServicePic_ID AND SPC.Comment_ID = C.Comment_ID AND P.Position_ID = 1"; 

Which delivers me this results:

foto1 1935-01-05 são martinho This place changed so much! But still is one or pr...
foto1 1935-01-05 swimming pools This place changed so much! But still is one or pr...
foto1 1935-01-05 swim This place changed so much! But still is one or pr...
foto1 1935-01-05 south coast This place changed so much! But still is one or pr...
foto1 1935-01-05 costa sul This place changed so much! But still is one or pr...
foto1 1935-01-05 sea This place changed so much! But still is one or pr...
foto1 1935-01-05 mar This place changed so much! But still is one or pr...
foto1 1935-01-05 lido This place changed so much! But still is one or pr...
foto1 1935-01-05 gorgulho This place changed so much! But still is one or pr...

It's easy to retrieve the SP.ServicePic and D.Date fields, because they will always be unique.. the thing is.. Keywords and comments can be different and a lot..

I'm wondering how can I put all this values in an array, my final goal is to have an array with this format:

$whatever = array(pic=>$pic,date=>$date, keywords=>$keywords, comments=>$comments);

Where $keywords and $comments are also arrays of keywords and comments obviously... and ideally, the repeated keywords/comments would be filtered.. I read that can be done on mysql, though i'm not sure how.

Can someone help me with this please?

[Crowly]

Updated the select with linebreaks and changed it to use JOINS instead of the older method of having it in the WHERE statement

$query="SELECT SP.ServicePic, D.Date, K.Keyword, C.Comment
FROM Service_Pic SP
INNER JOIN Position P ON SP.Position_ID = P.Position_ID
INNER JOIN Date D ON SP.Date_ID = D.Date_ID
INNER JOIN SPKeywords SPK ON SP.ServicePic_ID = SPK.ServicePic_ID
INNER JOIN SPComments SPC ON SP.ServicePic_ID=SPC.ServicePic_ID
INNER JOIN Keyword K ON SPK.Keyword_ID = K.Keyword_ID
INNER JOIN Comment C ON SPC.Comment_ID = C.Comment_ID
WHERE P.Position_ID = 1"; 


$result=mysql_query($query);

$output=array();
while ($row=mysql_fetch_assoc($result))
{
  if (!isset($output[row['servicepic']]))
    $output[row['servicepic']]=array('pic'=>'','date'='','keyword'=>array(),'comment'=>array());

  if (!isset($output[row['servicepic']]['pic']))
    $output[row['servicepic']]['pic']=row['servicepic'];

  if (!isset($output[row['servicepic']]['date']))
    $output[row['servicepic']]['date']=$row['date'];

  // !in_array to avoid the same keyword and comment appearing twice
  if (!in_array($row['keyword'],$output[row['servicepic']]['keyword']))
    $output[row['servicepic']]['keyword'][]=$row['keyword'];

  if (!in_array($row['comment'],$output[row['servicepic']]['comment']))
    $output[row['servicepic']]['comment'][]=$row['comment'];
}

echo "<pre>";
print_r($output);
echo "</pre>";

If i have understood it correctly, something like this might be what you are after. It should create an multidimentional array.
Not tested

[pedroteixeira07]

Hi again.

This is awsome, exactlly what I want.
There's some bug though which I'm trying to understand from the code.
This is my result of this code:

Array
(
    [foto1] => Array
        (
            [pic] => 
            [date] => 
            [keyword] => Array
                (
                    [0] => coast
                    [1] => church
                    [2] => roundabout
                )

        [comment] => Array
            (
                [0] => This place gives me bad memories! I had an accident just there next to the church where there used t
                [1] => Here is were I first meet the love of my life.
            )

    )

)

It's not getting the date, and ''foto1'' should be the value of pic.

And this is my objective:

Array
(
        (
            [pic] => foto1
            [date] => 01-01-2000
            [keyword] => Array
                (
                    [0] => coast
                    [1] => church
                    [2] => roundabout
                )

        [comment] => Array
            (
                [0] => This place gives me bad memories! I had an accident just there next to the church where there used t
                [1] => Here is were I first meet the love of my life.
            )

)

[bradgrafelman]

What's your code look like? Note that in Crowly's code snippet above, there are a lot of $'s missing (e.g. references to "row" should have been "$row").

[pedroteixeira07]

The query:

$positionId = $_POST['id']=1;

// Get l the information associated with that position on the map, hence the picture associated with that position
$query="SELECT SP.ServicePic, D.Date, K.Keyword, C.Comment 
FROM Service_Pic SP 
INNER JOIN Position P ON SP.Position_ID = P.Position_ID 
INNER JOIN Date D ON SP.Date_ID = D.Date_ID 
INNER JOIN SPKeywords SPK ON SP.ServicePic_ID = SPK.ServicePic_ID 
INNER JOIN SPComments SPC ON SP.ServicePic_ID=SPC.ServicePic_ID 
INNER JOIN Keyword K ON SPK.Keyword_ID = K.Keyword_ID 
INNER JOIN Comment C ON SPC.Comment_ID = C.Comment_ID 
WHERE P.Position_ID = 1"; 

The rest of the code:

$result=mysql_query($query); 

$output=array(); 
while ($row=mysql_fetch_assoc($result)) 
{ 
  if (!isset($output[$row['ServicePic']])) 
    $output[$row['ServicePic']] = array('pic'=>'','date'=>'','keyword'=>array(),'comment'=>array()); 

  if (!isset($output[$row['ServicePic']]['pic'])) 
    $output[$row['ServicePic']]['pic'] = $row['ServicePic']; 

  if (!isset($output[$row['ServicePic']]['date'])) 
    $output[$row['ServicePic']]['date']=$row['Date']; 

  // !in_array to avoid the same keyword and comment appearing twice 
  if (!in_array($row['Keyword'],$output[$row['ServicePic']]['keyword'])) 
    $output[$row['ServicePic']]['keyword'][]=$row['Keyword']; 

  if (!in_array($row['Comment'],$output[$row['ServicePic']]['comment'])) 
    $output[$row['ServicePic']]['comment'][]=$row['Comment']; 
} 

echo "<pre>"; 
print_r($output); 

I tried to change the first isset to:

if (!isset($output[$row['ServicePic']])) 
    $output  = array('pic'=>'','date'=>'','keyword'=>array(),'comment'=>array()); 

And delivered the correct 'pic' and 'date' values but some sort of error in keyword and comments

[Crowly]

bradgrafelman;10957236 wrote:

What's your code look like? Note that in Crowly's code snippet above, there are a lot of $'s missing (e.g. references to "row" should have been "$row").

Guess my copy+paste was done a bit fast and missed a vital bit :o

pedroteixeira07;10957237 wrote:

The query:
The rest of the code:

// these if's are faulty :(
  if (!isset($output[$row['ServicePic']]['pic'])) 
    $output[$row['ServicePic']]['pic'] = $row['ServicePic']; 

  if (!isset($output[$row['ServicePic']]['date'])) 
    $output[$row['ServicePic']]['date']=$row['Date']; 

Some of my if checks are incorrect, i changed the code a few times before posting, and i forgot to update some of the if's, replace !isset with empty or remove the if all together. If you remove the if it will only result is the value will be overwritten each time, but the end result should be the same

// new if's
if (empty($output[$row['ServicePic']]['pic'])) 
    $output[$row['ServicePic']]['pic'] = $row['ServicePic']; 

  if (empty($output[$row['ServicePic']]['date'])) 
    $output[$row['ServicePic']]['date']=$row['Date'];

[pedroteixeira07]

Thank you very much both.
It's retrieving the correct values already.
I'm just wondering why there's a main array and why is it called the value of the ServicePic:

[B]Array
(
    [foto1] => Array[/B]
        (
            [pic] => http://www.pedroteixeira.org/thennnow/imagesfoto1.png
            [date] => 01-01-1980
            [keyword] => Array
                (
                    [0] => coast
                    [1] => church
                    [2] => roundabout
                )

        [comment] => Array
            (
                [0] => This place gives me bad memories! I had an accident just there next to the church where there used t
                [1] => Here is were I first meet the love of my life.
            )

    )

)

[pedroteixeira07]

Ok I've added another field to retrieve from the query, ServicePic_ID and used it as the name of the main array instead of the field ServicePic.

Thanks guys, for all the support 😃

[Crowly]

pedroteixeira07;10957259 wrote:

I'm just wondering why there's a main array and why is it called the value of the ServicePic

It can be whatever, as long as it uniquely identifies the picture. I only had the four fields from your SELECT to go with, so i chose ServicePic 😉

Its a main array if you retrieve more than one picture, then will be something like this:

Array
(
    [foto1] => Array
        (
            [pic] => foto1
            [date] => 01-01-1935
            [keyword] => Array
                (
                    [0] => something
                    [1] => something 2
                )

        [comment] => Array
            (
                [0] => Something more
            )

    )

[foto2] => Array
    (
        [pic] => foto2
        [date] => 01-01-1940
        [keyword] => Array
            (
                [0] => something 3
            )

        [comment] => Array
            (
                [0] => Something more 3
                [1] => Something more 4
            )

    )

)

If you just retrieve one picture at the time, its no need for the main array.

[pedroteixeira07]

Actually in my case. It will be always one picture.

This is the response for an HTTP request made from the Android OS which I'm interested in the information of just one picture. 😃 Should I just remove the first line ?

[Crowly]

$result=mysql_query($query); 

// ive moved the initialization of $output outside the loop
$output = array('pic'=>'','date'=>'','keyword'=>array(),'comment'=>array()); 

while ($row=mysql_fetch_assoc($result)) 
{ 
  if (empty($output['pic'])) 
    $output['pic'] = $row['ServicePic']; 

  if (empty($output['date'])) 
    $output['date']=$row['Date']; 

  // !in_array to avoid the same keyword and comment appearing twice 
  if (!in_array($row['Keyword'],$output['keyword'])) 
    $output['keyword'][]=$row['Keyword']; 

  if (!in_array($row['Comment'],$output['comment'])) 
    $output['comment'][]=$row['Comment']; 
} 

echo "<pre>"; 
print_r($output);
echo "</pre>";

[pedroteixeira07]

That's it! Works like a charm, thank you very much really 😃 Helped me a lot.