Updating the database

[sunilmadhav]

Hi all,

 

I have a datebase and i want to update it. I have a text file and i have to update the database using the text file. If there is same record in the text file and the database it has to over write if the record is not there in the database but there in the text file it has to add in the database. Can any one suggest me how to do that as iam new to PHP. I am pasting the code here

<?php

session_start();
include("../connect2db.php");

$user_descrip = $_SESSION['user_descrip'];
$user_descrip="Administrator";
if($user_descrip!="Administrator")
{
header("Location: ../accessdenied.htm");
}
else
{
$table="nielupdate";

//AF
$sql="update $table set custdept='AF' where substring(BIC1,1,1)='F'";
mysql_query($sql);

//JF
$sql="update $table set custdept='JF' where substring(BIC1,1,1)='Y' and substring(BIC1,2,1)='F'";
mysql_query($sql);

//JNF
$sql="update $table set custdept='JNF' where substring(BIC1,1,1)='Y' and substring(BIC1,2,1)!='F'";
mysql_query($sql);

//ANF
$sql="update $table set custdept='ANF' where custdept='' and substring(BIC1,1,1)!='Y' and substring(BIC1,1,1)!='F' and BIC1!=''";
mysql_query($sql);

//Binding Paperback
$sql="update $table set binding='Paperback' where binding='' or binding= 'NULL' and substring(FMC,1,2)='BC'";
mysql_query($sql);

//Binding Hardback
$sql="update $table set binding='Hardback' where binding='' or binding= 'NULL' and substring(FMC,1,2)='BB'";
mysql_query($sql);

//JF
$sql="update $table set custdept='JF' where custdept='' and substring(dw,1,1)='8' and substring(dw,3,1)='3' and (substring(rc,1,1)='S' or substring(rc,1,1)='J' or substring(rc,1,1)='X')";
mysql_query($sql);


//AF
$sql="update $table set custdept='AF' where custdept='' and substring(dw,1,1)='8' and substring(dw,3,1)='3' and (substring(rc,1,1)!='S' or substring(rc,1,1)!='J' or substring(rc,1,1)!='X')";
mysql_query($sql);

//JNF
$sql="update $table set custdept='JNF' where custdept='' and substring(dw,1,1)!='8' and substring(dw,3,1)!='3' and dw!='' and (substring(rc,1,1)='S' or substring(rc,1,1)='J' or substring(rc,1,1)='X')";
mysql_query($sql);

//ANF
$sql="update $table set custdept='ANF' where custdept='' and substring(dw,1,1)!='8' and substring(dw,3,1)!='3' and dw!='' and (substring(rc,1,1)!='S' or substring(rc,1,1)!='J' or substring(rc,1,1)!='X')";
mysql_query($sql);


//fiction Y
$sql="update $table set fiction='Y' where custdept='AF' or custdept='JF'";
mysql_query($sql);

//fiction N
$sql="update $table set fiction='N' where custdept!='AF' and custdept!='JF'";
mysql_query($sql);

//copy date
$sql="update $table set pub_date=PDUK";
mysql_query($sql);

//fix type HW281208
$sql="update $table set type1='BOOKS' where type1=''";
mysql_query($sql);

//Update type1 and binding to BOOKS 
$sql0="select catalog_no,fmc from $table";
$res0=mysql_query($sql0);
while($ser0=mysql_fetch_array($res0))
{
       $fmc=$ser0['fmc'];
 $catalog_no=$ser0['catalog_no'];
 $num_rows = mysql_num_rows(mysql_query("select type1 from neilsen_codes where fmc='$fmc'"));
 If ($num_rows > 0)
 {
   $type1=mysql_result(mysql_query("select type1 from neilsen_codes where fmc='$fmc'"),0);
  }
 $num_rows = mysql_num_rows(mysql_query("select binding from neilsen_codes where fmc='$fmc'"));
 If ($num_rows > 0)
 {	  
 $binding=mysql_result(mysql_query("select binding from neilsen_codes where fmc='$fmc'"),0);
 }
 mysql_query("update $table set type1='$type1' where catalog_no='$catalog_no'");
 mysql_query("update $table set binding='$binding' where catalog_no='$catalog_no'");
}
}
?>

 

Thanks,

Sunil

[Catfish]

i see nothing about a text file in that code. just a heap of unverified UPDATE sql queries being sent to the server. Your question is extremely broad, and the code doesn't seem to reflect much towards what you want it to do.

[sunilmadhav]

hi,

 

no that code normally updates the database in that case its not updating all the fields so i created a text file before the update. i want to update the database using the text file so i am not able to do it .

I have a table called nielupdate an a text file which is the updation of the nielupdate table. i want to use the text file to update the table nielupdate . If the record is present in both the text file and the table it has to overwrite in the table if its not it has to add.

any solution..

 

Thanks,

Sunil

[cags]

It very much depends on the format of the file that your reading. You may be able to make use of the LOAD DATA INFILE syntax. Alternatively you may have to manually parse the contents of the file and use the INSERT INTO ... ON DUPLICATE UPDATE syntax.

[sunilmadhav]

hi cags,

 

When i use load data infile it gives me an error

 

Parse error: syntax error, unexpected T_STRING

 

any help please...

[cags]

How am I suppose to give you any help with that little information? What code are you using?

[ignace]

