PHP

Sample of search engine for multiple tables

Searching a string within a table is quite easy, but searching within multiple tables in a database need a little trick. I know it’s not the good one, perhaps everybody who read this can share yours too πŸ˜‰

Example: We have 3 tables with different content and structures for 3 different pages (News, Articles, Products)


< ?php $string = $_POST['what']; # 1. Search for News and store it into array $search_news = mysql_query("SELECT * FROM table_news WHERE news_title LIKE '%$string%' OR news_content LIKE '%$string%' "); while($result=mysql_fetch_array($search_news)){ $array_results[] = array($result['news_title'], "news.php?id=".$result['news_id']); } # 2. Search for Articles and store it into array $search_articles = mysql_query("SELECT * FROM table_articles WHERE articles_title LIKE '%$string%' OR articles_content LIKE '%$string%' "); while($result=mysql_fetch_array($search_articles)){ $array_results[] = array($result['articles_title'], "articles.php?id=".$result['articles_id']); } # 3. Search for products and store it into array $search_products = mysql_query("SELECT * FROM table_products WHERE products_name LIKE '%$string%' OR products_content LIKE '%$string%' "); while($result=mysql_fetch_array($search_products)){ $array_results[] = array($result['products_name'], "products.php?id=".$result['products_id']); } // all result now is in the array $array_results. index [0] is for Title, and index [1] is for the URL echo "

    ";
    foreach($array_results as $search_result){
    echo "

  1. $search_result[0]
  2. ";
    }
    echo "";
    ?>

11 Comments

  1. nice tutorial dude, keep on writing! ^_*

  2. kalo di gabung piye? pake join atau union.

  3. Binun, mang bisa yah ampe dua table gitu? knp gak dibikinin viewnya ajah?

  4. baguuus baguuuss… πŸ˜‰ tapi ga sebagus postingan ttg friendster yg kemaren..

    *kabuur*

  5. @linoxs
    MVC maksudnya ? kalau gitu harusnya dah diselesaiin sama model. Miyabi ruless..!!
    *halah* πŸ˜›

  6. @ agung,
    PLETAAAAK !!!
    *sambit agung pake termos

  7. The problem came when user only enter a single character. πŸ˜€

    Nice tips n e y.

  8. hehehe ngetes command dari consule FreeBSD, iso rak yaoooo

  9. friglob

    same thing you did, only shorter and extendable:

  10. friglob

    // get trimed filter from request and type-cast it to string
    $filter = (string)trim($_REQUEST[‘filter’]);

    // set default array for results
    $arr_results = array();

    // set array of categories/db tables we wanna work with
    $arr_categories = array(
    ‘news’,
    ‘articles’,
    ‘products’
    );

    // function to retrieve array data from specific table
    //
    function get_arr_db_data($p_cat_name, $p_filter = ”)
    {
    // to make sure we return array
    $_arr_db_result = array();
    // get data
    $sql_data = mysql_query(“SELECT * FROM table_”.$cat_name.” WHERE “.$cat_name.”_title LIKE ‘%”.(string)$p_filter.”%’ OR “.$cat_name.”_content LIKE ‘%”.(string)$p_filter.”%’ “);
    while($result=mysql_fetch_array($sql_data)){
    $_arr_db_result[] = array(
    ‘title’ => $result[$cat_name . ‘_title’],
    ‘url’ => $cat_name.”.php?id=” . $result[$cat_name.’_id’]
    );
    }
    return $_arr_db_result;
    }

    // loop categories/db tables and retrieve data
    // store data from all categories to one array
    // category name as array key
    //
    foreach( $arr_categories AS $cat_name ){
    $arr_results[$cat_name] = get_arr_db_data($cat_name);
    }

  11. PHP Developer

    don’t you know joins in mysql

Leave a Reply