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) [crayon-59e6eb9f775ab327136341/] 3. Process the above form (procesform.php) [crayon-59e6eb9f775b7047135387/] 4. Get file (get-file.php) [crayon-59e6eb9f775bd601961245/] 5. Download a file Now to download a file just link the file to get-file.php with an id e.g. [crayon-59e6eb9f775c1922321432/]  ...
Read More

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 [crayon-59e6eb9f77de1997451522/] Now let's clone the record [crayon-59e6eb9f77dea039862177/] 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...
Read More

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 [crayon-59e6eb9f780be786052492/] 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. [crayon-59e6eb9f780c6028426865/] Step 3: import.php Import the data [crayon-59e6eb9f780cb477810720/]  ...
Read More