Jump to content

$sql echo not working

webguync

By webguync
in MySQL Help

Recommended Posts

webguync Advanced Member

webguync

Posted
Posted

I need help echoing out my SQL statement in the following code. This is a data upload application which uploads a tab delemeted txt file into a MySQL table. I am getting a MySQL error.

 

MySQL Error: 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 'Group,Title) VALUES ('','','','','' at line 1

 

so I need to see the SQL to see why it isn't working. I have tried echoing out but not seeing the echo, so must be doing it wrong.

 

<?php
require_once('databaseClass.php');

if ($_POST) {
foreach($_POST as $key=>$value) {
	if (empty($value)) {
		if ($key == 'fileurl') {
			$errors[] = 'Please provide the URL to the text file containing the data you want to load';
			}
		else if ($key == 'dbname') {
			$errors[] = 'Please provide the name of the database into which you want to load the data';
			}
		else if ($key == 'dbuser') {
			$errors[] = 'Please provide the appropriate Username for the database';
			}
		else if ($key == 'db_pw') {
			$errors[] = 'Please provide the appropriate PW for the database';
			}
		else if ($key == 'dbtable') {
			$errors[] = 'Please provide the database table into which you would like to insert data';
			}
		else if ($key == 'fields') {
			$errors[] = 'Please specify the field names for the table';
			}
		}
	}
if (!isset($errors)) {
	$file = fopen('../'.$_POST['fileurl'], 'r');
	if ($file) {
		$pattern = '/[\n\r\t]/';
		while (!feof($file)) {
			$line = trim(fgets($file));
			$newline = preg_replace($pattern,'\t',$line);
			$lines[] = explode('\t',$newline);
			//echo (fgets($file));
			}
		fclose($file);
		if (count($lines) > 0) {
			$countSuccess = 0;
			$fields = explode(',',$_POST['fields']);
			//$entryCnt = count($tmp);
			$db = new Database('localhost',$_POST['dbuser'],$_POST['db_pw'],$_POST['dbname'],0);
			for ($i=0; $i<count($lines); $i++) {
				$tmp = NULL;
				$sql = 'INSERT INTO '.$_POST['dbtable'].' (';
				for ($k=0; $k<count($lines[$i]); $k++) {
					if (isset($lines[$i][$k]) && $lines[$i][$k] != NULL) {
						$tmp[] = $fields[$k];
						}
					}
				$sql .= implode(',',$tmp);

				$sql .= ') VALUES (';
				for ($j=0; $j<count($lines[$i]); $j++) {
					if (isset($lines[$i][$j]) && $lines[$i][$j] != NULL) {
						if (is_numeric($lines[$i][$j])) {
$sql .= $lines[$i][$j];
echo $sql;//trying to echo out SQL here

}
else {
  $sql .= "'".mysql_real_escape_string($lines[$i][$j])."'";
}
if($j != (count($lines[$i])-1)) {
  $sql .= ',';
}
}
					}
				$sql .= ')';

				$result = $db->query($sql);
				if($result) {
					$countSuccess++;
					}
				//escape $sql;

				}

			$db->close();
			if($countSuccess > 0) {
				header('Location: '.$_SERVER['PHP_SELF'].'?numInserted='.$countSuccess);
				}
			else {
				$errors[] = 'No data was inserted into the database.  Please check all fields again.';
				}
			//print_r($lines);
			}
		else {
			$errors[] = 'No data in designated file';
			}
		}
	else {
		$errors[] = 'Not able to open specified file.  Please check that it is the correct URL to text file.';
		}
	}
}


?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>LOAD DATA alternative</title>
<link href="load.css" rel="stylesheet" type="text/css" />
</head>
<body>
<?php 
if ($_GET) {
echo '<div id="success"><h1>'.$_GET['numInserted'].' rows inserted successfully!</h1></div>';
}
?>
<div id="loadform">
  <h1>Please fill in all fields.</h1>
  <?php if (isset($errors)) {
	echo '<div id="alert"><p>Please correct the following:</p><ul>';
	foreach ($errors as $item) {
		echo "<li>$item</li>";
		}
	echo '</ul></div>';
	}
?>
  <form name="loaddata" method="post" action="<?php $_SERVER['PHP_SELF']; ?>">
    <p>Text file URL (RELATIVE to etsi-dataservices.com/):</p>
    <input name="fileurl" type="text" class="longfield" />
    <br />
    <p>DB Name:</p>
    <input name="dbname" type="text" class="shortfield" />
    <br />
    <p>DB Username:</p>
    <input name="dbuser" type="text" class="shortfield" />
    <br />
    <p>DB PW:</p>
    <input name="db_pw" type="password" class="shortfield"  />
    <br />
    <p>DB Table Name:</p>
    <input name="dbtable" type="text" class="shortfield" />
    <br />
    <p>Comma-delimited list of table's field names (e.g.- name,id,score,etc.), in SAME ORDER as text file data:</p>
    <input name="fields" type="text" class="longfield" />
    <br />
    <br />
    <input name="submit" type="submit" />
  </form>
</div>
</body>
</html>

Link to comment
https://forums.phpfreaks.com/topic/224190-sql-echo-not-working/
Share on other sites
Maq Prolific Member

Maq

Posted
Posted

well, I am still debugging the upload tool, but I can echo out the SQL and insert that directly into the MySQL DB through PHPMyAdmin so at least I got that part to work :-)

 

Good.  You may want to mark this solved and if you have a another question you should start a new thread.

Archived

This topic is now archived and is closed to further replies.

Go to topic listing
×
×
  • 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.