Inserting Data and updating data in a DB.

[avo]

Hi all.

can anyone please help me out here?
Bellow is my code to read a text file loop through it pick lines and enter them into the database but i also need to do an update on each line once it as been read into the database thats where my problam is the second part of the code is trying to do this its working and adding data in the correct places but its not looping through like the first statement its remembering the first data from the insert staement .

[code]include ('includes/dbconfig.php');

$lines = file('numbers.txt');
$txt_file_name ="numbers.txt";
$start1 = 1-1;
$lim1 = 4;
$start2 = 11-1;
$lim2 = 1;

$total = count($lines);
$loop = 11;

mysql_connect ($dbhost,$dbuser, $dbpass) or die (mysql_error());
mysql_select_db($dbname)or die (mysql_error());[/code]
[code]
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-insert
  for ($j = $start1; $j < $lim1+$start1; $j++)
    $sql .='\''.$lines[$i+$j].'\',';
  for ($j = $start2; $j < $lim2+$start2; $j++)
    $sql .='\''.$lines[$i+$j].'\')';
    
// Build SQL-update
$sql_update = "UPDATE 'parts_db' SET 'p_id' = NULL ,";
    $sql_update .='\'part_number\'=\''.$lines[0].'\',';
    $sql_update .='\'part_des\'=\''.$lines[1].'\',';
    $sql_update .='\'quantity_in\'=\''.$lines[2].'\',';
    $sql_update .='\'location\'=\''.$lines[3].'\',';
    $sql_update .='\'reorder_quantity\'=\''.$lines[11].'\',';
      // Build SQL-update

//mysql_query($sql) or die ("Error: ". mysql_error(). " with query ". $sql);
//mysql_query($sql) or die (mysql_error());
echo "<br>";
echo "$sql";
echo "<br>";
echo "<br>";
echo "$sql_update";
echo "<br>";
}
[/code]
all help appriciated any ideas.
thanks in advance

[Barand]

Don't put quotes round column names, only round values.

[avo]

[!--quoteo(post=381125:date=Jun 7 2006, 08:47 PM:name=Barand)--][div class=\'quotetop\']QUOTE(Barand @ Jun 7 2006, 08:47 PM) [snapback]381125[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Don't put quotes round column names, only round values.
[/quote]
Hi thanks for the reply i have now sorted that
this is my output if i echo the select and update statement as you can see still can't get this to loop the update statement

all help appriciated

[code]INSERT INTO parts_db (part_number,part_des,quantity_in,location,reorder_quantity) VALUES ('M1643 ','Pcb Fco710 ','0 ','SLF 8 ','1 ')

UPDATE 'parts_db' SET 'p_id' = NULL ,part_number='M1643 ',part_des='Pcb Fco710 ',quantity_in='0 ',location='SLF 8 ',reorder_quantity='1 ',

INSERT INTO parts_db (part_number,part_des,quantity_in,location,reorder_quantity) VALUES ('M1271 ','Main Pcb FCO200 ','2 ','SLF 8 ','2 ')

UPDATE 'parts_db' SET 'p_id' = NULL ,part_number='M1643 ',part_des='Pcb Fco710 ',quantity_in='0 ',location='SLF 8 ',reorder_quantity='1 ',[/code]

the update retains the values from the first insert statement .

[Barand]

1 ) Having inserted the record with those values, why then do an update with same values.

2 ) An UPDATE without a WHERE clause will update every record in the table with those values.

[avo]

[!--quoteo(post=381574:date=Jun 8 2006, 09:17 PM:name=Barand)--][div class=\'quotetop\']QUOTE(Barand @ Jun 8 2006, 09:17 PM) [snapback]381574[/snapback][/div][div class=\'quotemain\'][!--quotec--]
1 ) Having inserted the record with those values, why then do an update with same values.

2 ) An UPDATE without a WHERE clause will update every record in the table with those values.
[/quote]
Hi Thanks

i have to do an update because when ive entered data in my database from the text file i am then unable to echo the values out the table until i gointo phpmyadmin and do an update manualy so i was going to try and do an update via php just after the data is entered into my db

upon inspection and exporting writen data from phpmyadmin at the end of each value it showed me \r\n but this was not pressent when i echoed the insert statement when inserting

when i do an update manualy via phpmyadmin return newline get removed from the export

do you have any idea why these values would be on the export but not visable in my table colums or when i echo the insert statement.

thanks in advance

[Barand]

This should suffice. Insert 2 records then list the table data.

[code]mysql_query("INSERT INTO parts_db (part_number, part_des, quantity_in, location, reorder_quantity)
                VALUES
                ('M1643', 'Pcb Fco710', '0', 'SLF 8', '1'),
                ('M1271', 'Main Pcb FCO200', '2', 'SLF 8', '2')" );

$res = mysql_query("SELECT part_number, part_des, quantity_in,  
             location, reorder_quantity
             FROM parts_db");
while ($row = mysql_fetch_row($res)) {

       echo join (', ', $row);
       echo '<br />';

}[/code]

As for th \r\n, browsers ignore them unless you covert them with nl2br()

[avo]

[!--quoteo(post=381655:date=Jun 9 2006, 01:10 AM:name=Barand)--][div class=\'quotetop\']QUOTE(Barand @ Jun 9 2006, 01:10 AM) [snapback]381655[/snapback][/div][div class=\'quotemain\'][!--quotec--]
This should suffice. Insert 2 records then list the table data.

[code]mysql_query("INSERT INTO parts_db (part_number, part_des, quantity_in, location, reorder_quantity)
                VALUES
                ('M1643', 'Pcb Fco710', '0', 'SLF 8', '1'),
                ('M1271', 'Main Pcb FCO200', '2', 'SLF 8', '2')" );

$res = mysql_query("SELECT part_number, part_des, quantity_in,  
             location, reorder_quantity
             FROM parts_db");
while ($row = mysql_fetch_row($res)) {

       echo join (', ', $row);
       echo '<br />';

}[/code]

As for th \r\n, browsers ignore them unless you covert them with nl2br()
[/quote]

HI thanks

just worked out my prob

all i needed to do was
[code]  for ($j = $start1; $j < $lim1+$start1; $j++)
    $sql .='\''.trim($lines[$i+$j]).'\',';
  for ($j = $start2; $j < $lim2+$start2; $j++)
    $sql .='\''.trim($lines[$i+$j]).'\')';[/code]

add the trim() function no need to update the db added straight to db without the white space