dynami links - HTML Forums

crazy8 · 05-09-2008, 09:47 AM

Well I am back with yet another little tweak I would like to figure out to do in my gallery. So I have links on a page that when clicked, they will display images. Thos links currently look like this...

PHP Code:


			
<a href="page.php?group=southdakota">South Dakota</a>

I would like to use the database to create the link so I dont have to add a link every time I create a new category. So lets say I re-write the group as "South Dakota" so the link looks like this...

PHP Code:


			
<a href="page.php?group=South Dakota">South Dakota</a>

how would I do this correctly? Would it be something like this...

PHP Code:


			
$sql = "SELECT * FROM images WHERE category='".mysql_real_escape_string($_GET['group'])."'";

$query = mysql_query($sql);

$count = mysql_num_rows($query);

if ($count => 1)

{        

echo '<a href="page.php?group=' .$rows['category']. '">' .$rows['id']. '</a>';

}

?>

I have tried this part...

PHP Code:


			
<a href="page.php?group=' .$rows['category']. '">' .$rows['id']. '</a>

without success so I know thats wrong somehow, just not sure how to pull it off.

Thanks for the help

ScareCrowe · 05-09-2008, 12:44 PM

In your example, in the echo statement '$rows' variable hasn't been defined anywhere, so it doesn't exist and has no value.
Also, your results will be a multi-dimensional array, so you will need to refer to them by the array key in addition, eg $rows[0][id] instead of $rows[id]

I think what you are looking for is something like this:

PHP Code:


			
$sql = "SELECT * FROM `images` WHERE `category`='" . mysql_real_escape_string( $_GET[ 'group' ] ) . "'";//might need urldecode on _GET[group]

$query = mysql_query( $sql );

if( mysql_num_rows( $query ) > 0 ){//if result count greater than zero

    $rows = mysql_fetch_assoc( $query );//assign results to variable

    foreach( $rows as $key => $link ){//loop thru results, assign each row to variable $link, and refer to each column: $link[ 'column_name' ]

        echo '<a href="page.php?group=' . $link[ 'category' ] . '">' . $link[ 'id' ] . '</a>';

    }

}

An observation: by using the category name as the value you are passing via GET, you will probably want to have some code to make sure the value is in the correct format. eg 'South Dakota' will probably get passed as 'South%20Dakota', which could muss up your results.

crazy8 · 05-15-2008, 07:43 AM

I never like doing this, but I am getting some bad results and just cant get it to work. Do you think you could take a look and see if you have any luck. This isnt the whole site, just three scripts. It would be greatly appreciated.

Thanks for your help.

ScareCrowe · 05-15-2008, 09:39 AM

Sure, I can take a peek at it if you would like.

I think what you are wanting is a page that will have a list of links to photo categories that when clicked will show a list of all photos in that category. The catch is you don't want to have to manually add a link for new categories. Is that right?
Then I think the best solution for you would be to create another table to store your categories something like:

`id` | `category_name`
------------------
1 | South Dakota
2 | Illinois
3 | Wyoming

Convert the 'category' field in your current image table from VARCHAR() to INT(), and use the category ID instead.

Now, you can query the category table to build your links:

PHP Code:


			
$sql .= "SELECT `category_name` FROM `category` ORDER BY `id` ASC";

$query = mysql_query( $sql );

if( mysql_num_rows( $query ) > 0 ){

    while( $row = mysql_fetch_assoc( $query ) ){

        echo '<a href="page.php?group=' . $row[ 'id' ] . '">' . $row[ 'category' ] . '</a>';

    }

}

This should produce links like so:
<a href="page.php?group=1">South Dakota</a>
<a href="page.php?group=2">Illinois</a>
<a href="page.php?group=3">Wyoming</a>

Now, here you have 2 options. You can do a simple query to poll only the data in the image table or you can use a more complex query to select data from both image and category tables.

For the first option, the sql you have now should do the trick, just need to change the echo statement to show images instead of links:

PHP Code:


			
$sql = "SELECT * FROM `images` WHERE `category`='" . $_GET[ 'group' ] . "'";

$query = mysql_query( $sql );

if( mysql_num_rows( $query ) > 0 ){

    while( $row = mysql_fetch_assoc( $query ) ){

        echo '<img src="' . $row[ 'img_path' ] . '" alt="">';

    }

}else{

        echo "Sorry no images found for that category!";

}

For the second option to include the category info in the image query, do a simple join like so:

PHP Code:


			
$sql = "SELECT * FROM `category`,`images` WHERE `images`.`category`=`category`.`id` AND `images`.`category`='" . $_GET[ 'group' ] . "'";

$query = mysql_query( $sql );

if( mysql_num_rows( $query ) > 0 ){

    while( $row = mysql_fetch_assoc( $query ) ){

        echo '<img src="' . $row[ 'img_path' ] . '" alt="' . $row[ 'category' ] . '">';

    }

}else{

        echo "Sorry no images found for that category!";

}

