query doesn't return result set properly

[vj5]

I have a search page, where users come in and search for group name, groupno, etc. When they enter groupname or groupno, they are taken to another page where they get a list of matching groupnames and when they click on particular groupname, they get the details of that record. The entire process works fine except in the second session, when they click on the particular groupname, they should see the details- for somereason all records don't show up even though it is the database. Here is my sql statement for both pages when echoed:first page:

SELECT DISTINCT(name), groupno FROM persHorizon where 1 AND `name` LIKE '%integration%' ORDER BY `name` ASC

second page:

SELECT UCASE(ph.groupno) as groupno, ph.name, ph.asof, b.FirstName, b.LastName, ph.base, ph.current, ph.`gro/decline` FROM tablename1 Policies p INNER JOIN tablename2 ph ON p.GroupNumber = ph.groupno INNER JOIN tablename3 b ON p.managing_broker_id = b.PersonID where 1 AND `groupno` LIKE '%667L4%' ORDER BY str_to_date(concat(right(asof, 4), '-', left(asof, 2), '-01'), '%Y-%m-%d') DESC

For example, if I search on the first page, "integration" , it bring up two records with integration specialists, and integrations specialists I and both have same groupno. These two records are in the database but it doesn't show up in the webpage. I tried to take the query and post in the PHPMYADMIN area, it doesn't work there to. Can someone please guide me on this issue?

[hgmme@wa]

I tried to take the query and post in the PHPMYADMIN area, it doesn't work there to.

What error did phpMyAdmin give when you tried to run it there? Did you double check that eVerY thInG Is CasEd riGht (specifically table/column names and search parameters)? Edit: On further thought are the search parameters case sensitive, cause I'm suddenly not sure if I've seen that mentioned?

[vj5]

What error did phpMyAdmin give when you tried to run it there? Did you double check that eVerY thInG Is CasEd riGht?

No error. Only empty result set in PHPMYADMIN. It is not for all records only particular ones. For example, integration specialists and integration specialists l and I have these records in the database.

[vj5]

What error did phpMyAdmin give when you tried to run it there? Did you double check that eVerY thInG Is CasEd riGht (specifically table/column names and search parameters)? Edit: On further thought are the search parameters case sensitive, cause I'm suddenly not sure if I've seen that mentioned?

what do you mean by case sensitive? When the same query is working for some records why not this one?

[hgmme@wa]

So the queries work, just not with all parameters? hmm... Then the only thing I could say is, "are you sure your search parameters that aren't working are cased/spaced... etc right?"

[vj5]

So the queries work, just not with all parameters? hmm... Then the only thing I could say is, "are you sure your search parameters that aren't working are cased/spaced... etc right?"

All that is right.

[vj5]

All that is right.

How to write a nested query to get the distinct groupno first and then to get the distinct name.? Both groupno and name is coming from one table. Can someone please help?

[justsomeguy]

Add a GROUP BY

SELECT DISTINCT(name), groupno FROM persHorizon where 1 AND `name` LIKE '%integration%' GROUP BY groupno ORDER BY `name` ASC

I think that will work..

[vj5]

Add a GROUP BY

SELECT DISTINCT(name), groupno FROM persHorizon where 1 AND `name` LIKE '%integration%' GROUP BY groupno ORDER BY `name` ASC

I think that will work..

I have the right groupnos now. But still when I click on that groupname, it is not showing the details of that record.I am passing the variables to second page through URL.here is the code:

SELECT UCASE(ph.groupno) as groupno, ph.name, ph.asof, b.FirstName, b.LastName, ph.base, ph.current, ph.`gro/decline` FROM tablename1 p INNER JOIN tablename2 ON p.GroupNumber = ph.groupno INNER JOIN tablename3  ON p.managing_broker_id = b.PersonID where 1 AND `groupno` LIKE '%667L4%' ORDER BY str_to_date(concat(right(asof, 4), '-', left(asof, 2), '-01'), '%Y-%m-%d') DESC

URL looks like this:

<a href='pers-searchresult.php?groupno=$groupno&asofstart=$asofstart&asofend=$asofend'>$groupname</a>

[justsomeguy]

Does that query work on phpMyAdmin? Does it return any results or show an error?

[vj5]

Does that query work on phpMyAdmin? Does it return any results or show an error?

It doesn't work on PHPMYADMIN. No Error only empty result set.

[justsomeguy]

Not working and returning an empty result set are two different things. If it doesn't work then you'll get an error, if it's just not returning any rows then it's working, it's just not matching anything. So either the WHERE clause is not matching any rows, or the joins aren't matching anything. Start over with the query getting everything, and test it after you add each join or condition to make sure it's still matching results.

[vj5]

Not working and returning an empty result set are two different things. If it doesn't work then you'll get an error, if it's just not returning any rows then it's working, it's just not matching anything. So either the WHERE clause is not matching any rows, or the joins aren't matching anything. Start over with the query getting everything, and test it after you add each join or condition to make sure it's still matching results.

Groupno comes from two different tables and I am matching based on the groupno from both tables. Now in where clause I have not mentioned from which table the groupno is coming:sql:

where 1 AND `groupno` LIKE '%667L4%' ORDER BY str_to_date(concat(right(asof, 4), '-', left(asof, 2), '-01'), '%Y-%m-%d') DESC

Is the problem because I haven't mentioned from which table the groupno is coming? Please guide me on this.

[justsomeguy]

If the column name is ambiguous then you will get an error. I can't do a lot without your data set, you will need to simplify the query down and test the individual parts to find out why it's not doing what you expect. You have two joins in there now, remove one of them and test that to make sure it works, then switch the joins and test it again to make sure that works also. Just simplify it down until you figure out which condition is causing it to not match anything. It's not something you can just look at and immediately figure it out, you need to test.