MySQL "UPDATE" - adding a new value to a single column

MasterACE14 · October 20, 2007

Afternoon Everyone,

I am trying to simply "UPDATE" a column in my database, and what I want to do is, add a new value, to a single column, without removing the current value, so I want to have each value separated by a comma ( , ).

Here's what I got:

<?php
// check if it is a weapon, armor, or vehicle
if($item_type == 'weapon') { $type = 'weapons'; }
elseif($item_type == 'armor') { $type = 'armors'; }
elseif($item_type == 'vehicle') { $type = 'vehicles'; }
  
  // run the queries for you - give you your weapon
  $sqlll = "UPDATE `cf_users` SET '$type' = '$type','$item_id' WHERE `id`='" . $player_accountid . "' LIMIT 1";
  $rsss = mysql_query($sqlll) or die('Query:<br />' . $sqlll . '<br /><br />Error:<br />' . mysql_error());

and I am getting this error:

Query:
UPDATE `cf_users` SET 'weapons' = 'weapons','2' WHERE `id`='1' LIMIT 1

Error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''weapons' = 'weapons','2' WHERE `id`='1' LIMIT 1' at line 1

Regards ACE

teng84 · October 20, 2007

try

UPDATE `cf_users` SET 'weapons' = 'weapons'+',2' WHERE `id`='1' LIMIT 1

teng84 · October 20, 2007

UPDATE `cf_users` SET 'weapons' = (select concat(weapons,',2' ) from `cf_users`WHERE `id`='1')'WHERE `id`='1' LIMIT 1

i dont know if theres still better way

Ninjakreborn · October 20, 2007

You need to be careful using + instead you want to do something like tihs to guarantee no issues arise.

Also your code is scary

<?php
<?php
// check if it is a weapon, armor, or vehicle
if($item_type == 'weapon') { 
  $type = 'weapons'; 
}elseif ($item_type == 'armor') { 
  $type = 'armors'; 
}elseif ($item_type == 'vehicle') { 
  $type = 'vehicles'; 
}
 
 // run the queries for you - give you your weapon
 $type = $type . ',' . $item_id;
 $update = "UPDATE `cf_users` SET '$type' = '$type' WHERE `id`='" . $player_accountid . "' LIMIT 1";

 $query = mysql_query($sqlll) or die('Query:<br />' . $sqlll . '<br /><br />Error:<br />' . mysql_error());
?>

MasterACE14 · October 20, 2007

I've tried your codes, but I keep getting MySQL errors :-\

Ninjakreborn · October 20, 2007

Post the entire code from your original page. From top to bottom.

teng84 · October 20, 2007

the error? hhmmmm heres the error

$type = $type . ',' . $item_id;

$update = "UPDATE `cf_users` SET '$type' = '$type'

Ninjakreborn · October 20, 2007

Ah, there you go. Nice catch.

MasterACE14 · October 20, 2007

still getting an error, and its not even echoing the query anymore :-\

Query:

Error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''weapons,2' = 'weapons,2' WHERE `id`='1' LIMIT 1' at line 1

Ninjakreborn · October 20, 2007

Post your current code (all of it) from top to bottom.

MasterACE14 · October 20, 2007

<?php // armory

/* includes */
include_once '/home/ace/public_html/conflictingforces/functions.php';
include_once '/home/ace/public_html/conflictingforces/weapons.php';
include_once '/home/ace/public_html/conflictingforces/armors.php';
include_once '/home/ace/public_html/conflictingforces/vehicles.php';

player_session();

/**********************/
// Player Select Variables //
/**********************/

// User Information
$player_accountid = player_table("id");
$player_username = player_table("username");

// money
$player_money = player_table("money");

// Inventory
$player_weaponid = player_table("weaponid");
$player_armorid = player_table("armorid");
$player_vehicleid = player_table("vehicleid");
$player_weapons = player_table("weapons");
$player_armors = player_table("armors");
$player_vehicles = player_table("vehicles");

// Equipped Items

// Names
$equippedweaponname = select_array($weapons,$player_weaponid,"weapon");
$equippedarmorname = select_array($armors,$player_armorid,"armor");
$equippedvehiclename = select_array($vehicles,$player_vehicleid,"vehicle");

