submitting sql queries through mysql

[svakanda]

So I have this database and this PHP program sitting on a server. The PHP program takes a flatfile and uploads it onto a single table on this database. It does this fine, however it does not erase the contents of the table beforehand.
So what I've been doing is going in through phpmyadmin, dropping the table, and then reinstating it with the following commands.
first I drop the table

DROP TABLE `tlb_itemdetail`

then I create it back with the correct settings

CREATE TABLE `tlb_itemdetail` (
  `vItemCode` int(6) NOT NULL auto_increment,
  `cat_id` int(11) NOT NULL default '1',
  `txProductTitle` text NOT NULL,
  `dPrice` double(6,2) NOT NULL default '0.00',
  `iWeight` int(6) NOT NULL default '0',
  `iVolume` int(8) NOT NULL default '0',
  `ebayPrice` double(6,2) NOT NULL default '0.00',
  `txHtmlCode` text NOT NULL,
  `vProductImage` text NOT NULL,
  `vCertificateImage` text NOT NULL,
  `vVolumetricImage` text NOT NULL,
  `vProductImage_Thumb` text NOT NULL,
  `vCertificateImage_Thumb` text NOT NULL,
  `vVolumetricImage_Thumb` text NOT NULL,
  PRIMARY KEY  (`vItemCode`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

This all works fine by hand. What i'd really like to do though, is automate the thing. So I figured I'd just drop those two sql queries into the function that uploads the text file...and it'd all work out. Simple right? only not. Here is the php code...

$sql1="DROP TABLE tlb_itemdetail";
 
$drop = mysql_query($sql1);
if ($drop) $dropstatus=1; else $dropstatus=0;
    
    $sql2="CREATE TABLE `tlb_itemdetail` (
`vItemCode` int( 6 ) NOT NULL AUTO_INCREMENT ,
`cat_id` int( 11 ) NOT NULL default '1',
`txProductTitle` text NOT NULL ,
`dPrice` double( 6, 2 ) NOT NULL default '0.00',
`iWeight` int( 6 ) NOT NULL default '0',
`iVolume` int( 8 ) NOT NULL default '0',
`ebayPrice` double( 6, 2 ) NOT NULL default '0.00',
`txHtmlCode` text NOT NULL ,
`vProductImage` text NOT NULL ,
`vCertificateImage` text NOT NULL ,
`vVolumetricImage` text NOT NULL ,
`vProductImage_Thumb` text NOT NULL ,
`vCertificateImage_Thumb` text NOT NULL ,
`vVolumetricImage_Thumb` text NOT NULL ,
PRIMARY KEY ( `vItemCode` )
) ENGINE = MYISAM DEFAULT CHARSET = latin1 AUTO_INCREMENT =1";
 
$add = mysql_query($sql2);
if ($add) $addstatus=1; else $addstatus=0;
 
        $sql3="INSERT INTO tlb_itemdetail(cat_id,txProductTitle,dPrice,iWeight,iVolume, ebayPrice,txHtmlCode,vProductImage,vCertificateImage,vVolumetricImage,vProductImage_Thumb,vCertificateImage_Thumb,vVolumetricImage_Thumb) VALUES('$cat_id','$txProductTitle','$dPrice','$iWeight','$iVolume','$ebayPrice','$txHtmlCode','$vProductImage','$vCertificateImage','$vVolumetricImage','$vProductImage_Thumb','$vCertificateImage_Thumb','$vVolumetricImage_Thumb')";
 
$res = mysql_query($sql3) ;
if($res) $newproductstatus=1; else $newproductstatus=0;

So all the queries return 'TRUE'...but it ends up with only a single item in the DB. Now I know that $sql3 section works fine as long as I reset the tlb_itemdetail table by hand through phpmyadmin...it's only when i try to execute the query from within php that it goes a bit wonky...and by wonky I just mean that only 1 item out of like 172 actually make it into the database. Now I'm quite sure that there are more efficient ways to do this...and I am definitely sure that I'm not understanding everything here. Could someone help point me in the right direction? I think it must have something to do with the $sql2 query, and probably the way it's formatted. I've tried taking out all the line breaks, but it didn't seem to help. Any thoughts for me good people?

 

[illusion]

You can just clear a table by using TRUNCATE TABLE `tlb_itemdetail`
... You don't need to drop it and readd, just truncate the table

 

[svakanda]

thanks for the response illusion, I just tried that but I'm still having problems. I'm going to go out on a limb and guess that my problems run much deeper

thanks for the input!

 

[ozonew4m]

it looks to me like the problem is your php loop.. $sql3 will only be run once with the code above..

how are you parsing the text file?