need search query help...

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
quince
Forum Newbie
Posts: 15
Joined: Tue Jul 05, 2005 10:17 am

need search query help...

Post 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.
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post 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...
quince
Forum Newbie
Posts: 15
Joined: Tue Jul 05, 2005 10:17 am

Post 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
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Moved to Databases
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post 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).
quince
Forum Newbie
Posts: 15
Joined: Tue Jul 05, 2005 10:17 am

Post 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
quince
Forum Newbie
Posts: 15
Joined: Tue Jul 05, 2005 10:17 am

Problem Solved

Post 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!
Post Reply