Page 1 of 1

Read from txt file and update or insert datas to database???

Posted: Thu Apr 08, 2004 1:46 am
by nevermiss9
Hey guyz, I'm still new to this so take it easy on me..

Ok here is my problem.. here is my code below

<?php
//testing read csv file and sorting it out.
$filename = "test.txt";
dbproducts();
if (!($fp = fopen($filename,"r")))
exit("Unable to open $filename.");
while (!feof($fp))
{
$i=$i+1;
$buffer = fgets($fp,1024);
$buffer = trim($buffer);

$pos = strpos($buffer, ",");
$barcode = substr($buffer, 0, $pos);

$buffer = trim(substr($buffer, $pos+1));
$pos = strpos($buffer, ",");
$name = substr($buffer, 0, $pos);

$buffer=trim(substr($buffer, $pos+1));
$price = $buffer;

$name = trim($name);
$pos = strpos($name, " ");
$name = substr($name, $pos);

if (mysql_query("SELECT fld_barcode FROM tbl_products WHERE fld_barcode=$barcode"))
{
mysql_query("UPDATE tbl_products SET fld_barcode='$barcode', fld_name='$name', fld_price='$price' WHERE fld_barcode=$barcode");
$jupdate=$jupdate+1;
} else {
mysql_query("INSERT INTO tbl_products (fld_barcode, fld_name, fld_price) VALUES ('$barcode', '$name', '$price')");
$jinsert=$jinsert+1;

}
}
fclose ($fp);
print ("$i products in total<br> $jupdate products update<br> $jinsert products inserted");
?>

my database fld_barcode is varchar(25).

I think the problem is the if statement.
if (mysql_query("SELECT fld_barcode FROM tbl_products WHERE fld_barcode=$barcode"))
{

the problem is if the barcode match or not in the database it will always end up the same. I change barcode to see the result and still the same thing.
What happening is if barcode has int only then it will do update goes into if statement.
but if the barcode has a char in it then it will do insert goes to else statement.
no matter if $barcode match or not with fld_barcode in the database.
What i'm doing wrong.

Posted: Thu Apr 08, 2004 2:36 am
by dipit
hi,
case: when barcode is chat the use if lie this
if (mysql_query("SELECT fld_barcode FROM tbl_products WHERE fld_barcode='$barcode' "))

and plz see case also (in php A is diff from a)
dipit

Posted: Thu Apr 08, 2004 5:26 am
by nevermiss9
that didn't help. even if change that line of code to yours
if (mysql_query("SELECT fld_barcode FROM tbl_products WHERE fld_barcode='$barcode' "))

the problem is even if there is no data in the database at all for it to look up and check if barcode exit. it always does update. when i put '&barcode' it will always do the if statement no matter if barcode doesn't exit.
it should be doing the else statement instead.

Posted: Thu Apr 08, 2004 5:38 am
by nevermiss9
ok what i'm trying do is write a script to update the database.
by fld_barcode Unique field. varchar(25)
fld_barcode will have mix letters n numbers sometimes only letters or numbers.

basical i'm writting a script to update the price and name by searching barcode. if barcode doesn't exit insert it into the database instead of updating it.

Posted: Thu Apr 08, 2004 6:06 am
by Wayne
you will need the quotes around $barcode in the query like dipit said if you are using chars, other thing .... where do you connect to the database? is anything getting inserted/update in the database or does it just get to that part of the if...else statement?

hi

Posted: Thu Apr 08, 2004 6:29 am
by dipit
you problem is solved ?
if not let me know in more simple words
dipit

Posted: Thu Apr 08, 2004 8:03 am
by nevermiss9
thanks for your help guys but i found out the problem i had to use mysql_num_rows to beable to do what i want..
here is my fix to the solution...

$result=mysql_query("SELECT fld_barcode FROM tbl_products WHERE fld_barcode='$barcode'");
$norows=mysql_num_rows($result);

if ($norows>0) {
mysql_query("UPDATE tbl_products SET fld_barcode='$barcode', fld_name='$name', fld_price='$price' WHERE fld_barcode=$barcode");
$jupdate=$jupdate+1;
} else {
mysql_query("INSERT INTO tbl_products (fld_barcode, fld_name, fld_price) VALUES ('$barcode', '$name', '$price')");
$jinsert=$jinsert+1;
print ("$barcode, $name, $price<br>");
}

using if (mysql_query()) will not give me a true or false answer.