// ID
$equippedweaponid = select_array($weapons,$player_weaponid,"id");
$equippedarmorid = select_array($armors,$player_armorid,"id");
$equippedvehicleid = select_array($vehicles,$player_vehicleid,"id");


// Purchase Weapon $_POST's
$item_bought = $_POST["buy"];
$item_price = $_POST["itemprice"];
$item_type = $_POST["itemtype"];
$item_id = $_POST["itemid"];

// connect to the database
$rs = mysql_connect( "localhost", "******", "*****" );
$rs = mysql_select_db( "ace_cf", $rs);

?>

<div id = "base">
<center><b><u>Armory</u></b><br></center><br>

<!-- Left Column -->
<div id = "leftcolumn">

<div id = "inventory_items">

<?php

if($player_money < $item_price) 
{ die("<b>You do not have enough money!</b>"); }

else {

  // current money - weapon price = your new money
  $new_money = $player_money - $item_price;
    
  // run the queries for you - insert your new amount of money
  $sqll = "UPDATE `cf_users` SET `money` = '$new_money' WHERE `id`='" . $player_accountid . "' LIMIT 1";
  $rss = mysql_query($sqll) or die('Query:<br />' . $sqll . '<br /><br />Error:<br />' . mysql_error());
  
// check if it is a weapon, armor, or vehicle
if($item_type == 'weapon') { 
   $type = 'weapons'; 
}elseif ($item_type == 'armor') { 
   $type = 'armors'; 
}elseif ($item_type == 'vehicle') { 
   $type = 'vehicles'; 
}
  
  // run the queries for you - give you your weapon
  $type = $type . ',' . $item_id;
  $update = "UPDATE `cf_users` SET '$type' = '$type' WHERE `id`='" . $player_accountid . "' LIMIT 1";

  $query = mysql_query($update) or die('Query:<br />' . $sqlll . '<br /><br />Error:<br />' . mysql_error());

?>

	<?php /* Grab weapon Array and Display it */

foreach ($weapons as $key => $val)
{	
if($item_bought == $val[$item_id]){
		echo '<table class="fix"><tr>';
		if($item_bought == "weapons"){
			$x= 'weapon';
		}
		elseif($item_bought == "armors"){
			$x= 'armor';
		}
		elseif($item_bought == "vehicles"){
			$x= 'vehicle';
		}
		echo '<td colspan="2">' . $item_type[$key][$x]. '</td></tr>';
		echo '<tr><td>id</td><td>' . $item_type[$key]['id'] . '</td></tr>';
		if($item_bought == "weapons"){
			$y= 'damage';
		}
		elseif($item_bought == "armors"){
			$y= 'defence';
		}
		elseif($item_bought == "vehicles"){
			$y= 'power';
		}
		echo '<tr><td>' . $x . '</td><td>' . $item_type[$key][$x] . '</td></tr>';
		echo '<tr><td>price</td><td>' . $item_type[$key]['price'] . '</td></tr>';
		echo '<tr><td>' . $y . '</td><td>' . $item_type[$key][$y] . '</td></tr>';
		echo '<tr><td>type</td><td>' . $item_type[$key]['type'] . '</td></tr>';
		echo '<tr><td>rarity</td><td>' . $item_type[$key]['rarity'] . '</td></tr>';
		echo '<tr><td>description</td><td>' . $item_type[$key]['description'] . '</td></tr>';
		echo '<tr><td>options</td><td>' . $item_type[$key]['options'] . '</td></tr>';
		echo '<td colspan="2"><b> Purchase Successful! </b></td></tr>';
		echo '</table><br />';
}

    }

	?>


	<?php /* Grab weapon Array and Display it */

foreach ($armors as $key => $val)
{	
if($item_bought == $val[$item_id]){
		echo '<table class="fix"><tr>';
		if($item_bought == "weapons"){
			$x= 'weapon';
		}
		elseif($item_bought == "armors"){
			$x= 'armor';
		}
		elseif($item_bought == "vehicles"){
			$x= 'vehicle';
		}
		echo '<td colspan="2">' . $item_type[$key][$x]. '</td></tr>';
		echo '<tr><td>id</td><td>' . $item_type[$key]['id'] . '</td></tr>';
		if($item_bought == "weapons"){
			$y= 'damage';
		}
		elseif($item_bought == "armors"){
			$y= 'defence';
		}
		elseif($item_bought == "vehicles"){
			$y= 'power';
		}
		echo '<tr><td>' . $x . '</td><td>' . $item_type[$key][$x] . '</td></tr>';
		echo '<tr><td>price</td><td>' . $item_type[$key]['price'] . '</td></tr>';
		echo '<tr><td>' . $y . '</td><td>' . $item_type[$key][$y] . '</td></tr>';
		echo '<tr><td>type</td><td>' . $item_type[$key]['type'] . '</td></tr>';
		echo '<tr><td>rarity</td><td>' . $item_type[$key]['rarity'] . '</td></tr>';
		echo '<tr><td>description</td><td>' . $item_type[$key]['description'] . '</td></tr>';
		echo '<tr><td>options</td><td>' . $item_type[$key]['options'] . '</td></tr>';
		echo '<td colspan="2"><b> Purchase Successful! </b></td></tr>';
		echo '</table><br />';
}

    }

	?>

	<?php /* Grab weapon Array and Display it */

foreach ($vehicles as $key => $val)
{	
if($item_bought == $val[$item_id]){
		echo '<table class="fix"><tr>';
		if($item_bought == "weapons"){
			$x= 'weapon';
		}
		elseif($item_bought == "armors"){
			$x= 'armor';
		}
		elseif($item_bought == "vehicles"){
			$x= 'vehicle';
		}
		echo '<td colspan="2">' . $item_type[$key][$x]. '</td></tr>';
		echo '<tr><td>id</td><td>' . $item_type[$key]['id'] . '</td></tr>';
		if($item_bought == "weapons"){
			$y= 'damage';
		}
		elseif($item_bought == "armors"){
			$y= 'defence';
		}
		elseif($item_bought == "vehicles"){
			$y= 'power';
		}
		echo '<tr><td>' . $x . '</td><td>' . $item_type[$key][$x] . '</td></tr>';
		echo '<tr><td>price</td><td>' . $item_type[$key]['price'] . '</td></tr>';
		echo '<tr><td>' . $y . '</td><td>' . $item_type[$key][$y] . '</td></tr>';
		echo '<tr><td>type</td><td>' . $item_type[$key]['type'] . '</td></tr>';
		echo '<tr><td>rarity</td><td>' . $item_type[$key]['rarity'] . '</td></tr>';
		echo '<tr><td>description</td><td>' . $item_type[$key]['description'] . '</td></tr>';
		echo '<tr><td>options</td><td>' . $item_type[$key]['options'] . '</td></tr>';
		echo '<td colspan="2"><b> Purchase Successful! </b></td></tr>';
		echo '</table><br />';
}

    }

} // end the 'else' statement
	?>

</div>

</div>


<!-- Right Column -->
<div id = "rightcolumn">



</div>

teng84 · October 20, 2007

<?php
// check if it is a weapon, armor, or vehicle
if($item_type == 'weapon') { 
   $type = 'weapons'; 
}elseif ($item_type == 'armor') { 
   $type = 'armors'; 
}elseif ($item_type == 'vehicle') { 
   $type = 'vehicles'; 
}
  
  // run the queries for you - give you your weapon
  $typex = $type . ',' . $item_id;
  $update = "UPDATE `cf_users` SET `$type` = '$typex' WHERE `id`='" . $player_accountid . "' LIMIT 1";

  $query = mysql_query($sqlll) or die('Query:<br />' . $sqlll . '<br /><br />Error:<br />' . mysql_error());
?>

try should work

MasterACE14 · October 20, 2007

Im not getting any errors now, but it doesn't change the database at all :-\

MasterACE14 · October 20, 2007

bump

MasterACE14 · October 21, 2007

bump

MasterACE14 · October 23, 2007

anyone?

Sign In

MySQL "UPDATE" - adding a new value to a single column

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information