The Cache: Technology Expert's Forum
 
*
Welcome, Guest. Please login or register. September 18, 2019, 10:35:05 AM

Login with username, password and session length


Pages: [1]
  Print  
Author Topic: submitting mysql queries through php  (Read 3988 times)
svakanda
Expert
****
Offline Offline

Posts: 131



View Profile
« on: April 07, 2008, 10:14:21 PM »

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: (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(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: (text)
$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?
Logged

a ship is safe in the harbor, but that's not what it's for.
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #1 on: April 07, 2008, 10:34:39 PM »

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 phpMyAdmin 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?
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
dink
Expert
****
Offline Offline

Posts: 349


View Profile
« Reply #2 on: April 07, 2008, 10:57:42 PM »

 Ditto

You could also use the 'update into ...' command.

In case you want to do what you've outlined:
Code:
$FeedFile = "something.txt";

mysql_query("drop table something");

mysql_query("CREATE TABLE something(
~
~
~
)") or die(mysql_error());
             
   $feed = fopen($FeedFile, 'r');             
               
                $rowNum = 0;
                $recCount = 0;

                while($data = fgetcsv($feed, 3000, "\t")){
                    if($rowNum > 0){
     
              $i~           = addslashes($data[0]);
              $i~            = addslashes($data[1]);
              $i~           = addslashes($data[2]);


$sql = mysql_query("insert into something

(~, ~, ~)
values ('$i~', '$i~', '$i~'
)") or die(mysql_error());
}
$recCount++;
$rowNum++;             
}
fclose($feed);




I think that's right.
Logged

[quote Nutballs]
the universe has a giant fist, and its got enough whoop ass for everyone.
[/quote]
vsloathe
vim ftw!
Global Moderator
Lifer
*****
Offline Offline

Posts: 1669



View Profile
« Reply #3 on: April 08, 2008, 05:52:19 AM »

I've always thought that as these things go, I'm a sloppy coder.

Thanks for making me feel better about my code.

 ROFLMAO ROFLMAO ROFLMAO

J/K
Logged

hai
svakanda
Expert
****
Offline Offline

Posts: 131



View Profile
« Reply #4 on: April 08, 2008, 07:38:29 AM »

I'm so glad I could be of service to you vs!  Thankyou Perk...no you aren't missing anything, quite the opposite in fact.   Idea...  I'll go try that out immediately!
Logged

a ship is safe in the harbor, but that's not what it's for.
svakanda
Expert
****
Offline Offline

Posts: 131



View Profile
« Reply #5 on: April 08, 2008, 08:00:58 AM »

okay It's still not working how I want it to.
When I drop the table by hand and recreate it, then load the values in with the sql3 query below it works fine...but if I try to truncate first with the sql2 query right before I load the new values with sql3, it only ends up with the VERY last item in the database, and nothing else.  Same as it was doing before.  Do I not understand something about putting sql queries back to back?  Do I need to slow the script down or something in between the queries?  doesn't the mysql_query wait for a response before continuing?

Could there be something else I'm doing not correctly?  Something terribly n0obish perhaps?

here is the code
Code:
$sql2 = "TRUNCATE TABLE tlb_itemdetail";

$add = mysql_query($sql2);
if ($add) $truncatestatus=1; else $truncatestatus=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) $insertstatus=1; else $insertstatus=0;
Logged

a ship is safe in the harbor, but that's not what it's for.
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« Reply #6 on: April 08, 2008, 09:09:37 AM »

im assuming you have a loop somewhere that we are not seeing.
since the code you are showing would only add 1 entry to the DB.
You would need a loop around the insert statement which loops through each of your rows you want to add.

or do you have the entire thing inside a loop, or being called from a loop? in which case you are deleting everything each time, which would explain why the LAST item is the only one in the db. only the insert should be looped, not the truncate.
Logged

I could eat a bowl of Alphabet Soup and shit a better argument than that.
svakanda
Expert
****
Offline Offline

Posts: 131



View Profile
« Reply #7 on: April 08, 2008, 10:03:49 AM »

thats IT!@!!!!!!
omg nutballs, you're totally making sense to me now, aghhhh  =)   No wonder I ended up with just the last item in the db, it was resetting after every item in the loop.

/noobs all over himself!

thanks nutballs, you just made my day!  I'll go try it this afternoon!
Logged

a ship is safe in the harbor, but that's not what it's for.
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« Reply #8 on: April 08, 2008, 10:10:08 AM »

lol. no worries.

its not just a noob thing, though its a funny one for the observers. thanks for the giggle.  D'oh!
Logged

I could eat a bowl of Alphabet Soup and shit a better argument than that.
svakanda
Expert
****
Offline Offline

Posts: 131



View Profile
« Reply #9 on: April 08, 2008, 11:16:25 PM »

yup! thar she did!

thankyou kindly sir nutballs
Logged

a ship is safe in the harbor, but that's not what it's for.
Pages: [1]
  Print  
 
Jump to:  

Perkiset's Place Home   Best of The Cache   phpMyIDE: MySQL Stored Procedures, Functions & Triggers
Politics @ Perkiset's   Pinkhat's Perspective   
cache
mart
coder
programmers
ajax
php
javascript
Powered by MySQL Powered by PHP Powered by SMF 1.1.2 | SMF © 2006-2007, Simple Machines LLC
Seo4Smf v0.2 © Webmaster's Talks


Valid XHTML 1.0! Valid CSS!