newbie question: mysql default field

doesnt appear to work :(

[benihana]

Hi
Ive just built my first db in mysql for a small site i need to get up and running asap. its pretty basic at the mo, with one table to hold sets of product details. I want to be able to have one image with each product. for simplicity's sake I have a field called 'image' which is VARCHAR(40) NOT NULL DEFAULT na.gif, I just want this to hold the file name for the image (as opposed to storing the image in the db). Not all products have images yet, so I set the default to na.gif - so if no image is defined it displays na.gif, which just says 'image not available'. seems ok in theory (to me:)), but just doent work. I have a set of forms for adding content - could it be that the form field for the image names is adding 'null' to the db if left blank?
hope this makes somesort of sense, as i said its my first go.....
cheers

[jatar_k]

when you describe the table does it give 'na.gif' for the default in the image col?

<added>could just be
VARCHAR(40) NOT NULL DEFAULT 'na.gif'

[benihana]

indeed it does. :)

<added> wouldnt 'na.gif' would give a broken link IF i did manage to get it to work though.? i.e. images/'na.gif'

[edited by: benihana at 10:08 pm (utc) on Jan. 21, 2003]

[jatar_k]

did you single quote it in the create table statement? Otherwise it may just look like a table.fieldname to mysql.

[benihana]

yeah i did single quote it, and it shows up fine in describe table.

am wondering if the subbmission form is actually taking the value from the images field and adding it irrespective of whether there is anything there, so in effect putting a null value in?

[jatar_k]

the problem must be in the insert then and you are probably in the neighbourhood.

I usually do something like

if (!isset($image) ĶĶ $image == "") {
$image = "na.gif";
}

nb- pipe chars must be replaced with real ones

[andreasfriedrich]

Any column not explicitly given a value is set to its default value. For example, if you specify a column list that doesn't name all the columns in the table, unnamed columns are set to their default values. [...] Our view is that checking of fields content should be done in the application and not in the database server.

[mysql.com...]

am wondering if the subbmission form is actually taking the value from the images field and adding it irrespective of whether there is anything there, so in effect putting a null value in?

Yes, thatīs exactly what you are doing. Only if you do not mention the column in your INSERT statement at all or if you are using the DEFAULT value will the db server use the default value.

As jatar_k pointed out in his last post and as mentioned in the above quote from the MySQL documentation you might want to check the value at the application level.

Andreas

[brotherhood of LAN]

You may also just want to have a default of "na" and add the .gif bit to your "front end" script. :)

[benihana]

thanks for your input everybody.

ive put the na image as the default value in the frontend scripts. its good to know whats actually going on - thanks for helping clear this up.

[hakre]

Yes, thatīs exactly what you are doing. Only if you do not mention the column in your INSERT statement at all or if you are using the DEFAULT value will the db server use the default value.

as i remember, you can even set it to NULL (not '') and mysql will then fix it to DEFAULT on the fly. even if it's not quite a good sql style.

nevertheless, why not perform a simple check if it's empty (=='') and then setting it to your default. this will save space in the db and you haven't got the default na.gif encoded in your db as default, but in your app. i think this is smoother.

[andreasfriedrich]

as i remember, you can even set it to NULL (not '') and mysql will then fix it to DEFAULT on the fly.

No hakre, MySQL will not do that. As explained in that quote from mysql.com the default value will be used only when the column name is not explicitly set or the DEFAULT keyword, which is available in versions 4+, is used.

You might confuse that with the fact that you may use NULL for a auto_increment column to increase the value by one.

Andreas