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:

PHP 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>');
                    }
                }
            } 


I'm sorry for the comments in italian but I didn't have time to delete them.
Anyway, the problem is that the $pic_code[$pic_num] 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):

<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>

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 $pic_code[$pic_num] value under each picture but apart from the first all are empty.
What should I do?

Thank you.

Andrea

[syosoft]

Not sure exactly what is wrong with your code....it's very different than my coding style, so i've taken the liberty of rewriting it.

PHP Code:

<?php
    $qr    = mysql_query('SELECT code FROM '.mysql_real_escape_string($_GET['scelta']));
    $table    = '';
    $per_row= 3;
    $i    = 0;

    while($row = mysql_fetch_assoc($qr))
    {
        if($i == $per_row)
        {
            $table .= '</tr><tr>';
            $i = 0;
        }

        $table .= '<td width="'.ceil(100/$per_row).'%" align="center"><a href="show.php?code='.$row['code'].'&gallery='.$_GET['scelta'].'">';
        $table .= '<img src="imagedb/thumbs/'.$row['code'].'-small.jpg" border="1" />';
        $table .= '</a></td>';

        ++$i;
    }

    if($i > 0)
    {
        while($i < $per_row)
        {
            $table .= '<td width="'.ceil(100/$per_row).'%"></td>';
            ++$i;
        }
    }
?>

<table width="60%" border="0" align="center">
<tr>

PHP Code:

<?php echo $table; ?>

</tr>
</table>

[asgozzi]

Originally Posted by syosoft

it's very different than my coding style, so i've taken the liberty of rewriting it

that's the same for me..it took a while to understand yours

anyway, I have other scripts requiring this part so I would prefer to keep it like that.
the problem comes with $pic_code[$pic_num]: it is a single column array

PHP Code:

$pic_code = mysql_fetch_array($result) 


but I'm not able to read any other row than the first (and consequently any other value) - it is not a problem of $pic_num because it gets correctly increased at each loop.

I have very limited experience of php coding and I'm treating $pic_code[$pic_num] in the C way.is this correct?

[syosoft]

The problem is that $row contains only a [0] and a ['code'] element and you're incrimenting $pic_num.

Just use $row[0] for every iteration and you should be fine.

[syosoft]

also, when in doubt, use the following:

PHP Code:

echo '<pre>';
print_r($row);
echo '</pre>'; 


Great for debugging.

[asgozzi]

hey, sorry to bother you again but I tried 3 things with no results:

1. $row[0]: gives nothing at all
2. $row['code']: works partially because each picture gets printed three times and stops only when

PHP Code:

$pic_num < $rows_nb 


3. $pic_code[0]: takes only the first picture and prints it until

PHP Code:

$pic_num < $rows_nb 


[syosoft]

Is it possible that when you run

PHP Code:

$pic_code = mysql_fetch_array($result); 


it advances the internal pointer....so that when you run...

PHP Code:

while ($row = mysql_fetch_assoc($result)) 


there is nothing to loop through...

Also, I would really like to see my code run because based on your perceived needs, it really should work like a charm.

Side note....are you sure you have at least 3 images in the db?

[asgozzi]

your code works without a glitch.thanks!
I'd still like to find out where I was wrong though..

[asgozzi]

Originally Posted by asgozzi

your code works without a glitch.thanks!

forget what I said.
now I get different pictures but not all that are in the database.
the script is dropping every fouth picture: it prints 1-2-3 but NOT 4 then 5-6-7 and NOT 8 and so on.
I'm sorry about all these questions but now that it's not my code I can't get it

[syosoft]

Sorry about taking a little while to get back to you. I thought I had explained why I believed your code wasn't working in an earlier post.

As far as why is the 4th not printing....that doesnt make any sense to me as my code is a very simple while() though all of the returned record sets. There is no code in there to skip anything.

Maybe you're expecting some behaviour that wasn't explained? Just to double check...try using the debug method i mentioned...set it up similar to:

PHP Code:

