1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Some MySQL help

Discussion in 'General PHP and MySQL Discussions' started by Russ, Dec 21, 2012.

  1. Russ

    Russ Well-Known Member

    First off thanks to Jake for helping me get this far :) I'm a complete novice at this, I've tried googling the crap out of this and can't seem to find a fix. I'll show you the code I'm working with first:

    <div id="loadouts">
                if (ISSET($_POST['action']))
                    if ($_POST['action'] == "Update")
                            $cust_loadout_id = $_POST['custloadoutid'];
                            $unique_id = $_POST['uniqueid'];
                            $db = @mysql_connect($hostname,$dbusername,$dbpassword);
                            if(!@mysql_select_db($databasename,$db)) {}
                            $query = "INSERT INTO cust_loadout_profile (cust_loadout_id, unique_id) VALUES ('$cust_loadout_id','$unique_id')";
                            $result = mysql_query($query);
                            print "<div>Updated Loadout</div>";
                            $mysqli = null;
                            $stmt = null;
                <form action="" method="POST">
                    <select name="custloadoutid" class="input_text">
                        <option value="2">Admin Loadout</option>
                        <option value="3"> Donator Loadout</option>
                XenForo_Session::startPublicSession(new Zend_Controller_Request_Http);
                $visitor = XenForo_Visitor::getInstance();
                echo '<input type="hidden" name="_xfToken" value="'; echo $visitor['csrf_token_page']; echo '"/>'; echo '
                    <input type="hidden" class="input_text" value="'; echo $visitor['customFields']['dayzuniqueid']; ?>" name="uniqueid">
                    <input type="submit" class="button" name="action" value="Update">
    Two things...
    • How am I able to make it so the query will check to see if there's an existing unique_id and if so update the column of that specific row with the new cust_loadout_id
      • Then if there's no match on the unique_id just create a new row how I have it now
    • Next I'd like some proper result messages,(IE no custom field defined to supply a value would cause it to fail) I know the way I have it, it'll just kick back that message no matter what.

    Hope that all made sense... I've seen a few ways of possibly doing it... run two queries, and some duplicate functions but I can't get the hang of anything past this point lol :).

    I do styles, not this crap!

    Any help would be appreciated.
  2. Chris D

    Chris D XenForo Developer Staff Member

    I do this, not styles crap! ;)

    1) Change your query to:

     $query "INSERT INTO cust_loadout_profile (cust_loadout_id, unique_id) VALUES ('$cust_loadout_id','$unique_id') ON DUPLICATE KEY UPDATE unique_id = VALUES(unique_id)";
    This is just a side note - try and reference bits of XenForo code in terms of its appearance. I'm just being pedantic, but this makes my eyes bleed less:

    $query "
        INSERT INTO cust_loadout_profile
            (cust_loadout_id, unique_id)
            unique_id = VALUES(unique_id)
    2) I read it a few times and I think I misunderstood (2am now so probably my fault. Could you explain again?
    Jake Bunce and Russ like this.
  3. Russ

    Russ Well-Known Member

    OMG Thank you so much Chris, I had the ON DUPLICATE KEY UPDATE but I think I had some of the syntax wrong.

    As for the second one I found a solution staring at it, I had a worry if someone didn't have a unique_id in their custom profile field it would still show Updated Loadout even though the database wouldn't update because unique_id wasn't filled.

    As for the spacing, well I'm speechless and dumb. (y)
  4. Russ

    Russ Well-Known Member

    Tried the new query and it appears not quite working, it'll still add a new row if you change the cust_loadout_id

    Basically the unique_id is staying the same(for each user) each time but the user is changing the cust_loadout_id
  5. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    Does the table have an index for unique_id? The index must be a unique index for that column.
    Russ likes this.
  6. Russ

    Russ Well-Known Member

    Speak english Jake ;p, not sure how to tell to be honest, I'm a nub:

  7. Chris D

    Chris D XenForo Developer Staff Member

    ****, I read your reply a few days ago and forgot to come back and carry on helping :(

    I think we basically need to set a primary key index on the unique_id field. On your screenshot, if you click on the little grey key icon for the "unique_id" row, that should set that as the primary key index.

    So, then we're saying "on duplicate key" (e.g. when you try to provide a duplicate unique_id) update the existing record rather than creating a new one.
    LPH, Jake Bunce and Russ like this.

Share This Page