Results 1 to 2 of 2
  1. #1

    Complex Mysql Query

    I'm making a search function to search for words in 2 tables
    The tables are a notes table and a job table.

    All the words in the search query have to be in one of the 3 fields (notes.note,, and job.desc) I'm searching in.

    Example if you searched for pink clock: pink could be in a note and clock could be in the name.

    Jobs can have 0 to an unlimited number of notes (notes.jid = and there can be an unlimited number of jobs.

    I can figure out how to make a function like this work searching in one table but I can't figure out how to join tables to make it search everything but also let a job with no notes where the words turn up in the description and name still show up.

    Thanks for any help.
    Video and Website Services
    Klingman Design, LLC

  2. #2
    Ok, I figured it out...
    For anyone wondering here is my code (slightly changed...).
    $_POST['search'] = trim($_POST['search']);
    if ($_POST['search'] != '') {
    $term = explode(' ',$_POST['search']);
    foreach ($term as $terms) {
    $id = '';
    $results = mysql_query("SELECT DISTINCT `jid` FROM notes WHERE `note` LIKE '%{$terms}%'");
    if (mysql_num_rows($results) > 0) {
    while ($row = mysql_fetch_array($results,MYSQL_ASSOC)) {
    $id .= " OR `id`='{$row['jid']}'";
    $search[] = "(`name` LIKE '%{$terms}%' OR `desc` LIKE '%{$terms}%'{$id})";
    $search = implode(' AND ',$search);
    $result = mysql_query("SELECT `id` FROM job WHERE {$search} ORDER BY `name`") or makepage('Error','<b>Mysql Error: Please contact an administrator and let them know.</b>','',1); 
    If anyone sees any security flaws in this or anything I can do to make it faster email me (the email button) and let me know.
    Last edited by wiseguy; 07-20-2005 at 02:36 PM.
    Video and Website Services
    Klingman Design, LLC

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts