How do I use PHP to do THIS to a MySQL query?

[ppowell]

			 $sql = 'SELECT ' . $subSectionSQLColsArray[$section][$subSection] . ' ' .
			    "FROM $subSection $subSection, $section $section " . 
			    "WHERE ${subSection}." . $idNameArray[$section] . "_id = ${section}.id ";

	 $sql = $this->dbAP->addFilterCriteriaToSQL($sql, $subSectionSQLColsNameArray[$section][$subSection], '', 'hasSubSection') . $sortSQL;

This block of code is designing a totally dynamic MySQL query.

Consider this.

$section = "partners"
$subSection = "feedback"

Table "partners" and table "feedback" are logically joined together by a foreign key constraint "feedback.partner_id".

No problem right?

Now, consider this:

$section = "students"
$subSection = "agencies"

A student can select an agency, but there is no foreign key constraint (the previous DBA felt a foreign key constraint would make it impossible to build a FULLTEXT search upon, so they're not joined), so the above PHP statement will break MySQL because there is no means of doing a WHERE clause.

So what on earth do I do? I have to keep the PHP statement as relatively intact as possible as it is part of an inheritable class method which means it has to be, you guessed it, fully FULLY dynamic!

Thanx
Phil

[ppowell]

Oh like DUH I should have realized.. just do a "SELECT * FROM $subSection LIMIT 1" and look for that particular field in the resultset!

		 $query = new MySQLQuery("SELECT * FROM $subSection LIMIT 1", $this->dbAP->getDBConn());
		 $result =@ $query->getResult();
		 if (!is_array($result) || @sizeof($result) == 0) {
		  $query = null;
		  $query = new MySQLQuery("DESC $subSection", $this->dbAP->getDBConn());
		  $result = $query->getResult();
		  if (@array_object_search($idNameArray[$section] . '_id', $result) !== false && @array_object_search($idNameArray[$section] . '_id', $result) !== null)
		   $hasForeignKey = true;
		 } else {
		  $hasForeignKey = $result[0]->{$idNameArray[$section] . '_id'};
		 }

// array_object_search
if (!function_exists('array_object_search')) {
	/**
	 * This function will search for either an array value or array key within an object array
	 *
	 * @access public
	 * @param mixed $needle
	 * @param object $haystack array object
	 * @param boolean $willSearchKeys (default false)
	 * @return int $index will return 'null' or 'false' if not found
	 */
	function array_object_search($needle, $haystack, $willSearchKeys = false) {
		for ($i = 0; $i < @sizeof($haystack); $i++) {
		 if ($willSearchKeys) {
		  $objPropArray = @array_keys(get_object_vars($haystack[$i]));
		 } else {
		  $objPropArray = @array_values(get_object_vars($haystack[$i]));
		 }
		 if (@array_search($needle, $objPropArray) !== null && @array_search($needle, $objPropArray) !== false) {
		  return $i;
		  break;
		 }
		}
		return null;
	}
}

Phil