while($rs = mysql_fetch_assoc($qr))
{
     echo '<pre>'; print_r($rs); echo '</pre>';

    .........continue with normal code........
} 


That will expose everything that is coming out of your database and might lead to an answer.

Best of luck.

[asgozzi]

Hi.
I tried what you said and I think the problem comes when building the table.

This is the array:

Array
(
[code] => a_060
)
Array
(
[code] => b_074
)
Array
(
[code] => b_075
)
Array
(
[code] => b_076
)
Array
(
[code] => b_077
)
Array
(
[code] => b_103
)
Array
(
[code] => b_104
)
Array
(
[code] => b_105
)

and this is the HTML table:

<table width="60%" border="0" align="center">
<tr>
<td width="34%" lign="center">
<a href="show.php?code=a_060&gallery=paolo">
<img rc="imagedb/thumbs/a_060-small.jpg" border="1" /></a></td>
<td width="34%" align="center">
<a href="show.php?code=b_074&gallery=paolo">
<img src="imagedb/thumbs/b_074-small.jpg" border="1" /></a></td>
<td width="34%" align="center">
<a href="show.php?code=b_075&gallery=paolo">
<img src="imagedb/thumbs/b_075-small.jpg" border="1" /></a></td>
</tr>
<tr>
<td width="34%" align="center">
<a href="show.php?code=b_077&gallery=paolo">
<img src="imagedb/thumbs/b_077-small.jpg" border="1" /></a></td>
<td width="34%" align="center">
<a href="show.php?code=b_103&gallery=paolo">
<img src="imagedb/thumbs/b_103-small.jpg" border="1" /></a></td>
<td width="34%" align="center">
<a href="show.php?code=b_104&gallery=paolo">
<img src="imagedb/thumbs/b_104-small.jpg" border="1" /></a></td>
</tr>
<tr>
<td width="34%" align="center">
<a href="show.php?code=b_106&gallery=paolo">
<img src="imagedb/thumbs/b_106-small.jpg" border="1" /></a></td>

As you can see b_076 and B_105 are not in the table while they are in the array.
I think your code drops a picture when closing a table row.
How can I fix this?

Thanks

Andrea

[syosoft]

I've modified the original code i sent you so now it emulates a database call, i've commented out the code that affects you so you can easily reimpliment it.

However, when i run this code, there is no skipping of numbers in the table cells. I'm not sure what is happening when you're running it or what is going on but this code works as expected.

Copy both the php and the code into the same file and name it something different and run it. Post back with your results please.

PHP Code:

<?php
    $_GET['scelenta'] = ''; # $qr = mysql_query('SELECT code FROM '.mysql_real_escape_string($_GET['scelta']));
    $table        = '';
    $per_row    = 3;
    $i            = 0;

    # Build fake records
    $records = array();
    for($j=0;$j<25;++$j)
        $records[$j] = array('code' => $j);

    foreach($records as $row) # while($row = mysql_fetch_assoc($qr))
    {
        if($i == $per_row)
        {
            $table .= '</tr><tr>'."\r\n";
            $i = 0;
        }

        $table .= '<td width="'.floor(100/$per_row).'%" align="center"><a href="show.php?code='.$row['code'].'&gallery='.$_GET['scelta'].'">'."\r\n";
        $table .= $row['code']; # '<img src="imagedb/thumbs/'.$row['code'].'-small.jpg" border="1" />';
        $table .= '</a></td>'."\r\n";

        ++$i;
    }

    if($i > 0)
    {
        while($i < $per_row)
        {
            $table .= '<td width="'.floor(100/$per_row).'%">&nbsp;</td>'."\r\n";
            ++$i;
        }
    }
?>

<table width="50%" align="center" border="1" cellpadding="10" cellspacing="0">
	<tr>
		<?php echo $table; ?>
	</tr>
</table>
<h2>Source code</h2>
<div style="border:1px solid #cccccc; overflow:auto; padding:5px; height:300px;">
	<?php highlight_file(__FILE__); ?>
</div>