Linux, Apache, MySQL, PHP

25-Oct-10 21:10

Hi
Thanks in advance for helping me. I have this mySQL query that fails i have no idea why i. Below is the code I left some space for readability

$lines=file("somefile5640.001");
$headerTable=explodeX(Array(",","\n"),$lines[0]);
$table ="HeaderRecord";

WriteArrayToHeaderDatabase($headerTable,$table);

function WriteArrayToHeaderDatabase($headerTable,$table)//Pass which table am i writing to
{

//I do the connecting to database here which is fine the problem is below

$query = "INSERT INTO ".$table."(FileIdentifier, Version, ReceiverIdentifier, Date, Time, FileGenerationNumber) VALUES";

for($i = 0, $c = count($headerTable); $i < $c; $i += 6) {
// Add the next batch of values to the query string
$query.= "('".$headerTable[$i]."','".$headerTable[$i+1]."','".$headerTable[$i+2]."','".$headerTable[$i+3]."','".$headerTable[$i+4]."','".$headerTable[$i+5]."')";
//$query .= "(".$headerTable[$i].",".$headerTable[$i+1].",".$headerTable[$i+2].",".$headerTable[$i+3].",".$headerTable[$i+4].",".$headerTable[$i+5].")";
echo "Query is failing";

// Add a comma is this is not the last batch
if($i + 1 < $c) {
//$query .= "";
$query .=",";
}

echo $query;
$result = mysql_query($query);

if (!$result) {
die('Query failed: ' . mysql_error());
}

}
return true;

// When i echo the query and run the code i get what shown below

Query is failingINSERT INTO HeaderRecord(FileIdentifier, Version, ReceiverIdentifier, Date, Time, FileGenerationNumber) VALUES('SOF','1','1500','19970519','112904','1 '),Query failed: 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

Geoff Williams25-Oct-10 22:28

Geoff Williams

25-Oct-10 22:28

Firstly, let me state that I'm no MYSQL expert. However, assuming the the INSERT INTO query works in the same way as it does in SQL Server, your problem is that you are trying to create multiple records using a single INSERT INTO query. You can only add one record at a time.

Gerben Jongerius26-Oct-10 1:08

26-Oct-10 1:08

Yes and no, MySql supports multiple inserts in one query just like was coded in the example.

That being said PHP does not support multiple queries in one mysql_query call, as is documented on PHP documentation.

nathionice26-Oct-10 1:55

26-Oct-10 1:55

Thanks for the response. I am trying to create one record but the query is repeated more than once. I don't know where in my code do i repeat the query.

Gerben Jongerius26-Oct-10 3:36

26-Oct-10 3:36

The problem is in your for loop. You start building up your insert just above it and then append the values in the loop. This might not seem like a problem, but when you look at what it does it is something like this:

q="insert into blaat(col1, col2) values"

Run 1:
q="insert into blaat(col1, col2) values('1','2'),"
mysql_query($q)

Run 2:
q="insert into blaat(col1, col2) values('1','2'),('3','4'),"
mysql_query($q)

Run 3:
q="insert into blaat(col1, col2) values('1','2'),('3','4'),('5','6'),"
mysql_query($q)

So the problem is probably two things. First you are appending to the q variable every time you loop through the for. This type of insert will work in a console MySQL client app, but I doubt that PHP supports it. Then there is a second issue with this, inside the loop you are running the mysql_query. So every single time you loop you are running the query over and over again.

The second issue is that you instantly add the ',' in the for loop. Since $i + 1 < $c is valid as soon as there is even one row to be inserted.

nathionice26-Oct-10 5:00

26-Oct-10 5:00

I honestly do not understand why it has to run(iterate) more than once as $i += 6 will make $i < $c false just after a single iteration. I'm pulling my hair here, i really need this sorted as in yersterday.

Gerben Jongerius26-Oct-10 20:34

26-Oct-10 20:34

First of you don't know (or at least I don't know) how many sets of 6 are in the array. But lets presume one set of 6, so 6 entries.

The loop will only run once, so your statement is true. However inside the loop (before the loop increments) you have that check to append the ','. Since the for loop only increases $i by 6 as it reaches its closing bracket the if statement inside the loop will always be true, don't forget that $i is stil 0 at this point.

Graham Breach26-Oct-10 2:57

Graham Breach

26-Oct-10 2:57

It's not easy to read, but it looks like the problem is a trailing comma in your query. Your loop adds 6 to $i with each iteration, but the test for adding a comma checks if $i + 1 < $c. This should be a 6 instead of a 1.

