Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Regular Coder
    Join Date
    Jan 2010
    Posts
    153
    Thanks
    53
    Thanked 0 Times in 0 Posts

    question about modifying mysql tables

    Hello. How can I incorporate the value of another cell into another when adding a row to mysql?

    For example, I have two columns in a mysql table, one of them is an autoincrement integer called "id" and the other is a text field called "name".

    What I want is that everytime a user submits a form, the "name" field gets the value of the "id" for that row infront of it. How can I achieve this?

    Thanks in advance!

  • #2
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,958
    Thanks
    120
    Thanked 76 Times in 76 Posts
    I think only with trigger, using LAST_INSERT_ID()

    doh I guess there is no point in doing that, you can get combined value any time with select.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Yes, or just create a VIEW that concatenates the two if it's something you do all the time and then use the VIEW in place of the raw SELECT.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    Regular Coder
    Join Date
    Jan 2010
    Posts
    153
    Thanks
    53
    Thanked 0 Times in 0 Posts
    So I tried this:
    PHP Code:
                $helloo2=mysql_query("CREATE VIEW question_id_num AS SELECT id,name FROM taskitems ORDER BY id DESC limit 0,5") or die($mysql_error());
                
    $helloo3=mysql_query("Update taskitems Set name = question_id_num.id.name") or die($mysql_error()); 
    But I'm getting this error: Fatal error: Function name must be a string in /home/tom/public_html/controlpaneladdassignment.php on line 26

    Line 26 is the first of the two lines. Any suggestions?

    Thanks in advance

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Yes, an important suggestion: Learn what a VIEW is.

    A VIEW is just a pre-defined SELECT on an existing table.

    It is *NOT* a table. It can *NOT* be used as a table for any purpose other than as a replacement for SELECT.

    Code:
    CREATE VIEW question_id_num 
    AS 
    SELECT CONCAT( CAST(id AS CHAR) ,name ) AS idName, list, of, other, fields, as, you, want
    FROM taskitems;
    Nothing more.

    You would still *ONLY* use the taskitems table when inserting or updating data.

    You would *ONLY* use the view when making reports, etc.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    Regular Coder
    Join Date
    Jan 2010
    Posts
    153
    Thanks
    53
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Yes, an important suggestion: Learn what a VIEW is.

    A VIEW is just a pre-defined SELECT on an existing table.

    It is *NOT* a table. It can *NOT* be used as a table for any purpose other than as a replacement for SELECT.

    Code:
    CREATE VIEW question_id_num 
    AS 
    SELECT CONCAT( CAST(id AS CHAR) ,name ) AS idName, list, of, other, fields, as, you, want
    FROM taskitems;
    Nothing more.

    You would still *ONLY* use the taskitems table when inserting or updating data.

    You would *ONLY* use the view when making reports, etc.
    Hi O.P, thanks for your reply.

    So I've been trying to understand views and slowly progressing.

    I tried your code and it works great. However, how do I go about concatenating with a dot in between the two? I tried this, but I get ".idname" outputted:
    Code:
    CREATE VIEW question_id_num AS  SELECT CONCAT_WS(CAST(id AS CHAR),'.',name) AS idname FROM taskitems;


    EDIT: Sorry I figured it out! Thanks!
    Last edited by wincode; 02-18-2012 at 06:57 PM.


  •  

    Posting Permissions

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