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++;
}

i catch a problem when i compiling

cjoki27-Oct-10 5:50

cjoki

27-Oct-10 5:50

Sorry I gave you a bad example as stated above (thanks, Gerben Jongerius for pointing that out).... the mysql_query needs to be outside the loop. To be accurate this is how that should be set up...

$query = "INSERT INTO ".$table."(FileIdentifier, Version, ReceiverIdentifier, Date, Time, FileGenerationNumber) VALUES";
$c = count($headerTable)-1; //<-- not sure about this....
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;
}

if you do not move it outside the loop then when you have more than one set of six records to process you will generate a bad query on the second pass.

if example what you currently have one the first pass will produce a query that looks like this on the first pass;
INSERT INTO HeaderRecord(FileIdentifier, Version, ReceiverIdentifier, Date, Time, FileGenerationNumber) VALUES('SOF','1','1500','19970519','112904','1 ')

and on the second pass;
INSERT INTO HeaderRecord(FileIdentifier, Version, ReceiverIdentifier, Date, Time, FileGenerationNumber) VALUES('SOF','1','1500','19970519','112904','1 '), ('','','','','','')

this has 2 errors, one is the duplucate data being entered into the table and the other is the empty data on the second pass.

If you move the mysql_query to outside of the loop you would get one insert like this;
INSERT INTO HeaderRecord(FileIdentifier, Version, ReceiverIdentifier, Date, Time, FileGenerationNumber) VALUES('SOF','1','1500','19970519','112904','1 '), ('','','','','','')

That will take care of the duplcate data issue. To fix the blank data look at your data source. Something is wrong with it adding an extra line as your output shows;
headerTable Count = 7
so one extra row is being added to your data source before it hits this function, this should be looked at.

Hope this helps
Chris J

hello about Cacti

lxlenovostar19-Oct-10 2:38

lxlenovostar

19-Oct-10 2:38

hello, i know the cacti is founded by php. i want to build it again by c++.
may be ? or give me some advices,
thank you

Re: hello about Cacti

cjoki19-Oct-10 4:39

cjoki

19-Oct-10 4:39

it sounds like you want to convert the php scripts to an executable. Besides doing a lot of coding by hand, I can only think of one application that may help you and it is called "hip hop". It is used by facebook to covert their php scripts to compiled executables to improve the performance. I am not sure how the cacti license is set up so your on you own there.

http://developers.facebook.com/blog/post/358[^]

lxlenovostar18-Oct-10 3:03

lxlenovostar

18-Oct-10 3:03

when i run:sudo make
the console write ouput :

compiling FrameworkTest.cpp
In file included from /usr/include/c++/4.4/memory:53,
from tests/Fit/FrameworkTest.cpp:15:
/usr/include/c++/4.4/bits/stl_tempbuf.h: In function ‘std::pair<_Tp*, int> std::get_temporary_buffer(ptrdiff_t)’:
/usr/include/c++/4.4/bits/stl_tempbuf.h:95: error: ‘operator new("/usr/include/c++/4.4/bits/stl_tempbuf.h", 94)’ cannot be used as a function

can you tell me why? thank you

Re: i catch a problem when i compiling

Iranian MM8-Sep-11 5:30

Iranian MM

8-Sep-11 5:30

Please Give us stl_tempbuf.h

problem in windows 7

mstanwar13-Oct-10 14:46

mstanwar

13-Oct-10 14:46

Why apache tomcat creates problem in windows 7 while working.its tomcat 6.0.26. i have tried on several systems but on every system having os as windows 7,it creates problem.
Actually ir starts working at that time but when we restart pc then its working stop and we need to reinstall it to work on.
Can anyone elaborate this reason?

Re: problem in windows 7

Mohamed7IBrahim18-Nov-10 3:48

Mohamed7IBrahim

18-Nov-10 3:48

this beacause apache company didnt devlop the version which support windows7 yet

Re: problem in windows 7

Ali Al Omairi(Abu AlHassan)13-Feb-11 14:50

Ali Al Omairi(Abu AlHassan)

13-Feb-11 14:50

Sir, read this article http://www.webdevelopersnotes.com/how-do-i/install-apache-windows-7.php[^]

Help people,so poeple can help you.

PHP File Open and Reading character by character

nathionice13-Oct-10 5:33