Let's suppose that we have MySQL database with table categories:
CREATE TABLE `categories` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`category` varchar(64) DEFAULT NULL,
`description` varchar(64) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
Then, in CakePHP framework, you want to call a simple query as you did it with mysql_query in PHP with no framework.
For that you have the class Model::query()
First, you build the model. You don't want to use predefined Models for a table (like the model Categories for DB table Categories). Because you want to use perhaps this query for extracting data from two tables which are related.
So, first you have to create the model. For this you have to create the file APP/Model/Categs.php
class Categ extends AppModel {
public $useTable = false; // it is mandatory to have this line. Whithout this line,
// the Controller will search for table Categ; it doesn't matter that you have a sql query
public function getDataFromMyTable(){
$d = $this->query("SELECT * FROM categories");
return $d;
}
}
Second, you have to create the Controller. The file will be: APP/Controller/CategsController.php
class CostsController extends AppController {
public function index(){
$this->loadModel('Categ');
$rezQuery = $this->Categ->getDataFromMyTable();
$this->set('cs', $rezQuery);
}
}
?>
And now we have to create the view. We create folder APP/View/Categs (if this folder doesn't exists). And then we create the file index.ctp into this folder. The name of the ctp file should be the same as name of the function from Controller. In index.ctp we write:
Unfortunately, importing XML into MySQL is more like chocolate cheesecake, but there are solutions available. Just follow this guide and you'll be on your way to integrating Web services with your database in no time. Starting from scratchWith the growing popularity of XML, developers have found an easy method to present data sets in a standardised way. What else does that sound like? A database! It's only natural that it should be simple to convert your information without a lot of fuss -- and you can. Some proprietary database manufacturers, such as Microsoft and IBM, have taken steps to integrate XML into their systems. This comes as no surprise since these two companies are both heavily involved in the XML standardisation project. Not wanting to be left behind, the creators of MySQL database incorporated a means for generating an XML data file. It's supported in version 3.23.48 and up. You can use the command line or facilitate the process with the programming language of your choice. To get started, you can download MySQL database for free from MySQL.com. The current release is sufficient to support this feature, and you don't need to compile it with any special parameters. Fruit filling
Once you're installed, created, and populated your database, execute the following command to generate an XML file:
mysqldump --xml databasename [tables]
If you'd like to save this to a file, simply use the standard *NIX method of outputting to a file:
mysqldump --xml databasename > filename.xml
This produces a well formed XML document. Because XML is datacentric, if you dump your entire database and it contains no information, your file will result in a series of empty tags based on the table names. Your output should look something like this:
It's really easy to get XML from MySQL, but how about the other direction? That's a little trickier. MySQL itself doesn't support this function, and with good reason. The database currently has no way to validate the XML file. This could result in a number of scenarios, ranging from a partial load to ignoring malformed tags and statements to simply forcing the entire load to fail. MySQL supports only cascading back-outs in current development versions. While it's not pretty from a native standpoint, you do have some options. One solution is Perl's DBIx::XML_RDB module. You can use this method to both import and export XML, though understandably the import is heavily dependent upon a correctly structured XML file. To get the data, the module essentially runs a query and formats the results in an XML file. Conversely, you can use the module to read an XML file, create a SQL query, and execute it. There is a simpler option as well. The DBIx::XML_RDB module ships with two utility scripts to facilitate the process: xml2sql.pl and sql2xml.pl. I found agreat tutorial on using this Perl module at O'Reilly's XML.com Web site. It will walk you through the process. Another, more generalized effort comes from Ron Bourret's XML-DBMS project . This is an ongoing effort to support XML imports and exports with relational databases using Perl and Java. There's also some very interesting work that supports mapping one database to another using XML as facilitating middleware. This is a community-oriented open source project being managed on SourceForge . Other languages, particularly Web scripting languages, haven't ignored the need to import XML into SQL databases either. There are similar efforts for Python, such as the xml2sql and dtd2sql modules, outlined in detail in this article from IBM , and a couple of projects in the works for PHP, such as the "XML MySQL class" project. Scrumptious
With these utilities, importing and exporting XML into and from MySQL is easy! Since MySQL is popular and free, it's been the test bed for integrating many scripting languages in XML, and as a result there are a number of tutorials and scripts specific for this database. With the power of a relational database and the ability to easily create XML files, MySQL can be an integral part of your Web services solution.