CODEWITHSUNDEEP
phpmysqlsqljoomla
Nicholas Arehart
Nicholas Arehart

Reputation: 135

Export/Import Table Function

I'm using Joomla 1.7 and I've got a little problem and was wondering if anyone could help. I have a component installed on my site that users can use to create playlists. These are stored in a table that contains fields for name, user id, playlist id, playlist name, and songs. The songs each have a unique id and a playlist is held in a field like so: 7,2,4,68,70.

What id like to do is create a an import/export feature. I figured the easiest thing for exporting would be to export a users playlist table as a sql file. Then for importing an sql file would have its fields read and new playlist table would be created using only the song field and name field. The user id field would be filled in with the current user and the playlist id field checked against existing playlist ids and a new one assigned.

Right now I know that current playlist creation is being managed by the component in components/com_muscol/models.php

I started trying to create the function but I am a little lost on how to export the data as an sql file:

function get_mysql(){
    $db =& JFactory::getDBO();

    $query = 'SELECT pl.*, us.name AS username FROM #__muscol_playlists AS pl         LEFT JOIN #__users AS us ON us.id = pl.user_id WHERE pl.id = ' . $this->_id ;
    $this->_db->setQuery( $query ); 
    }

Thanks for your help...

Upvotes: 0

Views: 1003

Answers (1)

wyqydsyq
wyqydsyq

Reputation: 2040

To export your data as SQL, just loop through it. Something like this should work:

$sql = array();
$query = mysql_query("YOUR QUERY HERE");

while($result = mysql_fetch_assoc($query){
    $cols = array('user_id'); // put ID's etc. into $cols and $vals
    $vals = array($user_id);
    foreach($result as $col => $val){
        $cols[] = $col;
        $vals[] = $val;
    }
    $sql[] = "INSERT INTO `#__muscol_playlists` (`".implode('`,`', $vals)."`) VALUES('".implode('`,`', $cols)."')";
}

echo explode("\n", $sql);

(note: This was written on the spot as an example and hasn't been tested at all, so don't expect it to magically work if you just copy paste it)

Upvotes: 1

Related Questions