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

Recommended Answers

All 2 Replies

I don't really have the time to read through all your code, but I will tell you that I usually get this error as the result of special characters inside of my sql statements (i.e. a -,&,%, !, etc where they don't belong). This could also be due to a stray apostrophe, quote, etc. Make sure you filter your posted data from the form input for apostrophes, like so:

//get the info from the post
 $paste_text = addslashes($_POST['txtPasteData']);
 $data_type = addslashes($_POST['rdoFileContentType']);

there may be something else awry, but it could be as simple as that

ok i'll try that..thanks

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.