Using VARCHAR on a number field

[MargateSteve]

Are there any complications using VARCHAR on a field that will only contain numbers?

 

I have a situation where I have a field which needs to accept actual numbers, 0 OR be left blank. I have tried all sorts of methods via php to leave the field blank if the form field is empty but it always enters a 0 in the database. I have come to the conclusion that on an update, there is no way to leave an INT field empty after an update.

 

I would prefer to leave the field as INT as that is the data it will hold, but as long as changing the field to VARCHAR would not be detrimental, I would happily go down that route.

 

Thanks

Steve

[requinix]

INT versus VARCHAR (ie, number versus string):

- Use numbers if the data is a number. An actual number. Just consisting of digits isn't enough (but does get you far).

- Use strings for just about everything else.

 

Default value versus empty versus NULL (ie, there's always a value versus the field can be left literally empty versus using a NULL:

- Use default values when you need a value all the time and simply might not have collected it. As in "if I don't have a number then I'll assume it's X".

- Use an empty value... Off the top of my head I don't have any examples of when you should do this.

- Use NULL when you want to indicate that the data is missing. Not there. You don't have it. NULL is not a value - it is an absence of a value.

 

Based on that and what I can guess about your purpose, I say a NULLable INT field. Stick a number in there when you have a number, or leave it as NULL when you are missing a number.

 

Beyond that general advice and assumptions with it, what is this field for?

[MargateSteve]

The field is to part the score of a soccer match.

 

Before the match is played the field is empty. Once the match is played it will contain a value, which could be zero. There may also be an occasion where someone updates the wrong match so it would need to be re-edited and the db field set to Null again.

 

The field is currently INT set to default to Null but everything I have tried (I am using my phone to write this but can post examples later) along the lines of 'if that form field is empty make db field empty' always puts in a zero.

[requinix]

Then what's your code?

[MargateSteve]

These are a couple of the ways I have tried to assign $hgoals to update a field to be blank if the form field is empty. Both of these and all of the variants I have included in the comments cause 0 to be put in the field.

 

 

if (!empty($_POST['home_goals']))
{
  $hgoals = $_POST['home_goals'];
}
else
{
          $hgoals = NULL;// or $hgoals = 'NULL' or $hgoals = ''
}

 

$hgoals = ($_POST['home_goals'] == 0 ? 'NULL' : $_POST['home_goals']);
//Also tried variants of == '0', == '', ? NULL, ? ''

 

The only other way I can think of is to run a conditional statement in php that only includes that field in the update script if the form field is not empty. However, as this will occur quite often in a soccer statistics site, I would rather not have to do that.

 

Steve

[MargateSteve]

Thinking about it, my alternative solution would not work to NULL a field if data has been put in there in error.

 

Steve

[PFMaBiSmAd]

Before the match is played the field is empty.

 

^^^ You shouldn't have records in a table until the actual data exists and has been inserted. If someone did insert a record incorrectly (i.e. a score got entered for a match that hasn't been played yet), you would either delete the record entirely to remove it or if the actual data for the wrongly inserted record now exits, update the record to hold its actual value.

[MargateSteve]

Before the match is played the field is empty.

 

^^^ You shouldn't have records in a table until the actual data exists and has been inserted. If someone did insert a record incorrectly (i.e. a score got entered for a match that hasn't been played yet), you would either delete the record entirely to remove it or if the actual data for the wrongly inserted record now exits, update the record to hold its actual value.

The only problem with that is that the matches are all added at the start of the season show all of the matches to be played. The current version of one of the pages using this is http://www.margate-fc.com/content/1st_team/fixtures_results/fixturelist.php?season_id=105. It shows a list of a particular teams matches, regardless of whether they have been played or not.

 

They are then updated with the goals once the game is played. It would be a bad idea to give the people who update the site access to phpMyAdmin to rectify any mistakes like this so I need to find a way for them to blank that fields, through a simple edit screen, if they make a mistake.

 

Steve

[PFMaBiSmAd]

That's a bad design. Your table that defines the schedule should be different from the table recording the scores.

 

Anyway, you would need to test if the submitted form field is an empty string. You would then form an UPDATE query that sets the INT field to a sql NULL keyword. Your INT field needs to be defined as not not-null (i.e. uncheck the not-null option). You also cannot have any single-quotes around the NULL keyword inside the query statement (the query would fail with an error for an INT field, perhaps depending on your database strict mode setting) -

 

<?php
$hgoals = $_POST['home_goals'] == '' ? 'NULL' : intval($_POST['home_goals']);
$query = "UPDATE your_table SET your_column = $hgoals WHERE some_where_condition";

 

Edit: I just tested and if strict mode is off for your mysql server and your query has single-quotes around the numerical value - $query = "UPDATE your_table SET your_column = '$hgoals' WHERE some_where_condition";, then the update trying to set it to the a null will result in setting the field to a zero value.

[MargateSteve]

Edit: I just tested and if strict mode is off for your mysql server and your query has single-quotes around the numerical value - $query = "UPDATE your_table SET your_column = '$hgoals' WHERE some_where_condition";, then the update trying to set it to the a null will result in setting the field to a zero value.

 

That was the key, removing the single quotes worked perfectly.

 

With regard to the design, the reason all matches are in the same table was on advice of others to make it simple to pull out some of the required statistics such as %of games played against %of games not played as well as showing a particular team or competitions matches, whether played or not, in one list.

 

Presumably I could have unioned the two tables to query these?

 

The other thing that put me off doing that way was the fact that eventually there may be dozens of people updating at the same time (eventually the site will be giving live updates on 100+ games at a time) and coding something that will delete something out of one table and insert it into the other, without any risk is a bit daunting for me. Also, as well as played or not played, there is a third state for a match - in play. Would you recommend a third table for that? So when a match starts it moves from the fixtures table to the live table and then when it ends it moves from the live table to the played table?

 

Steve

[PFMaBiSmAd]

You wouldn't move anything between tables when its status changes.

 

You need one table the holds the definition of your schedule - id, date/time, location, home team id, away team id, status (none, in progress, final, delayed, canceled, forfeit), and anything else you can think of that is unique and specific to any one match. The id would identify each match in the schedule. Other than adding new entries to this table for each new season, you would only need to update the status and perhaps some of the other fields (i.e. the date, time, or location if a match got moved to a different date, time, or location.)

 

You would have a second table to hold scores. It would have columns for the match_id, home team score, away team score, and probably columns to hold the initial creation date/time, last update date/time, and id of the last person that created/updated it. You would insert a row in this table for each match that is complete. If you are fixing errors in the values, my previous discussion above would apply. To get the results for any match, list of matches, or range of matches, you would do a LEFT JOIN of the first table with the second table.

[MargateSteve]

I never actually though of holding just the scores in a separate table. I will have a look into that.

 

Thanks

Steve