Multi Dimensional Array Problem

[meeble]

Hello,

I have the code:

# This is a recursive function, which either adds a value into a global array
# for use in the select statement, or recurses if the value is an
# array itself and does the same thing.
function DisassembleArray($key, $val)
{
global $queries;
if((!is_array($val)) && ($val != "&quot)
{
$queries[] = "$key = '$val'";
} else
{
foreach($val as $foo => $real_val)
{
DisassembleArray($key, $real_val);
}
}
}

# These are the names of the form fields, and they also correspond
# to field names in the table that we want to look through.
$valid_fields = array("residence", "workextime", "level", "desiredloc", "salarydes", "salarycur"

$queries = array();
foreach($_POST as $key => $val)
{
if(!in_array($key, $valid_fields)) { continue; }
DisassembleArray($key, $val);
}

# Start of the query.
$query = "SELECT * from details where fname LIKE '%$firstname%' AND lname LIKE '%$lastname%' AND emailaddress LIKE '%$emailaddress%'";
if(count($queries) > 0)
# If something's been selected, we append it to the query.
{
$query .= " and " . implode(" or ", $queries);
}
$query .= " order by lname desc";




On the form page I entered the search details into the following dropdown boxes:

residence: Wrexham, Yorkshire
workextime: 1 year, 2 years
level: Director, Senior Manager

This produces the SQL output on the next page...

"SELECT * from details where fname LIKE '%%%' AND lname LIKE '%%%' AND emailaddress LIKE '%%%' and residence = 'Wrexham' or residence = 'Yorkshire' or workextime = '1 year' or workextime = '2 years' or level = 'Director' or level = 'Senior Manager' order by lname desc"

when what I ACTUALLY want is:

"SELECT * from details where fname LIKE '%%%' AND lname LIKE '%%%' AND emailaddress LIKE '%%%' AND (residence = 'Wrexham' or residence = 'Yorkshire') AND (workextime = '1 year' or workextime = '2 years') AND (level = 'Director' or level = 'Senior Manager') order by lname desc"

I can't seem to change the code above to get this.

Any ideas please?

 

[welshtroll]

couldn't you replace

$query .= " and " . implode(" or ", $queries);
with
$query .= " and (" . implode(" or ", $queries).&quot";

'... and then it wouldn't compile?'

 

[meeble]

This gives:

SELECT * from details where fname LIKE '%%%' AND lname LIKE '%%%' AND emailaddress LIKE '%%%' and (residence = 'Wrexham' or residence = 'Yorkshire' or workextime = '1 year' or workextime = '2 years' or level = 'Director' or level = 'Senior Manager') order by lname desc

which is not what I want. I want:

SELECT * from details where fname LIKE '%%%' AND lname LIKE '%%%' AND emailaddress LIKE '%%%' AND (residence = 'Wrexham' or residence = 'Yorkshire') AND (workextime = '1 year' or workextime = '2 years') AND (level = 'Director' or level = 'Senior Manager') order by lname desc

Any ideas,

Cheers

 

[welshtroll]

Sorry misread the question, i thought that the queries to got inside the bracket was held in a single array element
ie
queries[1] = "residence = 'Wrexham' or residence = 'Yorkshire'"

Will two entries to the array with the same key always be together?
ie
queries[1] = "residence = 'Wrexham'"
queries[2] = "residence = 'Yorkshire'"
queries[3] = "workextime = '1 year'"
queries[4] = "workextime = '2 years'"

and not
queries[1] = "residence = 'Wrexham'"
queries[2] = "workextime = '1 year'"
queries[3] = "residence = 'Yorkshire'"
queries[4] = "workextime = '2 years'"

'... and then it wouldn't compile?'

 

[meeble]

Yes, unless you know a better way of doing things.

Cheers

James

 

[welshtroll]

I've tried to recreate something to achieve this

<?
$queries = array();
$queries[0] = &quot;residence = 'Wrexham'&quot;;
$queries[] = &quot;residence = 'Yorkshire'&quot;;
$queries[] = &quot;workextime = '1 year'&quot;;
$queries[] = &quot;workextime = '2 years'&quot;;
$queries[] = &quot;level = '3'&quot;;

$currtype = &quot;empty&quot;;
$bracketopen = false;
$qcount = sizeof($queries);
for ($i=0;$i<$qcount;$i++){
	$newtype = substr($queries[$i],0,strpos($queries[$i],'=')-1);
	if ($newtype == $currtype){
		$query .= &quot; or &quot;;
	}else{
		if ($bracketopen){
			$query .= &quot;) and (&quot;;
			$bracketopen = true;
		}else{
			$query .= &quot; and (&quot;;
			$bracketopen = true;
		}
	}
	$query .= $queries[$i];
	$currtype = $newtype;
}
if ($bracketopen){
	$query .= &quot;)&quot;;
	$bracketopen = false;
}
echo $query;
?>

example outout here

http://www.welshtroll.co.uk/tektest.php

Dunno if this helps, I only threw it together to resolve this, caution it only works if array elements with the same key are together.

Maybe someone else will have an easier solution.

'... and then it wouldn't compile?'

 

[DRJ478]

meeble,

Basically same question we explored in May, I believe 5/20.

Have you considered using 'FIND_IN_SET'? For that to work you just need to concatenate the choices with a comma.

FIND_IN_SET(str,strlist) at

http://www.mysql.com/doc/en/String_functions.html