Page 1 of 1

submitting mysql queries through php

Posted: Mon Apr 07, 2008 11:50 pm
by 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

Code: Select all

DROP TABLE `tlb_itemdetail`
then I create it back with the correct settings

Code: Select all

 
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...

Code: Select all

$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?

Re: submitting mysql queries through php

Posted: Tue Apr 08, 2008 2:41 am
by Kadanis
Just curious but why don't you just use a DELETE query before uploading the file. It would be a lot more efficient than dropping and recreating the table every time.

Code: Select all

 
DELETE FROM tlb_itemdetail;
 
This would empty the table.

Exactly how are you running this? Is there a loop over the INSERT statment? I only ask because an INSERT statement usually only generates one row. The row with the values passed by your variables.

If you are loading in from a flat file, I would have expected a LOAD DATA statement which would attempt to parse the whole file into a table

Code: Select all

 
LOAD DATA LOCAL INFILE '/path/to/file' 
            INTO TABLE `table_name` 
            FIELDS TERMINATED BY ','
            LINES TERMINATED BY '\r\n';
 
The above example will parse in a DOS based comma delimited text file with no field enclosing character.

Re: submitting mysql queries through php

Posted: Tue Apr 08, 2008 2:49 am
by onion2k
I think "TRUNCATE tlb_itemdetail;" is quicker than "DELETE FROM tlb_itemdetail;". It might reset any auto increment fields though.

Re: submitting mysql queries through php

Posted: Tue Apr 08, 2008 3:57 am
by Chris Corbyn
onion2k wrote:I think "TRUNCATE tlb_itemdetail;" is quicker than "DELETE FROM tlb_itemdetail;". It might reset any auto increment fields though.
It is immensely faster (almost instant) since it doesn't mess around with keys and quite literally blows away the table data. It will reset auto increment values yes. I prefer TRUNCATE over DELETE when I'm doing manual cleanups and admin stuff :)