inserting into mysql

Question

Hello,

I have the following database table created thus:

CREATE TABLE AUCTIONS (
  ARTICLE_NO      VARCHAR(20),
  ARTICLE_NAME    VARCHAR(100),
  SUBTITLE        VARCHAR(20),
  CURRENT_BID     DECIMAL(5,2),
  START_PRICE     DECIMAL(5,2),
  BID_COUNT       VARCHAR(20),
  QUANT_TOTAL     VARCHAR(20),
  QUANT_SOLD      VARCHAR(20),
  ACCESSSTARTS    VARCHAR(20),
  ACCESSENDS      VARCHAR(20),
  ACCESSORIGIN_END VARCHAR(20),
  SELLER_ID       VARCHAR(20),
  BEST_BIDDER_ID  VARCHAR(20),
  FINISHED        TINYINT,
  WATCH           TINYINT,
  BUYITNOW_PRICE  DECIMAL(5,2),
  PIC_URL         VARCHAR(20),
  PRIVATE_AUCTION TINYINT,
  AUCTION_TYPE    VARCHAR(20),
  ACCESSINSERT_DATE     VARCHAR(20),
  ACCESSUPDATE_DATE     VARCHAR(20),
  CAT_1_ID        VARCHAR(20),
  CAT_2_ID        VARCHAR(20),
  ARTICLE_DESC    TEXT,
  COUNTRYCODE     VARCHAR(20),
  LOCATION        VARCHAR(20),
  CONDITIONS      VARCHAR(20),
  REVISED         TINYINT,
  PAYPAL_ACCEPT   TINYINT,
  PRE_TERMINATED  TINYINT,
  SHIPPING_TO     VARCHAR(20),
  FEE_INSERTION   DECIMAL(5,2),
  FEE_FINAL       DECIMAL(5,2),
  FEE_LISTING     DECIMAL(5,2),
  PIC_XXL         TINYINT,
  PIC_DIASHOW     TINYINT,
  PIC_COUNT       VARCHAR(20),
  ITEM_SITE_ID    VARCHAR(20),
  STARTS          DATETIME,
  ENDS            DATETIME,
  ORIGIN_END      DATETIME,
  PRIMARY KEY ( `ARTICLE_NO` ));

Which is fine.

However when trying to input this row:

5555555 This is the best ARticle in the world!!!!!! True 55.55 3232.2 6 5 5 8.7.2008 17:18:37 8.7.2008 17:18:37 8.7.2008 17:18:37 5454 7877 1 1 46.44 http//www.x.com 1 good 8.7.2008 17:18:37 8.7.2008 17:18:37 22 44 ANZTHINGcanogoherehihhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 77 germanz none 1 1 1 446 everzwhere australia 22.2 22.2 22.2 1 1 5 1

As a tab delimited text file, there seems to be a problem around buy_it_nowprice

buy_it_nowprice shows correctly as 46.44 when doing select buy_it_nowprice from Auctions, but select pic_url from Auctions shows returns 1 instead of the website, and as a result all the subsequent records are out of place.

I am sure I have missed a field or something, but I can not work out what it is.

Answer 1

You have a space after 46.44, not a tab,

Answer 2

There is a space after the number. If you have the option of using another field delimiter from the application that creates the text file for you, that will probably make these kinds of problems easier to spot.

Answer 3

Check that the buyitnow_price and pic_url data fields are actually tab separated, it looks as though it might be a space, not a tab