Creating a database connection withiout using $this object

[saakshi]

I've a requirement to mark residential properties (catalog items) on google maps.
Further when a person zooms-in/zooms out on map i.e any event on map the property list marking pointers should reload as per the new map bounds.

I'm using AJAX for this but unable to figure out where shall i create this AJAX file to run the SQL queries passing the new map bounds to it.
I'm trying to create a new file in the typolight root folder, but how can i execute DB queries here??
Pls suggest.

[scare]

I would just copy flash.php and take a look at how it accesses database.

[saakshi]

Thanks Scare it helped a lot! I'm new to classes so facing problems.

i'm creating a class as pasted below, but getting Fatal Error:Class MapBounds contains 1 abstract method and must therefore be declared abstract or implement the remaining methods (Module::compile)

But if i make MapBounds an abstract class i wont be able to create it's object required here $objTemplate->total_entries = $this->parseCatalog($objProps, false, 'catalog_simple_map');

I need to extend MapBounds as----- MapBounds extends ModuleCatalog because i need variable values for SELECT fields, URLS etc. in my catalog.

Any suggestions on how can i get the values of SELECT fields in my catalog as right now it return only id of the selected value, is there any other function in ModuleCatalog which i can use?

class MapBounds extends ModuleCatalog
{
	public function __construct()
	{
		parent::__construct();
		//$this->strId = $this->Input->post('flashID');
	}
	
	public function run($NELat, $SWLat, $NELng, $SWLng)
	{
		/*if (!strlen($this->strId))
		{
			return;
		}*/
		echo "NELat========".$NELat;
		$this->import('String');
		$this->import('Database');
		$arrCatalog = array();
		$objProps = $this->Database->execute("SELECT * FROM catl_properties WHERE prop_latitude BETWEEN ".$SWLat." AND ".$NELat." AND prop_longitude BETWEEN ".$SWLng." AND ".$NELng);
		
		if ($objProps->numRows < 1)
		{			
			echo 'content=<p class="error">Could not find properties in these Map Bounds!</p>';

			return;
		}
		$objTemplate = new FrontendTemplate('catalog_simple_map');
		//$objTemplate->total_entries = $this->parseCatalog($objProps, false, 'catalog_simple_map');
		//print_r($objTemplate->total_entries);
		$i=0;
		while ($objProps->next()) {
			$arrCatalog[$i]['id'] = $objProps->id;
			$arrCatalog[$i]['data']['prop_category'] = $objProps->prop_category->value;
			$arrCatalog[$i]['data']['prop_type'] = $objProps->prop_type;
			$arrCatalog[$i]['data']['prop_title'] = $objProps->prop_title;
			$arrCatalog[$i]['data']['prop_area'] = $objProps->prop_area;
			$arrCatalog[$i]['data']['prop_class_val'] = $objProps->prop_title;
			$arrCatalog[$i]['data']['prop_leasing_hotline'] = $objProps->prop_leasing_hotline;
			$arrCatalog[$i]['data']['prop_purchase_price'] = $objProps->prop_purchase_price;
			$arrCatalog[$i]['data']['prop_image'] = $objProps->prop_title;
			$i++;
		}
		
		
		$objTemplate->entries = $arrCatalog;		
		$objTemplate->total_entries = $NELat;
		echo $objTemplate->parse();
	}
}
$objMapBounds = new MapBounds();
//$objMapBounds->strTable = 'catl_properties';
$objMapBounds->run($_REQUEST['NELat'], $_REQUEST['SWLat'], $_REQUEST['NELng'], $_REQUEST['SWLng']);

[scare]

OK, so I would first decide if you really need parseCatalog. I'm sure you don't. You may use sql joins to load additional information about referenced tables.

My run function would look something like this:

public function run($NELat, $SWLat, $NELng, $SWLng)
{
    $this->import('String');
    $this->import('Database');
    $query = "SELECT * FROM catl_properties WHERE prop_latitude BETWEEN ? AND ? AND prop_longitude BETWEEN ? AND ?";
    $objResults = $this->Database->prepare($query)->execute($NELat, $SWLat, $NELng, $SWLng);
    echo json_encode($objResults->fetchAllAssoc());
}

Simple

OK now about sql joins, it works something like this:

$query = "SELECT catl_properties.*, tl_taxonomy.alias, tl_taxonomy.name FROM catl_properties LEFT JOIN tl_taxonomy ON catl_properties.prop_type = tl_taxonomy.id WHERE ..."

This left join takes all columns from catl_properties plus alias and name columns from tl_taxonomy table. Row of tl_taxonomy table is selected so that its id matches prop_type in catl_properties table. This is pretty much what you need. If you don't know how to use sql joins, make sure you take time to learn that This way it's much faster than using parseCatalog.

But if you feel you need parseCatalog, just add empty compile function to your MapBounds class. I'm not sure if it works then, because there is complex initialization required, but you may give it a try.

[saakshi]

Thanks again you saved my day.
Right now i'm using simple join queries as suggested by you as its solving my problems.