Jump to content


Photo

'UNION'


  • Please log in to reply
9 replies to this topic

#1 xam

xam
  • Members
  • PipPip
  • Member
  • 23 posts

Posted 22 March 2006 - 01:20 AM

Hello,

$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);


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:

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


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

#2 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 22 March 2006 - 03:30 PM

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.

#3 xam

xam
  • Members
  • PipPip
  • Member
  • 23 posts

Posted 22 March 2006 - 08:26 PM

[!--quoteo(post=357315:date=Mar 22 2006, 04:30 PM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Mar 22 2006, 04:30 PM) View Post[/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 ..... ?

#4 xam

xam
  • Members
  • PipPip
  • Member
  • 23 posts

Posted 23 March 2006 - 09:06 PM

no help :( ?

#5 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 24 March 2006 - 12:00 AM

Sounds like something that ALTER TABLE should be able to do.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#6 xam

xam
  • Members
  • PipPip
  • Member
  • 23 posts

Posted 24 March 2006 - 02:22 PM

so ??

#7 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 24 March 2006 - 05:06 PM

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

#8 xam

xam
  • Members
  • PipPip
  • Member
  • 23 posts

Posted 24 March 2006 - 10:12 PM

[!--quoteo(post=357968:date=Mar 24 2006, 06:06 PM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Mar 24 2006, 06:06 PM) View Post[/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:
default-character-set=latin1

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

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

#9 xam

xam
  • Members
  • PipPip
  • Member
  • 23 posts

Posted 27 March 2006 - 03:50 PM

This forum ppl never help beginners at all..

#10 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 27 March 2006 - 05:24 PM

[!--quoteo(post=358923:date=Mar 27 2006, 10:50 AM:name=xam)--][div class=\'quotetop\']QUOTE(xam @ Mar 27 2006, 10:50 AM) View Post[/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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users