Hi,
I'm trying to upload a some files to a webpage im trying to crete but its giving me an error once i click the upload button it gives me this type of error
Uploading information to the system from a file
Student data is being uploaded.
DATABASE ERRORExecuting SQL :: 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 ''' at line 1
These are my codes for the upload
load/index.php
<?php
/**
*
* Short Description of the file
*
* Long Description of the file (if any)...
*
*
*/
//get the include file required
require_once("../../include/inc_global.php");
//set the page information
$UI->page_title = APP__NAME;
$UI->menu_selected = 'Admin';
$UI->breadcrumbs = array ('home' => null);
$UI->head();
?>
<script type=“text/javascript”>
function changeFormAction()
{
var strContentType = document.fileLoad.rdoFileContentType;
var srtSeparator = document.fileLoad.rdoFileSeperator;
document.getElementById("fileLoad").action = '../../tmp/readfile.php?rdoFileContentType=' + strContentType + '&rdoFileSeperator=' +rdoFileSeperator;
return true;
}
</script>
<?php
$UI->body();
$UI->content_start();
//build the content to be written to the screen
$page_intro = 'You can upload the data the system requires from this page';
$filename = 'Enter the file name to be used:';
$filecontent = 'Select the type of information you are uploading:<br>(This must follow the rules for the files and not contain any column headings)';
$filecontenttype = array(3);
$filecontenttype[1] = array('screen'=>'<b>Student Data</b><br>[institutional_reference, user_id, lastname, forename, email, module_code, password]', 'value'=>'1',);
$filecontenttype[2] = array('screen'=>'<b>Staff Data</b><br>[institutional_reference, user_id, lastname, forename, email, module_code, password, admin]', 'value'=>'2',);
$filecontenttype[3] = array('screen'=>'<b>Module Data</b><br>[module_code, module_title]', 'value'=>'3',);
$fileseparator = 'Select the type of file separator that has been used:';
$separator = array(3);
$separator[1] = array('screen'=> 'Comma separated', 'value'=>',',);
$separator[2] = array('screen'=> 'Tab separated', 'value'=>'\t',);
$separator[3] = array('screen'=> 'Semi-colon', 'value'=>';',);
$btn_name = 'Upload';
$pasteinstruction ='Copy and paste the contents of the file you want to add to the system';
?>
<p><?php echo $page_intro; ?></p>
<form id="fileLoad" enctype="multipart/form-data" action="../../tmp/readfile.php" method="POST" onsubmit="return changeFormAction()">
<div class="content_box">
<h2>Upload data via a file</h2>
<input type="hidden" name="MAX_FILE_SIZE" value="500000" />
<table class="option_list" style="width: 500px;">
<tr>
<td width='50%'>
<?php echo $filename; ?>
</td>
<td>
<input name="uploadedfile" type="file" />
</td>
</tr>
<tr>
<td>
<?php echo $filecontent; ?>
</td>
<td>
<?php
for($checkbox = 1; $checkbox<= count($filecontenttype)-1; $checkbox++){
echo '<input type="radio" name="rdoFileContentType" value="';
echo $filecontenttype[$checkbox]['value'];
echo '">';
echo $filecontenttype[$checkbox]['screen'];
echo '<br/>';
}
?>
</td>
</tr>
<tr>
<td>
<?php echo $fileseparator; ?>
</td>
<td>
<?php
for($checkbox = 1; $checkbox<= count($filecontenttype)-1; $checkbox++){
echo '<input type="radio" name="rdoFileSeperator" value="';
echo $separator[$checkbox]['value'];
echo '">';
echo $separator[$checkbox]['screen'];
echo '<br/>';
}
?>
</td>
</tr>
<tr>
<td>
</td>
<td>
<input type="submit" name="btnUpload" value="<?php echo $btn_name; ?>"/>
</td>
</tr>
</table>
</div>
</form>
<form action="pasteread.php" method="POST">
<div class="content_box">
<h2>Upload via 'cut and paste'</h2>
<table class="option_list" style="width: 500px;">
<tr>
<td>
<?php echo $pasteinstruction; ?>
</td>
<td>
<textarea name="txtPasteData" rows="10" cols="50"></textarea>
</td>
</tr>
<tr>
<td>
<?php echo $filecontent; ?>
</td>
<td>
<?php
for($checkbox = 1; $checkbox<= count($filecontenttype)-1; $checkbox++){
echo '<input type="radio" name="rdoFileContentType" value="';
echo $filecontenttype[$checkbox]['value'];
echo '">';
echo $filecontenttype[$checkbox]['screen'];
echo '<br/>';
}
?>
</td>
</tr>
<tr>
<td>
</td>
<td>
<input type="submit" name="btnUpload" value="<?php echo $btn_name; ?>"/>
</td>
</tr>
</table>
</div>
</form>
<?php
$UI->content_end();
?>load/pasteread.php
<?php
/**
*
* Take the information from the pasted information and add to the database
*
*
*/
//get the include file required
require_once("../../include/inc_global.php");
//functions
/**
* Function separate
*
* The function takes a string with two delimiters
* One delimiter is for the end of a line / row
* The second delimiter is for the individual fields which make up a row
* The function then returns a 2D array of the results
*
* @param string $orig_str
* @param chr $line_delimiter
* @param chr $field_delimiter
*
* @return array | boolean 2D array of the rows and fields
*/
function separate ($orig_str, $line_delimiter, $field_delimiter){
//get array of rows
$rows = explode($line_delimiter, $orig_str);
$num_rows = count($rows);
for($i = 0; $i<$num_rows; $i++){
$rows[$i] = explode($field_delimiter, $rows[$i]);
}
return $rows;
}
/**
* Function identify_delimiter
*
* function returns the delimiter if found or 0 is not found in the string
*
* @param string $source
* @param chr $delimiter
*
* @return array | boolean
*/
function identify_delimiter($source,$delimiter){
if (!( strpos($source, $delimiter)) === false){
return $delimiter;
}
}
/**
* Function to return the SQL to be run
*
* @param array $row_result
* @param array $datatype
* @param int $data_type
*
* @return array
*/
function build_sql($row_result,$data_type, $datatype){
$queryData = array();
switch ($data_type){
case 1:
//student
$queryData[0] = "user (user_type, institutional_reference, lastname, forename, email)";
$queryData[1] = $datatype[$data_type]. "','" . $row_result[0] . "','" . $row_result[1]. "','" .
$row_result[2] . "','" . $row_result[3];
$queryData[2] = $row_result[4];
break;
case 2:
//staff
$queryData[0] = "user (user_type, institutional_reference, lastname, forename, email)";
$queryData[1] = $datatype[$data_type]. "','" . $row_result[0] . "','" . $row_result[1]. "','" .
$row_result[2] . "','" . $row_result[3];
$queryData[2] = $row_result[4];
break;
case 3:
//module
$queryData[0] = "module (module_code, module_title)";
$queryData[1] = $row_result[0] . "','" . $row_result[1];
$queryData[2] = $row_result[4];
break;
default:
$queryData[0] = "";
$queryData[1] = "";
$queryData[2] = "";
}
return $queryData;
}
//set defined areas
$datatype = array (1=>'Student',
2=>'Staff',
3=>'Module',);
$delimiter = "";
$line_terminal ="";
$find_tab = chr(9);
$find_linefeed = chr(10);
$find_carrage_return = chr(13);
$find_comma = chr(44);
//strings for out put to the screen
$page_title = "Uploading data";
$page_intro = "The data is being read by capat.";
$none_delimiter = "There are no recognised separators in the text supplied";
$fail_separate = "It has not been possible to read the information";
$fail_read = "It has not been possible to read the information pasted";
$sucess = "The data has been loaded sucessfully.";
$no_module_match = "It has not been possible to match the user with a module you will need to do this separately.";
//get the info from the post
$paste_text = $_POST['txtPasteData'];
$data_type = $_POST['rdoFileContentType'];
$UI->page_title = APP__NAME;
$UI->menu_selected = 'Admin';
$UI->breadcrumbs = array ('home' => null);
$UI->head();
$UI->body();
$UI->content_start();
?>
<div class="content_box">
<?php
//look for the delimiter between the fields
//see if it is a tab character
$delimiter = identify_delimiter($paste_text,$find_tab);
if ($delimiter === 0){
//see if it is a comma
$delimiter = identify_delimiter($paste_text,$find_comma);
if ($delimiter === 0){
echo '<div class="error_box"><p>' . $none_delimiter . '</p></div>';
}
}
//look for the delimiter between the rows
//see if there is a line feed
$line_terminal = identify_delimiter($paste_text,$find_linefeed);
if ($line_terminal === 0){
//see if it is a carrage return
$line_terminal = identify_delimiter($paste_text,$find_carrage_return);
if ($line_terminal === 0){
echo '<div class="error_box"><p>' . $none_delimiter . ' rows</p></div>';
}
}
//break string into an array
if ($line_terminal && $delimiter){
$result = separate($paste_text, $line_terminal, $delimiter);
//with the results we need to id the type of data being uploaded
echo 'The data being lodaded is: ' . $datatype[$data_type];
$total_results = count($result);
for ($results = 0; $results < $total_results; $results++){
$row_result = array();
$row_result = $result[$results];
$query = build_sql($row_result,$data_type, $datatype);
if (!$query[2]==""){
$SQL = "INSERT INTO ". $query[0] ." VALUES ('". $query[1] ."');";
$execute = $DB->execute($SQL);
$insert_pos = $DB->get_insert_id();
//get the module ID
$SQL = "SELECT module_id FROM module WHERE module_code = '" . $query[2]."';";
$returned = $DB->fetch_row($SQL);
//handle their being no module ID
if (!is_array($returned)){
echo '<div class="error_box"><p>' . $no_module_match . '</p></div>';
}else{
//import the user module info
$insert = "INSERT INTO user_module (user_id, module_id) VALUES ('" . $insert_pos . "','" . $returned['module_id'] . "');";
$comp = $DB->execute($insert);
}
}else{
$SQL = "INSERT INTO module (module_code, module_title) VALUES ('". $row_result[0] ."','". mysql_real_escape_string($row_result[1]) ."');";
$insert = $DB->execute($SQL);
}
}
echo '<p>' . $sucess . '</p>';
}else{
echo '<div class="error_box"><p>' . $fail_separate . '</p></div>';
}
?></div><?php
$UI->content_end();
?>any help would be really appreciated..thanks
