Php select from two tables

I am trying to select two tables with where clause,

The problem: I am getting more than 2 result. something like 123451111 I only have two ids with the value 1. I think I am doing it wrong.

The tables don't have the same structure and are not related by any means. Any ideas?

<?php include_once("config.php");
   $s = '1';
$stmt =$mydb->prepare("select * FROM table1,table2 where table1.id = ? or table2.id = ?");
stmt->bind_param('ss', $s, $s);
echo $mydb->error;
$stmt->execute();

?>
<?php
$results  = $stmt->get_result();
while ($row = $results->fetch_assoc()) {
echo $row['id']."<br/>";
}
?>

asked Oct 29, 2013 at 4:04

6

You need to have a join between table1 and table2 on some unique column, say id.

select * FROM table1,table2 where table1.id = table2.id;

Additionally you can have multiple filter conditions( say you want to filter the tables on id=101 -

select * 
FROM table1,table2 
where table1.id = table2.id
and table1.id = 101;

Hope this helps. Whenever you have multiple tables in a SQL statement, you need to join them otherwise the engine would make cartesian product as it happens in Cartesian product of mathematical set theory.

Basically you should have at least n-1 join conditions where n is the number of tables used.

answered Oct 29, 2013 at 4:09

Aditya KakirdeAditya Kakirde

4,5651 gold badge12 silver badges10 bronze badges

2

Your question is a little problematic, but if your problem is not getting two id's, but you are getting one correctly with the use of a JOIN, you may be looking for a IN clause:

SELECT * 
FROM table1,table2 
WHERE table1.id = table2.id
AND table1.id IN (ID1, ID2);

Using IN instead of = lets you match multiple values to the table.id. This way, you get data from both tables and you get both ID's

answered Oct 30, 2013 at 1:05

Filipe SilvaFilipe Silva

20.8k5 gold badges50 silver badges67 bronze badges

This is join usage :

select t1.*,t2.* FROM table1 t1
left join table2 t2
on t1.id = t2.id
where t1.id = "keyword"

answered Oct 29, 2013 at 4:14

Hi friends, In this tutorial we will see how to join more than one tables in PHP & MySQL using SQL join statement and without using join statement, though this is very common requirement when it comes to database and we need sometimes to fetch data from more than one table as per tables relation so in that case we can write up join query to get data and by joining them it can be showed in proper format. so here in this tutorial i have taken two tables one is category and another is product so that you can understand easily. before proceeding you can check out the demo so that you can get idea about joining tables, so let's get started.

Consider Following Two tables.

1 : tbl_categories

CREATE TABLE IF NOT EXISTS `tbl_categories` (
  `cat_id` int(5) NOT NULL AUTO_INCREMENT,
  `cat_name` varchar(20) NOT NULL,
  PRIMARY KEY (`cat_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

Records for this table :

INSERT INTO `tbl_categories` (`cat_id`, `cat_name`) VALUES
(1, 'samsung'),
(2, 'nokia'),
(3, 'htc');

2 : tbl_products

CREATE TABLE IF NOT EXISTS `tbl_products` (
  `product_id` int(5) NOT NULL AUTO_INCREMENT,
  `product_name` varchar(30) NOT NULL,
  `cat_id` int(5) NOT NULL,
  PRIMARY KEY (`product_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

Records for this table :

INSERT INTO `tbl_products` (`product_id`, `product_name`, `cat_id`) VALUES
(1, 'galaxy note', 1),
(3, 'lumia 530', 2),
(5, 'htc grid', 3);

- In above two tables there exist one common column(field) named 'cat_id' based on which you can establish relatinship between these two tables.
- Now suppose you want to retrieve details from products table along with product category.
- You can do so using the following two methods.
1. Referencing two tables in single SQL statement.
2. Using JOIN statements.

1. Single SQL statement

In order to retrieve information from from two related tables you need to reference two tables in your SQL query.

:

SELECT tbl_a.column1 , tbl_a.column2 
       tbl_b.column1 , tbl_b.column2
FROM   tbl_a , tbl_b
WHERE  tbl_a.commonfield=tbl_b.commonfield

- in the above syntax WHERE condition establish relation between tables based on the common field.

Example.

index.php

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Using single SQL</title>
<style>
table,td,th
{
 padding:10px;
 border-collapse:collapse;
 font-family:Georgia, "Times New Roman", Times, serif;
 border:solid #ddd 2px;
}
</style>
</head>
<body>
<table align="center" border="1" width="100%">
<tr>
<th>product id</th>
<th>product name</th>
<th>category name</th>
</tr>
<?php
mysql_connect("localhost","root");
mysql_select_db("dbtuts");
$res=mysql_query("SELECT c.* , p.* FROM tbl_categories c,tbl_products p WHERE c.cat_id=p.cat_id");
while($row=mysql_fetch_array($res))
{
 ?>
    <tr>
    <td><p><?php echo $row['product_id']; ?></p></td>
    <td><p><?php echo $row['product_name']; ?></p></td>
    <td><p><?php echo $row['cat_name']; ?></p></td>
    </tr>
    <?php
}
?>
</table>
</body>
</html>

In above example Query used as follow :

SELECT c . * , p . *
FROM tbl_categories c, tbl_products p
WHERE c.cat_id = p.cat_id

Query explained :
c = tbl_categories
p = tbl_products
* = fetch all records from both tables.
1. c and p is the alias name of these two tables.
2. Both table should have common column, first is primary and second is foreign key.
3. 'cat_id' is primary key in categories table.
4. 'cat_id' is foreign key in products table.

Output : 

2. using JOIN

-In MySql JOINs allows the retrieval of data records from one or more tables having same relation between them, you can also use logical operator to get the desired output from MySql join queries.
1. Inner JOIN
- it is a default join type.
- when two tables are joined using INNER JOIN option it returns only those records from both tables or which there exist an entry for common field.
example :

SELECT tbl_a.column1 , tbl_a.column2
       tbl_b.column1 , tbl_b.column2
FROM   tbl_a INNER JOIN tbl_b
ON     tbl_a.commonfield=tbl_b.commonfield

2. Outer JOIN

SELECT tbl_a.column1 , tbl_a.column2
       tbl_b.column1 , tbl_b.column2
FROM   tbl_a LEFT OUTER JOIN tbl_b
ON     tbl_a.commonfield=tbl_b.commonfield

that's it...
I hope this post is helpful to you...
Download this Script from the given link.

How can I get data from two tables in PHP?

How can I get data from two tables?

How do you SELECT two tables in a single query?

How do I join two tables in a query?