A better understanding 3

[keepingbusy]

Hi all

Can someone please explain how the following works and if there is a way to overcome it?

I have set up a site using oscommerce (no problems with that). I now wish to upload a self-populated csv file that I will create and export from a table in Visual Foxpro. We have tried something called EasyPopulate but it appears to be a no go.

The table we have contains tens of thousands of music titles and altering the structure is not an issue but in any case, the basics at the moment are:

Artist and title
Price
Date available
Description
Product quantity
Image source
Product status (1 = in stock / 0 - out of stock)

I am a complete newbie to MySQL but I am able to use myphpadmin in our web hosting and find the database that takes care of the tables/databases on the website.

Amongst the databases is one called products

This database contains all the fields that I require above with the exception of description which is a text field and product name used to store the artist and title.

Having worked my way around the databases (there are a lot) I found another one called products_description. This file contains the two missing fields I require. The structures of these fields are products_name - varchar(64) and products_description - text

I have had no problem using myphpadmin to export csv files for me to view the structure but ideally it would be better placed if ALL the fields I require were in one table/database.

So to the point, will I have to create two csv files from exported data from my Visual FoxPro tables in the required format for the SQL file (if thats the right terminology) or is there a way I can either amend the existing ones and just create and upload one?

Sorry for the long winded post guys but I hope that makes sense in what I'm trying to achieve.

Many thanks

Lee


Windows XP
Visual FoxPro Version 6 & 9

 

[rac2]

ideally it would be better placed if ALL the fields I require were in one table/database."

No, I am afraid that when it comes to relational databases, there will always be many tables. You might say that a database consists of tables, or contains tables.

It sounds like the database you are looking at with phpadmin has two tables, one named products_description and another table named products.

Possibly both of those tables have a column named id, or product_id, that is an INT datatype. In one of the tables, I suspect products_description it is an AUTO_INCREMENT column and a PRIMARY KEY. Likely it will have a FOREIGN_KEY constraint in the other table.

The id columns are used to tie tables together with JOINs. Because sometimes we wish to look at information from multiple tables as if it came from one table.

So I think that you must create two files to export data, one to populate the products table and one to populate the products_description table.

It may be that the FoxPro database you have created has a single table. And that each different product is represented in one row in that table. If so it will be easier to export the data. Especially if the FoxPro table has an id column. If not, I suggest that you create one before you start the export. That column will be included in both CSV files.

If the existing MySQL tables have the constraints and the auto-increment features I mentioned, then populating them will require turning off the constraints and auto-increment during the import. I dont know how to do that.

HTH

 

[TonyGroves]

keepingbusy:

First, a note about terminology. A table is a collection of similarly-structured records; a database is a collection of tables and associated data.


Of course, as you say, the best solution is to merge the two tables into one, assuming there is a one-to-one correspondence between the two. The structure of your tables is not at all clear, but you might do it using something like:
[tt]
CREATE TABLE newproducts AS
SELECT *
FROM
products p
LEFT JOIN products_description d
ON p.artistandtitle = d.productsname
[/tt]
You would then need to recreate any indexes on the table.

If that can't be done, then, if you are using (I think) MySQL 5.0 or later, you could use the same CSV file to update both tables. You would use 2 SQL statements, something like:
[tt]
LOAD DATA LOCAL INFILE '/tmp/products.csv'
INTO TABLE products
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
(artistandtitle,price,dateavailable,@dummy,quantity,imagesource,status)
[/tt]
In that statement, the "@dummy" corresponds to the 4th field in the file, which gets ignored. With earlier MySQL versions, that trick can't be used; instead any fields-to-be-ignored must be at the end of each record in the file, and you would simply omit them from your LOAD DATA statement. The full LOAD DATA syntax is explained in the MySQL manual.


If you can't make sense of the above, then please post the structures of the relevant tables (i.e. the output of "SHOW CREATE TABLE tblname").

 

[keepingbusy]

Hi rac2

Thank you for your reply.

Picking up on some of your comments:

It sounds like the database you are looking at with phpadmin has two tables, one named products_description and another table named products.

Yes, there are two as you have mentioned and looking at the structures both have a primary keyname called products_id.

This is similar to FoxPro databases with a relational link etc to tables.

I have made a back of the whole SQL file from my project and if I'm right together with you comment:

The id columns are used to tie tables together with JOINs

I suppose the best course of action here would be to create two FoxPro tables with a common link (e.g. products_id field) populated with an incremental number in each matching up the two records (or JOINS as you descibe it).

My only reservation here is if this will have any impact on other tables within the database.

I will try this but I'm going to wait to see if anyone else has any comments regarding this.

Thank you again for your valuable post.

Lee


Windows XP
Visual FoxPro Version 6 & 9

 

[keepingbusy]

Hi TonyGroves

Think I must have posted the same time as you!

Thank you for your information and suggestions.

First, a note about terminology. A table is a collection of similarly-structured records; a database is a collection of tables and associated data.

Appears this is the same as FoxPro but please excuse my ignorance being a bit of a newbie to MySQL.

The version of MySQL I have access to is 4.1.19-standard-log

I will take a look at what you have suggested over the next few days and post back.

What are views on my comments My only reservation here is if this will have any impact on other tables within the database.

Thanks again

Lee

Windows XP
Visual FoxPro Version 6 & 9

 

[keepingbusy]

Below are the structures of the tables. As mentioned I will do some work on this and post back in the next few days.

Lee

Windows XP
Visual FoxPro Version 6 & 9

 

[TonyGroves]

My only reservation here is if this will have any impact on other tables within the database.

Merging the two tables (assuming they are one-to-one which they appear to be) won't have any impact on other tables, as all the tables are independent, unless you are using foreign key constraints which refer to the table which you are eliminating, which does not seem to be the case. Queries which refer to that table will of course have to be amended.

 

[keepingbusy]

Merging the two tables (assuming they are one-to-one which they appear to be) won't have any impact on other tables...

That's reassuring and my thanks for your last post.

I'll give a reply or result over the next few days.

Lee

Windows XP
Visual FoxPro Version 6 & 9

 

[keepingbusy]

Just an update on this thread, I have now been able to download the structure from both tables mentioned, import them into a FoxPro table, amend some of the prices of the records for test purposes, export the CSV file from the FoxPro table and upload the files back.

This has worked so I'm going to look at a software app now to automate the process so it appears at the moment, this process is possible.

Thanks again both, I'll post back soon

Lee

Windows XP
Visual FoxPro Version 6 & 9

 

[richcleverley]

Just be aware that many hosts don't allow you to remotely connect to mysql databases. Therefore, If you get an app that is used to connect to a database and update it (rather like a local version of phpmyadmin in effect) you may not be able to connect to your database itself. You could always run a local version on your own computer, do any changes then export using the app or phpmyadmin and import it to the main database using the hosts installation of phpmyadmin.

Just something to bear in mind!!

Richard

 

[keepingbusy]

Hi Richard

...do any changes then export using the app or phpmyadmin and import it to the main database using the hosts installation of phpmyadmin

Being new to all this, I found through phpmyadmin I was able to download two CSV files that relate to the information I was looking for.

So as you have quite rightly pointed out, I am now able to produce the required list through a FoxPro application which exports the CSV file from a different data source and upload back as a CSV with the same original structure through myphpadmin.

Thank you for your valuable post and information

(My thanks also to everyone else on this thread)

Lee

Windows XP
Visual FoxPro Version 6 & 9

 

[keepingbusy]

Hi all

As a follow up to this thread the structure of the tables have been created within FoxPro tables to re-create CSV files for upload. I've checked both structures and both appear to be the same as that shown above on a previous post I made.

I am trying to upload a newly populated list in the products table usng a products.csv file. After a minute I encounter this error:

Error
Invalid field count in CSV input on line 209.

I've checked to see if there is anything missing that may cause this error but cant find anything obvious.

Please can someone explain what this could be?

The products.csv file is 2.61MB (2,747,376 bytes) in size and I notice you can use:

Allow interrupt of import in case script detects it is close to time limit. This might be a good way to import large files, however it can break transactions.

I have tried this with the check box ticked and unticked but still receive the same error.

Many thanks

Lee

Windows XP
Visual FoxPro Version 6 & 9

 

[towerbase]

It would help if you showed us line 209 of the file and maybe a few lines on either side of line 209.

I would guess that line 209 contains a text field containing a comma e.g.

The Good, The Bad and The Ugly

and the whole title should be delimited with quotes e.g.

"The Good, The Bad and The Ugly"

but without seeing what is causing the error it's only a guess

Andrew
Hampshire, UK

 

[keepingbusy]

Andrew

Here are the lines from the CSV file:

"208","1","BETH ORTON CENTRAL RESERVATION","","",""
"209","1","BETH ORTON COMFORT OF STRANGER","","",""
"210","1","BETH ORTON GREATEST","","",""
"211","1","BETTE MIDLER EXPERIENCE DIVINE","","",""

Lee

Windows XP
Visual FoxPro Version 6 & 9

 

[keepingbusy]

Just an update on this thread for anyone in the future. I checked some of the other titles I was trying to upload and some did include a ,

I now run a piece of code within a Visual FoxPro APP to remove them before the file is uploaded.

Thanks for your time to all who posted.

Lee

Windows XP
Visual FoxPro Version 6 & 9