searching two tables with one query???

  • Mozzi
  • Student
  • Student
  • Mozzi
  • Posts: 70

Post 3+ Months Ago

hi.. im relative new coding. and I need some help.
I have 2 tables in my database.. they have the same fields.

I would like to search both of them and return the values..

Code: [ Select ]
`news` (
 `id` int(11) NOT NULL auto_increment,
 `heading` varchar(255) default NULL,
 `author` varchar(255) default NULL,
 `dates` date default NULL,
 `intro` longtext,
 `content` longtext,
 `picture` varchar(255) default NULL,
 `showpicture` varchar(255) default NULL,
 `pdf` varchar(255) default NULL,
 `showpdf` varchar(255) default NULL,
 `link` longtext,
  1. `news` (
  2.  `id` int(11) NOT NULL auto_increment,
  3.  `heading` varchar(255) default NULL,
  4.  `author` varchar(255) default NULL,
  5.  `dates` date default NULL,
  6.  `intro` longtext,
  7.  `content` longtext,
  8.  `picture` varchar(255) default NULL,
  9.  `showpicture` varchar(255) default NULL,
  10.  `pdf` varchar(255) default NULL,
  11.  `showpdf` varchar(255) default NULL,
  12.  `link` longtext,



Code: [ Select ]
`features` (
 `id` int(11) NOT NULL auto_increment,
 `heading` varchar(255) default NULL,
 `author` varchar(255) default NULL,
 `dates` date default NULL,
 `intro` longtext,
 `content` longtext,
 `picture` varchar(255) default NULL,
 `showpicture` varchar(255) default NULL,
 `pdf` varchar(255) default NULL,
 `showpdf` varchar(255) default NULL,
 `link` longtext,
  1. `features` (
  2.  `id` int(11) NOT NULL auto_increment,
  3.  `heading` varchar(255) default NULL,
  4.  `author` varchar(255) default NULL,
  5.  `dates` date default NULL,
  6.  `intro` longtext,
  7.  `content` longtext,
  8.  `picture` varchar(255) default NULL,
  9.  `showpicture` varchar(255) default NULL,
  10.  `pdf` varchar(255) default NULL,
  11.  `showpdf` varchar(255) default NULL,
  12.  `link` longtext,


This is what I had in mind.. as all the fields are the same .. its just the tables that are diffrent!!

Code: [ Select ]
SELECT *
FROM news, features
WHERE heading LIKE %colname% or author LIKE %colname% or intro LIKE %colname% or content LIKE %colname% or link LIKE %colname%
ORDER BY dates ASC
  1. SELECT *
  2. FROM news, features
  3. WHERE heading LIKE %colname% or author LIKE %colname% or intro LIKE %colname% or content LIKE %colname% or link LIKE %colname%
  4. ORDER BY dates ASC
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • SpooF
  • ٩๏̯͡๏۶
  • Bronze Member
  • User avatar
  • Posts: 3422
  • Loc: Richland, WA

Post 3+ Months Ago

So your trying to get all the rows from each table in a single query? Is there any reason you can't combine the tables and add another field to tell if its a a 'news' or 'feature'?
  • nexuslite
  • Newbie
  • Newbie
  • nexuslite
  • Posts: 10
  • Loc: Keizer, OR

Post 3+ Months Ago

If you don't need the id for any reason you can create a temporary table and insert both tables into it.

I create 2 tables one named table1 and one named table2 with the following code:

Code: [ Select ]
CREATE TABLE table (
 ID int(10) unsigned NOT NULL AUTO_INCREMENT,
 column1 int(10) unsigned NOT NULL,
 column2 int(10) unsigned NOT NULL,
 PRIMARY KEY (ID)
);
  1. CREATE TABLE table (
  2.  ID int(10) unsigned NOT NULL AUTO_INCREMENT,
  3.  column1 int(10) unsigned NOT NULL,
  4.  column2 int(10) unsigned NOT NULL,
  5.  PRIMARY KEY (ID)
  6. );


Then in PHP/SQL I create a temporary table and import both tables into it without the id field. After that you can just select from the temporary table which contains data from both tables.

Code: [ Select ]
$DBLink = mysql_connect('localhost', 'root', '');
mysql_select_db('test_db');

/* create temporary table */
$query = "CREATE TEMPORARY TABLE temporary_table ( column1 int(10) unsigned NOT NULL, column2 int(10) unsigned NOT NULL)";
mysql_query($query);

/* insert table1 into temporary table */
$query = "INSERT INTO combined_table (column1, column2) SELECT column1, column2 FROM table1";
mysql_query($query);

/* insert table2 into temporary table */
$query = "INSERT INTO temporary_table (column1, column2) SELECT column1, column2 FROM table2";
mysql_query($query);

/* select your data from temporary table */
$query = "SELECT * FROM temporary_table";
$result = mysql_query($query);
if (is_resource($result)) {
    while ($row = mysql_fetch_array($result)) {
        echo $row['column1'].', '.$row['column2'].'<br/>';
    }
}
  1. $DBLink = mysql_connect('localhost', 'root', '');
  2. mysql_select_db('test_db');
  3. /* create temporary table */
  4. $query = "CREATE TEMPORARY TABLE temporary_table ( column1 int(10) unsigned NOT NULL, column2 int(10) unsigned NOT NULL)";
  5. mysql_query($query);
  6. /* insert table1 into temporary table */
  7. $query = "INSERT INTO combined_table (column1, column2) SELECT column1, column2 FROM table1";
  8. mysql_query($query);
  9. /* insert table2 into temporary table */
  10. $query = "INSERT INTO temporary_table (column1, column2) SELECT column1, column2 FROM table2";
  11. mysql_query($query);
  12. /* select your data from temporary table */
  13. $query = "SELECT * FROM temporary_table";
  14. $result = mysql_query($query);
  15. if (is_resource($result)) {
  16.     while ($row = mysql_fetch_array($result)) {
  17.         echo $row['column1'].', '.$row['column2'].'<br/>';
  18.     }
  19. }


Probably a slow way to do it but it may solve the problem.

Post Information

  • Total Posts in this topic: 3 posts
  • Users browsing this forum: No registered users and 94 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.