2

Im working with a mysql database via php.

I have a table with some values that are = NULL

I select these values in php:

$opponentInv = db_execute("Select * from inventoryon where playerid = ".$defendid.";");
$opponentInv = mysql_fetch_assoc($opponentInv);

Then i insert the values into a another table:

db_execute("INSERT INTO `inventoryCombat` (`attackid` ,`defendid` ,`money` ,`item1` ,`item2` ,`item3`, `item4` ,`item5` ,`item6`, `time`)VALUES ('".$attackid."', '".$defendid."', '".$opponentInv["money"]."', '".$opponentInv["item1"]."', '".$opponentInv["item2"]."', '".$opponentInv["item3"]."', '".$opponentInv["item4"]."', '".$opponentInv["item5"]."', '".$opponentInv["item6"]."', '".$time."');");

The issue is that when i insert the values into the second table, they are always coming out as 0. The values in the inventoryCombat table are 0 when they should be NULL (what they are in the inventoryon table). The table is set to accept NULL as values.

freshintake
  • 23
  • 2

4 Answers4

1

Firstly I'd strongly recommend that you use prepared statements instead of building a literal SQL string. Not only is this a better practice and helps you to write a more secure application, it also handles NULL values correctly without requiring any extra work.

If you want to continue using the method you are currently using then you will need to explicitly check for undefined values and insert the string 'NULL' into your SQL. In other words, you need to do this:

INSERT INTO inventoryCombat (item1) VALUES (NULL);

Instead of what you are currently doing:

INSERT INTO inventoryCombat (item1) VALUES ('');

If it still doesn't work, double-check that the field you are trying to insert NULL into is set to allow NULL values. You can use SHOW CREATE TABLE inventoryCombat to do this.

I'd also recommend normalizing your database. Having columns called item1, item2, item3 etc. is a sign of a bad design. Your current design will, for example, make it more complicated to perform what should be simple queries such as 'How many players possess item X?' or 'Add item X to player P unless she is already holding 6 items'.

Community
  • 1
  • 1
Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
0

There are 2 more solutions:- Even if it has accepted the values as 0 instead of NULL, this can be updated as NULL like this:- update set =NULL WHERE ;

Also you need to check if the column is set to be NULLABLE, then only above SQL will work, else it will again set the column with 0 value and will be of no use.

Further you can do the same via your SQL Manager interface. I am not sure about all but EMS postgresql manager works this with command CTRL+SHIFT+0 and few others like sql navicat for mysql and oracle use CTRL+0 [Please check your sql manager programs on your own]. Cheers!

Vandana
  • 37
  • 3
0

Values in array $opponentInv is may be set to 0.

Like $opponentInv["money"] = 0; that is why it is being saved as 0 in inventoryCombat table.

Try to set value of $opponentInv["money"] as NULL in your coding, instead if 0 or ''.

e.g 

$opponentInv["money"] = ($opponentInv["money"] == 0 || $opponentInv["money"] == '')?NULL:$opponentInv["money"];
Poonam Bhatt
  • 10,154
  • 16
  • 53
  • 72
0

This seems to be that you're storing string(varchar, text) data into number field whose default value is set to 0.

Aqeel Ahmad
  • 473
  • 8
  • 14
  • the values are int the in the inventoryon table, and they are also int in inventoryCombat, both have default set to null – freshintake Nov 22 '10 at 07:10
  • in this case 0 value can only means that the first table contains the data with 0 which is being populated and filled in associated array before the issue of next insert command. – Aqeel Ahmad Nov 22 '10 at 08:30