This text is replaced by the Flash movie.

Archive for the ‘MySQL’ Category

Joomla 1.5 Article Trash

December 4th, 2010
While debugging the component I created I noticed that the query was returning some articles I didn't knew existed. I found out that these articles are just trashed.

The simple way to separate article trash from active articles is to get the state datafield of the table #__content equals to 1.

1
2
3
4
5
6
7
8
9
10
11
$query = "SELECT a.*, n.*, t.*, n.link as linkThisPage"
. "\n FROM #__componentnews_news AS n"
. "\n LEFT JOIN #__content AS a ON a.id = n.pr_id"
. "\n LEFT JOIN #__componentnews_types AS t ON t.id = n.type_id"
. "\n WHERE ( a.state = 1 )"
. "\n AND ( a.publish_up = " .$database->Quote($nullDate) 
. " OR a.publish_up <= " . $database->Quote($now) . ")"
. "\n AND ( a.publish_down = " . $database->Quote($nullDate)
. " OR a.publish_down >= " . $database->Quote($now) . " )"
. "\n ORDER BY $orderby"
. "\n limit $skip," . $count;

Where in this query . "\n WHERE ( a.state = 1 )" should do it.

Cake Baking Models – Cannot see other tables

November 7th, 2009

Problem.
After I updated the database, I added 1 table; relate it to another table, and go to the command line to bake what I did, after typing "cake bake model" at the command prompt, I found out that the recently added table was not listed.

Cake PHP Bake Model Problem

 

Reason.
Cake caches your model schema. 

Solution.
1.) 
Clear your cache by deleting all files in your app's tmp/cache/models/
and tmp/cache/persistent/

2.) 
Disable caching in your app's config/core.php // make sure to enable back again in production setup

/**
 * Turn off all caching application-wide.  *  
 */  
Configure::write('Cache.disable', true);

After doing the solution, I can now successfully bake the new table.

Right way to use sql…

March 3rd, 2009
Right way to use sql join with conditions (Displaying no match data).
After hours trying to figure out what's wrong with my query for displaying list of cities with how many Lease/Sale were there. The problem was getting the cities even not yet added on Lease or Sale type, that means I need to list cities that has no match yet or 0 count.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<?
global $mainframe;
$ebconfig = &$mainframe->getParams();
$prop_Type = $ebconfig->get('prop_type', "Lease");
 
 
$db_city =& JFactory::getDBO();
$query_city = "SELECT t1.city, t1.id, COUNT( t2.propCity ) AS city_count
FROM #__listing_city AS t1
LEFT OUTER JOIN #__listing_listings AS t2 ON t2.propCity = t1.city
WHERE t2.propType = '$prop_Type' AND t2.published = 1
GROUP BY t1.city
ORDER BY t1.city ASC";
?>


The code above does not display the cities that has no match on another table. The where clause was giving the problem...
1
2
3
4
5
6
7
8
9
10
11
12
13
14
<?
global $mainframe;
$ebconfig = &$mainframe->getParams();
$prop_Type = $ebconfig->get('prop_type', "Lease");
 
 
$db_city =& JFactory::getDBO();
$query_city = "SELECT t1.city, t1.id, COUNT( t2.propCity ) AS city_count
FROM #__listing_city AS t1
LEFT OUTER JOIN #__listing_listings AS t2 ON t2.propCity = t1.city
AND t2.published = 1 AND t2.propType = '$prop_Type'
GROUP BY t1.city
ORDER BY t1.city ASC";
?>


Instead putting the condition on "ON Clause" after the matching table condition, fixed the problem.

MySQL Delete Duplicate Entries

October 19th, 2006

This Is the Best way to delete duplicate entries in MySQL

alter ignore table table_name add unique index(column_name);

For example your table_name.column_name has “some” duplicate entries the best way to do is to put unique index on the column_name that has duplicates

Is that simple.