Forum Home
Press F1
 
Thread ID: 73632 2006-10-27 01:29:00 MYSql - remove all records from table- but leave index set for next record Morgenmuffel (187) Press F1
Post ID Timestamp Content User
494683 2006-10-27 01:29:00 Hi all

What I am needing to do is delete all the records in a table, but leave the index intact

Ok i am explaining it badly

lets say i have 5 records in the database
and i delete all 5, then decide to add a new record i want the new records Index to be 6, but with the query i am using (see below)

$query = "TRUNCATE table images"

It is coming out as record number 1

I realise it is probably obvious, but with a screaming 1 month old baby next to me, i can't really think that clearly 		Morgenmuffel (187)
494684 2006-10-27 01:54:00 If you have an id / index field defined as NOT NULL AUTO_INCREMENT in the table, your new record should get the sequential id / index when you insert new records using INSERT INTO etc (although the field is NOT NULL, do not specify its value in your query).

I do not know much about MySQL, thus not sure if this would help... 		developer (4447)
494685 2006-10-27 03:10:00 Thats what i figured but it doesn't seem to work that way

truncate wipes everything including the record of where the index is at, forcing it to start again, basically i have to do delete everything in the table but ensure the index record thingy doesn't get wiped,


I think i'l try something like

DELETE from images where id > 0

That should leave the index record intact (I hope)


I know it is obvious but coding with a baby in the house is not easy, your brain gets turned to mush and concentrating is a problem with random cries all around you 		Morgenmuffel (187)
494686 2006-10-27 03:35:00 Just use:

DELETE FROM images;

This will match all records as there are no selection criteria.

P.S. Why are you doing this? 		TGoddard (7263)
494687 2006-10-27 03:56:00 I'll try that


Why am i doing it. it's a little complicated

My client has an online classroom
at every step of the lesson there is one record containing the instructions, image name and location of the associated image file
currently the way they are stored is
the first record will have an image called 1.jpg (renamed to match the record on upload)
the second will have 2.jpg
etc

Up until recently we used to just truncate the table and unlink the image files
when we wanted to clear a classroom
But now we want to move the images to another section when the class is finished,
if i truncate the table it sets the index back to one, so when the next record for the new class is added it will be called record 1 and the associated image is 1.jpg, when i go to transfer these to the new location, it will overwrite the existing 1.jpg, which I don't want

Cheers 		Morgenmuffel (187)
494688 2006-10-27 06:23:00 Just use:

DELETE FROM images;



Nope that sets the index back to one, there must be a way of doing it 		Morgenmuffel (187)
494689 2006-10-27 07:26:00 Is this what you're trying to do?

UPDATE table_name SET row_name = 6 		Breezzee (2913)
494690 2006-10-27 09:49:00 You would be much better off with a more flexible system.

Since you talk about uploading I presume this is a database for a web application or web service. You could add a classes table, add a foreign key to the images table referring to the class and select the images for the current class from your application. Take this as an example:

CREATE TABLE classes (
id INT UNSIGNED AUTO_INCREMENT,
name VARCHAR(255),
start_date DATE,
end_date DATE,
PRIMARY KEY (id)
) ENGINE=MyISAM;

CREATE TABLE images (
id INT UNSIGNED AUTO_INCREMENT,
class_id INT UNSIGNED NOT NULL REFERENCES classes(id),
caption TEXT,
file_name VARCHAR(255),
PRIMARY KEY (id)
) ENGINE=MyISAM;

You can find all current classes easily:

SELECT * FROM classes WHERE start_date < NOW() AND end_date > NOW();

You can easily find the images for a class (insert appropriate class id):

SELECT * FROM images WHERE class_id = <class id>;

The general rule with databases is to retain data wherever possible. You might want to add an archives section for previous classes at a later date. Data is valuable.

If I were you I would also randomize the file names. You can take the extension off the original file (limit this to alphabetic characters) and append it to a random name. This prevents people from deliberately manipulating the provided file name to attempt a security compromise. Even the people uploading the images shouldn't necessarily have full access to your account on the server :) 		TGoddard (7263)
494691 2006-10-27 09:56:00 Nope that sets the index back to one, there must be a way of doing it

BTW, this violates a common principle of database design. The primary key should be independent of the actual data held in the table and should be able to be arbitrarily allocated. The primary key is effectively only an internal database reference and should not be used other than to refer to database records. Even numbers such as member IDs or registration numbers are often kept in a separate column. I would use random file names and store the file name in the table. 		TGoddard (7263)
494692 2006-10-27 09:56:00 Have you considered keeping the index programmatically?
Instead of letting it automatically increases, manually assign a value to the index of the first record (e.g. 1). And then the index for every future record is SELECT MAX index of all the current records + whatever the increment is (e.g. 5 + 1 = 6, specify index = 6 when using INSERT INTO). Get the SELECT MAX value first if you want to delete all the current records (in the case mentioned, it would be getting 5 + 1 = 6 first, then delete all records, then insert the new record with index = 6). 		developer (4447)
1 2