form modified query
Posted: Mon Mar 27, 2006 9:12 pm
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):
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:
the additional where conditions (from the form) get added in php on the actual page.
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
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 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");
}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