Importing into Database

[Luke Hickton]

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

[Jon Erickson]

The date will need to be inputted as a 10 digit unix time stamp - the standard for database times and dates!

[Ashton]

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

[Luke Hickton]

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

[Ashton]

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 ?

[Ashton]

@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(); 
	?>
	

[Luke Hickton]

Fantastic! Thank you Ashton, Guides all over the internet don't mention when to use them and when to not!

[Ashton]

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.