What's the best way to do this?

[Kryptix]

I have a table with the following structure:

 

OWNER - ID - SLOT - AMOUNT

 

OWNER is the user ID who owns the item. ID is the ID of the item. SLOT is the position it's in within the inventory and AMOUNT is obviously the quantity.

 

I need a script to find out what's the highest SLOT and then add a new entry into the next slot.

 

How would you do this?

 

I was going to query the database and ORDER BY `SLOT` DESC with only one result, and then do a new entry with $query['slot'] + 1 as the slot.

 

Is there an easier, more efficient way to do this?

[premiso]

Depending on the MySQL version, this should work, if I read it correct:

 

UPDATE table_name SET slot = slot+1 WHERE OWNDERID = (SELECT OWNERID FROM table_name ORDER BY SLOT DESC LIMIT 1) LIMIT 1

 

Un-tested and it does depend on Mysql 4+ I believe for the subquery.

[Kryptix]

Thanks. Here's the current code that I've just written. It's working fine but is there anyway to make it more efficient?

 

	} else if ($page == "test") {
	$check = $db->query("SELECT `amount` FROM `rscd_bank` WHERE `owner` = '" . $pun_user['id'] . "' AND `id` = '1295'") or die("error");
	$check = $db->fetch_assoc($check);
	$check = $check['amount'];

	if ($check == 0) {
		$slot = $db->query("SELECT `slot` FROM `rscd_bank` WHERE `owner` = '" . $pun_user['id'] . "' ORDER BY `slot` DESC LIMIT 1") or die("error");
		$slot = $db->fetch_assoc($slot);
		$slot = $slot['slot'] + 1;

		$insert = $db->query("INSERT INTO `rscd_bank` (`owner` , `id` , `amount` , `slot`) VALUES ('" . $pun_user['id'] . "', '1295', '1', '" . $slot . "');");	

		$slot++;
	} else {
		$check++;

		$update = $db->query("UPDATE `rscd_bank` SET `amount` = '" . $check . "' WHERE `owner` = '" . $pun_user['id'] . "' AND `id` = '1295'") or die("error");
	}
	?>
<div class='menu'>
	<div class='m_title'>Subscribe</div>
	<div class='m_content jtext'>
			Thank you for your purchase! <?php if ($check == 0) { echo "A new RSCEmulation Subscription Card has been added into slot " . $slot . " of your bank account."; } else { echo "Another RSCEmulation Subscription Card has been added to your bank account."; } ?>
	</div>
</div>

[premiso]

I think the way you have it will be the best / only way as far as I know how to do it. I tried doing an insert statement with a select from the current table. It will only work if you are selecting from a different table, unfortunately.

[Kryptix]

Alright, thanks for your help (again) mate.