...

View Full Version : Data from mysql in a 3 columns html table



asgozzi
09-30-2006, 06:14 PM
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:


$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
09-30-2006, 08:09 PM
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
$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 echo $table; ?>
</tr>
</table>

asgozzi
09-30-2006, 08:29 PM
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

$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
09-30-2006, 08:42 PM
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
09-30-2006, 08:43 PM
also, when in doubt, use the following:



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


Great for debugging.

asgozzi
09-30-2006, 08:56 PM
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

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

$pic_num < $rows_nb

syosoft
09-30-2006, 09:04 PM
Is it possible that when you run



$pic_code = mysql_fetch_array($result);


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



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
09-30-2006, 09:11 PM
your code works without a glitch.thanks!
I'd still like to find out where I was wrong though..

asgozzi
09-30-2006, 10:31 PM
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
10-01-2006, 06:09 AM
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:



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
10-01-2006, 09:39 AM
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
10-01-2006, 09:55 AM
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
$_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>



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum