Putting Results into Database

[dptalon135]

Hello, I am trying to build a small gallery script to use on some of my sites, and I have run into a road block that I can't seem to figure out. I have a mySQL table for each gallery, and an entry in it for each image in that gallery. I am trying to figure out is there a way to take all of those entries and display them in a table, with 3 columns and infinite rows? Any help is greatly appreciated.

[GBahle]

I'm not sure what you mean:

Two things: First, you should not have a table for each gallery in your db, since in theory, that could require an infinite number of tables (same goes for columns in any one table).
A design that's compliant with standard ER-Models would be one (or two tables) with columns for image name, URL, description, ... plus either a column with the gallery name or else (in case of two tables) a foreign key referencing the gallery ID from the table that stores the gallery information. Two tables make sense when there's more than a name for each gallery (descriptive text, folder information, etc..). The focus of database design is, among other things, to avoid redundancy.

Second: Assuming your db is outlined as above, you connect to it using the standard PHP functions (check www.php.net for the description plus examples).
Then you execute a statement like "Select imgName, imgURL from images Where gallery = 'desiredGalleryName';" (for 1 table) or else
"Select imgName, imgURL from images, galleries Where images.gID = galleries.ID AND galleries.Name = 'desiredGalleryName';"
With the results of this query, you can then build a table using a while loop.

Hope that helps. Let me know either way 😉

[dptalon135]

I understand what you mean about seperate tables, but that is not a concern for me now... I was planning on redoing that eventually, and at my other site, it is the way you described. What I am trying to do is this: look at the gallery at my site http://j2s.coasterdom.com I want to set the code up so I can get the data for the thumbnails and put them into the HTML table. Don't ask how I'm doing it now... I don't think I know myself :rolleyes: .

[GBahle]

Here's some sample code from a site i built a while ago. I hope it helps (it uses odbc, but that can be easily changed to mysql)

function Output_Entries() { 

$cnx = odbc_connect( 'yourDB' , 'yourUsername', 'yourPwd' ); 

if (!$cnx) { 
    Error_handler( "Database error while connecting" , $cnx ); 
} 

$cur= odbc_exec( $cnx, "select * from pers;" ); // Never do a select * in actual code!

if (!$cur) { 
    Error_handler( "Database error while executing query" , $cnx ); 
} 

odbc_close( $cnx); // Close your connection (Never forget that)

$resultRows = 0;  // count the rows of the queries result (you might not need that)
while (odbc_fetch_row($cur)) $resultRows++;
odbc_fetch_row($cur, 0);

// print the table
$nbrow=0; 
print "<table width=\"80%\">";
while( odbc_fetch_row( $cur ) ) { // as long as there are rows available do
    $nbrow++; // increase the row count (you might not need that)
	$ID = odbc_result ($cur, 1); // get whatever information you need from your query result
	$LName = odbc_result ($cur, 4);
	$GName = odbc_result ($cur, 5);

	// your print statements go here. If e.g. you want to have 5 pictures per row, you'd
	// insert a <tr> tag whenever $nbrow % 5 = 1 and a </tr> whenever $nbrow % 5 = 0

} 

print "</table>"; // Close the table. Dont forget to fill the last line if neccessary (e.g.
				  // when you've got 23 images and 5 per row, you'd need to add 2 "empty"
				  // cells (like <td>&nbsp;</td>. The fill in code goes in after the while
				  // loop but before the </table> and would run from $nbrow % 5 to 5 or sth
} 

[dptalon135]

I am not exactly a guru at PHP... would you mind explaining how to do the < tr > stuff? I can tell that this script is what I need. Thanks!

[GBahle]

add a

if ($nbrow % $numberOfItemsPerRow == 1) {
        print "<tr>";
}

// here goes the code that prints the cells

if ($nbrow % $numberOfItemsPerROw == 0) {
        print "</tr>";
}

//after the while loop

if ($nbrow % $numberOfItemsPerRow != 0) {  // got to fill the last row
         while ($nbrow % $numberOfItemsPerRow != 0) {
                 print "<td>&nbsp;</td>";
         }
         print "</tr>";
}

Edited for a little coding mistake. Also, edited my second post in this thread

[dptalon135]

Well I just can't seem to get this to work. It just displays a blank table, no data. What could I be doing wrong?

[GBahle]

in the part where i wrote //your print statements go here :
that's where you actually create the table cells.
So there should be a line like

print "<td><a href=\"$yourLink\"><img src=\"$yourSrc\" border=\"0\"></a></td>\n";

if that line's missing, there wouldn't be anything displayed. I'm short on time atm, but I can put the whole thing together tomorrow if you've still got problems then.

