text file in db error !

[avo]

HI All

what i am doing is reading a text file and selecting lines from that file and repeating every 11 lines
all is working fine just for one thing .

went i echo the $sql variable out i can see what is going to be inserted into the db and all is good but when i put mysql_query line in instead of the echo line it comes up with this 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 '' at line 2

Any Ideas PLease?

my code is.
[code]
<?
include ('includes/dbconfig.php');

// Get the lines from the file, put into an array
$lines = file("numbers.txt");

// Set limits
// lines 1 to 4, starts at 0, goes for 4 lines (1,2,3,4)
$start1 = 1-1; // In an array, Line 1 is Array Index 0
$lim1 = 4;
// lines 10 to 11, starts at 10, goes for 1 line (11)
$start2 = 11-1; // In an array, Line 11 is Array Index 10
$lim2 = 1;

// Total lines in the file
$total = count($lines); // PHP method to find the size of an array

// Loop after this many lines
$loop = 11; // every 11 lines you will start over

$conn = mysql_connect ($dbhost,$dbuser, $dbpass) or die (mysql_error());
mysql_select_db($dbname, $conn )or die (mysql_error());

for ($i = 0; $i+$loop < $total; $i += $loop)
{

  // Start SQL
$sql = 'INSERT INTO parts_db ( part_number, part_des, quantity_in, location, reorder_quantity )
VALUES (';

  // Build SQL
  for ($j = $start1; $j < $lim1+$start1; $j++)
    $sql .= '\'' . $lines[$i+$j] . '\', ';
  for ($j = $start2; $j < $lim2+$start2; $j++)
    $sql .= '\'' . $lines[$i+$j] . '\', ';
  for ($j = $start3; $j < $lim3+$start3; $j++)
    $sql .= '\'' . $lines[$i+$j] . '\')';

//mysql_query($sql, $conn) or die (mysql_error());
echo "$sql <br>";
}
?>
[/code]

Thanks in advance.

[avo]

anyone shed any light on this one please .
im still trying things to no evail

i know the error is pointing to the second line of the loop rather than the second line of the code
just dont know what.

error im getting still is :
[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]
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 2
[/quote]

all help greatly appriciated
cheers.

[AndyB]

Make a minor change so you get the error message AND see the query that was being tried. Post your result. That ought to point the way to solving this:

[code]mysql_query($sql, $conn) or die ("Error: ". mysql_error(). " with query ". $sql);[/code]

[avo]

[!--quoteo(post=379902:date=Jun 4 2006, 04:13 PM:name=AndyB)--][div class=\'quotetop\']QUOTE(AndyB @ Jun 4 2006, 04:13 PM) [snapback]379902[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Make a minor change so you get the error message AND see the query that was being tried. Post your result. That ought to point the way to solving this:

[code]mysql_query($sql, $conn) or die ("Error: ". mysql_error(). " with query ". $sql);[/code]
[/quote]

Thanks Andy

Just found my error!

It was looking at me all the time
[code]  for ($j = $start3; $j < $lim3+$start3; $j++)
    $sql .= '\'' . $lines[$i+$j] . '\')';[/code]

I was trying to add an extra line of information in the db and there was no Coolum for it originally i was grabbing more information from my text file then reduced it but left this code in there

Thanks always appreciate your help.

[kenrbnsn]

To make debugging easier, try the following code:
[code]<?php
$tmp = array();
  // Build SQL
  for ($j = $start1; $j < $lim1+$start1; $j++)
    $tmp[] = mysql_real_escape_string($lines[$i+$j]);
  for ($j = $start2; $j < $lim2+$start2; $j++)
    $tmp[] = mysql_real_escape_string($lines[$i+$j]);
  for ($j = $start3; $j < $lim3+$start3; $j++)
    $tmp[] = mysql_real_escape_string($lines[$i+$j]);

$sql .= "'" . implode("', '",$tmp) . "')";
?>[/code]
This will put the values into a temporary array and then build the comma separated value string with the implode() function. It also ensures that each value has had any problomatic characters properly escaped with the [a href=\"http://www.php.net/mysql_real_escape_string\" target=\"_blank\"]mysql_real_escape_string()[/a] function.

Ken