Jump to content


Photo

Inserting Data and updating data in a DB.


  • Please log in to reply
6 replies to this topic

#1 avo

avo
  • Members
  • PipPipPip
  • Advanced Member
  • 148 posts
  • Locationstaffordshire uk

Posted 07 June 2006 - 02:55 PM

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 .

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());
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>";
}
all help appriciated any ideas.
thanks in advance
Im loving it ........

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,025 posts

Posted 07 June 2006 - 07:47 PM

Don't put quotes round column names, only round values.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 avo

avo
  • Members
  • PipPipPip
  • Advanced Member
  • 148 posts
  • Locationstaffordshire uk

Posted 07 June 2006 - 07:57 PM

[!--quoteo(post=381125:date=Jun 7 2006, 08:47 PM:name=Barand)--][div class=\'quotetop\']QUOTE(Barand @ Jun 7 2006, 08:47 PM) View Post[/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

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 ',

the update retains the values from the first insert statement .
Im loving it ........

#4 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,025 posts

Posted 08 June 2006 - 08:17 PM

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.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#5 avo

avo
  • Members
  • PipPipPip
  • Advanced Member
  • 148 posts
  • Locationstaffordshire uk

Posted 08 June 2006 - 08:45 PM

[!--quoteo(post=381574:date=Jun 8 2006, 09:17 PM:name=Barand)--][div class=\'quotetop\']QUOTE(Barand @ Jun 8 2006, 09:17 PM) View Post[/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
Im loving it ........

#6 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,025 posts

Posted 09 June 2006 - 12:10 AM

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

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 />';

}

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

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#7 avo

avo
  • Members
  • PipPipPip
  • Advanced Member
  • 148 posts
  • Locationstaffordshire uk

Posted 09 June 2006 - 10:32 PM

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

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 />';

}

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
  for ($j = $start1; $j < $lim1+$start1; $j++)
    $sql .='\''.trim($lines[$i+$j]).'\',';
  for ($j = $start2; $j < $lim2+$start2; $j++)
    $sql .='\''.trim($lines[$i+$j]).'\')';

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


Im loving it ........




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users