Take a look at this page and see if it is doing what you want your page to do:
http://scarecrowe.com/gallery/gallery.php

If you are wanting auto-creation of links for categories, I think the second table to store the category info is the way to go.
crazy8, Check your PMs.....

crazy8 · 05-15-2008, 12:55 PM

That is exactly what I am trying to do. Thanks for your help. I will see if I can piece this together and let ya know how it goes.
Thanks again

crazy8 · 05-16-2008, 08:38 AM

Ok so I have my new table set up now, but now I will have to do a join im assuming. So how do I change my INSERT statement for my upload form? I have look at alot of examples and just cant seem to find what I need.

PHP Code:


			
<style type="text/css">

fieldset {

background:#fff;

    width: 655px;

    }

#message {

    font-size: .95 em;

    color: #b80101;

    font-style: italic;

    font-weight: bold;

}

#delete {

    clear: both;

    width: 500px;

}

.menu {

    float:left;

}

</style>



<?php 

include_once ('db_connect_images.php'); 

?>



<?php

ini_set("memory_limit","100M");

//This page allows users to upload files tot he server

//Set the page title and include the header

$page_title = 'Upload an Image';



//include ('db_scripts/db_connect_insert_2.php');

//$db->connect('images');



if (isset($_POST['action']))

{



$folder=$_POST['folder'];

$thumbs=$_POST['thumbfolder'];



$category=$_POST['category'];



if (!file_exists($folder)) {

    mkdir($folder,0755,TRUE);

    mkdir($thumbs,0755,TRUE);

    }

if (file_exists($folder)) {

    

    $imageDir = $folder; //// root directory to main images folder with trailing slash

    $imageName = $imageDir . $_FILES['image_file']['name']; //// 

    

    if (move_uploaded_file($_FILES['image_file']['tmp_name'], $imageName))

    {    



        $array = explode(".", $_FILES['image_file']['name']);

        $nr    = count($array);

        $ext  = '.' . $array[$nr-1];    

    

        ///// Insert data into the DB

        $insert = "INSERT INTO images 

        (category, dir, thumb, ext, comment, date_added) 

        VALUES 

        ('" . $category . "', '" . $imageDir . "', '" . $thumbs . "', '" . $ext . "', '$_POST[comment]', NOW())";

        $insertresults = mysql_query($insert) or die(mysql_error());

        $lastpicid = mysql_insert_id();



        $newfilename =  $lastpicid . $ext;

        rename($imageName, $imageDir . $newfilename);

        include_once('class.thumbnail.php');

        

        ////// TAKE THE ORIGINAL IMAGE AND RESIZE IT TO A SLIGHTLY SMALLER ONE 

        

        //// go and get the uploaded image

        $large = new Thumbnail($folder . $newfilename . '');

        //// and resize it to dimensions equal to or smaller than 800x600

        $large->resize(800,600);

        //// now save it 

        $large->save($folder . $newfilename . '',150);

        //$large->destruct(); //only needed for PHP4



        ////// NOW TAKE THE NEWLY RESIZED IMAGE AND CREATE A THUMBNAIL FROM IT

        

        // go and get the resized image

        $thumb = new Thumbnail($folder . $newfilename . '');

        // resize it to no larger than 200x200

        $thumb->resize(150,150);

        // now to create thumbnails all the same size, create a 120x120 image, starting from the center

        //$thumb->cropFromCenter(200);

        $thumb->createReflection(0,0,0,true,'#ffffff');

        /// save the resized/cropped image to the thumbnails folder

        $thumb->save($thumbs . $newfilename . '',150);

        //$thumb->destruct(); //only needed for PHP4

        

        if ($insertresults)

        {

            $message = 'Image Added';

        }

         

        else 

        {

            $message = 'Image Failed';

        }

    }

    }

     else

    {

        $message = 'Image Failed to Upload';

    }    

}



if (isset($_POST['delete']))

{

    foreach ($_POST as $id => $value)

    {

        if (is_numeric($id) && $value == 'delete')

        {

            $sql = mysql_query("SELECT * FROM images WHERE id = '". $id ."'");

            $rows = mysql_fetch_array($sql);

            

            $query = "DELETE from images WHERE id = '" . $id . "'";

            $result = mysql_query($query);

            

            $largeimg = unlink($rows['dir'] . $id . $rows['ext'] . ''); //image/

            $thumbimg = unlink($rows['thumb'] . $id . $rows['ext'] . ''); //imagethumbs/    

        }        

    }

}

        

?>



<?php



echo '<div class = "nround" align="center">';

echo '<div class = "ntop"><h2>Upload Images</h2></div>';

echo '<div class = "nmiddle" align="center">';

echo '<fieldset class="display" align="center">';

echo '<div class="display" align="center" >';

$sql = "SELECT * FROM images ORDER BY id ASC";

$query = mysql_query($sql);

$count = mysql_num_rows($query);

if ($count == 0)

{

    echo 'You have not uploaded any pictures yet!';

}

else

