Dynamic SQL

[woodburndesigns]

Hey Everyone,

I am trying to build a search form and results page but I continue to run into the same error. When I search the first time the $sort argument defaults to the correct value. But as soon as I try to sort the data using an argument passed into the below function I get the "...not a valid MySQL result resource..." error. What is the deal?

function nanny_search($workable_area_ID,$sort = 'n.employment_type ASC'){
		global $connection;
		$query = "SELECT 
				n.nanny_ID,
				n.first_name,
				n.last_name,
				n.previous_name,
				n.title,
				n.dob,
				n.city,
				n.state,
				n.zip_code,
				n.preferred_method,
				n.hourly_wages,
				n.weekly_wages,
				n.two_years_childcare_experience,
				n.avmonth,
				n.avday,
				n.avyear,
				n.cities_towns,
				n.drivers_license,
				n.own_transportation,
				n.household_duties,
				n.ability_perform_duties,
				n.limitations,
				n.sun_availability,
				n.mon_availability,
				n.tues_availability,
				n.wed_availability,
				n.thurs_availability,
				n.fri_availability,
				n.sat_availability,
				n.certifications,
				n.willing_cpr,
				n.special_needs_exp,
				n.special_needs_details,
				n.infant_care_exp,
				n.child_care_style,
				n.why_nanny,
				n.personal_description,
				n.smoker,
				n.smoker_refrain,
				n.additional_comments,
				n.photo,
				e.employment_type,
				CURDATE(),
				(YEAR(CURDATE())-YEAR(n.dob))
				- (RIGHT(CURDATE(),5)<RIGHT(n.dob,5))
    			AS age
				FROM nanny_registration	n 
				INNER JOIN nanny_areas a
					ON n.nanny_ID = a.nanny_ID
				LEFT JOIN employment_type e
					ON n.employment_type = e.employment_type_ID		
				WHERE a.workable_area_ID = {$workable_area_ID}
				ORDER BY '{$sort}'";
				$nanny_search_set = mysql_query($query, $connection);
				return $nanny_search_set;
	}

[laserlight]

ORDER BY '{$sort}'

should be:

ORDER BY $sort

If you substitute the default argument for $sort, you would see that your original code generates:

ORDER BY 'n.employment_type ASC'

Which obviously is not correct.

[woodburndesigns]

Thank you LaserLight. You are totally right about that error in the syntax. I changed that but I am still getting the same error.

[woodburndesigns]

For your reference this is the code that passes the argument into the function. Maybe there is something wrong there?

nanny_search_string = $POST['workable_areas'];
if(isset($POST['sort'])){
$nanny_search_string .= ',' .$_POST['sort'];
}
$nanny_search_set = nanny_search($nanny_search_string);

[laserlight]

I suggest that you print out the SQL statement before executing it to visually check for errors in the generated SQL statement.

Aside from that, you may want to see more on what exactly is the error reported by MySQL. The debugging change for that is to write:

return mysql_query($query, $connection) or die(mysql_error());

Of course, in production code the error should be handled more gracefully.

Other possible reasons for the error include a failed connection to the database server, or that the database was not selected.

[woodburndesigns]

So I took this sql and entered into phpmyadmin (of course I replace the dynamic code with hard coded values) and everything worked beautifully. I am at a total loss of why this is not working. The problem is definitely all in the $sort section. As soon as I get rid of that the code works fine. :mad: