Luke Hickton Posted August 8, 2019 Share Posted August 8, 2019 Good Morning, I am currently looking at importing a wealth of information into the perscom_service _records table I tried a variety of methods through phpMyAdmin to import it including a manual test and I believe the date is causing us an issue. Our old database has recorded it as "01/10/2012 17:00" rather than the int(10) that perscom uses. Is there a method I can use to convert the dates or any method that would allow me to import all the information. We currently have 4400 records of just position appointments and resigning. I don't mind if the method is done through a php script or something, all data is currently held in a CSV file from our old database. King regards Luke Quote Link to comment Share on other sites More sharing options...
Administrators Jon Erickson Posted August 8, 2019 Administrators Share Posted August 8, 2019 The date will need to be inputted as a 10 digit unix time stamp - the standard for database times and dates! Quote Owner Deschutes Design Group LLC email | jon@deschutesdesigngroup.com Link to comment Share on other sites More sharing options...
Ashton Posted August 8, 2019 Share Posted August 8, 2019 Open the csv in excel and follow the below link. This will convert date and time into a 10 digit unix timestamp which is what you need and like Jon said is the standard for db's ? https://www.extendoffice.com/documents/excel/2473-excel-timestamp-to-date.html Quote Link to comment Share on other sites More sharing options...
Luke Hickton Posted August 8, 2019 Author Share Posted August 8, 2019 Unfortunately, I'm finding the old system is nothing like standard, it's been over years by various people adding bits and pieces and is a mess - hence the move to Perscom ? This is the code I'm using: <?php $db = new mysqli('servername', 'username', 'password', 'dbname'); if($db->connect_errno > 0){ die('Unable to connect to database [' . $db->connect_error . ']'); } $sql = "INSERT INTO perscom_service_records (service_records_soldier, service_records_text, service_records_date, service_records_document, service_records_notification, service_records_item_class, service_records_item_id, service_records_action) VALUES ('1', 'Appointed as Updates NCO (Army)', '1349110800', '5', '0', 'NULL', 'NULL', '1') ('1', 'Appointed as Unit Updates Officer (Unit Updates)', '1357149600', '5', '0', 'NULL', 'NULL', '1') ('1', 'Appointed as NCOC Instructor (Training)', '1349049600', '5', '0', 'NULL', 'NULL', '1') ('1', 'Appointed as Corps Administration Officer (Administration)', '1356998400', '5', '0', 'NULL', 'NULL', '1') ('1', 'Appointed as Officers Academy Registrar (Training)', '1393982097.9999998', '5', '0', 'NULL', 'NULL', '1') ('1', 'Appointed as Port Section Officer - Illustrious (Navy)', '1393113600', '5', '0', 'NULL', 'NULL', '1') ('1', 'Appointed as Unit Updates Officer (Navy)', '1393891200', '5', '0', 'NULL', 'NULL', '1') ('1', 'Appointed as Officers Academy Commanding Officer (Training)', '1404086400', '5', '0', 'NULL', 'NULL', '1') ('1', 'Appointed as Officers Academy Instructor (Training)', '1414540800', '5', '0', 'NULL', 'NULL', '1') ('1', 'Appointed as Forums Officer (Site Maintenance)', '1420502400', '5', '0', 'NULL', 'NULL', '1') ('1', 'Appointed as Logistics Officer (WCC)', '1452643200', '5', '0', 'NULL', 'NULL', '1') ('1', 'Appointed as Officers Academy Instructor (Training)', '1420588800', '5', '0', 'NULL', 'NULL', '1') "; if ($db->query($sql) === true) { echo "Records inserted successfully."; } else { echo "ERROR: Could not able to execute $sql. " .$db->error; } $db->close(); ?> and this is the error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '('1', 'Appointed as Unit Updates Officer (Unit Updates)', '1357149600', '5', '0'' at line 4 I will admit, this is one of my first ever attempts at coding some like this so, so I do apologise! Thanks for the help so far Luke Quote Link to comment Share on other sites More sharing options...
Ashton Posted August 8, 2019 Share Posted August 8, 2019 2 hours ago, Luke Hickton said: Unfortunately, I'm finding the old system is nothing like standard, it's been over years by various people adding bits and pieces and is a mess - hence the move to Perscom ? This is the code I'm using: <?php $db = new mysqli('servername', 'username', 'password', 'dbname'); if($db->connect_errno > 0){ die('Unable to connect to database [' . $db->connect_error . ']'); } $sql = "INSERT INTO perscom_service_records (service_records_soldier, service_records_text, service_records_date, service_records_document, service_records_notification, service_records_item_class, service_records_item_id, service_records_action) VALUES ('1', 'Appointed as Updates NCO (Army)', '1349110800', '5', '0', 'NULL', 'NULL', '1') ('1', 'Appointed as Unit Updates Officer (Unit Updates)', '1357149600', '5', '0', 'NULL', 'NULL', '1') ('1', 'Appointed as NCOC Instructor (Training)', '1349049600', '5', '0', 'NULL', 'NULL', '1') ('1', 'Appointed as Corps Administration Officer (Administration)', '1356998400', '5', '0', 'NULL', 'NULL', '1') ('1', 'Appointed as Officers Academy Registrar (Training)', '1393982097.9999998', '5', '0', 'NULL', 'NULL', '1') ('1', 'Appointed as Port Section Officer - Illustrious (Navy)', '1393113600', '5', '0', 'NULL', 'NULL', '1') ('1', 'Appointed as Unit Updates Officer (Navy)', '1393891200', '5', '0', 'NULL', 'NULL', '1') ('1', 'Appointed as Officers Academy Commanding Officer (Training)', '1404086400', '5', '0', 'NULL', 'NULL', '1') ('1', 'Appointed as Officers Academy Instructor (Training)', '1414540800', '5', '0', 'NULL', 'NULL', '1') ('1', 'Appointed as Forums Officer (Site Maintenance)', '1420502400', '5', '0', 'NULL', 'NULL', '1') ('1', 'Appointed as Logistics Officer (WCC)', '1452643200', '5', '0', 'NULL', 'NULL', '1') ('1', 'Appointed as Officers Academy Instructor (Training)', '1420588800', '5', '0', 'NULL', 'NULL', '1') "; if ($db->query($sql) === true) { echo "Records inserted successfully."; } else { echo "ERROR: Could not able to execute $sql. " .$db->error; } $db->close(); ?> and this is the error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '('1', 'Appointed as Unit Updates Officer (Unit Updates)', '1357149600', '5', '0'' at line 4 I will admit, this is one of my first ever attempts at coding some like this so, so I do apologise! Thanks for the help so far Luke Ill run this on my test enviroment soon and find the error, hard to find on my phone ? 1 Quote Link to comment Share on other sites More sharing options...
Ashton Posted August 8, 2019 Share Posted August 8, 2019 @Luke Hickton See below - you need to remove the ' ' around the values apart from service record name and ensure there is a , at the end of each line. Tested on my environment and it works fine. <?php $db = new mysqli('servername', 'user', 'password', 'dbname'); if($db->connect_errno > 0){ die('Unable to connect to database [' . $db->connect_error . ']'); } $sql = "INSERT INTO perscom_service_records (service_records_soldier, service_records_text, service_records_date, service_records_document, service_records_notification, service_records_item_class, service_records_item_id, service_records_action) VALUES (1, 'Appointed as Updates NCO (Army)', 1349110800, 5, 0, NULL, NULL, 1), (1, 'Appointed as Unit Updates Officer (Unit Updates)', 1357149600, 5, 0, NULL, NULL, 1), (1, 'Appointed as NCOC Instructor (Training)', 1349049600, 5, 0, NULL, NULL, 1), (1, 'Appointed as Corps Administration Officer (Administration)', 1356998400, 5, 0, NULL, NULL, 1), (1, 'Appointed as Officers Academy Registrar (Training)', 1393982097, 5, 0, NULL, NULL, 1), (1, 'Appointed as Port Section Officer - Illustrious (Navy)', 1393113600, 5, 0, NULL, NULL, 1), (1, 'Appointed as Unit Updates Officer (Navy)', 1393891200, 5, 0, NULL, NULL, 1), (1, 'Appointed as Officers Academy Commanding Officer (Training)', 1404086400, 5, 0, NULL, NULL, 1), (1, 'Appointed as Officers Academy Instructor (Training)', 1414540800, 5, 0, NULL, NULL, 1), (1, 'Appointed as Forums Officer (Site Maintenance)', 1420502400, 5, 0, NULL, NULL, 1), (1, 'Appointed as Logistics Officer (WCC)', 1452643200, 5, 0, NULL, NULL, 1), (1, 'Appointed as Officers Academy Instructor (Training)', 1420588800, 5, 0, NULL, NULL, 1) "; if ($db->query($sql) === true) { echo "Records inserted successfully."; } else { echo "ERROR: Could not able to execute $sql. " .$db->error; } $db->close(); ?> 1 Quote Link to comment Share on other sites More sharing options...
Luke Hickton Posted August 9, 2019 Author Share Posted August 9, 2019 Fantastic! Thank you Ashton, Guides all over the internet don't mention when to use them and when to not! Quote Link to comment Share on other sites More sharing options...
Ashton Posted August 9, 2019 Share Posted August 9, 2019 5 hours ago, Luke Hickton said: Fantastic! Thank you Ashton, Guides all over the internet don't mention when to use them and when to not! Happy to help dude. Documentation on some sql and php stuff is sparse, you will eventually pick it up with experience. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.