an array in an array

[tallberg]

I'm attempting to retieve records from a table containing a tip(text) and an id(int). The id is used to search through a image table containing assosiated images. ie a tip can have many images but an image only has 1 tip.
I thought the best way would be to use a multi dimension array to store tips as the first part and the image as the second part. This is what i got so far.

Thanks if any one has a suggestion.

[code] while($row = mysql_fetch_array($rs)){
 
  $count++;
 
  $a_month[$count] = $row["month"];
  $a_tip[$count] = $row["tip"];
  $tip_id[$count] = $row["tip_id"];  
}
 
  for($i=0; $i<sizeof($tip_id); $i++)
  {
$query = "SELECT * FROM  tubs_tips_images WHERE tip_id = '".$tip_id[$i]."'";
  $rs = mysql_query($query) or die (mysql_error());
 
  while($row = mysql_fetch_array($rs)){
     
  $count++;
 
  $tip_images[$i][$count] = $row["image_id"];
 
  }
  }
  [/code]

[Psycho]

You should be able to get all the data with one query. Can you post the query you are using for the first result set?

[Psycho]

Well, not knowing how you are going to use the data, it is difficult to advise how best to put the valures into an array. But, I will make a generic recommendation. Assuming the name of the table in the first query is "tablename" this is what I would do:

[code]$query = "SELECT t1.month, t1.tip, t1.tip_id, t2.image_id
          FROM tabelname t1
            LEFT JOIN tubs_tips_images t2
              ON t1.tip_id = t2.tip_id";

$rs = mysql_query($query) or die (mysql_error());

while($row = mysql_fetch_array($rs)){

    $dataset[$row['tip_id']]['month']    = $row['month'];
    $dataset[$row['tip_id']]['tip']      = $row['tip'];
    $dataset[$row['tip_id']]['images'][] = $row['image_id'];

}[/code]

[tallberg]

Query for first result set. thanks for help will try out what you suggested.


$query = "SELECT * FROM  tubs_tips WHERE month = '$date'";
$rs = mysql_query($query) or die (mysql_error());

[ninja]

the most immediate problem i see is that you already assigned your first query result to $rs and then turn around and assign your 2nd query to the same variable.

[Psycho]

@ninja, in his first example he was done using $rs before assigning the second result set to it.

@tallberg, looking at your first query, you could get all the data needed with this single query:

SELECT tips.month, tips.tip, tips.tip_id, images.image_id
FROM tabelname tips
  LEFT JOIN tubs_tips_images images
  ON tips.tip_id = images.tip_id
WHERE tips.month = '$date'

As for putting it into an array would be fairly simple - one example I gave above. But the best format for the array will depend upon how you will use the data.

[ninja]

whoops.  guess i didn't catch that little } in there.

[tallberg]

I want to format the data by displaying tips (one after the other) with there assosiciated images. All in a table.

Thanks for replying

[Psycho]

[quote author=tallberg link=topic=124691.msg517279#msg517279 date=1170181878]
I want to format the data by displaying tips (one after the other) with there assosiciated images. All in a table.[/quote]
Then you do not need to use an array at all (unless you need to reuse the data in the same page).

Try this (you will need to correct the image path):
[code]<?php
function showTipRow($tipData, $images) {
    echo '<tr>';
    echo '<td>' . $tipData['tip_id'] . '</td>';
    echo '<td>' . $tipData['tip'] . '</td>';
    echo '<td>' . $tipData['month'] . '</td>';
    echo '<td>' . $images . '</td>';
    echo '</tr>';
    return;
}

$query = "SELECT tips.month, tips.tip, tips.tip_id, images.image_id
          FROM tabelname tips
            LEFT JOIN tubs_tips_images images
            ON tips.tip_id = images.tip_id
          WHERE tips.month = '$date'";

$rs = mysql_query($query) or die (mysql_error());

echo '<table>';

$currentTipID="";

while($row = mysql_fetch_array($rs)){

    if ($row['tip_id']!=$currentTipID && $currentTipID!="") {
        showTipRow($row, $images);
        $currentTipID = $row['tip_id'];
        $images = "";
    }

    $images .= '<img src="pathtoimages\'.$row['image_id'].'.jpg"><br>';
}

showTipRow($row, $images);
echo '</table>';
?>[/code]

[tallberg]

I realy cant understand what going on there.
I sure you dirrecting me somewhere useful.

A the moment it produces a blank screen.

[Psycho]

Well, since I do not have access to all of your code and your database I have to make some assumptions. I am thinking that the query is running successfully but not returning any results.

Let's go one step at a time. Try this:
[code]<?php
$query = "SELECT tips.month, tips.tip, tips.tip_id, images.image_id
          FROM tabelname tips
            LEFT JOIN tubs_tips_images images
            ON tips.tip_id = images.tip_id
          WHERE tips.month = '$date'";

$rs = mysql_query($query) or die (mysql_error());
$numRecords = mysql_num_rows($rs);
echo "There were $numRecords in the result set";
?>[/code]

[tallberg]

That works.

[Psycho]

OK, great - I'm also assuming the result wasn't 0.

This should display all the records in the result set. Let me know if it works.:
[code]<?php
$query = "SELECT tips.month, tips.tip, tips.tip_id, images.image_id
          FROM tabelname tips
            LEFT JOIN tubs_tips_images images
            ON tips.tip_id = images.tip_id
          WHERE tips.month = '$date'";

$rs = mysql_query($query) or die (mysql_error());

echo "<table>";
while($row = mysql_fetch_array($rs)){
  echo "<tr>";
  echo "<td>{$row['tip']}</td>";
  echo "<td>{$row['tip_id']}</td>";
  echo "<td>{$row['month']}</td>";
  echo "<td>{$row['image_id']}</td>";
  echo "</tr>";
}
echo "</table>";
?>[/code]

[tallberg]

That works a treat. Thanks very much.

[Psycho]

Is that all you wanted? I was thinking you wanted one row for each tip with multiple images in the last column. If that's the case, then my original code was more to what you were looking for.

I found my error in the original code if you want to use it. This should work as I envisioned:
[code]<?php
function showTipRow($tipData, $images) {
    echo '<tr>';
    echo '<td>' . $tipData['tip_id'] . '</td>';
    echo '<td>' . $tipData['tip'] . '</td>';
    echo '<td>' . $tipData['month'] . '</td>';
    echo '<td>' . $images . '</td>';
    echo '</tr>';
    return;
}

$query = "SELECT tips.month, tips.tip, tips.tip_id, images.image_id
          FROM tabelname tips
            LEFT JOIN tubs_tips_images images
            ON tips.tip_id = images.tip_id
          WHERE tips.month = '$date'";

$rs = mysql_query($query) or die (mysql_error());

echo '<table>';

$currentTipID="";

while($row = mysql_fetch_array($rs)){

    if ($row['tip_id']!=$currentTipID) {
        if ($currentTipID!="") {
            showTipRow($row, $images);
            $images = "";
        }
        $currentTipID = $row['tip_id'];
    }

    $images .= '<img src="pathtoimages\'.$row['image_id'].'.jpg"><br>';
}

showTipRow($row, $images);
echo '</table>';
?>[code][/code][/code]