Page 1 of 1

need search query help...

Posted: Wed Jul 06, 2005 3:48 pm
by quince
this query worked fine.

Code: Select all

&quote;SELECT c.*, b.business_name FROM customer_contact_info c, business b WHERE c.$srtMe 

LIKE'%$question%' AND c.business_id = b.business_id ORDER BY $srtMe ASC LIMIT $page_a,$page_b&quote;

the only time this doesn't work is when I'm seaching a business name since the business names are in the business table and not the cusotmer table. i made this query which I thought would work.

Code: Select all

SELECT c.*, b.business_name FROM customer_contact_info c, business b WHERE b.$srtMe 

LIKE'%$question%' AND c.business_id = b.business_id ORDER BY $srtMe ASC LIMIT $page_a,$page_b&quote;
the query returns results, but not acurate ones.

the customer table references the business table at business_id. can anyone tell me what I'm doing wrong or how to fix this.

Posted: Wed Jul 06, 2005 4:08 pm
by Burrito
change the join type you should if only exists on one table it does.

http://dev.mysql.com/doc/mysql/en/join.html

check the MySQL manual you should...

Posted: Wed Jul 06, 2005 4:19 pm
by quince
does yoda speak on tuesday too? LMAO... :lol:

Actually it is two tables. the business table and the contact table. This is so there can be multiple contacts at each business. the contacts table references the business table at business_id.


Thanks for all the help Jedi Master :wink: !

Q

Posted: Wed Jul 06, 2005 4:24 pm
by John Cartwright
Moved to Databases

Posted: Wed Jul 06, 2005 4:27 pm
by Burrito
so have a record on the customer table it could, but on the business table it might not.

not work using '=' as your table join this will.

look at other join types you should (hint: inner).

Posted: Wed Jul 06, 2005 9:22 pm
by quince
Actually the system is set up so that you have to have a business. I don't know how much that matters, but I 've been trying alsorts of different inner join's and none of them worked for me I tried a left, right , and outer join none of them worked either.

Here is what I have now.

Code: Select all

&quote;SELECT customer_contact_info.*, business.business_name FROM customer_contact_info 

INNER JOIN business.business_name USING (business_id) WHERE business.business_name LIKE'%$question% ORDER BY business_name ASC LIMIT 

$page_a,$page_b&quote;
Thanks for the help!
Q

Problem Solved

Posted: Thu Jul 07, 2005 12:37 pm
by quince
hey Luke I used the force and it just came to me.
:lol:

the above query worked fine, It was a problem with a variable name. Flash and PHP = ton's of shizzle going on.

Thanks for all the help!