Page 1 of 1

many to many - one to many

Posted: Mon May 15, 2006 6:27 am
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:

Posted: Mon May 15, 2006 7:02 am
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 :)

Posted: Mon May 15, 2006 12:59 pm
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...

Posted: Mon May 15, 2006 6:48 pm
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..

Posted: Mon May 15, 2006 10:35 pm
by corillo181
:?

Posted: Mon May 15, 2006 10:49 pm
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);
}
?>

Posted: Mon May 15, 2006 11:14 pm
by corillo181
that works but with a problem..

it echos or prints out the first value twice..

Posted: Mon May 15, 2006 11:30 pm
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.

Posted: Tue May 16, 2006 1:53 am
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..

Posted: Tue May 16, 2006 7:48 am
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

Posted: Tue May 16, 2006 11:10 am
by RobertGonzalez
Nice catch BadgerC82. I looked right over that one. :oops:

Posted: Tue May 16, 2006 11:26 am
by BadgerC82
hehe yeah we all do that from time to time :D