That is a PHP error not a SQL error. You have an error in your PHP syntax. Provide your PHP code and we may be able to pinpoint the location of the problem.

 

In addition to cags suggestion: INSERT INTO .. ON DUPLICATE KEY UPDATE I like to note that this will only work if you want to insert a value in a column which is a primary key or unique index and will otherwise not work. As per the manual:

 

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed. The affected-rows value per row is 1 if the row is inserted as a new row and 2 if an existing row is updated. See Section 12.2.5.3, “INSERT ... ON DUPLICATE KEY UPDATE Syntax”.

[sunilmadhav]

hi,

 

iam posting the PHP code

<?php

session_start();
include("../connect2db.php");

//$user_descrip = $_SESSION['user_descrip'];
$user_descrip="Rondo Administrator";
if($user_descrip!="Rondo Administrator")
{
header("Location: ../accessdenied.htm");
}
else
{
$table="nielupdate";

//AF
$sql="update $table set custdept='AF' where substring(BIC1,1,1)='F'";
mysql_query($sql);

//JF
$sql="update $table set custdept='JF' where substring(BIC1,1,1)='Y' and substring(BIC1,2,1)='F'";
mysql_query($sql);

//JNF
$sql="update $table set custdept='JNF' where substring(BIC1,1,1)='Y' and substring(BIC1,2,1)!='F'";
mysql_query($sql);

//ANF
$sql="update $table set custdept='ANF' where custdept='' and substring(BIC1,1,1)!='Y' and substring(BIC1,1,1)!='F' and BIC1!=''";
mysql_query($sql);

//Binding Paperback
$sql="update $table set binding='Paperback' where binding='' or binding= 'NULL' and substring(FMC,1,2)='BC'";
mysql_query($sql);

//Binding Hardback
$sql="update $table set binding='Hardback' where binding='' or binding= 'NULL' and substring(FMC,1,2)='BB'";
mysql_query($sql);

//JF
$sql="update $table set custdept='JF' where custdept='' and substring(dw,1,1)='8' and substring(dw,3,1)='3' and (substring(rc,1,1)='S' or substring(rc,1,1)='J' or substring(rc,1,1)='X')";
mysql_query($sql);


//AF
$sql="update $table set custdept='AF' where custdept='' and substring(dw,1,1)='8' and substring(dw,3,1)='3' and (substring(rc,1,1)!='S' or substring(rc,1,1)!='J' or substring(rc,1,1)!='X')";
mysql_query($sql);

//JNF
$sql="update $table set custdept='JNF' where custdept='' and substring(dw,1,1)!='8' and substring(dw,3,1)!='3' and dw!='' and (substring(rc,1,1)='S' or substring(rc,1,1)='J' or substring(rc,1,1)='X')";
mysql_query($sql);

//ANF
$sql="update $table set custdept='ANF' where custdept='' and substring(dw,1,1)!='8' and substring(dw,3,1)!='3' and dw!='' and (substring(rc,1,1)!='S' or substring(rc,1,1)!='J' or substring(rc,1,1)!='X')";
mysql_query($sql);


//fiction Y
$sql="update $table set fiction='Y' where custdept='AF' or custdept='JF'";
mysql_query($sql);

//fiction N
$sql="update $table set fiction='N' where custdept!='AF' and custdept!='JF'";
mysql_query($sql);

//copy date
$sql="update $table set pub_date=PDUK";
mysql_query($sql);

//fix type HW281208
$sql="update $table set type1='BOOKS' where type1=''";
mysql_query($sql);

//Update type1 and binding to BOOKS 
$sql0="select catalog_no,fmc from $table";
$res0=mysql_query($sql0);
while($ser0=mysql_fetch_array($res0))
{
       $fmc=$ser0['fmc'];
 $catalog_no=$ser0['catalog_no'];
 $num_rows = mysql_num_rows(mysql_query("select type1 from neilsen_codes where fmc='$fmc'"));
 If ($num_rows > 0)
 {
   $type1=mysql_result(mysql_query("select type1 from neilsen_codes where fmc='$fmc'"),0);
  }
 $num_rows = mysql_num_rows(mysql_query("select binding from neilsen_codes where fmc='$fmc'"));
 If ($num_rows > 0)
 {	  
 $binding=mysql_result(mysql_query("select binding from neilsen_codes where fmc='$fmc'"),0);
 }
 mysql_query("update $table set type1='$type1' where catalog_no='$catalog_no'");
 mysql_query("update $table set binding='$binding' where catalog_no='$catalog_no'");
}
$sql="load data infile 'W:\Nielsen13_Data\upd_20090527c.txt' into table $table";
    //$sql="insert ignore into $table select * from 'W:\Nielsen13_Data\upd_20090527c.txt'";
    mysql_query($sql);

   }
?>

[ignace]

Parse error: syntax error, unexpected T_STRING

 

This gives you a line number which is it and what code is there on this line + surrounding lines?

 

Also what is in: W:\Nielsen13_Data\upd_20090527c.txt?

[sunilmadhav]

hi ignace,

 

W:\Nielsen13_Data\upd_20090527c.txt

 

W:\Nielsen13_Data\ ----  is the folder in which the text file is present and "upd_20090527c.txt" is the text file which has the data. Using this text file i am trying to update the data. Now there is no error but the table is not updating from the text file to the database..

 

Thanks,

sunil