Default Field Value to MySQL Database

I'm using a HTML form that captures user input for several fields, some of which can be left blank by the user, one particular field is called 'pasref'. What I would like to be able to do, if at all possible, is if this field is left blank by the user, for the default value to become the text 'Not Allocated' and for this to be saved as part of the record to a mySQL database.

I just admit I'm fairly new to this type of programming and I'm not sure whether this can be done within the mySQL database or whether it needs to be done as part of the php script that saves each record. I just wondered whether it would be at all possible please that someone could show me what I need to do?

I've included my PHP script below if it helps.

<?php
require("phpfile.php");
// Gets data from URL parameters
$userid = $_GET['userid'];
$locationid = $_GET['locationid'];
$pasref = $_GET['pasref'];
$additionalcomments = $_GET['additionalcomments'];

// Opens a connection to a MySQL server
$connection = mysql_connect ("hostname", $username, $password);
if (!$connection) {
  die('Not connected : ' . mysql_error());
}

// Set the active MySQL database
$db_selected = mysql_select_db($database, $connection);
if (!$db_selected) {
  die ('Can\'t use db : ' . mysql_error());
}

// Insert new row with user data
$query = sprintf("INSERT INTO finds " .
         " (userid, locationid, pasref, additionalcomments ) " .
     开发者_开发问答    " VALUES ('%s', '%s', '%s', '%s');",
         mysql_real_escape_string($userid),
         mysql_real_escape_string($locationid),
         mysql_real_escape_string($pasref),
         mysql_real_escape_string($additionalcomments));

$result = mysql_query($query);

if (!$result) {
  die('Invalid query: ' . mysql_error());
}

?>

You can set the default value on the column of mysql. You need to execute a query something like this:

ALTER TABLE finds MODIFY pasref varchar(100) default 'Not Allocated';

Change varchar(100) to whatever length it should be for your field.

Alternatively you could just set it in php:

if( empty($_GET['pasref']) ) {
    $pasref = 'Not Allocated';
}
else {
    $pasref = $_GET['pasref'];
}

Or finally you could put it as a default value in your form. Though the user would need to clear it if they want something else:

<input type="text" name="pasref" value="Not Allocated" />

Finally just to note in your PHP you need to escape your inputs to the database with mysql_real_escape_string or use PDO with placeholders. As is you have a SQL injection vulnerability.

It's also better to use POST. Change the form method to POST in your HTML and reference $_POST instead of $_GET

This can be solved by changing the column properties in the mysql database and setting the pasref column to not null and setting a default value.

Easily done if you can access the db using phpmyadmin

ALTER TABLE finds ALTER pasref SET DEFAULT = 'Not Allocated'

So when a new row is inserted with no value for column_name, Not Allocated with be used. It can also be done pretty easily with an interface like phpMyAdmin or MySQL Workbench if you have access to those tools.

Now all you have to do is check if pasref is empty in your PHP code and make sure you don't insert anything (an empty string "" is something) when it is the case.

you can just change the line

$pasref = $_GET['pasref'];

to

$pasref = $_GET['pasref'] == '' ? 'Not Allocated' : $_GET['pasref'];

Use this:

$query = " UPDATE $tabla SET fecha_ultimo_cambio=DEFAULT WHERE id in ($id) ";