PHP Help

Dup · 2 May 2016 at 14:01

Right, so I'm a front end dev and don't really dabble in much else (yet).

Basically I'm using CMSMS and the LISE/Listit module. Sadly the module has a terrible search so I'm writing a plugin that will query the modules tables and pull back what I need back into the template (Smarty). This means the user can still use the modules admin which is nice and easy to use and I'm displaying the

I have somehow managed to make it work despite limited experience, despite one issues where I'm now stuck and would like some help please.

The search is explicit in that the categories (makes) and titles (models) are pulled into the search form dynamically so there's no free-form user input there and no guessing as such.

The problem is it pulls through all the data fine and spits it out, but if you search 'any' for the category (make) it doesn't pull through a name as it's not being searched against. I now it would never work as I have done it, but I'm not sure how I would go about making it work.

The problem is there's a table for the items, table for the categories (makes) and a table that says what items are in each category. Code and tables below:

PHP:

function smarty_cms_function_vehicles($params, &$smarty) {

	# Get parameters of the search
	$search_parameters = [];

	if (!empty($_GET) || isset($params)) {
		$parameters = $params;

		if (!empty($_GET)) { # URL Parameters or search form submitted via GET (default)
			$search_parameters = $_GET;
		} else if (!empty($parameters)) { # Directly called
			$search_parameters = $parameters;
		}

		# Assign incoming values to search chunks
		foreach ($search_parameters as $key => $value) {
			if (is_array($value)) {
				$value = implode(',', $value);
			} else {
				$value = ($value == 'any' || $value == 'Select Model') ? '' : $value;
			}

			$search_chunks[$key] = $value;
			$make = $search_chunks['make'];
			$model = $search_chunks['model'];
		}
	}

	if (!function_exists('getMake')) {
		function getMake($make) {
			# Get make
			$db     = cmsms()->GetDb();
			$sql    = "SELECT * FROM cms_module_lisevehicles_category WHERE category_name=? LIMIT 1";
			$result = $db->Execute($sql, [
				$make
			]);

			$row = $result->FetchRow();
			$make = $row['category_id'];
			$makeName = $row['category_name'];
			return array($make, $makeName);
		}
	}

	$makes = getMake($make);
	$make = $makes[0];
	$makeName = $makes[1];



	if (!function_exists('getIds')) {
		function getIds($make) {
			$ids = [];
			# Get ids
			$db     = cmsms()->GetDb();
			$sql    = "SELECT item_id FROM cms_module_lisevehicles_item_categories WHERE category_id=?";
			$result = $db->Execute($sql, [
				$make
			]);

			while($row = $result->FetchRow()) {
				$id  = $row['item_id'];
				$ids[] = $id;
			}
			return $ids;
		}
	}

	$test = getIds($make);
	$ids = implode(',',array_map('intval',$test));


	if (!function_exists('getVehicles')) {
		function getVehicles($ids,$model,$makeName) {
			$vehicles = [];
			# Get vehicles
			$db     = cmsms()->GetDb();

			if (empty($ids) && empty($model)) {
				$sql    = "SELECT * FROM cms_module_lisevehicles_item";
				$result = $db->Execute($sql);
			} else if (empty($model)) {
				$sql    = "SELECT * FROM cms_module_lisevehicles_item WHERE item_id IN($ids)";
				$result = $db->Execute($sql);
			} else if (!empty($model)) {
  			$sql    = "SELECT * FROM cms_module_lisevehicles_item WHERE item_id IN($ids) AND title=?";
				$result = $db->Execute($sql, [
					$model
				]);
			}

			while($row = $result->FetchRow()) {
				$vehicle = new stdClass();

				$vehicle->make    = $makeName;
				$vehicle->model   = $row['title'];


				$vehicles[] = $vehicle;
			}

			return $vehicles;
		}
	}

	$vehicles = getVehicles($ids,$model,$makeName);
	$smarty->assign('vehicles', $vehicles);
}

cms_module_lisevehicles_item_categories:

cms_module_lisevehicles_category:

cms_module_lisevehicles_item:

Any help would be greatly appreciated. I'm sure it could be done better and my logics probably all wrong. Any insight on making it better and cleaner would be amazing <3

Dup · 3 May 2016 at 00:17

That's mega mega helpful, thank you! I really appreciate it

I've had a brief look and joining tables and that seems like the wya forward. I will get stuck in tomorrow (this) evening.

Cheers for the tip on santising. Should protect it from sillyness!

Yes the loop is not necessary. I started off looking at a completely unrelated plugin to see how it was structured. They had a loop that was checking for POST, GET etc and I lazily just half left it in there in my haste to make progress.

CMSMS can sent paramters to the function.

Right now it's calling {vehicles} however I could say {vehicles pagelimit=10} and then use that to only show 10 results and add in pagination. Not sure if I'm going to bother with that yet, not sure it's necessary as there won't be that many results (in theory) but it would be a nice learning exercise.

Can't wait to give this a go tomorrow, thanks again

Dup · 4 May 2016 at 00:41

Excellent, that's the basic search sorted. Much better! Thank you

Not sure the if statement is the way to go. Could it be done only writing one query using case? Had a look to see if I could use case to see if $make was null/emptry then select all but not sure that's how it can work. Any ideas or am I barking up the wrong tree.

You'll have to excuse me I'm not progmatically minded, not done enough of it to make sense of what I'm reading online at times!

PHP:

function smarty_cms_function_vehicles($params, &$smarty) {

	# Get parameters of the search
	$search_parameters = [];

	if (!empty($_GET)){
		$search_parameters = $_GET;

		# Assign incoming values to search chunks
		foreach ($search_parameters as $key => $value) {
			if (is_array($value)) {
				$value = implode(',', $value);
			} else {
				$value = ($value == 'any' || $value == 'Any Model') ? '' : $value;
			}

			$search_chunks[$key] = $value;
			$make = $search_chunks['make'];
			$model = $search_chunks['model'];
		}
	}

	if (!function_exists('getVehicles')) {
		function getVehicles($make, $model) {
			$vehicles = [];
			# Get vehicles
			$db     = cmsms()->GetDb();

			if (empty($make) && empty($model)) {
				$sql    = "SELECT *
				FROM cms_module_lisevehicles_item a
				JOIN cms_module_lisevehicles_item_categories b ON a.item_id = b.item_id
				JOIN cms_module_lisevehicles_category c ON b.category_id = c.category_id";
				$result = $db->Execute($sql);
			} else if (empty($model)) {
				$sql    = "SELECT *
				FROM cms_module_lisevehicles_item a
				JOIN cms_module_lisevehicles_item_categories b ON a.item_id = b.item_id
				JOIN cms_module_lisevehicles_category c ON b.category_id = c.category_id
				WHERE c.category_name = ?";
				$result = $db->Execute($sql, [
					$make
				]);
			} else if (!empty($model)) {
				$sql    = "SELECT *
				FROM cms_module_lisevehicles_item a
				JOIN cms_module_lisevehicles_item_categories b ON a.item_id = b.item_id
				JOIN cms_module_lisevehicles_category c ON b.category_id = c.category_id
				WHERE c.category_name = ? AND a.title = ?";
				$result = $db->Execute($sql, [
					$make,
					$model
				]);
			}

			while($row = $result->FetchRow()) {
				$vehicle = new stdClass();

				$vehicle->make    = $row['category_name'];
				$vehicle->model   = $row['title'];

				$vehicles[] = $vehicle;
			}

			return $vehicles;
		}
	}

	$vehicles = getVehicles($make,$model);
	echo "<pre>";
	print_r(array_values($vehicles));
	echo "</pre>";
	$smarty->assign('vehicles', $vehicles);
}

Dup · 19 Jun 2016 at 21:30

Thanks for your help. I finally made some progress on this after caring my my dying father this passed few months.

I have almost got it where I want it:

PHP:

<?php

