PHP Null Variables & MYSQL

[Y2Neildotcom]

Hi all,

 

I have a web page that allows the user to insert rows into a MySQL database. THe page is for my football team and it inserts the name of the player and also a time for if they were substituted during the game.

 

The MySQL database has two fields for these times, both are set to BIGINT(20) and both have a default of NULL. These are called 'on' and 'off'.

 

My PHP form contains a dropdown box for the time's on and off that lists the numbers 0-90 with values of 0-90 as well as a blank (-) with a value of blank ("").

 

<label><select name="off[1]" id="off1">
     <option value="" selected="selected">-</option>
      <option value="1">01</option>
      <option value="2">02</option>
      <option value="3">03</option>
      <option value="4">04</option>
      <option value="5">05</option>

 

Because you could have up to 16 rows inserting at once (but no fewer than 11), the insert code from the form to the database is as such:

 

$players = (isset($_POST['players'])) ? $_POST['players'] : null;
$players = (is_array($players)) ? $players : array();

$r = 0; // Count records inserted

// For each player submitted on form...
foreach($players as $p) {
// Get player input data
$player_id = (isset($_POST['player_id'][$p])) ? $_POST['player_id'][$p] : ''; // Player ID
        $on = (isset($_POST['on'][$p])) ? (int) $_POST['on'][$p] : NULL; // Player On time
$off = (isset($_POST['off'][$p])) ? (int) $_POST['off'][$p] : NULL; // Player Off time
$player_pos_type = (isset($_POST['player_pos_type'][$p])) ? $_POST['player_pos_type'][$p] : ''; // Player Pos Type
$sub_type_id = (isset($_POST['sub_type_id'][$p])) ? $_POST['sub_type_id'][$p] : ''; // Player Sub Type ID
$sql="INSERT INTO `apps` (`player_id`, `match_id`, `team_id`, `on`, `off`, `player_pos_id`, `sub_type_id`) 
VALUES
('$player_id','$_POST[match_id]','$_POST[team_id]',$on,$off,'$player_pos_type','$sub_type_id')";
if (!empty($player_pos_type)) {
	if (!mysql_query($sql,$con)) {
		die('Error: ' . mysql_error());
	} else {
		$r++;
	}
}
}

 

When I submit a form the data that enters my database when I select a blank on or off time always goes in as a value of '0' when it is expected to be 'NULL'.

 

I've tried several variations on the tenary operator, changing the condition is false from 'NULL' to null, to "NULL" etc but everytime it enters as '0'.

 

I even went to debug by setting $off = 'NULL' and ignoring whatever was entered via the form but this still entered every row as '0'.

 

This leads me to beleive I have an issue with the data being submitted. Do I need to change the PHP form itself?

 

I've been told that when a PHP variable with a value of null is inserted into a string, it just becomes an empty string, but I'm not sure on how to fix this.

 

Can anyone offer a solution to this?

 

Many thanks.

[dragon_sa]

check your mysql table if that column is set as an integer it will set it to 0, also try echoing your results to the page and see what the values are it is inserting to the database.

[Y2Neildotcom]

Thanks,

 

My table is set thusly:

 

Field Type                 Null Default

    on bigint(20) Yes NULL

            off bigint(20) Yes NULL

 

Echoing the results for on and off came up:

 

on: ''

off: ''

 

So it is going through as blank, so are you suggesting that because the column is an integer it'll always insert blank as '0' even though the defaul is null?

[dragon_sa]

correct it expects a number

 

bigint is your problem and thus changing your value from NULL to 0 as it expects a number, change type to char or varchar if you want to use NULL

[dragon_sa]

The other thing you can do is leave the column as bigint and check for the 0 when you bring the data back out, if ($on==0) { $on=""; }

or what ever you want it to be.

[Y2Neildotcom]

Thank you.

 

I've changed the table so now the columns are varchar's but they're still entering as '0'! This is incredibly frustrating.

 

Am now going to add the if statement, assuming I can figure out where I need to put it.

[dragon_sa]

You would use the if statement after you query the database to read it back onto a page

so when you get the database variable for example $on=$result['on']; after this you would put if ($on==0) {$on=""; }

[Y2Neildotcom]

Thank you so much for all your help! I now have working code!

 

You don't know how much of a relief this is! THANK YOU!

[dragon_sa]

another thing you can try is putting quotes around the NULL value

 

$on = (isset($_POST['on'][$p])) ? (int) $_POST['on'][$p] : "NULL"; // Player On time

[Pikachu2000]

another thing you can try is putting quotes around the NULL value

 

$on = (isset($_POST['on'][$p])) ? (int) $_POST['on'][$p] : "NULL"; // Player On time

If the isset() returns FALSE, that would make the $on a string with the value of "NULL", not a NULL value. There's a big difference between NULL and "NULL"

 

The problem is that you're using isset() to check against a field that has <option value=""> selected. The isset() will return TRUE and set the value to an empty string, which is what the value actually is.