How do you SELECT multiple tables from mysql ?

[cork]

Hi,
I'm kinda new to php and mysql. I'm making a basic shopping cart using 2 database tables. I want my cart to display items from the 2 tables into 1 html table.

I ran into a problem giving the 2 database tables the same id name, so now I want to change them. But I want to keep them at auto_increment.

Is it possible to give the id a name like TKT0000 that will auto increment as TKT0001, TKT0002 and so on? This will at least give the items from the different tables a different id name so the cart will work correctly.

Thanks in advance!

[cork]

I'm making a shopping cart. I have 2 database tables with items to buy. I'm trying to SELECT IDs from both tables so that the shopping items can be displayed to the user in an HTML table. Right now, I am only pulling the ID from 1 table ("summercamp"). How do I pull info from 2 tables?

Here is my code that pertains to the SELECT statement:

foreach ($contents as $id=>$qty) {
			$sql = 'SELECT * FROM summercamp WHERE id = '.$id;
			$result = $db->query($sql);
			$row = $result->fetch();
			extract($row);
			$output[] = '<td><a href="cart.php?action=delete&id='.$id.'" class="r"><img src="/images/deletecart.gif" alt="delete" border="0"/></a>';
			$output[] = '<td>'.$title.'</td><td>"'.$description.'"</td>';
			$output[] = '<td class="red">$'.$price.'</td>';
			$output[] = '<td><input type="text" name="qty'.$id.'" value="'.$qty.'" size="3" maxlength="3" /></td>';
			$output[] = '</td>';
			$total += $price * $qty;
			$output[] = '</tr>';
		}

[Ancient Dragon]

This is how I code a join in Sybase SQL, I assume MySql is nearly the same. Note that if you use the * in the select clause the fields from table2 can not duplicate the names in summercamp. If there is such a duplicate then one or both name have to be changed in the select clause. In the following example both tables have an id field and we want to retrieve both if them. So I coded it to give a unique name to the id field in each table.

SELECT summercamp.id   summid,
            <other fields from summercamp here>,
            table2.table2id,
            <other table 2 fields here>
FROM summercamp, table2
where summercamp.id = '.$id
and table2.id = summercamp.id

[Ancient Dragon]

This is how I code a join in Sybase SQL, I assume MySql is nearly the same. Note that if you use the * in the select clause the fields from table2 can not duplicate the names in summercamp. If there is such a duplicate then one or both name have to be changed in the select clause. In the following example both tables have an id field and we want to retrieve both if them. So I coded it to give a unique name to the id field in each table.

SELECT summercamp.id   summid,
            <other fields from summercamp here>,
            table2.table2id,
            <other table 2 fields here>
FROM summercamp, table2
where summercamp.id = '.$id
and table2.id = summercamp.id

I just noticed an error table2.id table2id

[cork]

Thanks for your reply. So, my field names for the two tables cannot be the same?

Here is the revised code after taking your advice. Is this what you mean?

$sql = 'SELECT summercamp.id id, title, description, price tickets.id tkt_id, tkt_title, tkt_description, tkt_price FROM summercamp, tickets WHERE summercamp.id = '.$id' and tickets.id = '.$id;

[phper]

There are many ways that you can join 2 tables.

You can read-up further on joins here:

http://en.wikipedia.org/wiki/Join_(SQL)

Regards,
Alex

[uzairk]

I have problem about multiple query. How I will select data from multiple tables in query.so kindly guide me about this query..muskan

[kflorida78]

Hi,
I'm kinda new to php and mysql. I'm making a basic shopping cart using 2 database tables. I want my cart to display items from the 2 tables into 1 html table.

I ran into a problem giving the 2 database tables the same id name, so now I want to change them. But I want to keep them at auto_increment.

Is it possible to give the id a name like TKT0000 that will auto increment as TKT0001, TKT0002 and so on? This will at least give the items from the different tables a different id name so the cart will work correctly.

Thanks in advance!

I'm not a SQL expert, so maybe someone more knowledgeable will come along. But do a Google search for MySQL INNER JOIN as that is what you need to do.

[richieking]

You are contributing the following post:
This is very simple. lets say you got table1, table2, table2.

There are several ways to query mysql server, thus inner join, left join and right join.

There is another easy for noob. check this out.

$query="SELECT * FROM table1,table2,table3 WHERE table1.t1_ID=table2.t2_ID AND table2.t2_ID=table3.t3_ID";
// QUERY DB
$result= mysql_query($query)or die (mysql_error());
//RUN THROUGH
while($row=mysql_fetch_array($result))
{
echo $row[];// BLAR BLAR BLAR

}

This will come out with the exact info for each user id you wanted.
This is only to give you an idea. explore.
;)

[diafol]

You can use the same fieldnames from different tables by giving them an alias with the AS keyword.

... table1.id AS id1 ... table2.id AS id2 ...

kflorida78 has it - INNER JOIN is usually the most useful JOIN. LEFT JOIN and RIGHT JOIN can also be used. MySQL also allows you to avoid the INNER JOIN keywords as shown by richieking. Personally I find the keywords reassuring, but it can get a little messy if you have to join more than 2 tables.

[richieking]

Yep Ardav. The as keyword is also very operational. Personally, i don't really advice the JOIN stuffs to a noob. The logic of the joins needs to be really mastered. If not, you can pull results that are not needed for a particular account.

I prefer the using the table ID's. Also inner join is very easy and it works the same.
ciao.

[Caeon]

I like to do:

$sql = "SELECT t.*,t2.* FROM table1 t
        LEFT JOIN table2 t2 ON t.id = t2.id"
$result= mysql_query($sql)or die (mysql_error());

while ($row = mysql_fetch_array($result))
{
   echo $row[title]; // or whatever else you're trying to do.
}

When using LEFT JOIN, the tables have to have a field that is the same that you can connect. In the example I wrote: both table1 and table2 both had an id field that had the same information, so I could LEFT JOIN the id columns and get the information from both tables.

I hope this helps! Good luck!

[Ancient Dragon]

using wild cards may result in sql errors if two or more tables have duplicate column names. The result set must have unique column names.

[ppostma1]

to merge (not join) two different sets of data into one result the keyword is UNION
Permission:
user_id, type (ENUM: region,country,office), value (name of region,country,office)

countryXregion:
pacific, japan
pacific, korea
africa, kenya

Select all countries and countries by region by a user_id

(SELECT c.`country` AS `value` FROM permissions p, countryXregion c WHERE p.user_id=X AND p.`type`='region' AND p.`value`=c.`region` ) UNION DISTINCT (SELECT `value` FROM offer_permissions WHERE user_id=X AND `type`='country')

[Rennie_1]

You need to try SQL query like...

SELECT saad_categories.id, test.sell_price1 FROM saad_categories JOIN test ON (saad_categories.id = test.sell_price1)