Joining two Mysql Queries with php

Question

Basically I have 2 SQL queries from 2 different databases and I am trying to compare where they are equal and then join together the other information for that value. My first query contains an id and a product name, my second query contains a product name and components. So I'm trying to join them on the product name and then show the other two bits of information with them. The db I selected is being used in the second query. Any idea what I should do? So far I have this, which only seems to show one result:

$catid = mysql_query("Select a.entry_id, b.cat_name from blog.exp_category_posts a inner join blog.exp_categories b on a.cat_id=b.cat_id where b.Group_ID = 3");

$results = mysql_query("Select a.name, c.product from wr_scientific a inner join wr_scientific_products b on a.id=b.scienc_id Join xcart_products c on b.prod_id=c.productid LIMIT 1000");

while($row1 = mysql_fetch_array($catid)){
$row2= explode("&#8482;", $row1['cat_name']);
$row3= explode("&#174;", $row2[0]);

while($row = mysql_fetch_array($results)){
$rowpro = explode("&trade;", $row['product']);
$rowprod = explode("&reg;", $rowpro[0]);

if($rowprod[0] == $row3[0]){
echo $rowprod[0].$row['name'].$row1['entry_id'];
 }  
  }
 }

Thanks for any help

RandomSeed · Accepted Answer · 2012-06-13 19:32:20Z

1

If the two databases are located on the same instance of MySQL (~ same machine), then you can refer to a table in say db2 from (say) db1 by prefixing the table name with the database name.

E.g:

USE db1 ;
SELECT
    db1.table_in_db1.id,  -- you can specify the database name here, but
    table_in_db2.id,      -- there is no ambiguity, the database name is optional
    field_in_table_in_db2 -- the same way the table name is optionnal when there is no ambiguity
FROM
    db1.table_in_db1      -- database name is optionnal here, current database is assumed
JOIN
    db2.table_in_db2
ON ...

edited Jun 13, 2012 at 19:32

answered Jun 13, 2012 at 19:27

RandomSeed

29.9k6 gold badges54 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Joining two Mysql Queries with php

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related