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 9 of 9
  1. #1
    Regular Coder
    Join Date
    Jul 2008
    Location
    Blackpool, UK
    Posts
    176
    Thanks
    4
    Thanked 0 Times in 0 Posts

    UPDATE Query Help

    Hi Guys,


    I have two tables

    portfolio & portfolio_cat

    portfolio table contains the following

    id
    cat_id
    jobtitle
    location
    description
    image1
    image2
    image3

    portfolio_cat table contains the following

    cat_id
    cat_name

    In the first page I did whereby I added the info to the database. The cat_id had values of 1, 2 and 3 for the relevant category.

    Now once you have added say value 2 to the database, i am now creating an edit page.

    I am struggling to write the query as i am very new to PHP.

    cat_id is the associated link to both tables.


    Does anybody know what the query would be so that it would update the cat_id in the portfolio table. Or do I just need to simply update the portfolio table?????

    In the form on the edit page I will have to write a query that drags the category info into the drop down box, then if they change a selection I need to update that and that is where i struggle


    Any help would be great


    Cheers Dan

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Typically you would use a key that doesn't ever need to be changed. Once a category is inserted, it is given a certain primary key, in your case cat_id, and then that category can remain that key forever. All other tables that refer to that category, such as your portfolio table, will use that cat_id and will not have to change, unless they change the category a portfolio is in. In that case, yes, you need to use a query to update the cat_id to point to the new category.

  • #3
    Regular Coder
    Join Date
    Jul 2008
    Location
    Blackpool, UK
    Posts
    176
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Hi

    So how do I write the query to update the category if it is changed???

    I am very new to PHP and have not got to queries that involve multiple tables

  • #4
    Regular Coder
    Join Date
    May 2008
    Location
    Ohio
    Posts
    231
    Thanks
    3
    Thanked 21 Times in 21 Posts
    If you're just editing the category of a single portfolio, use this:

    "UPDATE portfolio SET portfolio.cat_id='$new_id' WHERE portfolio.cat_id='$old_id' AND portfolio.id='$portfolio_id_to_edit'"
    zok@zoklet:~$ whereis zok
    zok: http://zoklet.net | http://zoklet.net/otg | /derzok/at/gmail/dot/com

  • #5
    Regular Coder
    Join Date
    Jul 2008
    Location
    Blackpool, UK
    Posts
    176
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Hi derzok,


    So where you have portfolio.cat_id=$new_id and $old_id and $new_portfolio_ID to edit.

    How do I know what to put in those?


    Sorry to sound dumb

  • #6
    Regular Coder
    Join Date
    May 2008
    Location
    Ohio
    Posts
    231
    Thanks
    3
    Thanked 21 Times in 21 Posts
    $new_id is the new category ID that you're giving to the portfolio. $portfolio_id is the unique identifier that you've given to that portfolio - a way for you to differentiate between two portfolios with identical data. I suppose you could actually leave out $old_id - since $portfolio_id would accomplish that and more. So instead it would be:

    "UPDATE portfolio SET portfolio.cat_id='$new_id' WHERE portfolio.id='$portfolio_id_to_edit'"

    Either way, you can set those values in a number of ways. Depending on what your purpose is, you might consider using a form (in which case you'd pull the variables out of $_POST). Alternatively, you could set the values in the URL (like the URL to this thread - see the "?t=145825" part of the URL? - it's called the query string. You extract data from it using $_GET).

    I hope that helps.
    zok@zoklet:~$ whereis zok
    zok: http://zoklet.net | http://zoklet.net/otg | /derzok/at/gmail/dot/com

  • #7
    Regular Coder
    Join Date
    Jul 2008
    Location
    Blackpool, UK
    Posts
    176
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Hi Derzok

    Thanks for your help, I undrstand now. I will be using a form and post option.


    Basically I have been given the task of adding to code that is already there.
    An admin panel was set up then the client wanted to seperate the portfolio into three categories. I did this in the addportfolio page. And i knew I had to do an update query with the post thing because it was a form, I just didnt know whether the query would change because I was using two tables, I thought it would be a much more difficult query because of the inner join thingy.

    Right I will get to work on this page. Thanks for our help. I may post more tomorrow if i get stuck.

  • #8
    Regular Coder
    Join Date
    May 2008
    Location
    Ohio
    Posts
    231
    Thanks
    3
    Thanked 21 Times in 21 Posts
    Good luck
    zok@zoklet:~$ whereis zok
    zok: http://zoklet.net | http://zoklet.net/otg | /derzok/at/gmail/dot/com

  • #9
    Regular Coder
    Join Date
    Jul 2008
    Location
    Blackpool, UK
    Posts
    176
    Thanks
    4
    Thanked 0 Times in 0 Posts
    So I really am struggling with this editpage.


    If anybody could read through the posts above and help me write the full thing then that would be great.

    Here is a quick recap.

    An existing edit.php page has been set up. This page now needs a drop down menu added to it so that admin can edit the categories in the drop down and these can be updated in the database.

    Above the query for getting the update is fine. But I am using a form when the form is submitted it then updates.

    My friend told me I would either need to get the "$portfolio_id_to_edit" will need to go in a hidden field or from the URL.

    I really have no idea, as I am brand new to PHP.

    If anybody can help between the hours of 9am and 5pm tomorrow (FRIDAY) and has MSN then maybe we could chat realtime and help with this page for me. you would be a lifesaver.


    Dan


  •  

    Posting Permissions

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