-
sdm7001
-
-
Offline
-
Junior Member
-
- Posts: 20
-
Karma: 0
-
|
I have several thousand records to import.
I see that there is no import feature in Cook ... is the easiest way to do this through the MySQL DB after the component is installed to Joomla?
Thank you,
SDM
|
|
-
admin
-
-
Offline
-
Administrator
-
-
Chef
- Posts: 3711
- Thank you received: 987
-
Karma: 140
-
|
Not available yet.
Sorry.
|
Coding is now a piece of cake
The following user(s) said Thank You: sdm7001
|
-
sdm7001
-
-
Offline
-
Junior Member
-
- Posts: 20
-
Karma: 0
-
|
Is the easiest way to do this through the MySQL DB after the component is installed to Joomla?
|
|
-
JoomGuy
-
-
Offline
-
Moderator
-
-
Joomla Enthusiast, Lover of Cooking
- Posts: 1115
- Thank you received: 195
-
Karma: 64
-
|
Its the only way currently (locally).
I'd suggest reading the data from a csv file as its dead easy!
The other advantages of doing this is that you can;
- Transform the data before input
- custom validation & checking before input
- Make it dynamic in a function you could reuse for any of your tables by passing table name in via input field, pass the field names in the first line of your csv file or get them from array_keys() on your db
Gez
|
Need help with your Cook/Joomla Project? . PM me to find out what I can help with. NO time wasters please!!!
The following user(s) said Thank You: sdm7001
|
-
VeCrea
-
-
Offline
-
Platinum Member
-
-
Absolute JCook fan
- Posts: 473
- Thank you received: 100
-
Karma: 30
-
|
Have been importing many times through php my admin, never had a problem
|
|
-
JoomGuy
-
-
Offline
-
Moderator
-
-
Joomla Enthusiast, Lover of Cooking
- Posts: 1115
- Thank you received: 195
-
Karma: 64
-
|
Something that I forgot to ask for clarification on...
Are you asking if there's a way to import records into the sandbox, or how to build it into your component - i.e. you want to create the functionality for your component to import records?
There are many security & performance implications of J-Cook allowing such a feature in the sandbox. Namely, the server resources getting slammed by many many users importing potentially millions of records at any time + the potential to do a lot of damage to the J-Cook server. This has been discussed previously here on the forum and understandably, admin's response to such a feature is NO!
If on the other hand, you want to look at the CSV option I mentioned earlier on in the topic, I will post a brief description/code on how to do it shortly.
This is something that you may be able to put in a helper script to call from your component as a function.
Gez
|
Need help with your Cook/Joomla Project? . PM me to find out what I can help with. NO time wasters please!!!
The following user(s) said Thank You: admin
|
-
JoomGuy
-
-
Offline
-
Moderator
-
-
Joomla Enthusiast, Lover of Cooking
- Posts: 1115
- Thank you received: 195
-
Karma: 64
-
|
OK, here it goes...
The following pre-supposes that your CSV file has been formed correctly, i.e. integers ARE NOT enclosed in quotations and 'strings' ARE.
It's strength is that an associative array is built out of the first line of the file which will allow you to easily transform/manipulate your input data. For example, say for instance, you have some fields "address1, address2, address3", all of which you want to insert into one field, with a little bit of if/else evaluation, they can easily be appended to a new var in the loop.
N.B. To achieve this, you will also have to overwrite the $keys var to account for this. This function could then be extended to take a third argument - an array of $newKeys - with which to build your new array (lines of csv) before they are imploded into a string of values to insert. Furthermore, you could make it optional by checking for it's existence at the top of the function, only overwriting it if it exists.
TO DO:
- Add the following to your component's helper file;
function importCSVFile($file,$table)
{
//GET THE CSV FILE & CONTENTS
$csv = array_map("str_getcsv", file("$file.csv",FILE_SKIP_EMPTY_LINES));
$keys = array_shift($csv);
//Make the data an associative array to target your data using $csv['your_field_name']
foreach ($csv as $i=>$row) {
$csv[$i] = array_combine($keys, $row);
}
/* ...NOTE ANY CUSTOM DATA MANIPULATION GOES HERE... */
//Ensure that whatever new array you've assigned replaces $csv in the next foreach() loop
/* ...END CUSTOM */
//Loop to prepare each row of vals inside parentheses
foreach($csv as $row=>$value){
$rowVals[] = "(". implode(", ", $value) . ")" ;
}
//Get the keys from the CSV to use as the field names in query
$keys = implode($keys, ", ");
//Build the insert
$sql = "INSERT INTO $table ($keys) ";
$sql .= "VALUES ". implode(", ", $rowVals);
return $sql;
}
- To call the function, simply pass the 2 arguments $file & $table (both strings) as follows;
$query = importCSVFile("path_to/my_csv_file", "my_table"); N.B.
$file = file path with no extension (.csv added in function), $table = the name of the table
- To check your query,
- Run your query
$db =& JFactory::getDBO();
$db->setQuery($query);
$db->query();
Hope it helps!!!
Gez
|
Need help with your Cook/Joomla Project? . PM me to find out what I can help with. NO time wasters please!!!
Last Edit: 27 Sep 2012 12:15 by JoomGuy.
|
-
VeCrea
-
-
Offline
-
Platinum Member
-
-
Absolute JCook fan
- Posts: 473
- Thank you received: 100
-
Karma: 30
-
|
Man... k++
Now the same thing for exporting csv please
Thanks
|
Last Edit: 27 Sep 2012 12:41 by VeCrea.
|
-
admin
-
-
Offline
-
Administrator
-
-
Chef
- Posts: 3711
- Thank you received: 987
-
Karma: 140
-
|
Now the same thing for exporting csv please
MDR !!!
VeCrea can understand the joke.
|
Coding is now a piece of cake
|
-
JoomGuy
-
-
Offline
-
Moderator
-
-
Joomla Enthusiast, Lover of Cooking
- Posts: 1115
- Thank you received: 195
-
Karma: 64
-
|
I haven't actually done that ever!
Honestly, it shouldn't be too difficult - importing is supposed to be a lot more difficult and I'm no expert by any stretch of the imagination. Let me take a look!
Thanks for the Karma!
Gez
|
Need help with your Cook/Joomla Project? . PM me to find out what I can help with. NO time wasters please!!!
|
-
JoomGuy
-
-
Offline
-
Moderator
-
-
Joomla Enthusiast, Lover of Cooking
- Posts: 1115
- Thank you received: 195
-
Karma: 64
-
|
Just a bit of testing and a function to export CSV to file with or without attachment (download) will exist very soon!
|
Need help with your Cook/Joomla Project? . PM me to find out what I can help with. NO time wasters please!!!
The following user(s) said Thank You: admin
|
-
JoomGuy
-
-
Offline
-
Moderator
-
-
Joomla Enthusiast, Lover of Cooking
- Posts: 1115
- Thank you received: 195
-
Karma: 64
-
|
READY!!! Here we go...
Similar to the export script I provided, this could be added to your helper functions
I've taken this a bit further in that a few more parameters/args can be passed to grab certain fields, add a where clause, overwrite the file name by appending the time() function, auto download the file and grab the field names as headers or not...
Here's the script: function exportCSVFile($file, $table, $fields = false, $where = false, $overwrite = false, $download = true, $headers = true) {
//If Overwrite False, check if the file exists and make unique
if(!$overwrite){
if(file_exists($file)) {
$file = $file . "_" . time() . '.csv';
}
}
// Check if download required
if($download) {
// send response headers to the browser
header( 'Content-Type: text/csv' );
header( 'Content-Disposition: attachment;filename='.$file);
$fp = fopen('php://output', 'w');
} else {
$fp = fopen($file, 'w');
}
//The Query
// Have fields been passed?
if(!$fields) {
$fields = "*";
}
// Has where clause been passed?
if(!$where) {
$where = "";
}else{
$where = "WHERE " . $where;
}
$query = "SELECT $fields FROM $table $where";
$db = JFactory::getDBO();
$db->setQuery($query);
$result = $db->query($query);
if($headers) {
// output header row (if at least one row exists)
$row = mysql_fetch_assoc($result);
if($row) {
fputcsv($fp, array_keys($row));
// reset pointer back to beginning
mysql_data_seek($result, 0);
}
}
while($row = mysql_fetch_assoc($result)) {
$line = "";
$comma = "";
foreach($row as $value) { //Escape quotes properly
$line .= $comma . '"' . str_replace('"', '""', $value) . '"';
$comma = ",";
}
$line .= "\n";
fputs($fp, $line);
}
//Close the file
fclose($fp);
} To call the function...$export = exportCSVFile("your_filename.csv", "table_name", false, "WHERE your field = true"); The args
You must pass at least 2 arguments (the first 2) - $file & $table.
Arguments 3, 4, 5, 6 & 7 are optional as they are set to default values in the function.
- $fields can be passed a comma separated list of fields from the $table - example "field1, field2, field3"
- $where can be passed a valid where clause - example "id > 100" or "id >10 AND published = 1"
- $overwrite can be passed a true or false - true will always overwrite the filename passed in arg1 if it exists, false (default) will append an underscore "_" and the current time() if the file exists already.
- $download can be set true (default) or false and will force a download of the file as opposed to storing it
- $headers allows you to auto grab field names for the first line of the file - set this = true or false.
If you want to pass any non-required arg, naturally, you will have to pass any consecutive args that come after 2 until you reach the arg(s) you wish to pass.
Hope this helps!
Gez
|
Need help with your Cook/Joomla Project? . PM me to find out what I can help with. NO time wasters please!!!
Last Edit: 27 Sep 2012 19:27 by JoomGuy.
The following user(s) said Thank You: VeCrea
|
-
VeCrea
-
-
Offline
-
Platinum Member
-
-
Absolute JCook fan
- Posts: 473
- Thank you received: 100
-
Karma: 30
-
|
Will stay up all night trying this. would be soooooo perfect
|
|
-
JoomGuy
-
-
Offline
-
Moderator
-
-
Joomla Enthusiast, Lover of Cooking
- Posts: 1115
- Thank you received: 195
-
Karma: 64
-
|
It worked first time - UNBELIEVABLE!
One thing to note - currently, if you pass in field names that don't exist, the SQL errors get printed in the CSV output.
Gez
|
Need help with your Cook/Joomla Project? . PM me to find out what I can help with. NO time wasters please!!!
|
-
JoomGuy
-
-
Offline
-
Moderator
-
-
Joomla Enthusiast, Lover of Cooking
- Posts: 1115
- Thank you received: 195
-
Karma: 64
-
|
|
Need help with your Cook/Joomla Project? . PM me to find out what I can help with. NO time wasters please!!!
|
|