Dynamic Tables in PHP

[FRANK(WGH)]

This may seem elementary to some, but I'm just learning php and I need some help.
I'm creating a Photo Gallery for a website and I want to be able to make a standard template and let php handle the rest.
Right now, I'm using an array to hold all of the information needed for the gallery including the filenames of the images.

PHP Code:

$show = array ( "TSO" => array(    "title" => "Trans-Siberian Orchestra",
                                        "folder" => "TSO/",
                                        "date" => "11/13/05",
                                        "img1" => "PB130055.jpg",
                                        "img2" => "PB130069.jpg",
                                        "img3" => "PB130124.jpg",
                                        "img4" => "PB133303.jpg",
                                        ), 
); 


Then I create a static table with 4 columns and call it a day. I'd like the ability specify the number of images I have and let php create the table.

Here's an example of what I've got so far. http://www.rockshotzinc.com/gallery-...k=TSO&pic=img1

Can anyone help me out.

[SamC]

Instead of using an array to hold all of the information, why not use a MySQL (or some other) database? It would be much easier I think, for when you have many more photos to organize in your photo gallery.

Once you have a database setup, just setup the tables in HTML and have PHP code within the table to echo the <a href=" "><img /></a> code and setup a config section that will define how many images you want per page. If I have some time later I will edit this post with an example of what I mean. It is slightly more complex than what you are looking at right now, but in the long run, once you get into more advanced PHP it ill be much more simpler.

EDIT:
Ok, here is some basic code. Anything that is in CAPS would be a MySQL column or row.

PHP Code:

<table>
<tr> 
<?php
//config section
$column_limit = 5; //change to howver many columns you want
//MySQL information
$server = "[insert here]"; //url for the mysql server
$DBuser = "[insert here]"; //database username
$DBpass = "[insert here]"; //database password
$DB = "[insert here]"; //actual database name
//column counter
$column_counter = 0;

$conn = mysql_connect($server,$DBuser,$DBpass); //connect to server
mysql_select_db($DB); //select database
        $query = "SELECT * FROM PICTURES"; //select items from column PICTURES
        $results = mysql_query($query);

while($row = mysql_fetch_assoc($results)) 
        {
        if($column_counter == $column_limit)    
        {
        echo '</tr><tr>'; //End current row, and start a new row
                $column_counter = 0; //reset the column counter
        }
        else {
        echo '<td><a href="' .$row[PICTURE_URL]. '" ><img src="' .$row[PICTURE_URL]. '" /></a><br>'; //Echo a link to the image, and display the image as well
        echo 'Added to gallery:' .$row[PICTURE_DATE]. '</td>'; //display the dat it was added
        $column_counter++; //Add one to the counter
        }
        }
?>
</tr>
    </table>

[FRANK(WGH)]

Thanks for the fast reply.
I decided not to use a database quite yet because I haven't learned a lot about them, and every time I try to use databases, I end up failing for some reason and I can't troubleshoot because I have no idea what I'm looking at.

I've run into some trouble again with databases.
Could you show me a sample of the connection configuration you would use. I specified localhost as my server and then 127.0.0.1 and neither worked.
Also,
What does the database structure look like. I created a database with a table called picture. what columns do I need to have for your script to work.

I do want to learn from this, so I'm not asking you to write this all for me, but I need some examples to learn from.

Thanks again

[Doliver]

First of all, you gotta make sure you have MySQL running on your server.
If you have it in a hosted server, prolly MySQL is already OK. All you have to do is get the required info, like the server Addres, user, and password. (normally they are available when you log into a CP or something)

If you are trying to run your application on your own computer, things get a little bit more difficult. You must do all the instalation and configuration steps and make sure it's working. I don't remember all the steps now, but its not hard to google tutorials with this kind of info.

I'll wait for you to answer and explain a little better how you are running your system and then I'll help.

[SamC]

Originally Posted by FRANK(WGH)

Could you show me a sample of the connection configuration you would use. I specified localhost as my server and then 127.0.0.1 and neither worked.
Also, What does the database structure look like. I created a database with a table called picture. what columns do I need to have for your script to work.

OK, so the connection configuration.
First of all, PHP has a bunch of built in functions that allow for many things to happen with MySQL. You can see all of those here scroll towards the botton for all of the function or search for "Innehållsförteckning".

The way I setup the MySQL connect in the script above is this.

PHP Code:

$server = "[insert here]"; //url for the mysql server
$DBuser = "[insert here]"; //database username
$DBpass = "[insert here]"; //database password

$conn = mysql_connect($server,$DBuser,$DBpass); //connect to server 


That part is pretty straightforward. For the sake of simplicity, I will show you how to setup the database structure as well.
First, create a MySQL database and call is 'FRC_0048_gallery' (I used FRC_0048 because you need a unique name since you are most likely on a shared server)
Then within the database 'FRC_0048_gallery' create a column with the name of 'PICTURES'
Then create 2 rows called 'PICTURE_URL' and 'PICTURE_DATE'

Then using all of that, it comes together to make a PHP script that looks like this

PHP Code:

<table>
<tr> 
<?php
//config section
$column_limit = 5; //change to howver many columns you want
//MySQL information
$server = "[insert here]"; //url for the mysql server
$DBuser = "[insert here]"; //database username
$DBpass = "[insert here]"; //database password
$DB = "FRC_0048_gallery"; //actual database name
//column counter
$column_counter = 0;

