SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Zealot
    Join Date
    Jul 2004
    Location
    Dublin
    Posts
    197
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Using JOIN in an INSERT statement

    Hi,

    How do I use a join witin an insert statement?


    Let's say I take values from a form which are inserted in the transaction table:

    "INSERT INTO transaction SET
    item = '$_POST[new_item]',
    price= '$_POST[new_price]',
    quantity= '$_POST[new_quantity]',";

    How can I insert the relevant user id into this record?

    I'm using sessions so I guess there is some way I could get the ID value from the session but what is the sql syntax I need to insert the User ID from a table called user?

    I'm using Mysql & PHP.

  2. #2
    SitePoint Wizard
    Join Date
    Aug 2004
    Location
    California
    Posts
    1,672
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Whether you get $userid from the session or from the user table via SELECT it would be something like this:

    $sql = "INSERT INTO transaction SET
    item = \"$userid\",
    item = \"{$_POST[new_item]}\",
    price= \"{$_POST[new_price]}\",
    quantity= \"{$_POST[new_quantity]}\"
    ";

  3. #3
    SitePoint Wizard swdev's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    1,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    To make things more secure, I would use the following code.This assumes that your user is set in the session variable as UserId.

    PHP Code:
        
        $new_item 
    '';
        if (
    true === isset($_POST['new_item']))
        {
          
    $new_item mysql_real_escape_string($_POST['new_item']);
        }
        
        
    $new_price '';
         if (
    true === isset($_POST['new_price']))
         {
           
    $new_price mysql_real_escape_string($_POST['new_price']);
         }
        
        
    $new_quantity '';
         if (
    true === isset($_POST['new_quantity']))
         {
           
    $new_quantity mysql_real_escape_string($_POST['new_quantity']);
         }
        
        
    $userid '';
        if (
    true === isset($_SESSION['UserId']))
        {
          
    $userid $_SESSION['UserId'];
        }
        
        
    $sql 'INSERT INTO transaction SET '
        
    $sql .= ' UserId = \''$userid '\'';
        
    $sql .= ' Item = \''$new_item '\'';
        
    $sql .= ' Price = \''$new_price '\'';
        
    $sql .= ' Quantity = \''$new_quantity '\'';
        
        echo 
    $sql//<== for diagnostic purposes only
        
    $result mysql_query($sql) or die ($sql ' failed due to ' mysql_error()); 
    Basically this code checks for the item, price and quantity. If they are not set inthe form, then default values ('') are used. If they are set, then run the mysql_real_escape_string function on then to escape problem characters.
    Then print the query so you can see that it looks correct.
    Then execute the query, exiting if there is a problem.

    Hope this helps

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    if you are inserting literal values, use the INSERT VALUES syntax, not the INSERT SET syntax, which is a mysql abomination

    from the point of view of the sql, values coming in from form fields are literals

    Code:
    insert
      into transactions
         ( item
         , price
         , quantity
         )
    values
         ( 123
         , 9.37
         , 2
         )
    now let's say that you wanted to insert a userid, and for some reason you didn't have the userid, you just had the user name, and you need to look up the userid in the users table

    now you need INSERT SELECT syntax

    just place the literal values (from the form fields) into the SELECT list

    Code:
    insert
      into transactions
         ( item
         , price
         , quantity
         , userid
         )
    select 123
         , 9.37
         , 2
         , userid
      from users
     where username = 'r937'
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard swdev's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    1,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937

    Why is INSERT SET an abomination? and why is INSERT VALUES better?

    I would have thought the SET version was better, because you can see exactly which value is going to what column in the table, where as with the VALUES version, you are relying on the order being the same in the INSERT and VALUES parts.

    Hope you can clear this up

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    INSERT SET is non-standard

    should you ever decide to replace mysql on the backend with another database, you will find it necessary to rewrite your code to INSERT VALUES
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard swdev's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    1,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah - ok. I will use INSERT VALUES in future. Many thanks for that
    I do read a lot of your posts in the MySQL forum. Man you are clever


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
  •