Jump to content

'UNION'


xam

Recommended Posts

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]
Link to comment
Share on other sites

[!--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 ..... ?
Link to comment
Share on other sites

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
Link to comment
Share on other sites

[!--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"
Link to comment
Share on other sites

[!--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.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.