{

    echo '   <form action="" method="POST">';

    while ($rows = mysql_fetch_array($query))

    {

        echo'<div style="border: 2px solid #fff; margin: 5px; float: left;">

<div>

<a href="'. $rows['dir'] . $rows['id'] . $rows['ext'] . '" rel="lightbox"><img src="'. $rows['thumb'] . $rows['id'] . $rows['ext'] . '"/></a>  

</div>

    <div>

    <input type="hidden" name="image_name" value="' . $rows['image_name'] . '">

    <input type="checkbox" name="' . $rows['id'] . '" value="delete">

    </div>

</div>';

    }

    echo '<div id="delete"><input type="submit" name="delete" value="Delete Images">    </div>

</form>';

}

echo '</div>';

echo '</fieldset>';



?>



<div align="center"><h2><font color="#b80101">Upload Images</font></h2></div>

    <form method="POST" enctype="multipart/form-data" name="image_upload_form" action="">

    <fieldset>

    <div align="left">

    <p>Image Folders Location <input type="text" size="32" name="folder" value="" /></p>

    <p>Thumbnail Folder Location <input type="text" size="32" name="thumbfolder" value="" /></p><br />

    <p>Place Images Were Taken <input type="text" size="32" name="category" value="" /></p>

    </div>

    

    <div align="left">

    <p>Upload Image: <input type="file" name="image_file" size="20"><br>

    <font size="1">Click browse to upload a local file</font><br>

    Comment: <textarea name="comment" rows="2" cols="25"></textarea>

    <br>

    <input type="hidden" name="type" value="multiple" />

    <input name="action" type="submit" value="Upload Image">

    <input type="hidden" name="type" value="multiple">

</div>

</fieldset>

</form>

Thanks so much for your help

ScareCrowe · 05-16-2008, 09:14 AM

Actually, you should not need to change your INSERT statement at all. The join part will come into play during SELECTS, not INSERTS.

What you want to do is modify the upload form to show a dropdown list of available categories instead. So you should query the DB for the categories, and change the 'category' field in the form to a select tag instead.

I would do something along lines of:

PHP Code:


			
<?php

# above removed for example

echo '</div>';

echo '</fieldset>';

#add sql code to generate list of categories

$sql = "SELECT `id`, `category` FROM `categories` ORDER BY `id` ASC";

$query = mysql_query( $sql );

$options = '';

while( $row = mysql_fetch_assoc( $query ) ){

    $options .= "<option value=\"" . $row[ 'id' ] . "\">" . $row[ 'category' ] . "</option>\n";

}

?>



<div align="center"><h2><font color="#b80101">Upload Images</font></h2></div>

    <form method="POST" enctype="multipart/form-data" name="image_upload_form" action="">

    <fieldset>

    <div align="left">

    <p>Image Folders Location <input type="text" size="32" name="folder" value="" /></p>

    <p>Thumbnail Folder Location <input type="text" size="32" name="thumbfolder" value="" /></p><br />

    <p>Place Images Were Taken <select name="category"><?php echo $options;?></select></p>

    </div>

    

    <div align="left">

    <p>Upload Image: <input type="file" name="image_file" size="20"><br>

    <font size="1">Click browse to upload a local file</font><br>

    Comment: <textarea name="comment" rows="2" cols="25"></textarea>

    <br>

    <input type="hidden" name="type" value="multiple" />

    <input name="action" type="submit" value="Upload Image">

    <input type="hidden" name="type" value="multiple">

</div>

</fieldset>

</form>

Let me know how that works out!

crazy8 · 05-19-2008, 12:52 PM

Well couldnt I leave the categories stuff the way it is? I set it up so that categories get created if its a new category.

crazy8 · 05-19-2008, 12:53 PM

Well couldnt I leave the categories stuff the way it is? I set it up so that categories get created if its a new category.

ScareCrowe · 05-19-2008, 04:37 PM

Quote:

Originally Posted by crazy8

Well couldnt I leave the categories stuff the way it is? I set it up so that categories get created if its a new category.

I suppose you could leave them like that, although compared to the solution I gave you, I am not really sure why you would want to.

To keep them the same way will need a different solution for getting them to list automatically.

PHP Code:


			
$sql .= "SELECT DISTINCT(`category`) as `category` FROM `images` ORDER BY `category` ASC";

$query = mysql_query( $sql );

if( mysql_num_rows( $query ) > 0 ){

    while( $row = mysql_fetch_assoc( $query ) ){

        echo '<a href="page.php?group=' . $row[ 'category' ] . '">' . $row[ 'category' ] . '</a>';

    }

}

As you can see, DISTINCT() will select all the unique values for column `category`.
The first catch here is, it is going to scan the whole image table every time you list the category links, compared to just the category table.
The second catch is that your tables will be larger. Storing a 2 or 3 digit number in the category column will use less space than something like 'South Dakota'.

I didn't test or put any data validation in it, but the code above should list your categories as links automatically in conjunction with the method you are using in your addition form.