[SOLVED] full database search

[sarasoorah]

Hello everyone,

 

I would like to ask if it is possible to search MySQL database which contains about 12 tables

in one query, because I want to use "LIMIT 0,30" for paging because the returned result would be large.

 

I tried writing the following but I didn't give me any results.

SELECT scholarship.serialNum, scholarship.category, scholarship.description, acceptnums.serialNum,
acceptnums.category, acceptnums.description, conference.serialNum, conference.category,
conference.description, occupancy.serialNum, occupancy.category, occupancy.description,
hiring.serialNum, hiring.category, hiring.description, stcases.serialNum, stcases.category,
stcases.description, extrahours.serialNum, extrahours.category, extrahours.description, resplan.serialNum,
resplan.category, resplan.description, boards.serialNum, boards.category, boards.description,
retiring.serialNum, retiring.category, retiring.description, services.serialNum, services.category,
services.description, other.serialNum, other.category, other.description

FROM scholarship, acceptnums,transRules, conference, occupancy, hiring, stcases, extrahours,
resplan, boards,boardMembers, retiring, services, other 

WHERE scholarship.description LIKE '%sara%' OR scholarship.decision LIKE '%sara%' 
OR scholarship.name LIKE '%sara%' OR acceptnums.description LIKE '%sara%' 
OR acceptnums.decision LIKE '%sara%' OR acceptnums.nums LIKE '%sara%' 
OR conference.description LIKE '%sara%' OR conference.decision LIKE '%sara%' 
OR conference.name LIKE '%sara%' OR occupancy.description LIKE '%sara%' 
OR occupancy.decision LIKE '%sara%' OR occupancy.name LIKE '%sara%' 
OR hiring.description LIKE '%sara%' OR hiring.decision LIKE '%sara%' 
OR hiring.dept LIKE '%sara%' OR stcases.description LIKE '%sara%' 
OR stcases.decision LIKE '%sara%' OR stcases.caseDescr LIKE '%sara%' 
OR extrahours.description LIKE '%sara%' OR extrahours.decision LIKE '%sara%' 
OR extrahours.dept LIKE '%sara%' OR resplan.description LIKE '%sara%' 
OR resplan.decision LIKE '%sara%' OR resplan.name LIKE '%sara%' 
OR boards.description LIKE '%sara%' OR boards.decision LIKE '%sara%' 
OR boards.dept LIKE '%sara%' OR retiring.description LIKE '%sara%' 
OR retiring.decision LIKE '%sara%' OR retiring.dept LIKE '%sara%' 
OR services.description LIKE '%sara%' OR services.decision LIKE '%sara%' 
OR services.dept LIKE '%sara%' OR other.description LIKE '%sara%' 
OR other.decision LIKE '%sara%' OR other.dept LIKE '%sara%' 

 

I pasted the above in PHPMyAdmin and no results retured, and also no errors... ??? ??? ???

 

please help,

[bubblegum.anarchy]

Either connect all the tables with indexes (foreign keys) or use UNION

 

     SELECT 'scholarship', count(*) AS matches FROM scholarship 
     WHERE scholarship.description LIKE '%sara%' 
          OR scholarship.decision LIKE '%sara%' 
          OR scholarship.name LIKE '%sara%' 
          OR acceptnums.description LIKE '%sara%'
UNION 
     SELECT 'acceptnums', count(*) AS contains_matches FROM acceptnums 
     WHERE acceptnums.decision LIKE '%sara%' OR acceptnums.nums LIKE '%sara%'
UNION
     etc...

 

... and then another query based on the results to display the records for matching tables.

[sarasoorah]

Thank you very much,

I used the union, it worked