I have tried to build a script for uploading a .csv master csv in file in mysql. But PHP is given me error while executing it.
PHP Notice: Undefined offset: 76 in /home/vikas/importcdr.php on line 45
PHP Notice: Undefined offset: 75 in /home/vikas/importcdr.php on line 45
PHP Notice: Undefined offset: 74 in /home/vikas/importcdr.php on line 45
PHP Notice: Undefined offset: 73 in /home/vikas/importcdr.php on line 45
PHP Notice: Undefined offset: 72 in /home/vikas/importcdr.php on line 45
PHP Notice: Undefined offset: 71 in /home/vikas/importcdr.php on line 45
PHP Notice: Undefined offset: 70 in /home/vikas/importcdr.php on line 45
PHP Notice: Undefined offset: 69 in /home/vikas/importcdr.php on line 45
PHP Notice: Undefined offset: 68 in /home/vikas/importcdr.php on line 45
PHP Notice: Undefined offset: 67 in /home/vikas/importcdr.php on line 45
PHP Notice: Undefined offset: 66 in /home/vikas/importcdr.php on line 45
and sql too giving error.
Invalid query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘iN[OWW ]OVOioeveFiG]dw*wez_M_UUOGenynODE_ID, MMSC_ENTITY, EVENT_TYPE, SDR_TYPE, ‘ at line 1
SQL: iN[OWW ]OVOioeveFiG]dw
Please check and help where is the mistake.
<?php
/*** process cdr file (
* values into a mysql database which is created for use
* The script will only insert NEW records so it is safe
*
/
/
* Modifications and suggestions are welcome
*/
$locale_db_host = ‘localhost’;
$locale_db_name = ‘DB_Name’;
$locale_db_login = ‘user’;
$locale_db_pass = ‘password’;
if($argc == 2) {
$logfile = $argv[1];
} else {
print(“Usage “.$argv[0].” <filename>n”);
print(“Where filename is the path to the MMSC CDR’s
print(“This script is safe to run multiple times on a growing log file as it only imports records that are newer than the databasen”);
exit(0);
}
// connect to db
$linkmb = mysql_connect($locale_db_host, $locale_db_login, $locale_db_pass) or die(“Could not connect : ” . mysql_error());
mysql_select_db($locale_db_name, $linkmb) or die(“Could not select database $locale_db_name”);
//**
$rows = 0;
$handle = fopen($logfile, “r”);
while (($data = fgetcsv($handle, 1000, “,”)) !== FALSE) {
// NOTE: the fields in Master.csv can vary. This should work by default on all installations but you may have to edit the next line to match your configuration
list($SEQUENCE, $NODE_ID, $MMSC_ENTITY, $EVENT_TYPE, $SDR_TYPE, $SDR_OPERATION_TIME_LOCAL, $OPERATION_STATUS, $CAUSE_CODE, $CAUSE_DETAIL, $VERSION_ID, $MSG_ID, $SUBMIT_TIME_LOCAL, $VALIDITY_PERIOD, $DEFER_PERIOD, $MESSAGE_SIZE, $NUMBER_OF_RECIPIENTS, $CONTENT_TYPE, $TRANSCODED, $MESSAGE_CLASS, $MESSAGE_PRIORITY, $ENABLED_FEATURES, $MESSAGE_DELIVERY_RESULT, $A_IMSI, $A_TYPE, $A_DOMAIN, $A_MSISDN, $A_ALIAS, $A_PREFERRED_LANGUAGE, $A_PREPAID_IND, $A_SUB_ID, $A_COS, $A_COI, $B_IMSI, $B_TYPE, $B_DOMAIN, $B_MSISDN, $B_ALIAS, $B_PREFERRED_LANGUAGE, $B_PREPAID_IND, $B_COS, $B_SUB_ID, $B_COI, $B_BOX, $VAS_BOX, $SECURITY_LEVEL, $VAS_MSISDN, $VAS_BILLING_CODE, $VAS_COI, $TRANSACTION_ID, $NAS_IP, $REMOTE_IP, $CONTEXT_ID, $ACCESS_DURATION, $URI, $HOST_IN, $HOST_OUT, $NOTIFICATION_TYPE, $BEARER_TYPE, $CHARGED_PARTY, $CHARGED_PARTY_IND, $TARIFF, $PREPAID_STATUS, $MSISDN, $RECURRENT_CHARGES, $PBS_STATUS_CODE, $A_SGSN_IP, $B_SGSN_IP, $A_SGSN_OPERATOR_ID, $B_SGSN_OPERATOR_ID, $A_MSC_VLR_ID, $B_MSC_VLR_ID, $A_ROAMING_CLASS, $B_ROAMING_CLASS, $PDU_SIZE, $TRANSFORMED_SIZE, $READ_STATUS, $CFD_CONDITION ) = $data;
/
$sql=”SELECT EVENT_TYPE, MSG_ID, TRANSACTION_ID”.
” FROM cdr”.
” WHERE EVENT_TYPE=’$EVENT_TYPE'”.
” AND MSG_ID=’$MSG_ID'”.
” AND TRANSACTION_ID=’$TRANSACTION_ID'”.
” LIMIT 1″;
if(!($result = mysql_query($sql, $linkmb))) {
print(“Invalid query: ” . mysql_error().”n”);
print(“SQL: $sqln”);
die();
}
if(mysql_num_rows($result) == 0) { // we found a new record so add it to the DB
// 3) insert each row in the database
$sql = “INSERT INTO cdr FIELDS TERMINATED BY “|” LINES TERMINATED BY ‘n’ (SEQUENCE, NODE_ID, MMSC_ENTITY, EVENT_TYPE, SDR_TYPE, SDR_OPERATION_TIME_LOCAL, OPERATION_STATUS, CAUSE_CODE, CAUSE_DETAIL, VERSION_ID, MSG_ID, SUBMIT_TIME_LOCAL, VALIDITY_PERIOD, DEFER_PERIOD, MESSAGE_SIZE, NUMBER_OF_RECIPIENTS, CONTENT_TYPE, TRANSCODED, MESSAGE_CLASS, MESSAGE_PRIORITY, ENABLED_FEATURES, MESSAGE_DELIVERY_RESULT, A_IMSI, A_TYPE, A_DOMAIN, A_MSISDN, A_ALIAS, A_PREFERRED_LANGUAGE, A_PREPAID_IND, A_SUB_ID, A_COS, A_COI, B_IMSI, B_TYPE, B_DOMAIN, B_MSISDN, B_ALIAS, B_PREFERRED_LANGUAGE, B_PREPAID_IND, B_COS, B_SUB_ID, B_COI, B_BOX, VAS_BOX, SECURITY_LEVEL, VAS_MSISDN, VAS_BILLING_CODE, VAS_COI, TRANSACTION_ID, NAS_IP, REMOTE_IP, CONTEXT_ID, ACCESS_DURATION, URI, HOST_IN, HOST_OUT, NOTIFICATION_TYPE, BEARER_TYPE, CHARGED_PARTY, CHARGED_PARTY_IND, TARIFF, PREPAID_STATUS, MSISDN, RECURRENT_CHARGES, PBS_STATUS_CODE, A_SGSN_IP, B_SGSN_IP, A_SGSN_OPERATOR_ID, B_SGSN_OPERATOR_ID, A_MSC_VLR_ID, B_MSC_VLR_ID, A_ROAMING_CLASS, B_ROAMING_CLASS, PDU_SIZE, TRANSFORMED_SIZE, READ_STATUS, CFD_CONDITION) VALUES(”$SEQUENCE’, ‘$NODE_ID’, ‘$MMSC_ENTITY’, ‘$EVENT_TYPE’, ‘$SDR_TYPE’, ‘$SDR_OPERATION_TIME_LOCAL’, ‘$OPERATION_STATUS’, ‘$CAUSE_CODE’, ‘$CAUSE_DETAIL’, ‘$VERSION_ID’, ‘$MSG_ID’, ‘$SUBMIT_TIME_LOCAL’, ‘$VALIDITY_PERIOD’, ‘$DEFER_PERIOD’, ‘$MESSAGE_SIZE’, ‘$NUMBER_OF_RECIPIENTS’, ‘$CONTENT_TYPE’, ‘$TRANSCODED’, ‘$MESSAGE_CLASS’, ‘$MESSAGE_PRIORITY’, ‘$ENABLED_FEATURES’, ‘$MESSAGE_DELIVERY_RESULT’, ‘$A_IMSI’, ‘$A_TYPE’, ‘$A_DOMAIN’, ‘$A_MSISDN’, ‘$A_ALIAS’, ‘$A_PREFERRED_LANGUAGE’, ‘$A_PREPAID_IND’, ‘$A_SUB_ID’, ‘$A_COS’, ‘$A_COI’, ‘$B_IMSI’, ‘$B_TYPE’, ‘$B_DOMAIN’, ‘$B_MSISDN’, ‘$B_ALIAS’, ‘$B_PREFERRED_LANGUAGE’, ‘$B_PREPAID_IND’, ‘$B_COS’, ‘$B_SUB_ID’, ‘$B_COI’, ‘$B_BOX’, ‘$VAS_BOX’, ‘$SECURITY_LEVEL’, ‘$VAS_MSISDN’, ‘$VAS_BILLING_CODE’, ‘$VAS_COI’, ‘$TRANSACTION_ID’, ‘$NAS_IP’, ‘$REMOTE_IP’, ‘$CONTEXT_ID’, ‘$ACCESS_DURATION’, ‘$URI’, ‘$HOST_IN’, ‘$HOST_OUT’, ‘$NOTIFICATION_TYPE’, ‘$BEARER_TYPE’, ‘$CHARGED_PARTY’, ‘$CHARGED_PARTY_IND’, ‘$TARIFF’, ‘$PREPAID_STATUS’, ‘$MSISDN’, ‘$RECURRENT_CHARGES’, ‘$PBS_STATUS_CODE’, ‘$A_SGSN_IP’, ‘$B_SGSN_IP’, ‘$A_SGSN_OPERATOR_ID’, ‘$B_SGSN_OPERATOR_ID’, ‘$A_MSC_VLR_ID’, ‘$B_MSC_VLR_ID’, ‘$A_ROAMING_CLASS’, ‘$B_ROAMING_CLASS’, ‘$PDU_SIZE’, ‘$TRANSFORMED_SIZE’, ‘$READ_STATUS’, ‘$CFD_CONDITION’)”;
if(!($result2 = mysql_query($sql, $linkmb))) {
print(“Invalid query: ” . mysql_error().”n”);
print(“SQL: $sqln”);
die();
}
print(“Inserted: $EVENT_TYPE $MSG_ID $TRANSACTION_IDn”);
$rows++;
} else {
print(“Not unique: $EVENT_TYPE $MSG_ID $TRANSACTION_IDn”);
}
}
fclose($handle);
print(“$rows importedn”);
?>