Hi everyone,i'm new in the forum and in MySQL too.i have one question and i can't find a way to answer it.
if i have 2 tables in one database,customers and customers_payments.how can create a connection between them in order to join them and get for example a payment_date for a user?
when i design them i have something like this in my mind:

customers (id not null unsigned auto_increment, fanme varchar, lname varchar, email varchar, primary key(id)
customers_payment(id not null unsigned,customer_id not null auto_increment,months int, payment_date varchar,foregn key(id),primary key(customer_id)

So how do i make a join between those two,when i design them i make id a primary key,how am i supposed to put the id as foreign key in the customers_payment?plz if anyone has any idea or explanation of how should i continue post it
thanks in advance

Hi in innodb you can create a foreign key,other wise run this querry it is also worked.


select customers.fanme,customers.iname,customers.email,cu stomers_payment.customer_id,customers_payment.mont ha,customers_payment.payment_date join customers_payment On customers.id = customers_payment.id

MARK AS SOLVED if its help you.

thanks for the reply.it's a good suggestion but i can't understand how mysql understands that the two id's are connected,and if i have many records how it will now that the correct user has the ceorrect id,if i try to connect php and mysql?
thanks

hi,

If u create a table type innodb then mysql will understand parent child relation ship other wise which query i was given in that mysql will not understand parent child relation ship, its just an dba assumption of parent child relationship.

sorry but i can't understand.i did this in order to create my two tables

CREATE TABLE customers (cid INT UNSIGNED NOT NULL AUTO_INCREMENT,
fname VARCHAR(20) NOT NULL,
lname VARCHAR(40) NOT NULL,
email VARCHAR(60) NOT NULL,
PRIMARY KEY (cid));

and

CREATE TABLE customers_payment (id INT NOT NULL AUTO_INCREMENT,
months INT NOT NULL,
payment_date VARCHAR(50) NOT NULL,
cid INT,
PRIMARY KEY (id),
FOREIGN KEY (cid) REFERENCES customers(cid) );

Is tis correct?so you suggest that i should add inndb at the end of those two tables?sorry for allthis newbie questions but i'm totally new in this

yes, but if they are not InnoDB tables, then the FK is ineffective

please do a SHOW CREATE TABLE for each table, and paste the results here

have you inserted any rows into these tables yet?

you mean describe?

DESCRIBE customers;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| cid | int(10) unsigned | NO | PRI | NULL | auto_increment |
| fname | varchar(20) | NO | | NULL | |
| lname | varchar(40) | NO | | NULL | |
| email | varchar(60) | NO | | NULL | |
+-------+------------------+------+-----+---------+----------------+

DESCRIBE customers_payment;
+--------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+----------------+
| cpid | mediumint(9) | NO | PRI | NULL | auto_increment |
| months | int(11) | NO | | NULL | |
| payment_date | varchar(50) | NO | | NULL | |
| cid | int(11) | YES | MUL | NULL | |
+--------------+--------------+------+-----+---------+----------------+

Yes i allready added some data and i think it works correctly.What do you think?thanks for all the help and replies

hey not describe the table first write the querry
show create table (table name) then paste it.

For Example
show create table amit


"Table" "Create Table"
"amit" "CREATE TABLE `amit` (
`book_id` int(100) default NULL,
`first_name` varchar(15) default NULL,
`last_name` varchar(15) default NULL,
`start_date` date default NULL,
`end_date` date default NULL,
`salary` float(8,2) default NULL,
`city` varchar(10) default NULL,
`description` varchar(15) default NULL
) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=latin1"

here you see the ENGINE type is MyISAM in this you change the ENGINE type in Innodb on both the table.

sorry i didn't understand at first because i didn't know the command.
when i try show create table customers; i get this

| customers | CREATE TABLE `customers` (
`cid` int(10) unsigned NOT NULL auto_increment,
`fname` varchar(20) NOT NULL,
`lname` varchar(40) NOT NULL,
`email` varchar(60) NOT NULL,
PRIMARY KEY (`cid`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 |

It works correctly so do i still have to change it to innodb?
thanks for all the help and time

if you create a foreign key then offcourse change it to innodb

thanks for all the help and replies.It is solved