form modified query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
bimo
Forum Contributor
Posts: 100
Joined: Fri Apr 16, 2004 11:18 pm
Location: MD

form modified query

Post by bimo »

I am trying to create a form that changes the mysql search query according to what is entered in the text boxes and/or which radio buttons are selected. I have it to the point where it's almost working and somtimes generates the query that it should but even when it does there's one small problem: it's not working...

So, I'd like to get your opinion on either of two questions (or both):
1) Why isn't the query working? Are the nested queries messing it up? If so, what might be a better way to write the forthcoming example?
2) Does it make sense to generate the query like this (with an HTML form and some unsophisticated php)?

Q1 - here's an example of the the type of query that is generated when most of the form elements have a value (w/ line breakks to make it easier to read):

Code: Select all

select 
distinct v.`video_id`, 
v.`video_thumb`, 
p.`name`, 
p.`orig_from_loc_id` as `home`, 
v.`location_id`, 
(select distinct cit.`name` from cities`cit` where cit.`city_id` = l.`city_id` and l.`location_id` = v.`location_id`) as `city`, 
(select cou.`name` from countries`cou` where cou.`country_id` = l.`country_id` and l.`location_id` = v.`location_id`) as `country`, 
v.`date_rec` from videos`v`, 
people`p`, cities`cit`, 
countries`cou`, 
locations`l` 
where 
p.`person_id` = v.`person_id` 
AND p.`name` like '%james%' 
AND (select cit.`city_id` from cities`cit` where cit.`name` = "new orleans") = (select l.`city_id` from locations`l` where v.`location_id` = l.`location_id`) 
AND p.`build` = 3 
AND p.`skin_color_id` = 2 
AND p.`hair_length_id` = 2 
AND p.`hair_color_id` = 5 
order by v.`date_rec`
The db sql dump is at vidbb.org/db/vidbb_0 20060327 2104.sql

The actual db isn't up at the moment because I'm in the process of moving it.


Q2 - Here's How it's being generated - The main part of the db access happens in the following class method:

Code: Select all

function funcName($wheres = "")
	{
		
		// this query gets the information about a video
		$connection = $this->conn;
		$query = 	"select distinct
					  v.`video_id`,
					  v.`video_thumb`,
					  p.`name`,
					  p.`orig_from_loc_id` as `home`,
					  v.`location_id`,
					  (select distinct cit.`name`
						from cities`cit`
						where cit.`city_id` = l.`city_id`
						and l.`location_id` = v.`location_id`) as `city`,
					  (select cou.`name`
						from countries`cou`
						where cou.`country_id` = l.`country_id`
						and l.`location_id` = v.`location_id`) as `country`,
					  v.`date_rec`
					from
					  videos`v`,
					  people`p`,
					  cities`cit`,
					  countries`cou`,
					  locations`l`
					where
					  p.`person_id` = v.`person_id`
					".$wheres."
					order by v.`date_rec`";
					  //and v.`crisis_id` = 1
					
		print($query);
		$results = $connection->GetAll($query); 
		
		if(count($results) > 0)
			return($results);
		else return("no results");
	}
the additional where conditions (from the form) get added in php on the actual page.

Code: Select all

// instantiate $count
$count = 0;

// count the number of elements in the $_POST array
foreach($_POST as $el)
{
	if($el != NULL) $count++;
	print($count);
}

// check if form elements have been filled out
// if any have, build the query
if($count > 0) 
{
	$wheres = "
	AND
	";
	
	if($count > 0) // if there are completed form elements
		if($_POST['name'] != "")
		{
			$wheres .= "p.`name` like '%" . $_POST['name'] . "%'";
			$count--;
			if($count > 0) $wheres .= " AND ";
			print("</br>$count<br>");
		}
	
	if($count > 0) // if there are completed form elements
		if($_POST['orig_from_city'] != "")
		{
			$ofcit = $_POST['orig_from_city'];
			$wheres .= "(select cit.`city_id` from cities`cit` where cit.`name` = \"$ofcit\") = (select l.`city_id` from locations`l` where v.`location_id` = l.`location_id`)";//"p.`orig_from_city` like '%" . $_POST['orig_from_city'] . "%'";//
			$count--;
			if($count > 0) $wheres .= " AND ";
			print("</br>$count<br>");
		}
	
	.....yadda...


	if($count > 0) // if there are completed form elements
		if($_POST['build'])
		{
			$wheres .= "p.`build` = " . $_POST['build'];
			$count--;
			if($count > 0) $wheres .= " AND ";
			print("</br>$count<br>");
		}
	
	if($count > 0) // if there are completed form elements
		if($_POST['tone'])
		{
			$wheres .= "p.`skin_color_id` = " . $_POST['tone'];
			$count--;
			if($count > 0) $wheres .= " AND ";
			print("</br>$count<br>");
		}
	

	...more yadda...
	

	$peeps = $classInst->funcName($wheres); 
	print("<h2>People</h2>");
	// rall videos column
	print("<div id='col'>");
	if(is_array($peeps));// && $all_vids != "no videos")
	{
		if($peeps == "no results") print("No videos that match these criteria were found.  Try being less specific ");
		print_r($peeps);
		foreach($peeps as $peep)
		{
			print("<a href=\"crisis_main.php?crisis_id=".$peep[1]."&name=".$peep[0]."\">".html_ent($peep[0])."</a><br>");
		}
	}
	print("</div>");
}

I feel like there should be a better way of doing that without making it too complex.

I hope that makes sense. I would really appreciate any advice/help.

b
Post Reply