[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 247
  • Last Modified:

how to make php / mysql code usable on both installations

hi there,
this question is in an attempt to conclude code compatibility accross installations.
attached you will find the data differences between the two installations of the code - CanopyXchange & QuoteMe, and TruckCapXchange.
now in the supplier manager, there are filters - which currently only work on the regional fields (red values), as well as the Status and Type fields (blue values).
now the latest code filters the suppliers on the Supplier Manager by using the above mentioned fields. (tcx installation records in attached xlsx document).
i however, need to implement that where suppliers have multiple regional bindings via the 4_servicesuppliers table, that those are used in the regional filters instead. the latest php and compiled mysql query where these supplier records are filtered - is as follows (allowing for multiple regions to be selected):
function getrecords($country="",$state="",$county="",$zip="",$status="",$type="") {
	global $conn;
	$wsql = array();
	if ($country!="") {
		$f_country = explode(",",$country);
		for ($i = 0; $i < count($f_country); $i++) {
			$f_country[$i] = "bigint_CountryID = ".$f_country[$i];
		}
		array_push($wsql,"(".implode(" OR ",$f_country).")");
	}
	if ($state!="") {
		$f_state = explode(",",$state);
		for ($i = 0; $i < count($f_state); $i++) {
			$f_state[$i] = "bigint_StateID = ".$f_state[$i];
		}
		array_push($wsql,"(".implode(" OR ",$f_state).")");
	}
	if ($county!="") {
		$f_county = explode(",",$county);
		for ($i = 0; $i < count($f_county); $i++) {
			$f_county[$i] = "bigint_CountyID = ".$f_county[$i];
		}
		array_push($wsql,"(".implode(" OR ",$f_county).")");
	}
	if ($zip!="") {
		$f_zip = explode(",",$zip);
		for ($i = 0; $i < count($f_zip); $i++) {
			$f_zip[$i] = "bigint_ZipCodeID = ".$f_zip[$i];
		}
		array_push($wsql,"(".implode(" OR ",$f_zip).")");
	}
	if ($status!="") {
		$f_status = "smallint_SupplierStatus = ".$status;
		array_push($wsql,$f_status);
	}
	if ($type!="") {
		$f_type = "tinyint_DefaultType = ".$type;
		array_push($wsql,$f_type);
	}
	$tsql = "SELECT * FROM 5_suppliers ".
			(($country!=""||$state!=""||$county!=""||$zip!=""||$status!=""||$type!="")?"WHERE ".implode(" AND ",$wsql):"").
			" ORDER BY text_SupplierName ASC;";
	$result = mysql_query_errors($tsql , $conn , __FILE__ , __LINE__ );
	if ($result) {
		while ($row = mysql_fetch_array($result)) {
			echo "	<supplier>\n";
			echo "		<supplierid>" . $row["bigint_SupplierID"] . "</supplierid>\n";
			echo "		<suppliername>" . xmlentities($row["text_SupplierName"]) . ": " . xmlentities($row["text_ContactFirstName"]) . " " . xmlentities($row["text_ContactSurname"]) . " (" . $row["bigint_ContactTel"] . ")" . "</suppliername>\n";
			echo "	</supplier>\n";
		}
	}
}

Open in new window

a more to the point question i guess would be, how do i alter the php/mysql to use the regional binding records if available, instead of the supplier records? i am guessing i will need to do a left join select - but am unsure about the exact implementation of if the secondary table has records or not.
many thanks in advance for the assistance provided.
CX---QM-vs-TCX-supplier-manager-.xlsx
supplier-manager.jpg
0
intellisource
Asked:
intellisource
  • 2
1 Solution
 
intellisourceAuthor Commented:
ok i realised that i do not have to make it EITHER/OR depending on the usage of regional bindings.
it can actually just be OR regardless! ;)
the following function will continue to work with TCX and now also work with CX & QM! :D
function getrecords($country="",$state="",$county="",$zip="",$status="",$type="") {
	global $conn;
	$wsql = array();
	$rsql = array();
	if ($country!="") {
		$f_country = explode(",",$country);
		for ($i = 0; $i < count($f_country); $i++) {
			$r_country[$i] = "SS.bigint_RegionID = ".$f_country[$i];
			$f_country[$i] = "S.bigint_CountryID = ".$f_country[$i];
		}
		array_push($wsql,"(".implode(" OR ",$f_country).")");
		array_push($rsql,"(".implode(" OR ",$r_country).")");
	}
	if ($state!="") {
		$f_state = explode(",",$state);
		for ($i = 0; $i < count($f_state); $i++) {
			$r_state[$i] = "SS.bigint_RegionID = ".$f_state[$i];
			$f_state[$i] = "S.bigint_StateID = ".$f_state[$i];
		}
		array_push($wsql,"(".implode(" OR ",$f_state).")");
		array_push($rsql,"(".implode(" OR ",$r_state).")");
	}
	if ($county!="") {
		$f_county = explode(",",$county);
		for ($i = 0; $i < count($f_county); $i++) {
			$r_county[$i] = "SS.bigint_RegionID = ".$f_county[$i];
			$f_county[$i] = "S.bigint_CountyID = ".$f_county[$i];
		}
		array_push($wsql,"(".implode(" OR ",$f_county).")");
		array_push($rsql,"(".implode(" OR ",$r_county).")");
	}
	if ($zip!="") {
		$f_zip = explode(",",$zip);
		for ($i = 0; $i < count($f_zip); $i++) {
			$r_zip[$i] = "SS.bigint_RegionID = ".$f_zip[$i];
			$f_zip[$i] = "S.bigint_ZipCodeID = ".$f_zip[$i];
		}
		array_push($wsql,"(".implode(" OR ",$f_zip).")");
		array_push($rsql,"(".implode(" OR ",$r_zip).")");
	}
	if ($status!="") {
		$f_status = "S.smallint_SupplierStatus = ".$status;
		array_push($wsql,$f_status);
	}
	if ($type!="") {
		$f_type = "S.tinyint_DefaultType = ".$type;
		array_push($wsql,$f_type);
	}
	$tsql = "SELECT * FROM 5_suppliers S LEFT JOIN 4_servicesuppliers SS ON S.bigint_SupplierID = SS.bigint_SupplierID ".
			(($country!=""||$state!=""||$county!=""||$zip!=""||$status!=""||$type!="")?"WHERE (".implode(" OR ",$rsql).") OR (".implode(" AND ",$wsql).") ":"").
			" GROUP BY S.text_SupplierName, S.bigint_SupplierID ORDER BY S.text_SupplierName ASC;";
	$result = mysql_query_errors($tsql , $conn , __FILE__ , __LINE__ );
	if ($result) {
		while ($row = mysql_fetch_array($result)) {
			echo "	<supplier>\n";
			echo "		<supplierid>" . $row["bigint_SupplierID"] . "</supplierid>\n";
			echo "		<suppliername>" . xmlentities($row["text_SupplierName"]) . ": " . xmlentities($row["text_ContactFirstName"]) . " " . xmlentities($row["text_ContactSurname"]) . " (" . $row["bigint_ContactTel"] . ")" . "</suppliername>\n";
			echo "	</supplier>\n";
		}
	}
}

Open in new window

0
 
intellisourceAuthor Commented:
yep, after a few hours of comparing differences, contemplating how to implement i realised it can be done without having to check wether the suppliers have regional bindings in the 4_servicesuppliers table :D
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now