RE: [PHP-DB] MultSelect ListBox hell!

On 22 September 2004 18:45, Stuart Felenstein wrote: > Just to confirm, > This is what I'm going to start with: Yeah, I'd say you've pretty much got it, except... > //base sql statement > $sql = "select * from jobs where record_deleted = 'NO' "; > > if (isset($_POST['states'])){ Your SQL is going to need some sort of conjunction here, as your WHERE phrase already has an initial condition in it. I'm guessing you'll want an AND, so: $sql .= 'AND '; > //check to see if the states is an array > // multiple items or just one > if (is_array($_POST['state'])) You've switched from $_POST['states'] to $_POST['state'] -- fix whichever is wrong ;) > $sql .= "state='".implode("' OR state='", > $_POST['state'])."'"; Given the conditions you want your WHERE phrase to test, you're going to need more parentheses to force the ORs to be evaluated before the ANDs; this is where the IN syntax, IMO, is more readable. So you want either: $sql .= "(state='".implode("' OR state='",$_POST['state'])."')"; or: $sql .= "state IN ('".implode("','",$_POST['state'])."')"; > > }else{ > //$_POST['state'] is not an array > $sql .= "state = '".$_POST['state']."' "; > }//end if > > if (isset($_POST['job'])){ > if (isset($_POST['state'])){ $sql .= " AND "; } And throughout this second block you've used a cut'n'paste of the first block without altering ['state'] to ['job'] -- just the sort of oversight that can give you the raving heebie-jeebies somewhere down the line if you fail to fix it! ;) > > //add in the AND if the state is set > //check to see if the states is an array > //multiple items or just one > > if (is_array($_POST['state'])) > $sql .= "state='".implode("' OR state='", > $_POST['state'])."'"; > $sql .= ")"; > }else{ > $_POST['job'] is not an array > $sql .= "job = '".$_POST['job']."' "; > } > //end if Given the moderate complexity of this code, I'd strongly recommend echo-ing out $sql immediately before it's used whilst you're in testing mode. When your query fails, you'll already be one step ahead in working out what the problem is. Cheers! Mike --------------------------------------------------------------------- Mike Ford, Electronic Information Services Adviser, Learning Support Services, Learning & Information Services, JG125, James Graham Building, Leeds Metropolitan University, Headingley Campus, LEEDS, LS6 3QS, United Kingdom Email: m.ford@leedsmet.ac.uk Tel: +44 113 283 2600 extn 4730 Fax: +44 113 283 3211