Jump to content

Forums

Importing into Database


Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 ?

  • Thanks 1
Link to comment
Share on other sites

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

  • Thanks 1
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.