SitePoint Sponsor

User Tag List

Results 1 to 8 of 8

Thread: MySql question

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    Feb 2002
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySql question

    Hey guys, im new to this forum but I had a question about mysql.

    I am pretty new to mysql and I want to be able to have 2 tables where one of the fields from the one table is always equal to that field in the other table.

    Reason bieng, I dont want to add about 15 columns to the one table that already has 8, i want a whole new table for that info and i want to link them using the id's of the person. So when you click on a link of the person and it sends the id to the webbrowser i can search the id from the other table instead of the one it just loaded.

    Ok so basicly I want 2 tables to somehow share one column.

    Thanks

    Bryan

  2. #2
    We like music. weirdbeardmt's Avatar
    Join Date
    May 2001
    Location
    Channel Islands Girth: Footlong
    Posts
    5,882
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would have a field in table2 called "userid" or something and then you could do a query like:

    PHP Code:
    SELECT from table1,table2 WHERE table1.id=table2.userid 
    or a join:

    PHP Code:
    SELECT from table1 LEFT JOIN table2 ON table1.id=table2.userid 
    or have I missed the point?
    I swear to drunk I'm not God.
    Matt's debating is not a crime
    Hint: Don't buy a stupid dwarf Clicky

  3. #3
    SitePoint Member
    Join Date
    Feb 2002
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you have the point sort of, i want to know if its possible that like, if insert something in one table then it will automaticly make a id on the other table equal to the new id i just made.

  4. #4
    We like music. weirdbeardmt's Avatar
    Join Date
    May 2001
    Location
    Channel Islands Girth: Footlong
    Posts
    5,882
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeh you can.

    What you will need to do is something like this:

    PHP Code:
    //Do the insert for the first table
    $insert=mysql_query("INSERT INTO table1 SET...");

    //Get the ID that was assigned to it
    $insertedid=mysql_insert_id($insert);

    //Use this to link table2 to table1
    $insert=mysql_query("INSERT into table2 SET userid=$insertedid..."); 
    Does that make sense?
    I swear to drunk I'm not God.
    Matt's debating is not a crime
    Hint: Don't buy a stupid dwarf Clicky

  5. #5
    We like music. weirdbeardmt's Avatar
    Join Date
    May 2001
    Location
    Channel Islands Girth: Footlong
    Posts
    5,882
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In fact you could do it a little better (assuming you are not going to want to use the ID again...)

    PHP Code:
    $insert=mysql_query("INSERT INTO table1 SET...");
    $insert1=mysql_query("INSERT INTO table2 (userid) VALUES (mysql_insert_id($insert))"); 
    Either of the above should work

    For more info look at http://www.mysql.com/doc/m/y/mysql_insert_id.html
    Last edited by weirdbeardmt; Feb 22, 2002 at 06:45.
    I swear to drunk I'm not God.
    Matt's debating is not a crime
    Hint: Don't buy a stupid dwarf Clicky

  6. #6
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    weird, just FYI, mysql_insert_id() doesn't take a "result id" parameter, as you are doing. the only parameter it CAN take is the connection id. i always just call it as

    PHP Code:
    $id mysql_insert_id(); 
    www.php.net/mysql-insert-id

    and for your second example to work, it should be written like this

    PHP Code:
    $insert1 mysql_query("INSERT INTO table2 (userid) VALUES (" mysql_insert_id() . ")"); 
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR

  7. #7
    We like music. weirdbeardmt's Avatar
    Join Date
    May 2001
    Location
    Channel Islands Girth: Footlong
    Posts
    5,882
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    weird, just FYI, mysql_insert_id() doesn't take a "result id" parameter, as you are doing. the only parameter it CAN take is the connection id. i always just call it as
    OK.


    and for your second example to work, it should be written like this
    Not according to the mysql manual unless I have interpreted it wrongly.
    I swear to drunk I'm not God.
    Matt's debating is not a crime
    Hint: Don't buy a stupid dwarf Clicky

  8. #8
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by weirdbeardmt
    Not according to the mysql manual unless I have interpreted it wrongly.
    yes, you must have. i says basically the same thing as the PHP manual entry for mysql_insert_id() (your link is for MySQL's C API, you know?). in your example, you were putting the function IN the query, rather that concatenating its value. try the query -- it will fail. mysql_insert_id() is not a MySQL function (in the sense that you don't use it in a query), but a MySQL API function (used in PHP in this case).


Bookmarks

Posting Permissions

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