So I have this database and this


  program sitting on a server.  The


  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


 myadmin, dropping the table, and then reinstating it with the following commands.
first I drop the table
[code=text]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(Applause 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`)

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



[code=text]$sql1="ApplauseROP 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` )

$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

php's only when i try to execute the query from within


  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?


Svak... may I ask why you must drop and rebuild the table every time?

If you "truncate table tlb_itemdetail" MySQL will do this for you each time, and reset the autoincrement as well, which is why I assume you're going through this work.

In this way, you can use


 MyAdmin to make the table exactly the way you want it, then just clear it before import rather than worrying about rebuilding it right as well.

There is also a command to reset the autoincrement value, so you could delete * from tlb_imtedetail then set the autoincrement and you'd get the same effect again.

Am I missing something here?

Perkiset's Place Home   Politics @ Perkiset's