Just let me know

[dptalon135]

Yeah I have that part... and it still doesn't show anything.

[GBahle]

Could you please post the complete code fragment you are using atm? And are you certain that your database connection and sql statement are working properly? Also, aside from the database approach, there's an img gallery script available that reads all the images in a directory. All you got to do is place the script in the directory containing the images, and it will display them. The URL is http://www.phpbuilder.com/board/showthread.php?s=&threadid=10209059&highlight=Photogallery

[dptalon135]

Here is what I am currently using from what you gave me:

    $cnx = mysql_connect("localhost", "dptalon", "jsjsmso");
	mysql_select_db("dptalon",$cnx);

if (!$cnx) { 
    die("Database error while connecting"); 
} 

$cur = mysql_query("SELECT image_name, image_desc FROM gallery_park_bgw ORDER BY ID ASC");

if (!$cur) { 
    die("Database error while executing query"); 
} 

mysql_close($cnx); // Close your connection (Never forget that)

$resultRows = 0;  // count the rows of the queries result (you might not need that)
while (mysql_fetch_row($cur))$resultRows++;
// print the table
$nbrow=0; 
print "<table border=\"0\" width=\"100%\">";
while(mysql_fetch_row($cur)) { // as long as there are rows available do
    $nbrow++; // increase the row count (you might not need that)
    $image = mysql_result ($cur, 1); // get whatever information you need from your query result
    $description = mysql_result ($cur, 2);

if ($nbrow % $numberOfItemsPerRow == 1) {
print "<tr>";
}
// here goes the code that prints the cells
   print "<td align=\"center\"><a href=\"gallery2.php?page=image&image=$image&id=$id\"><img src=\"gallery/bgw/t_$image.jpg\" border=\"0\" alt=\"$description\"></a></td>\n";
if ($nbrow % $numberOfItemsPerRow == 0) {
print "</tr>";
}}

//after the while loop
if ($nbrow % $numberOfItemsPerRow != 0) {  // got to fill the last row
      while ($nbrow % $numberOfItemsPerRow != 0) {
		 print "<td>&nbsp;</td>";
	  }
   print "</tr>";
}

print "</table>";

I need something that will use separate thumbnails, not just shrunken regular images.

[GBahle]

Ouch, you left out a very important line:

In the block that counts the results (which you might or might need)

$resultRows = 0; // count the rows of the queries result (you might not need that)
while (mysql_fetch_row($cur))$resultRows++;

you ommitted the last line (the odbc_fetch_row($cur, 0))

This causes the script to remain at the last row of the query result, and thus there's nothing to display. Either leave out the above lines (if you don't need the img count before the display, afterwards $nbrow holds it anyway) or add the missing one.

Also, just noticed a little oversight: In the block commented with // got to fill the last row, inside the while loop you have to add $nbrow++;

let me know if that fixed it.

[dptalon135]

Ok, here goes...
First, if I have that whole count block thing, I get this error:
"Warning: Wrong parameter count for mysql_fetch_row() in /home/httpd/vhosts/coasterdom.com/web_users/j2s/test.php on line 19"

Then, if I completely remove that block, I get a result I can't explain. See it for yourself at http://www.coasterdom.com/~j2s/test.php BE SURE TO HIT STOP ON THE BROWSER QUICKLY! To say the least, that result isn't right either :p . Anyway, thanks for all your help so far!

[GBahle]

ok, seems that the mysql functions are sufficiently different from the odbc ones to cause problems. Replace your code with the following:

while($row = mysql_fetch_row($cur)) { // as long as there are rows available do
        $nbrow++; // increase the row count (you might not need that)
        $image = $row[0]; // get whatever information you need from your query result
        $description = $row[1];

let me know if that works.

[dptalon135]

Ok, now the images display, but just in a column. What do I change to put 3 images per row? Again, thanks for your help!

[GBahle]

Did you set $numberOfItemsPerRow = 3 somewhere before the while loop?
Also, there's a non terminating loop in the code you use. In one of my earlier posts i mentioned that i forgot to add $nbrow++; to the last while loop. This is not optional. The fragment should look like this:

//after the while loop
if ($nbrow % $numberOfItemsPerRow != 0) {  // got to fill the last row
      while ($nbrow % $numberOfItemsPerRow != 0) {
         print "<td>&nbsp;</td>";
         $nbrow++; //this is missing!
      }
   print "</tr>";
}

if this doesnt fix your problem, please post your current code (again g)

[dptalon135]

Got it working! I knew I should have set that variable... doh :rolleyes: ! Anyway, thank you very much for all your help!!