Some MySQL help

[Russ]

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">
            <?php
            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>
                </select>
            <?php 
            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">
            </form>
          </div>

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.

 

[Chris D]

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)
	VALUES
		('$cust_loadout_id','$unique_id')
	ON DUPLICATE KEY UPDATE
		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?

 

[Russ]

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)
VALUES
('$cust_loadout_id','$unique_id')
ON DUPLICATE KEY UPDATE
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?

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.

 

[Russ]

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

 

[Jake Bunce]

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

 

[Russ]

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

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

 

[Chris D]

****, 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.