Create Event by Importing JSON files in Open Event Server

Apart from the usual way of creating an event in  FOSSASIA’s Orga Server project by using POST requests in Events API, another way of creating events is importing a zip file which is an archive of multiple JSON files. This way you can create a large event like FOSSASIA with lots of data related to sessions, speakers, microlocations, sponsors just by uploading JSON files to the system. Sample JSON file can be found in the open-event project of FOSSASIA. The basic workflow of importing an event and how it works is as follows: First step is similar to uploading files to the server. We need to send a POST request with a multipart form data with the zipped archive containing the JSON files. The POST request starts a celery task to start importing data from JSON files and storing them in the database. The celery task URL is returned as a response to the POST request. You can use this celery task for polling purposes to get the status. If the status is FAILURE, we get the error text along with it. If status is SUCCESS we get the resulting event data In the celery task, each JSON file is read separately and the data is stored in the db with the proper relations. Sending a GET request to the above mentioned celery task, after the task has been completed returns the event id along with the event URL. Let’s see how each of these points work in the background. Uploading ZIP containing JSON Files For uploading a zip archive instead of sending a JSON data in the POST request we send a multipart form data. The multipart/form-data format of sending data allows an entire file to be sent as a data in the POST request along with the relevant file informations. One can know about various form content types here . An example cURL request looks something like this: curl -H "Authorization: JWT <access token>" -X POST -F 'file=@event1.zip' http://localhost:5000/v1/events/import/json The above cURL request uploads a file event1.zip from your current directory with the key as ‘file’ to the endpoint /v1/events/import/json. The user uploading the feels needs to have a JWT authentication key or in other words be logged in to the system as it is necessary to create an event. @import_routes.route('/events/import/<string:source_type>', methods=['POST']) @jwt_required() def import_event(source_type): if source_type == 'json': file_path = get_file_from_request(['zip']) else: file_path = None abort(404) from helpers.tasks import import_event_task task = import_event_task.delay(email=current_identity.email, file=file_path, source_type=source_type, creator_id=current_identity.id) # create import job create_import_job(task.id) # if testing if current_app.config.get('CELERY_ALWAYS_EAGER'): TASK_RESULTS[task.id] = { 'result': task.get(), 'state': task.state } return jsonify( task_url=url_for('tasks.celery_task', task_id=task.id) ) After the request is received we check if a file exists in the key ‘file’ of the form-data. If it is there, we save the file and get the path to the saved file. Then we send this path over to the celery task and run the task with the .delay() function of celery. After the celery task is started, the corresponding data about the import job is…

Continue ReadingCreate Event by Importing JSON files in Open Event Server

Import Excel File Data in MYSQL Database using PHP

In this post I will explain how to Import Excel Sheet Data in MySQL Database using PHP. If you follow the below steps we will successfully achieve the target. For this tutorial we are going to work with a sample CSV file, which has the following fields. I will show an example of User Table of Engelsystem which contains the following fields Nick Name, First Name, Last Name, Email, Current City, Password, Mobile Number, Age. Steps to Import Excel File Data in MYSQL Database using PHP Step 1 First you have to create mysql database. mysql> CREATE DATABASE engelsystem; Step 2 Create table in your choosen database. mysql> use DATABASE engelsystem; The Table schema looks something like this. -- Table structure for table `User` mysql> CREATE TABLE IF NOT EXISTS `User` ( `UID` int(11) NOT NULL AUTO_INCREMENT, `Nick` varchar(23) NOT NULL DEFAULT '', `First Name` varchar(23) NOT NULL DEFAULT '', `Last Name` varchar(23) NOT NULL DEFAULT '', `email` varchar(123) DEFAULT NULL, `Age` int(4) DEFAULT NULL, `current_city` varchar(255) DEFAULT NULL, `Password` varchar(128) DEFAULT NULL, `Mobile` varchar(40) DEFAULT NULL, PRIMARY KEY (`UID`), UNIQUE KEY `Nick` (`Nick`), ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ; Step 3 create your php excelsheet data uploading file. This is a sample code which I used for my project. Ex: import_data.php <?php function admin_import() { if (isset($_REQUEST['upload'])) { $ok = true; $file = $_FILES['csv_file']['tmp_name']; $handle = fopen($file, "r"); if ($file == NULL) { error(_('Please select a file to import')); redirect(page_link_to('admin_export')); } else { while(($filesop = fgetcsv($handle, 1000, ",")) !== false) { $nick_name = $filesop[0]; $first_name = $filesop[1]; $last_name = $filesop[2]; $email = $filesop[3]; $age = $filesop[4]; $current_city = $filesop[5]; $password = $filesop[6]; $mobile = $filesop[7]; // example error handling. We can add more as required for the database. if ( strlen($email) && preg_match("/^[a-z0-9._+-]{1,64}@(?:[a-z0-9-]{1,63}\.){1,125}[a-z]{2,63}$/", $mail) > 0) { if (! check_email($email)) { $ok = false; $msg .= error(_("E-mail address is not correct."), true); } } // error handling for password if (strlen($password) >= MIN_PASSWORD_LENGTH) { $ok = true; } else { $ok = false; $msg .= error(sprintf(_("Your password is too short (please use at least %s characters)."), MIN_PASSWORD_LENGTH), true); } // If the tests pass we can insert it into the database. if ($ok) { $sql = sql_query(" INSERT INTO `User` SET `Nick Name`='" . sql_escape($nick_name) . "', `First Name`='" . sql_escape($first_name) . "', `Last Name`='" . sql_escape($last_name) . "', `email`='" . sql_escape($email) . "', `Age`='" . sql_escape($age) . "', `current_city`='" . sql_escape($current_city) . "', `Password`='" . sql_escape($password) . "', `mobile`='" . sql_escape($mobile) . "',"); } } if ($sql) { success(_("You database has imported successfully!")); redirect(page_link_to('admin_export')); } else { error(_('Sorry! There is some problem in the import file.')); redirect(page_link_to('admin_export')); } } } //form_submit($name, $label) Renders the submit button of a form //form_file($name, $label) Renders a form file box return page_with_title("Import Data", array( msg(), div('row', array( div('col-md-12', array( form(array( form_file('csv_file', _("Import user data from a csv file")), form_submit('upload', _("Import")) )) )) )) )); } ?> Step 4 The view of import_data.php looks something like this. Now that import_data.php is up and…

Continue ReadingImport Excel File Data in MYSQL Database using PHP