sandalian.com

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 "";
    ?>

In

11 responses

  1. steve Avatar
    steve

    nice tutorial dude, keep on writing! ^_*

  2. kiyat Avatar
    kiyat

    kalo di gabung piye? pake join atau union.

  3. linoxs Avatar
    linoxs

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

  4. agung Avatar
    agung

    baguuus baguuuss… 😉 tapi ga sebagus postingan ttg friendster yg kemaren..

    *kabuur*

  5. agung Avatar
    agung

    @linoxs
    MVC maksudnya ? kalau gitu harusnya dah diselesaiin sama model. Miyabi ruless..!!
    *halah* 😛

  6. linoxs Avatar
    linoxs

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

  7. Arief Fajar Nursyamsu Avatar
    Arief Fajar Nursyamsu

    The problem came when user only enter a single character. 😀

    Nice tips n e y.

  8. pandit Avatar
    pandit

    hehehe ngetes command dari consule FreeBSD, iso rak yaoooo

  9. friglob Avatar
    friglob

    same thing you did, only shorter and extendable:

  10. friglob Avatar
    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 Avatar
    PHP Developer

    don’t you know joins in mysql

Leave a Reply

Verified by MonsterInsights