Jump to content

Inserting Data and updating data in a DB.


avo

Recommended Posts

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
Link to comment
Share on other sites

[!--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 .
Link to comment
Share on other sites

[!--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
Link to comment
Share on other sites

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()
Link to comment
Share on other sites

[!--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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.