$sql echo not working

webguync · January 12, 2011

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>

Maq · January 12, 2011

'Group' is a reserved MySQL word. You can do one of two things:

- Place backtics ` around your 'Group' column everywhere it's used.

- Rename your column and change the appropriate queries.

BlueSkyIS · January 12, 2011

if you want to each your $sql, do it right before it's executed.

Rayhan Muktader · January 12, 2011

Hey,

if (is_numeric($lines[$i][$j])

should return false because

$lines[$i][$j]

should contain an array. I Hope that helps.

And yes, group is a reserved mysql keyword. You might want to rename that field to make your life easier.

Rayhan Muktader

webguync · January 12, 2011

ok thanks, that help me out.

Maq · January 12, 2011

ok thanks, that help me out.

Did you get it working?

webguync · January 12, 2011

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 :-)

Maq · January 12, 2011

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.

Sign In

$sql echo not working

Recommended Posts

webguync

Link to comment

Share on other sites

Maq

Link to comment

Share on other sites

BlueSkyIS

Link to comment

Share on other sites

Rayhan Muktader

Link to comment

Share on other sites

webguync

Link to comment

Share on other sites

Maq

Link to comment

Share on other sites

webguync

Link to comment

Share on other sites

Maq

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information