|
Page 3 of 6
We're going to do a first pass of the application to get it into Joomla! and use the Joomla! database connector. At the moment we've got this bit of code that connects to the database:
mysql_connect('localhost',"username", "password");
@mysql_select_db('portal') or die( "Unable to select database");
We can replace that with the following which will pick up the Joomla! database connector:
$dbo =& JFactory::getDBO();
When we progress through our file we have lines with "mysql_query" that we now need to replace with "$dbo->setQuery($query)" and "$dbo->Query()". We also replace "mysql_error" with "$dbo->getErrorMsg()". We also need to prefix our tables with "#__" which is used by Joomla! to handle database prefixes - this allows for us to later call the table "jos_portal" instead of just "portal", later on we can swap "jos_" out for a different prefix. For example:
$query = "INSERT INTO portal VALUES('','" . $_REQUEST['name'] . "', '" . $_REQUEST['url'] . "',0, '" . $_COOKIE['username'] . "')";
mysql_query($query) or die("Unable to create new link: " . mysql_error() . "<br>$query");
now becomes: $query = "INSERT INTO #__portal VALUES('','" . $_REQUEST['name'] . "', '" . $_REQUEST['url'] . "',0, '" . $_COOKIE['username'] . "')";
$dbo->setQuery($query);
$dbo->Query() or die("Unable to create new link: " . $dbo->getErrorMsg() . "<br>$query");
Eventually we'll get to the point where we remove the die statement, but for now we'll keep with the style of the application. The next part is where we redirect the user which also has a simple query. Later on we'll use the JTable::hit function to handle what it does here, but for now we're just going to replace it with the database stuff:
$query = "SELECT hits FROM portal WHERE url = '".$redir."'";
$result = @mysql_query($query);
$line = mysql_fetch_array($result, MYSQL_ASSOC);
$line['hits']++;
$query = "UPDATE portal SET hits = ". $line['hits'] ." WHERE url = '".$redir."'";
@mysql_query($query);
This then becomes:
$query = "SELECT hits FROM #__portal WHERE url = '".$redir."'";
$dbo->setQuery($query);
$result = $dbo->Query($query);
$lines = $dbo->loadAssocList();
$line = $lines[0];
$line['hits']++;
$query = "UPDATE #__portal SET hits = ". $line['hits'] ." WHERE url = '".$redir."'";
$dbo->setQuery($query);
$dbo->Query();
The last bit of MySQL we need replace is the bit that handles outputting the list of links for the portal:
$query = "SELECT * FROM portal WHERE username = '".$_COOKIE['username'] . "' OR username = '' ORDER BY hits DESC";
$result = mysql_query($query) or die("Unable to get a listing of addresses");
$count = mysql_num_rows($result);
$i = -1;
if($count > 0) {
while($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
This we'll replace with a different method for getting the data:
$query = "SELECT * FROM #__portal WHERE username = '".$_COOKIE['username'] . "' OR username = '' ORDER BY hits DESC";
$dbo->setQuery($query);
$result = $dbo->Query() or die("Unable to get a listing of addresses");
$count = $dbo->getNumRows();
$i = -1;
if($count > 0) {
$lines = $dbo->loadAssocList();
foreach($lines as $line) {
Last but not least we can get rid of the "mysql_close()" at the bottom of the file. Whilst we're here we'll get rid of the "?>" at the bottom of the file as well.
If we create our table in our Joomla! database now we're going to need to use a prefix to get everything to work properly. By default the prefix for Joomla! installs is "jos_". So we need to change the SQL create statement had before to have the prefix: CREATE TABLE `jos_portal` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(100) NOT NULL default '',
`url` text NOT NULL,
`hits` int(11) NOT NULL default '0',
`username` varchar(50) NOT NULL default '',
PRIMARY KEY (`id`))
ENGINE=MyISAM COMMENT='Portal Database';
Run the SQL in your favourite SQL tool, such as MySQL Query Browser or phpMyAdmin. At this point if we create a folder in our components folder called "com_portal" and then put our modifications into a file called "portal.php" in that directory. We can also go to "index.php?option=com_portal" now however it will just present a username login box until we progress, however we can fool this by going to "index.php?option=com_portal&username=username". At the moment its a bit ugly looking, we can fix this by removing the <html> and <body> tags as well as their respective close tags in the last chunk of HTML at the bottom. We can also wipe out the head tag and the title as well. Its not going to have any thing in it and trying to add an entry isn't going to work properly yet but we at least have something. To prove that it works with the database, run the following in your query tool (e.g. MySQL Query Browser or phpMyAdmin) and we'll get a basic entry:
INSERT INTO `jos_portal` VALUES(0, 'Sam Moffatt Consulting', 'http://', 0, '');
Now we should see a result of our effort. This is what it looks like with the default install of Joomla! 1.5 with sample data installed using the rhuk_milkyway template.
| 
