Display images from database with Delete option.

[AON07]

Okay i'm getting the basics of php but I have no idea where to even start to code this
idea... I have a database "DB_Name" and a table "images"
This table has the following columns (id, category, image name, image path, caption)

Keep in mind all my images are stored in a folder (NOT in the DB)
so the information in the DB would look like this.

(1, category1, image1.jpg, images/, caption discription)

this is what i'm trying to accomplish, I want to be able to delete the images from the folder and the database.

1. Query the database and display the categories listed in the 'category' as a hyperlink so that i may select certain categories to browse through to delete images only in that category.

I believe this code should do this just fine. but i haven't tested it yet so if you see any mistakes let me know.

Display categories to choose from:

<?php
include 'config.php';

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

$sql = mysql_query("SELECT id,category FROM images ORDER BY category"); 
$row = mysql_fetch_array($sql);

while($row = mysql_fetch_array($sql));

echo <a href="displaycategory.php?name=$row">$row</a>
?>

2. when the hyperlink is clicked i want to query the database and display all of the images with that specific category name using pagination. I also want a delete hyperlink displayed under each image.

3. when the delete hyperlink is clicked i want it to remove the image from the folder & the Database..

Can anyone point me in the right direction or if you have some example code i could use to put it all together. any help would be greatly appreciated.

[digital-ether]

Okay i'm getting the basics of php but I have no idea where to even start to code this
idea... I have a database "DB_Name" and a table "images"
This table has the following columns (id, category, image name, image path, caption)

Keep in mind all my images are stored in a folder (NOT in the DB)
so the information in the DB would look like this.

(1, category1, image1.jpg, images/, caption discription)

this is what i'm trying to accomplish, I want to be able to delete the images from the folder and the database.

1. Query the database and display the categories listed in the 'category' as a hyperlink so that i may select certain categories to browse through to delete images only in that category.

I believe this code should do this just fine. but i haven't tested it yet so if you see any mistakes let me know.

Display categories to choose from:

<?php
include 'config.php';

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

$sql = mysql_query("SELECT id,category FROM images ORDER BY category"); 
$row = mysql_fetch_array($sql);

while($row = mysql_fetch_array($sql));

echo <a href="displaycategory.php?name=$row">$row</a>
?>

2. when the hyperlink is clicked i want to query the database and display all of the images with that specific category name using pagination. I also want a delete hyperlink displayed under each image.

3. when the delete hyperlink is clicked i want it to remove the image from the folder & the Database..

Can anyone point me in the right direction or if you have some example code i could use to put it all together. any help would be greatly appreciated.

The code you have looks ok, apart from a syntax errors, that you probably know.

<?php
include 'config.php';

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

$sql = mysql_query("SELECT id,category FROM images ORDER BY category"); 


while($row = mysql_fetch_array($sql)) {

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

}
?>

You probably want to have the category ID in links, so on the page that displays categories you select the category based on its ID, and not its name (which is non-unique).

in displaycategory.php

<?php
include 'config.php';

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

// you'll need some form of pagination
$start = intval(isset($_GET['start']) ? $_GET['start'] : 0);
$limit = intval(isset($_GET['limit']) ? $_GET['limit'] : 50);

// select the images in this category limited by that set in pagination
$sql = mysql_query("SELECT id, title, thumb_path FROM images WHERE category_id = ".intval($_GET['id'])." ORDER BY title, LIMIT $start, $limit"); 


while($row = mysql_fetch_array($sql)) {

// displays image
echo '<a href="display_full_image.php?id='.$row['id'].'"><img src="'.$row['thumb_path'].'" /></a>';
// display delete button
echo '<a href="delete_image.php?id='.$row['id'].'">Delete</a>';

}


// your pagination code goes here.. 

?>

then in your delete_image.php

You need to query the db for the image path, so you can unlink it from the filesystem:

$query = "SELECT path from images where id = ".intval($_GET['id'])." LIMIT 1";

Then use the unlink() function to delete the given file.
http://www.php.net/manual/en/function.unlink.php

For deleting you can have a simple SQL query:

$query = "delete from images where id = ".intval($_GET['id'])." LIMIT 1";

Note, having "limit 1" in there is also a good practice. It improves the SQL query since MySQL will know to stop searching for id=? when it has found just one. Otherwise it has to scan the whole dataset. (Not much difference here, but worse with complex queries).

After making the delete query, check to see how may rows it actually affected, with

mysql_affected_rows()

http://www.php.net/function.mysql-affected-rows

That way you can display an ok if it worked, or error message if there wasn't any image to delete by that id.

:)

[AON07]

Thank you for the code you supplied, I think it gave me good starting point to start tweaking it to my exact needs. However i still need some assistance, I messed with this all weekend and I seemed to just make it worse. please help..

On the Deleteimage.php page i used the following code.

<?php
include 'config.php';

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

$sql = mysql_query("SELECT id, category FROM images ORDER BY category"); 

while($row = mysql_fetch_array($sql))
{
echo '<a href="displaycategory.php?id='.$row['id'].'">'.$row['category'].'</a>';
}
?>

Here are the things I need help fixing with the way this script displays it's results.

1. it is displaying all links in a row.

2. it is repeating and listing multiple links for the same category.

3. it needs to display one link for each category found in the database.
in a vertical column assorted from A-Z.

Basically these "categories" are photo albums. and in each photo album you will have multiple images.. so I will have multiple lisitings in my DB with the same category.
I want to just display one link for each category and then when that link is clicked i want it to display the images associated with that category.

Now for the second part. in the displaycategory.php I am using the following code.

<?php
include 'config.php';

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

//You'll need some form of pagination
$start = intval(isset($_GET['start']) ? $_GET['start'] : 0);
$limit = intval(isset($_GET['limit']) ? $_GET['limit'] : 50);

// select the images in the category
//limited by that set in pagination

$sql = mysql_query("SELECT id, image_name, image_path FROM images WHERE category = ".intval($_GET['id'])." ORDER BY image_name, LIMIT $start, $limit");

$row = mysql_fetch_array($sql); {

//displays image
echo '<a href="display_full_image.php?id='.$row['id'].'"><img src="'.$row['image_path'].$row['image_name'].'" /></a>';
//display delete button
echo '<a href="delete_image.php?id='.$row['id'].'">Delete</a>';

}

//Pagination code goes here..

?>

I am having the following problems with the code above.

1. Getting the following error when link from Deleteimage.php is clicked.
"WARNING: mysql_fetcharray(): supplied argument is not valid MySQL result resource in displaycategory.php on line 87.

2. Line 87 reads. $row = mysql_fetch_array($sql); {
(look on line #17 in the pasted code)

3. Image is not shown, only a box with a red X indicating that the image can not be found.
My database has two seperate columns one for the directory folder and one for the image name. So i think that the script just isn't putting the path together incorrectly.

4. When Category link is clicked it should display all images with in that
category in the database.

5. Need a good place for a pagination script.

6. This is just a thought if it would make things easier.....
Either when the Delete link is clicked the page needs to refresh back to the
same page with out showing the image that was just deleted. Unless it would
be easier to just diplay a "check box" next to each image with one delete button
so the delete query could just be submitted once but preform multiple delete's.

Sorry this is so long, unfortunatly what i'm trying to accomplish is outside my range of knowledge right now. Thank you in advance.