'UNION'

[xam]

Hello,

[code]$limit        = $settings['Search Results Per Page'];
    $extraSql     = "$searchby LIKE '%".$searchtext."%'";

    if($searchby == 'article')
    {
        $extraSql .= " OR description LIKE '%".$searchtext."%'";
    }

    // Select all online articles which are in date and are displayed on a category
    $sql = "SELECT n.categoryid, n.articleid, n.title, c.name as categoryname
            FROM " . TABLE_PREFIX . "p2_news n
            INNER JOIN " . TABLE_PREFIX . "categories c
            ON c.categoryid = n.categoryid
            INNER JOIN " . TABLE_PREFIX . "pagesort p
            ON p.categoryid = n.categoryid AND p.pluginid in ('2', '2000', '2001')                                                
              WHERE $extraSql AND (n.settings & 2)
            AND (n.datestart = 0 OR n.datestart < '" . time() . "')
            AND (n.dateend   = 0 OR n.dateend   > '" . time() . "')
            LIMIT $start, " . ($limit + 1) . "";

    $getarticles = $DB->query($sql);

    $rows = $DB->get_num_rows($getarticles);[/code]


Can someone help me about above code ?
I've 2 tables: p2_news and p2000_news, How I modify this 2 tables for my search page?
Above code is works perfect, When someone put a text in search field, it show results from p2_news database but I want to show results from 2 tables (p2_news and p2_news)..

I did some modify for it but I got error shown below:

[code]Invalid SQL: SELECT n.categoryid, n.articleid, n.title, c.name as categoryname
            FROM sd_p2_news n
            INNER JOIN sd_categories c
            ON c.categoryid = n.categoryid
            INNER JOIN sd_pagesort p
            ON p.categoryid = n.categoryid AND p.pluginid in ('2', '2000', '2001')                                                
              WHERE title LIKE '%3%' AND (n.settings & 2)
            AND (n.datestart = 0 OR n.datestart < '1142989835')
            AND (n.dateend   = 0 OR n.dateend   > '1142989835')
            LIMIT 0, 11
            UNION ALL
            SELECT n.categoryid, n.articleid, n.title, c.name as categoryname
            FROM sd_p2000_news n
            INNER JOIN sd_categories c
            ON c.categoryid = n.categoryid
            INNER JOIN sd_pagesort p
            ON p.categoryid = n.categoryid AND p.pluginid in ('2', '2000', '2001')                                                
              WHERE title LIKE '%3%' AND (n.settings & 2)
            AND (n.datestart = 0 OR n.datestart < '1142989835')
            AND (n.dateend   = 0 OR n.dateend   > '1142989835')
            LIMIT 0, 11            
Error: Illegal mix of collations for operation 'UNION'
Error number: 1271 [/code]


Or Is this possible, SELECT n.categoryid, n.articleid, n.title, c.name as categoryname FROM " . TABLE_PREFIX . "p2_news n[b] [u]AND " . TABLE_PREFIX . "p2000_news[/u][/b]

[wickning1]

This is a bug that has been fixed in the latest versions of 4.1 and 5.0. Until you upgrade, you'll have to set the columns in both tables to the same collation, or change the collation inside the query - in one half of your union.

[xam]

[!--quoteo(post=357315:date=Mar 22 2006, 04:30 PM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Mar 22 2006, 04:30 PM) [snapback]357315[/snapback][/div][div class=\'quotemain\'][!--quotec--]
This is a bug that has been fixed in the latest versions of 4.1 and 5.0. Until you upgrade, you'll have to set the columns in both tables to the same collation, or change the collation inside the query - in one half of your union.
[/quote]

I'm using mysql Client API version 5.0.18... could u tell me that how I set the columns ...or change the collation inside ..... ?

[xam]

no help :( ?

[fenway]

Sounds like something that ALTER TABLE should be able to do.

[xam]

so ??

[wickning1]

First you need to know what collations you are using. The database can have a default collation, each table can have a default collation, and each column can have a collation. phpMyAdmin should show you this information, or you can log into the command line client and run commands like "SHOW CREATE TABLE mytable"

Then you need to use ALTER TABLE to make them all match. Something like:

ALTER TABLE mytable CHANGE mycolumn mycolumn TINYTEXT CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL

[xam]

[!--quoteo(post=357968:date=Mar 24 2006, 06:06 PM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Mar 24 2006, 06:06 PM) [snapback]357968[/snapback][/div][div class=\'quotemain\'][!--quotec--]
First you need to know what collations you are using. The database can have a default collation, each table can have a default collation, and each column can have a collation. phpMyAdmin should show you this information, or you can log into the command line client and run commands like "SHOW CREATE TABLE mytable"

Then you need to use ALTER TABLE to make them all match. Something like:

ALTER TABLE mytable CHANGE mycolumn mycolumn TINYTEXT CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL
[/quote]

My my.ini setting about default collation:
[code]default-character-set=latin1[/code]

Phpmyadmin results for each table:
[code]Varchar, text, mediumtext etc= latin1_general_ci. - int, tinyint etc= empty..[/code]

I made a new table to see my default collation so i know its "latin1_swedish_ci"

[xam]

This forum ppl never help beginners at all..

[fenway]

[!--quoteo(post=358923:date=Mar 27 2006, 10:50 AM:name=xam)--][div class=\'quotetop\']QUOTE(xam @ Mar 27 2006, 10:50 AM) [snapback]358923[/snapback][/div][div class=\'quotemain\'][!--quotec--]
This forum ppl never help beginners at all..
[/quote]
I totally disagree -- you've asked a question that pertains to a very complex issue, and with a difficult query as well. wickning1 has given all of the available information to solve your problem, including sample code.