Results 1 to 11 of 11
  1. #1
    Join Date
    May 2009
    Posts
    6

    Unanswered: Problem connecting tables

    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
    Last edited by Zoeeeee; 05-03-09 at 11:59.

  2. #2
    Join Date
    Apr 2009
    Posts
    9
    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.

  3. #3
    Join Date
    May 2009
    Posts
    6
    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

  4. #4
    Join Date
    Apr 2009
    Posts
    9
    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.

  5. #5
    Join Date
    May 2009
    Posts
    6
    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

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Zoeeeee
    Is tis correct?
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    May 2009
    Posts
    6
    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

  8. #8
    Join Date
    Apr 2009
    Posts
    9
    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.

  9. #9
    Join Date
    May 2009
    Posts
    6
    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
    Last edited by Zoeeeee; 05-05-09 at 10:23.

  10. #10
    Join Date
    Apr 2009
    Posts
    9

    Lightbulb

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

  11. #11
    Join Date
    May 2009
    Posts
    6
    thanks for all the help and replies.It is solved
    Last edited by Zoeeeee; 05-06-09 at 05:23.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •