Jump to content


Photo

Data from mysql in a 3 columns html table


  • Please log in to reply
9 replies to this topic

#1 asgozzi

asgozzi
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 29 September 2006 - 12:46 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?

#2 craygo

craygo
  • Staff Alumni
  • Advanced Member
  • 1,973 posts
  • LocationRhode Island

Posted 29 September 2006 - 12:56 PM

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

Let us know

Ray

#3 asgozzi

asgozzi
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 29 September 2006 - 01:21 PM

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

#4 craygo

craygo
  • Staff Alumni
  • Advanced Member
  • 1,973 posts
  • LocationRhode Island

Posted 29 September 2006 - 01:34 PM

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

Post your table structure and will help you out better

Ray

#5 asgozzi

asgozzi
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 29 September 2006 - 02:19 PM

This is one of the db tables:

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 ;


#6 jerastraub

jerastraub
  • Members
  • PipPipPip
  • Advanced Member
  • 77 posts

Posted 29 September 2006 - 02:25 PM

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.

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


#7 asgozzi

asgozzi
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 30 September 2006 - 02:13 PM

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

#8 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 01 October 2006 - 01:01 PM

The array is built with

$pic_code = mysql_fetch_array($result);
and accessing it like
$pic_code[0]; $pic_code[1];
... doesn't work


Have you checked you have a valid result... Try adding this to your
mysql_query()
statement:

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

This should allow you to debug it a little easier.

Regards
Huggie
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#9 asgozzi

asgozzi
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 01 October 2006 - 04:01 PM

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

#10 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 01 October 2006 - 04:29 PM

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

Regards
Huggie
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users