Getting seach rersult from multiple parameters?

[jmansa]

I'm trying to make a search script where the user can set multiple parameters, but am not succesfull...

Here is what I have:
$sql="SELECT c.ccountry
, c.cregion
, c.cclub
, c.created
, c.clubid
, c.creator
, c.seoname
, c.clubname
, cm.members FROM ".$prefix."_club c
INNER JOIN
(SELECT COUNT(*) as members FROM ".$prefix."_club_users WHERE clubid='c.clubid' ";
if(($mem_low > 1) || ($mem_high < 26)){
$sql.="AND cm.members > $mem_low AND cm.members < $mem_high";
}
$sql.=") cm ";
if($ccountry){
$sql.="WHERE c.ccountry=$ccountry ";
}
if(($cstate) && ($cstate != ''._CHOOSE.' '._STATE_REGION.'')){
$sql.="AND c.cregion=$cstate ";
}
if($ccourse){
$sql.="AND c.cclub=$ccourse ";
}
$sql.="ORDER BY c.created DESC";
$result = mysql_query($sql) or die("Error: (" . mysql_errno() . ") " . mysql_error());
$r_count = 0;
while($row = mysql_fetch_array($result)){
I get this error:
Error: (1054) Unknown column 'cm.members' in 'where clause'

Can't figure out how to get passed this...

Please help :-)

[Fumigator]

Your temp table is aliased as "cm", but only to the objects outside that subquery that builds the temp table. You can't refer to "cm" as you are building the temp table.

You can, and should, reference your ".$prefix."_club_users table.

[BubikolRamios]

I dont know why you are giving us all this php stuff, dont know about php, but can't you stop execution here:


$result = mysql_query($sql)


and give us only contence of $sql ? (If you look there you will probably see problem yourself)

We are not magicians to see what is in '.$prefix'

[Old Pedant]

DEBUG DEBUG DEBUG!!!!

*before* the line
$result = mysql_query($sql) or die("Error: (" . mysql_errno() . ") " . mysql_error());

Add in a debug line:

echo "DEBUG SQL: " . $sql . "<hr>";


EDIT : LOL! Great minds run in the same gutter.

[Old Pedant]

I see another goof, besides what Fumigator saw:

WHERE clubid='c.clubid'

NO NO NO! That is using apostrophes, so you'd be looking for the *STRING* "c.clubid".

You need to do
WHERE clubid=c.`clubid`

or, much more simply an just as correctly
WHERE clubid=c.clubid

[jmansa]

Your temp table is aliased as "cm", but only to the objects outside that subquery that builds the temp table. You can't refer to "cm" as you are building the temp table.

You can, and should, reference your ".$prefix."_club_users table.

Well if I remove the cm. I still have a problem... I have narowed it down to this just to simplify it a bit:

$sql="SELECT COUNT(*) members FROM club_users WHERE clubid=150 AND members > $mem_low AND members < $mem_high";

It comes with this error:
DEBUG SQL: SELECT COUNT(*) members FROM club_users WHERE clubid=150 AND members > 1 AND members < 1
Error: (1054) Unknown column 'members' in 'where clause'

How do I get around this???

[Coyote6]

Looks like you have one of two problems:
1. looks like you forgot the as part of the select statement...

SELECT COUNT(*) as members

2. If you are selecting a count from a table, how can you be limiting the number of rows returned by that same count...

Example table....

ex1
id | name
1 | a
2 | b
3 | c
4 | d
5 | e

/* Results in 5 */
SELECT COUNT(*) as `count` FROM `ex1`


Now if you limit the query by that same `count` you create an error or endless loop.

/* Only result that can return true is 1
because only 1 id can be 1 and also
return the value of 1. (If the id is unique.
Other possibilities may exist if not unique.) */

SELECT COUNT(*) as `count` FROM `ex1` WHERE id=count;

/* Now if we are trying to gather less than or
equal to statements you the count would still
be lowering while trying to limit the query. */

SELECT COUNT(*) as `count` FROM `ex1` WHERE id<`count`;

/* If the count was 5 then it would try to grab the id<5
which only gets a count of 4 which then repeats the
same process./*



There may be a time when it retrieves the right value but does not seem like a good way to get the data. The database design may have some flaws in it if you are having to rely on a count determined by the where clause to limit itself.

I'm not for sure but what may be happening is that you have a column named members and you are trying to rename the count to the same thing and instead of limiting it based on the tables values it is limiting it on count value which may be causing the errors.

[Coyote6]

Looks like you have one of two problems:
1. looks like you forgot the as part of the select statement...

SELECT COUNT(*) as members


Nevermind on this...
SELECT COUNT(*) members and SELECT COUNT(*) as members both work.

And I created a test table and it seems mysql will not use a count within the WHERE clause but will use the tables value if the count is named the same thing...

I still don't think it's a good idea but make sure you have a column named members in your table.

[Fumigator]

Let's step back and take a look at what you are trying to accomplish here.

Is your aim to select rows from your prefix_club table only if there are a certain number of club users related to a club? If so, this kind of query will get you there:


SELECT * FROM t_club as c
WHERE EXISTS (
SELECT 1 FROM t_club_users as cu
WHERE c.club_id = cu.club_id
GROUP BY cu.club_id
HAVING COUNT(*) BETWEEN $min and $max)

[jmansa]

Let's step back and take a look at what you are trying to accomplish here.

Is your aim to select rows from your prefix_club table only if there are a certain number of club users related to a club? If so, this kind of query will get you there:


SELECT * FROM t_club as c
WHERE EXISTS (
SELECT 1 FROM t_club_users as cu
WHERE c.club_id = cu.club_id
GROUP BY cu.club_id
HAVING COUNT(*) BETWEEN $min and $max)


Exactly what I needed... Thanks alot man :-)