$conn = mysql_connect($server,$DBuser,$DBpass); //connect to server
mysql_select_db($DB); //select database
        $query = "SELECT * FROM PICTURES"; //select items from column PICTURES
        $results = mysql_query($query);

while($row = mysql_fetch_assoc($results)) 
        {
        if($column_counter == $column_limit)    
        {
        echo '</tr><tr>'; //End current row, and start a new row
                $column_counter = 0; //reset the column counter
        }
        else {
        echo '<td><a href="' .$row[PICTURE_URL]. '" ><img src="' .$row[PICTURE_URL]. '" /></a><br>'; //Echo a link to the image, and display the image as well
        echo 'Added to gallery:' .$row[PICTURE_DATE]. '</td>'; //display the dat it was added
        $column_counter++; //Add one to the counter
        }
        }
?>
</tr>
    </table>

[artdutra04]

If you want to get serious with PHP and MySQL, install Apache on your computer, and then install PHP, MySQL, and PHPmyAdmin. I'd suggest XAMPP, since it's an all-in-one package.

Once you get going, you should be able to fire up your browser (Firefox of course! ) and type in http://localhost/ or http://localhost:8080/ into the address bar and see what the server on your computer is doing. Next, use PHPmyAdmin (through your web browser) to set up some test MySQL database tables.

Once these are created, you can write and execute PHP files on your own computer, and test all your database scripts on your computer instead of having to FTP every file to a remote server to test it.

It looks like Sam has the actual coding-end pretty much wrapped up for a simple demo, so I'll leave that to them.

[FRANK(WGH)]

Thanks for all your help guys. I've gotten really far with what you've given me.
I'll post a link once I get something assembled.

[Uberbots]

PHP Code:

echo "<table>";
foreach($show as $i => $d) {
  echo "<tr>";
  foreach($show[$i] as $j => $jd) {
    echo "<td>" . $jd . "</td>";
  }
  echo "</tr>";
}
echo "</table>"; 


with your array, that is the easiest way to do it.
(i love arrays <3)

and sam, it would be more efficient to use a LIMIT statement in your SQL query to limit the rows listed (and you can do pages with that too).

[SamC]

Originally Posted by Uberbots

[php]
and sam, it would be more efficient to use a LIMIT statement in your SQL query to limit the rows listed (and you can do pages with that too).

I was going to add the LIMIT in the query, but I figured since he was just learning about MySQL and PHP, just give him little chunks at a time. Thanks for pointing that out though.

With the database limit it would be,

PHP Code:

[...CODE CUT...]
$image_limit = 20; //change to any number.
[...CODE CUT...]
mysql_select_db($DB); //select database
        $query = '"SELECT * FROM `PICTURES` LIMIT 0,' .$image_limit. '"'; //select items from column PICTURES
        $results = mysql_query($query); 


Here is a quick definition of the LIMIT command. LIMIT is used to limit the number of results returned by MySQL. It requires 2 values, X and Y. X being the first result you want (The starting position) Y being the number of results you want. I said LIMIT 0, [20] because 0 is always the lowest result in the database, so the output would be the first 20 results in your database.

[FRANK(WGH)]

Seems I've run into a small snag. The counter for the gallery view leaves out a photo. It counts 1 to 5 then skips 6 and goes to 7. Any ideas on why or how I can fix that.

PHP Code:

$column_limit = 5; //change to however many columns you want
$column_counter = 0;//column counter

if($column_counter == $column_limit) 
         {
    echo '</tr><tr>'; //End current row, and start a new row. 
        $column_counter = 0; //reset the column counter
      } 


Also,
I want to create a page that shows only one picture. How do I call that one picture with a URL string. Ex. index.php?photo=25
I've tried to think that out all night, and I've gotten nowhere

[FRANK(WGH)]

I figured out the second part of my last post.

I added where id = $photo into the query. $photo is defined in the URL. index-test.php?link=web&photo=5

PHP Code:

$query  = "SELECT id, title, description, path, image, date FROM photos WHERE id = $photo"; 


That allowed me to view the picture with id = 5.

I'm still stumped on the first part of my last post though.

[FRANK(WGH)]

Any ideas on why the math is off by one?? I've really been getting frustrated with it. I've tried a few different things, and nothing seems to be fixing my problem.

[Travis Hoffman]

F, I'm just a dumb C-programmer (emphasis on dumb), but I think you are "wasting" a fetch of the Picture 6 data at the entry to the while loop before your final end of row check (if counter == limit) takes place within the loop. You read the data but then your limit check kicks in, starts a new row, and resets the counter before anything is done with that data. You then exit the while loop and reenter it, fetching Pic 7 data, which becomes the first entry in your new row.

If you change your loop to look like this.......

PHP Code:

while($row = mysql_fetch_assoc($results))  
{ 
        echo '<td><a href="' .$row[PICTURE_URL]. '" ><img src="' .$row[PICTURE_URL]. '" /></a><br>'; //Echo a link to the image, and display the image as well 
 
        echo 'Added to gallery:' .$row[PICTURE_DATE]. '</td>'; //display the dat it was added 
 
        $column_counter++; //Add one to the counter 
 
        if($column_counter == $column_limit)     
        { 
 
                echo '</tr><tr>'; //End current row, and start a new row 
                
                $column_counter = 0; //reset the column counter 
        
        }
 
} 


I believe the code will produce the desired results. Please, all html/php junkies, confirm this.