SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Zealot Gman's Avatar
    Join Date
    Jan 2002
    Location
    Sarasota, FL
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Update table using JOIN?

    Hi all,

    I need some help on how to go aobut this...

    I have a table called myinfo with CompanyID, company, address, city, state and the other fields in the below query.

    I also have a table called admin with id, CompanyID, username, password

    How can I do an update that will update all the contents in myinfo WHERE the admin = CompanyID of myinfo? I've been trying things, but just can not get it.

    Im running MySQL 4.0.20
    PHP Code:
    mysql_query("UPDATE myinfo 
    INNER JOIN admin
    SET company = '
    $_POST[company]',
    address = '
    $_POST[address]',
    city = '
    $_POST[city]',
    state = '
    $_POST[state]',
    zip = '
    $_POST[zip]',
    phone = '
    $_POST[phone]'
    ,supportemail = '
    $_POST[supportemail]'
    ,website = '
    $_POST[website]'
    ,supporturl = '
    $_POST[supporturl]'
    ,forums = '
    $_POST[forums]'
    ,mburl = '
    $_POST[mburl]
    WHERE myinfo.CompanyID = '
    $_SESSION[ausername]'")
    or die(
    mysql_error()); 

  2. #2
    3MTA3
    Join Date
    Jul 2003
    Location
    Florida
    Posts
    1,016
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Are you getting any error messages?

    Also, if the field CompanyId is a foreign key in the admin table then wouldn't they have the same value stored in them? If so then try this:

    PHP Code:
    $get_CompanyId $_SESSION['ausername'];
    $get_Company $_POST['company'];
    $get_address $_POST['address'];
    $get_city $_POST['city'];
    $get_state $_POST['state'];
    $get_zip $_POST['zip'];
    $get_phone $_POST['phone'];
    $get_supportemail $_POST['supportemail'];
    $get_website $_POST['website'];
    $get_supporturl $_POST['supporturl'];
    $get_forums $_POST['forums'];
    $get_mburl $_POST['mburl'];

    $sql "UPDATE myinfo SET company='$get_Company', 
                address = '
    $get_address',
                city = '
    $get_city',
                state = '
    $get_state',
                zip = '
    $get_zip',
                phone = '
    $get_phone',
                supportemail = '
    $get_supportemail',
                website = '
    $get_website',
                supporturl = '
    $get_supporturl',
                forums = '
    $get_forums',
                mburl = '
    $get_mburl'
                WHERE CompanyID = '
    $get_CompanyId'";
    mysql_query($sql) or die(mysql_error()); 
    And if the field CompanyId (or any other fields) is set as an integer in the database, then remove the single quotes from the variable ($get_CompanyId) in the SQL string.


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
  •