cmsBuilder and PHP's "LOAD DATA LOCAL INFILE"
7 posts by 2 authors in: Forums > CMS Builder
Last Post: December 5, 2008 (RSS)
By rcrofoot - December 1, 2008
I am trying to allow quick data entry into a cmsBuilder section editor by taking an excel spreadsheet, converting it to "tab delimited", and using PHP's "[font "Courier"]LOAD DATA LOCAL INFILE" command...rather than my customer having to enter hundreds of records, one at a time...
[font "Courier"]I was planning to:
[font "Courier"]1)have my customer use cms builder to upload the xls file to the server,
[font "Courier"]2)and then run a custom PHP webpage that would let them select the xls file on the server, convert to tab delimited format, and populate the appropriate cms table, resulting in the data displaying on their site...
[font "Courier"]Is there any way I can cut this down to a 1-step process for my customer using cms builder, or do you think bypassing cms altogether, and doing it all on a PHP page is the way to go...
[font "Courier"]Just trying to think this through before I start billing time...
[font "Courier"]Thanks for any thoughts/suggestions you might have...
[font "Courier"]Rick
Re: [rcrofoot] cmsBuilder and PHP's "LOAD DATA LOCAL INFILE"
By Dave - December 1, 2008
We have a generic import module we're working on called "Import Any" that lets you import CSV files into CMS Builder. We're still working out pricing for that but feel free to email me direct at dave@interactivetools.com and we can see if it will work for you.
I'll need to know what the data looks like. As importers in general can be tricky and require data consistency.
Next, if you're able to import directly into the MySQL, that would work as well. Some tools like phpMyAdmin etc have some functionality to do this.
Hope that helps.
interactivetools.com
Re: [Dave] cmsBuilder and PHP's "LOAD DATA LOCAL INFILE"
By rcrofoot - December 3, 2008
In trying to use the php LOAD DATA command so my client can use a tab delimited text file to populate their site, it seems that because I am using an "upload" field in my CMS menu, an additional table needs to be considered when doing a bulk insert...The tables are "cms_certifications" (this was created from the CMS menu I created), and cms_uploads...I looked at these tables' data dictionary and the data itself, and because these 2 tables appear related, I'm beginning to think that :LOAD DATA" will get very messy and is not the way to go...
Rather, my next thought is to read each line of the text file, and perform a SQL INSERT INTO...That way I could use the "auto number value generated from each line (primary key in cms_certifications), and use it as the forein key value in cms_uploads...
It would be nice if I only had to deal with 1 table...Would the software you mentioned yesterday help me with completing the project...
Rick
Re: [rcrofoot] cmsBuilder and PHP's "LOAD DATA LOCAL INFILE"
By Dave - December 3, 2008
Do you need to import the data just once or on an ongoing basis? And is there images or uploads in the content (those are trickier)?
If you're able to loop over the data in php then inserting it directly might be a workable solution.
interactivetools.com
Re: [Dave] cmsBuilder and PHP's "LOAD DATA LOCAL INFILE"
By rcrofoot - December 3, 2008
I'm trying to work thru the php 'load data' issue, and figure out the best approach...I'll keep you updated...
As an aside, and something you might not be aware of: I created a section editor with an "update" field, and it does not show up when I view the table fields in phpMyAdmin...Just to be sure, I created another section editor with an upload field (and various other text & list fields), and when I view the new table in phpMyAdmin, I see all fields except the upload field...I'm trying to make sense of this...I just checked the cms_uploads table, and it's not there either...Nowhere do I see the upload field outside of the CMS environment...Is it not a field in the mysql table that CMS just created...Am I missing something...???
Also, my client has just requested that the upload field "certificate_pdf" be displayed on the CMS Editor ListPage. The ListPage Fields textbox contains the following: dragSortOrder,product,model,certificate_pdf...
But even though certificate_pdf is specified (this is the upload field), absolutely no data is displayed...All the other fields specified display their data...I would have thought the pdf filename would display by including the name of the upload field "certificate_pdf"...I then added the pdf filename to the Title & Caption fields within the upload field for 1 record, and still nothing would display on the Editor ListPage for this field...
Any help or enlightenment would be appreciated...
Thanks, Rick
Re: [Dave] cmsBuilder and PHP's "LOAD DATA LOCAL INFILE"
By rcrofoot - December 4, 2008
Please disregard all previous posts regarding "LOAD DATA"...I found that by using INSERT INTO for both my cms_certifications & cms_uploads tables, I could accomplish my objective...
One question: when inserting data into the cms_uploads table, I noticed I could not write a numeric 1[/#ff0000] to the 'order' field, even though its datatype is int(10)...(I used 1[/#ff0000] because that's what cmsBuilder writes when I tell it to save a record)...So I simply ommitted the field and value from the INSERT INTO statement, and as a result a 0[/#ff0000] (zero) was written instead...Is this ok to do, or will it come back later and make life difficult...What's the purpose of the 'order' field anyway??? :-)
Thanks for your help...Rick
Re: [rcrofoot] cmsBuilder and PHP's "LOAD DATA LOCAL INFILE"
By Dave - December 5, 2008
I'm not sure why you couldn't write a 1 because as you said the field is an int. The purpose of that field is to store the sorting order of the uploads within the record.
Hope that helps!
interactivetools.com