Data from mysql in a 3 columns html table

[asgozzi]

Hi.
I'm trying to write a script that fetchs image codes from a mysql database to display them into a 3 columns html table.

I got this so far:
[code]
$selection = $_GET['scelta']; // Leggi il contenuto della variabile che determina la gallery
$query  = 'SELECT code FROM `'. $selection.'`;'; // Codice SQL per la query
$result = mysql_query($query);
// E' necessario contare le righe risultanti della query per poi controllare che la tabella grafica abbia sempre 3 colonne
$rows_nb = mysql_num_rows($result);
print($rows_nb);
// Genero l'array associativo dalla query e inizializzo a 0 la variabile che conterà le immagini
$pic_num = 0;
$pic_code = mysql_fetch_array($result);

print('<table width="60%" border="0" align="center">'); // Stampo l'intestazione HTML della tabella
while ($row = mysql_fetch_assoc($result))
{
print('<tr>'); // Costruzione della riga
for ($tb_rows=0;$tb_rows<3;$tb_rows++)
{
if ($pic_num < $rows_nb) // Controllo per essere sicuri di non aver sforato con il numero di immagini
{
// Creo la cella, seleziono l'immagine giusta e inserisco il il tag e il link
print('<td><div align="center"><a href="show.php?code='. $pic_code[$pic_num] .'&gallery='. $selection .'"><img src="imagedb/thumbs/'.$pic_code[$pic_num] .'-small.jpg" border="1" /></a></div></td>');

/* DEBUGGING START */
print('<p>PIC_CODE: '. $pic_code[$pic_num] .'</p>');
/* DEBUGGING STOP */

$pic_num++; // Aumento il contatore delle foto inserite
}
else
{
print('<td></td>');
}
}
}
[/code]

I'm sorry for the comments in italian but I didn't have time to delete them.
Anyway, the problem is that the [b]$pic_code[$pic_num][/b] trick doesn't work: only the first picture gets displayed and all the other are broken.
This is what happens in the generated page (first & second pics):

[code]
<td><div align="center"><a href="show.php?code=a_011"><img src="imagedb/thumbs/a_011-small.jpg" border="1" /></a></div></td>
<td><div align="center"><a href="show.php?code="><img src="imagedb/thumbs/-small.jpg" border="1" /></a></div></td>
[/code]

You can see that the link and location of the first image are ok but all the other aren't.
I tried printing out the [b]$pic_code[$pic_num][/b] value under each picture but apart from the first all are empty.
What should I do?

[craygo]

why are you fetching 2 arrays?? the reason you are only getting the first row is because this line
[code]$pic_code = mysql_fetch_array($result);[/code]
is not in a loop so it only fetches the first row.
I would try removing the above line and changing this
[code]print('<p>PIC_CODE: '. $pic_code[$pic_num] .'</p>');[/code]
to this
[code]print('<p>PIC_CODE: '. $row[$pic_num] .'</p>');[/code]

Let us know

Ray

[asgozzi]

still nothing.
now it can't even get the first picture code.
it's like if you couldn't access the array cells, but I can't understand why..

[craygo]

do you actually have a field for the pic code in the table??

Post your table structure and will help you out better

Ray

[asgozzi]

This is one of the db tables:

[code]
CREATE TABLE `avi` (
`id` INT( 4 ) NOT NULL AUTO_INCREMENT ,
`code` VARCHAR( 6 ) NOT NULL ,
`description` VARCHAR( 200 ) ,
`views` INT( 3 ) ,
PRIMARY KEY ( `id` )
) TYPE = MYISAM ;
[/code]

[jerastraub]

This is what I use for displaying 3 columns by 3 rows deep of pictures with link. Of you course you would have to change the variables to your.

[code]  <table width="100%" class="quick" border="0" cellspacing="0" cellpadding="0">

<?php
$query = "SELECT *
    FROM databasename
        WHERE something='something' limit 9";
$result = mysql_query($query) or die("There was a problem with the SQL query: " . mysql_error());
if($result && mysql_num_rows($result) > 0)
{
    $i = 0;
    $max_columns = 3;
    while($row = mysql_fetch_array($result))       
  {
      // make the variables easy to deal with
      extract($row);

      // open row if counter is zero
      if($i == 0)
          echo "<tr>";

      // make sure we have a valid product
    // make sure we have a valid product
// if($product != "" && $product != null) // temporary comment out
  echo <<<HTML
    <td align=center><table class="quick" width="100%" height="350" border="0" align=center cellspacing="0" cellpadding="0">
  <tr>
    <td align=center><a href="http://pluswomen.thefreestuffvault.com/product-{$ProductID}.htm">
<img src="{$BigImage}" border="0" height=180 alt="{$Name}"></a></td>
  </tr>
</table></td>
HTML;
   
      // increment counter - if counter = max columns, reset counter and close row
      if(++$i == $max_columns)
      {
          echo "</tr>";
          $i=0;
      }  // end if
  } // end while
} // end if results

// clean up table - makes your code valid!
if($i < $max_columns)
{
    for($j=$i; $j<$max_columns;$j++)
        echo "";
}
?>
</tr>
</table>[/code]

[asgozzi]

thanks jerastraub but I can't make your code work.
my problem comes where you have $ProductID, $BigImage and $Name: I have a single column array and I can't make the pointer go to the next value DOWN the column.
the array is built with [/b]$pic_code = mysql_fetch_array($result);[/b] and accessing it like $pic_code[0],$pic_code[1],... doesn't work

[HuggieBear]

[quote]
The array is built with [code=php:0]$pic_code = mysql_fetch_array($result);[/code] and accessing it like [code=php:0]$pic_code[0]; $pic_code[1];[/code]... doesn't work
[/quote]

Have you checked you have a valid result... Try adding this to your [code=php:0]mysql_query()[/code] statement:

[code]
<?php
$result = mysql_query($sql) or die ("Can't execute $sql: " . mysql_error()); // Will echo if the query itself is failing
$pic_code = mysql_fetch_array($result);
var_dump($pic_code); // Will echo the results returned from the DB
?>
[/code]

This should allow you to debug it a little easier.

Regards
Huggie

[asgozzi]

the query works fine, I believe the problem comes when trying to access the array cells..

[HuggieBear]

ok, well my advice would be to post your code for us to take a look at.

Regards
Huggie