Query Problem

[cover]

So why is it that the following code returns data from a single table
just fine? When 'all' is selected, all records are returned or if
another selection is selected, only the ones specific to that group
are returned for a single table. Code follows.

$query =
"SELECT *
FROM $table
WHERE 1 = 1 ";
if($area != "All") $query .= "and area = '".$area."'" ;
$result = mysql_query($qu ery);


------------------------------------------------------------------------------------------------------------
but where two tables have been successfully joined from a single form
and data from the form is going into 2 separate tables I run into
trouble on the query. The 'all' still returns everything as it
should, pulling data from the two tables but individual selections
used in a query form drop down that includes 'all' (i.e. to narrow to
more specific areas), will not work any longer?

$query =
"SELECT shakers.area, shakers.equipna me, shakers.equipno ,
shakers.coupler , motors.mccloc, motors.hp, motors.frame, motors.amps,
motors.rpm, shakers.notes
FROM shakers, motors
WHERE shakers.equipno = motors.equipno ";
if($area != "All") $query .= "and area = '".$area."'" ;
$result = mysql_query($qu ery);

thanks again for any replies...

Chris

[Lüpher Cypher]

Re: Query Problem

cover wrote:[color=blue]
> So why is it that the following code returns data from a single table
> just fine? When 'all' is selected, all records are returned or if
> another selection is selected, only the ones specific to that group
> are returned for a single table. Code follows.
>
> $query =
> "SELECT *
> FROM $table
> WHERE 1 = 1 ";
> if($area != "All") $query .= "and area = '".$area."'" ;
> $result = mysql_query($qu ery);
>
>
> ------------------------------------------------------------------------------------------------------------
> but where two tables have been successfully joined from a single form
> and data from the form is going into 2 separate tables I run into
> trouble on the query. The 'all' still returns everything as it
> should, pulling data from the two tables but individual selections
> used in a query form drop down that includes 'all' (i.e. to narrow to
> more specific areas), will not work any longer?
>
> $query =
> "SELECT shakers.area, shakers.equipna me, shakers.equipno ,
> shakers.coupler , motors.mccloc, motors.hp, motors.frame, motors.amps,
> motors.rpm, shakers.notes
> FROM shakers, motors
> WHERE shakers.equipno = motors.equipno ";
> if($area != "All") $query .= "and area = '".$area."'" ;
> $result = mysql_query($qu ery);
>
> thanks again for any replies...
>[/color]

I think the problem might be that area is an ambiguous name in this
query (it is in both tables). Try changing it to
if ($area != "All") $query .= "and (shakers.area=' $area' or
motors.area='$a rea')";



--

- lüpher
---------------------------------------------
"Man sieht nur das, was man weiß" (Goethe)

[cover]

Re: Query Problem

On Mon, 02 Jan 2006 06:18:00 GMT, L?pher Cypher
<lupher.cypher@ verizon.net> wrote:
[color=blue]
>I think the problem might be that area is an ambiguous name in this
>query (it is in both tables). Try changing it to
>if ($area != "All") $query .= "and (shakers.area=' $area' or
>motors.area='$ area')";[/color]

Hey that did the trick - thanks SO much. I've spent several hours
trying to debug that. Appreciate your help... :-) THANK YOU

[cover]

Re: Query Problem

On Mon, 02 Jan 2006 06:18:00 GMT, L?pher Cypher
<lupher.cypher@ verizon.net> wrote:
[color=blue]
>I think the problem might be that area is an ambiguous name in this
>query (it is in both tables). Try changing it to
>if ($area != "All") $query .= "and (shakers.area=' $area' or
>motors.area='$ area')";[/color]


Maybe this presses the envelope a little bit but if

if ($area != "All") $query .= "and (shakers.area=' $area' or
motors.area='$a rea')";

works well for two tables, why doesn't

if ($area != "All") $query .= "and (shakers.area=' $area' or
motors.area='$a rea' or contacts.area=' $area')";

work for three tables? Obviousl syntax error of some kind but...

[cover]

Re: Query Problem

Geez, just found my syntax error - works fine now. Needed a blank
space where there wasn't one in the code on the line above. Am
posting the code just in case it might help someone else on a three
table query.

WHERE shakers.equipno = motors.equipno and shakers.equipno =
contacts.equipn o "; ( <= insert space between o&" )
if ($area != "All") $query .= "and (shakers.area=' $area' or
motors.area='$a rea' or contacts.area=' $area')";



On Mon, 02 Jan 2006 14:34:29 -0800, cover
<coverlandNOSPA M914@yahoo.com> wrote:
[color=blue]
>On Mon, 02 Jan 2006 06:18:00 GMT, L?pher Cypher
><lupher.cypher @verizon.net> wrote:
>[color=green]
>>I think the problem might be that area is an ambiguous name in this
>>query (it is in both tables). Try changing it to
>>if ($area != "All") $query .= "and (shakers.area=' $area' or
>>motors.area=' $area')";[/color]
>
>
>Maybe this presses the envelope a little bit but if
>
>if ($area != "All") $query .= "and (shakers.area=' $area' or
>motors.area='$ area')";
>
>works well for two tables, why doesn't
>
>if ($area != "All") $query .= "and (shakers.area=' $area' or
>motors.area='$ area' or contacts.area=' $area')";
>
>work for three tables? Obviousl syntax error of some kind but...[/color]

[Lüpher Cypher]

Re: Query Problem

cover wrote:[color=blue]
> Geez, just found my syntax error - works fine now. Needed a blank
> space where there wasn't one in the code on the line above. Am
> posting the code just in case it might help someone else on a three
> table query.
>[/color]

A good practice is to see if the query actually executed :)

$result = mysql_query($qu ery);
if (!$result) {
// log or output error
echo "Error executing query '$query': ".mysql_error() ;
retrun;
}


--

- lüpher
---------------------------------------------
"Man sieht nur das, was man weiß" (Goethe)