function smarty_cms_function_vehicles($params, &$smarty) {

	$details = isset($params['details']) ? $params['details'] : false;
	$limit   = $params['limit'];

	# Get parameters of the search
	$search_parameters = [];

	if (!empty($_GET)){
		$search_parameters = $_GET;

		# Assign incoming values to search chunks
		foreach ($search_parameters as $key => $value) {
			if (is_array($value)) {
				$value = implode(',', $value);
			} else {
				$value = ($value == 'any' || $value == 'Any Model') ? '' : $value;
			}

			$search_chunks[$key] = $value;
			$make = filter_var($search_chunks['make'], FILTER_SANITIZE_STRING);
			$model = filter_var($search_chunks['model'], FILTER_SANITIZE_STRING);
			$minPrice = filter_var($search_chunks['minPrice'], FILTER_VALIDATE_INT);
			$maxPrice = filter_var($search_chunks['maxPrice'], FILTER_VALIDATE_INT);
		}
	}

	if (!$details) {

		if (!function_exists('getVehicles')) {
			function getVehicles($make,$model,$minPrice,$maxPrice,$limit) {
				$vehicles = [];
				# Get vehicles
				$db = cmsms()->GetDb();

				$sql = 'SELECT c.category_name, a.title, CASE WHEN e.fielddef_id = 3 THEN d.value END AS price, CASE WHEN e.fielddef_id = 4 THEN d.value END AS photo FROM cms_module_lisevehicles_item a
				JOIN cms_module_lisevehicles_item_categories b ON a.item_id = b.item_id
				JOIN cms_module_lisevehicles_category c ON b.category_id = c.category_id
				JOIN cms_module_lisevehicles_fieldval d ON a.item_id = d.item_id
				JOIN cms_module_lisevehicles_fielddef e ON d.fielddef_id = e.fielddef_id';

				if (empty($make) && empty($model) && empty($minPrice) && empty($maxPrice)) {
						$sql .= ' WHERE e.alias = "price"';
						if (!empty($limit)) {
							$sql .= ' LIMIT '.$limit.'';
						}
				    $result = $db->Execute($sql);
				} else {
				    $sql .= ' WHERE ';
				    if (!empty($make)) {
			        $sql .= 'c.category_name = '.$db->qstr($make).'';
							$sql .= ' AND e.alias = "price"';
				    }
				    if (!empty($model)) {
			        if (!empty($make)) $sql .= ' AND ';
			        $sql .= 'a.title = '.$db->qstr($model).'';
							$sql .= ' AND e.alias = "price"';
				    }
				    if (!empty($minPrice && empty($maxPrice))) {
							if (!empty($make)) $sql .= ' AND ';
							$sql .= 'e.alias = "price" AND d.value >= '.$db->qstr($minPrice).'';
				    }
				    if (!empty($maxPrice) && empty($minPrice)) {
							if (!empty($make)) $sql .= ' AND ';
							$sql .= 'e.alias = "price" AND d.value <= '.$db->qstr($maxPrice).'';
				    }
				    if (!empty($maxPrice) && !empty($minPrice)) {
							if (!empty($make)) $sql .= ' AND ';
							$sql .= 'e.alias = "price" AND d.value BETWEEN '.$db->qstr($minPrice).' AND '.$db->qstr($maxPrice).'';
				    }
						if (!empty($limit)) {
							$sql .= ' LIMIT '.$limit.'';
						}
				    $result = $db->Execute($sql);
				}

				echo $sql;

				echo "<pre>";
				echo print_r($result);
				echo "</pre>";

				while($row = $result->FetchRow()) {
					$vehicle = new stdClass();

					$vehicle->make    = $row['category_name'];
					$vehicle->model   = $row['title'];
					$vehicle->price   = $row['price'];
					$vehicle->photo   = $row['photo'];

					$vehicles[] = $vehicle;
				}

				return $vehicles;
			}
		}

		$vehicles = getVehicles($make,$model,$minPrice,$maxPrice,$limit);
		$smarty->assign('vehicles', $vehicles);

	} else {
		if (!function_exists('getVehicle')) {
			function getVehicle($make,$model) {
				# Get vehicles
			if (empty($make) || empty($model)) {
	  		echo "<div class=\"error\">";
				echo "Vehicle not found";
				echo "</div>";
			} else {

				$db = cmsms()->GetDb();

				$sql = 'SELECT c.category_name, a.title, MAX(CASE WHEN e.fielddef_id = 3 THEN d.value END) AS price, MAX(CASE WHEN e.fielddef_id = 4 THEN d.value END) AS photo, MAX(CASE WHEN e.fielddef_id = 5 THEN d.value END) AS description FROM cms_module_lisevehicles_item a
				JOIN cms_module_lisevehicles_item_categories b ON a.item_id = b.item_id
				JOIN cms_module_lisevehicles_category c ON b.category_id = c.category_id
				JOIN cms_module_lisevehicles_fieldval d ON a.item_id = d.item_id
				JOIN cms_module_lisevehicles_fielddef e ON d.fielddef_id = e.fielddef_id';

				    $sql .= ' WHERE ';
				    if (!empty($make)) {
			        $sql .= 'c.category_name = '.$db->qstr($make).'';
				    }
				    if (!empty($model)) {
			        if (!empty($make)) $sql .= ' AND ';
			        $sql .= 'a.title = '.$db->qstr($model).'';
				    }

				    $result = $db->Execute($sql);

						$row = $result->fetchRow();
						$vehicle = new stdClass();

						$vehicle->make          = $row['category_name'];
						$vehicle->model         = $row['title'];
						$vehicle->price  				= $row['price'];
						$vehicle->photo   			= $row['photo'];
						$vehicle->description   = $row['description'];

				return $vehicle;
			}
		}
	}

		$vehicle = getVehicle($make,$model);
		$smarty->assign('vehicle', $vehicle);
	}
}

function smarty_cms_about_function_vehicles() {
?>
	<h3>Version 1.0</h3>
<?php
}
?>

The problem is it's not returning the photo. Bascially I want to pull mutliple values from the d.value coumn into it's own field in the results.

The table looks like this:

The sql output looks like this for the fields:

Code:

    [fields] => Array
        (
            [category_name] => Audi
            [title] => A3 2.0 TDI
            [price] => 100
            [photo] => 
        )

If I'm pulling a single result (second half of my code there) it works using MAX and the CASE. For multiple results the query fails. Any ideas?

Dup · 22 Jun 2016 at 01:01

radderfire said:
Here is a suggestion, although I am not sure. It could be the fact that photo is not included in a JOIN statement. JOIN is a bit like venn diagrams, so you have to join where your data resides in order to display it. If you haven't joined anything related to photo, you won't get photo results.

You were sort of on the money there. There may be a much more efficient way of doing this, but to prevent duplicates and to pull the relevant rows into the correct objects, I ended up with this query:

PHP:

$sql = 'SELECT c.category_name, a.title, d.value as "price", d2.value as "photo", d3.value as "spec", d4.value as "deal"
FROM cms_module_lisevehicles_item a
JOIN cms_module_lisevehicles_item_categories b ON a.item_id = b.item_id
JOIN cms_module_lisevehicles_category c ON b.category_id = c.category_id
JOIN cms_module_lisevehicles_fieldval d ON a.item_id = d.item_id AND d.fielddef_id = "3"
LEFT JOIN cms_module_lisevehicles_fieldval d2 ON a.item_id = d2.item_id AND d2.fielddef_id = "4"
LEFT JOIN cms_module_lisevehicles_fieldval d3 ON a.item_id = d3.item_id AND d3.fielddef_id = "6"
LEFT JOIN cms_module_lisevehicles_fieldval d4 ON a.item_id = d4.item_id AND d4.fielddef_id = "7"
JOIN cms_module_lisevehicles_fielddef e ON d.fielddef_id = e.fielddef_id';

Which spits out:

Code:

Array
(
    [0] => stdClass Object
        (
            [make] => Audi
            [model] => A3
            [price] => 100
            [photo] => images/vehicles/A161000_medium.jpg
            [spec] => 2.0 TDI
            [deal] => 2 year deal
        )

    [1] => stdClass Object
        (
            [make] => BMW
            [model] => 335i
            [price] => 150
            [photo] => images/vehicles/2017-BMW-3-Series-Pictures.jpg
            [spec] => M Sport Coupe
            [deal] => 
        )

    [2] => stdClass Object
        (
            [make] => Audi
            [model] => A1
            [price] => 200
            [photo] => images/vehicles/2017-Audi-A1-concecpt.jpg
            [spec] => 1.6 TDI
            [deal] => 2 year deal
        )

    [3] => stdClass Object
        (
            [make] => BMW
            [model] => 335i
            [price] => 150
            [photo] => images/vehicles/2017-BMW-3-Series-Pictures.jpg
            [spec] => M Sport Coupe
            [deal] => 
        )

)