many to many - one to many

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
corillo181
Forum Commoner
Posts: 76
Joined: Wed Apr 26, 2006 3:02 pm

many to many - one to many

Post by corillo181 »

ok.. every tutorial i see and every person that answers me tell me the same..

make 2 table..

one with both id's

if a many to many make 3 tables the 3rd table to connected to the other ids..

now let me put it as clear as i can..

please do not tell me how it works with out examples..

if one is connected to another how does it happen..

how when i update table one will i be able to update table 2 with the id..

as i said before.. i just want to know how to connect the tables and use them..

i seen example but they are already done..

such a s

table_id
1
2
3
table_id2 table_id
1. 2
2. 3
3. 2

my big question is HOW!!!!!!!..

thank you..

if is possible whats the query to make id from table one go to tzable 2.. i don't know wich way to put it any more :cry:
BadgerC82
Forum Commoner
Posts: 25
Joined: Tue Feb 07, 2006 6:53 am

Post by BadgerC82 »

It is not very clear from what you have stated exactly what you are looking to do...

Say for example you have a table Products:

Product

product_id
product_title etc....

and a table Basket:

Basket

basket_id
basket_user etc...

You could allow multiple products to be associated with with a shopping basket by creating a table called products_in_basket (god I need to be more creative with my naming ;) ).

Products_in_Basket

products_in_basket_id
product_id
basket_id

Now when you create a new basket you do the following:

$query = "INSERT INTO basket(you items)VALUES(their values ( remember to check for hacking) )";
$result = mysql_query($query);

// Fetch the id of the basket
$id = mysql_insert_id();

// Insert products into linking table

$query = "INSERT INTO Products_in_Basket (product_id, basket_id)VALUES('$product_id', '$id')";
$result = mysql_query($query);

//etc etc etc etc etc.

Hope this is of some help :)
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

I really do not understand what the problem is... The only i can remotely come up with is with a reference to http://www.php.net/mysql_insert_id... Since this returns the generated id when you insert in the first table, and then can use that value for the insert statement in the second table...
corillo181
Forum Commoner
Posts: 76
Joined: Wed Apr 26, 2006 3:02 pm

Post by corillo181 »

thanx that did help and it works.. but now i'm having problem select it from the tables i thought this would wokr fine but is not..

Code: Select all

<?php
include 'includes/db.php';

$con="SELECT testa.name,testb.last FROM testb,testa WHERE a_id='4'";
$query=mysql_query($con);
while($row=mysql_fetch_array($query)){
print_r($row);

}
a_id is the same id in both table.. is the main id of table a and the connecting id for table be with table a..
corillo181
Forum Commoner
Posts: 76
Joined: Wed Apr 26, 2006 3:02 pm

Post by corillo181 »

:?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

corillo181 wrote:thanx that did help and it works.. but now i'm having problem select it from the tables i thought this would wokr fine but is not..

Code: Select all

<?php
include 'includes/db.php';

$con="SELECT testa.name,testb.last FROM testb,testa WHERE a_id ='4'";
$query=mysql_query($con);
while($row=mysql_fetch_array($query)){
print_r($row);

}
a_id is the same id in both table.. is the main id of table a and the connecting id for table be with table a..
Your query is going to choke because you have not told MySQL what table to compare the column a_id to.

Code: Select all

<?php
include 'includes/db.php';

$con="SELECT testa.name, testb.last FROM testb, testa WHERE testa.a_id = 4";
$query=mysql_query($con) or die(mysql_error());
while($row=mysql_fetch_array($query)){
    print_r($row);
}
?>
corillo181
Forum Commoner
Posts: 76
Joined: Wed Apr 26, 2006 3:02 pm

Post by corillo181 »

that works but with a problem..

it echos or prints out the first value twice..
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Run the same query in phpMyAdmin (or whatever administrator you choose) and see what is returned. It might be the query and what it is selecting.
corillo181
Forum Commoner
Posts: 76
Joined: Wed Apr 26, 2006 3:02 pm

Post by corillo181 »

thats crazy im using phpmyadmin and i ran the query and still it show the name twice and then the last nae on the second query.. i checked to see if i had 2 name of the same but snce is with auto increment id that wouldt be possible..
BadgerC82
Forum Commoner
Posts: 25
Joined: Tue Feb 07, 2006 6:53 am

Post by BadgerC82 »

You are not joining your tables in the WHERE clause. You need to do something like the following I believe.

Code: Select all

$query = "SELECT testa.*, testb.* FROM testa, testb WHERE testa.link = testb.link AND a_id = '4'";
testa.link and testb.link are whatever links your table together... Otherwise you query will just return multiples of this cause you will be pulling all the names out for each tablea.

If you want more help post the code structure of testa and testb
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Nice catch BadgerC82. I looked right over that one. :oops:
BadgerC82
Forum Commoner
Posts: 25
Joined: Tue Feb 07, 2006 6:53 am

Post by BadgerC82 »

hehe yeah we all do that from time to time :D
Post Reply