cjoki26-Oct-10 5:04

cjoki

26-Oct-10 5:04

I have used used loops to make a multiple insert query like above but form a tad different. I suspect the issue is in the query contruction itself.

Also why are you reassigning the $c variable with every loop of the for? This will slow down your code.

I guess the $headerTable is an single diminsional array? Currious why you did not use a multi-diminsional array...?

It looks like you are skipping 6 rows at a time...so looking at your error message if the values are all of the correct type (they are all varchar or similar?) then it looks like there maybe a second row starting

...
VALUES('SOF','1','1500','19970519','112904','1 '),
...

note the comma on the end there.

also
...
MySQL server version for the right syntax to use near ''
...
I suspect you may have a extra line of data in the array with an empty row sent to your function

Can you post the $query when you echo it? You should be able to copy and past it into the mysql admin tool, phpmyadmin, or other system that will let you run the query inside the tool. although I suspect you will see the same error.

btw I would write this function like this...

<?php
	WriteArrayToHeaderDatabase($headerTable,$table);
	function WriteArrayToHeaderDatabase($headerTable,$table)//Pass which table am i writing to
	{
		//I do the connecting to database here which is fine the problem is below
		$query = "INSERT INTO ".$table."(FileIdentifier, Version, ReceiverIdentifier, Date, Time, FileGenerationNumber) VALUES";
		
		$c = count($headerTable);
		echo "headerTable Count = ".$c;
		for($i = 0; $i < $c; $i += 6) 
		{
			// Add the next batch of values to the query string
			if($i==0)
			{
				$query.= "('".$headerTable[$i]."','".$headerTable[$i+1]."','".$headerTable[$i+2]."','".$headerTable[$i+3]."','".$headerTable[$i+4]."','".$headerTable[$i+5]."')";
			}
			else
			{
				$query.= ", ('".$headerTable[$i]."','".$headerTable[$i+1]."','".$headerTable[$i+2]."','".$headerTable[$i+3]."','".$headerTable[$i+4]."','".$headerTable[$i+5]."')";
			}
			echo $query; 
			$result = mysql_query($query) or die('Query failed: ' . mysql_error());			
		}
		return true;
	}
?>

this will run faster and is easier to read. It will also tell you how many rows are in the array sent to your variable.

nathionice26-Oct-10 5:42

26-Oct-10 5:42

I get the following error

headerTable Count = 7INSERT INTO HeaderRecord(FileIdentifier, Version, ReceiverIdentifier, Date, Time, FileGenerationNumber) VALUES('SOF','1','1500','19970519','112904','1 ')INSERT INTO HeaderRecord(FileIdentifier, Version, ReceiverIdentifier, Date, Time, FileGenerationNumber) VALUES('SOF','1','1500','19970519','112904','1 '), ('','','','','','')Query failed: Duplicate entry '1' for key 2

Below is the rewritten code(whole code) as per your suggestions

Gerben Jongerius26-Oct-10 20:29

26-Oct-10 20:29

In the future could you please post your code in the <pre> tag, which would make it more readable for us. But back to your issue. You still have $result = mysql_query($query) or die('Query failed: ' . mysql_error()); line of code inside your loop. Please move it below the for loop and try again.

nathionice26-Oct-10 20:53

26-Oct-10 20:53

I have changed the code to the following and decremented array count by 1 on $c = count($headerTable)-1; and it seems to work
Thanks a lot guys for helping

$query = "INSERT INTO ".$table."(FileIdentifier, Version, ReceiverIdentifier, Date, Time, FileGenerationNumber) VALUES";

    $c = count($headerTable)-1;
    echo "headerTable Count = ".$c;
     $numberOfLoops=0;
    for($i = 0; $i < $c; $i += 6)
    {
        // Add the next batch of values to the query string
        if($i==0)
        {
            $query.= "('".$headerTable[$i]."','".$headerTable[$i+1]."','".$headerTable[$i+2]."','".$headerTable[$i+3]."','".$headerTable[$i+4]."','".$headerTable[$i+5]."')";
        }
        else
        {
            $query.= ", ('".$headerTable[$i]."','".$headerTable[$i+1]."','".$headerTable[$i+2]."','".$headerTable[$i+3]."','".$headerTable[$i+4]."','".$headerTable[$i+5]."')";
        }
        echo $query;
        echo 'The number of loops';
        echo $numberOfLoops++;
        $result = mysql_query($query) or die('Query failed: ' . mysql_error());


    }
    return true;
    echo 'The number of loops';
    echo $numberOfLoops++;
}