Storing files and images in mysql database

Biggest advantage of this approach is, you are not required to maintain a seperate directory for documents or images and this proves to be really handy in case you move directories or change servers. You need not worry about the links from the database. Also you need not worry about any complex version control systems to store documents with a same name.

1. Create a table

1. id – INT (6)
2. filename – VARCHAR (100)
3. filetype – VARCHAR (50)
4. filesize – VARCHAR (20)
5. file – BLOB or MEDIUM BLOB

2. File upload (uploadfile.php)

3. Process the above form (procesform.php)

4. Get file (get-file.php)

5. Download a file

Now to download a file just link the file to get-file.php with an id
e.g.

 

Cloning a database record in MySQL

I thought that cloning a database record should be easy until I decided to write a query for it.

I then realized that there is no simple query which clones a record.

The steps though are pretty simple:
1. Select the record you want to clone
2. Insert the values in the table

Example
Lets consider you have an events management database where the primary key is the eventid and you would like to clone the events frequently

First of all, here is the query you would use to select the event you want to clone

Now let’s clone the record

Thus, we have combined INSERT and SELECT query in one query.

But there is a problem in this query. When you try running this query, you might get a “duplicate key” error. That’s because the record clones everything including the primary key which is the eventid.

To avoid that, we need to change our steps as follows
1. Create a temporary table
2. Select the record to be cloned and store it in a temporary table
3. Update the primary key in the temporary table so that its not duplicate
4. Insert the record from the temporary table into the main table
5. Drop temporary table

And here’s the query

 

Import CSV data to MySQL table

Here is a simple code to import CSV data into a MySQL table.
There is no need to use MySQL frontend.
Below is web based form to select a CSV file and import the records in a MySQL table

Step 1: add.php First of all create a form to select the file
Note that the values of the select box should match the table names in the MySQL database e.g. table_name_A, table_name_B, etc

Step 2: config.php Create a config file
Always a good idea to create a config file to declare your database connection and file import settings.

Step 3: import.php Import the data