submitting mysql queries through php

To The Live Thread

Thread: submitting mysql queries through php

Back to category: Database Stuff

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

php

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

ROP 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

php

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

perkiset

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

php

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?

Thread Categories

		Best of The Cache Home
		Search The Cache