Getting Default value for Field using PHP?

[Quarterbore]

I am stumped and none of my reference manuals are providing much help. I have a script were the default value is used to define how many of an item a user gets when they register on vBulletin.

Previously, I have set this value to a value of "3" with instructions for people that if they wish to change this they can do so by going into phpmyAdmin and updating the default value.

Well, I need to fix this and I have the php code written to UPDATE the default value BUT I can not figure out how to query the field to get the default value in PHP so that I can show this value to the Site Admin so that they can make an informed decision about making an update.

Specifically, my field is in the vBulletin user table and the field is defined as follows when I add the field:

$vbulletin->db->query_write("ALTER TABLE " . TABLE_PREFIX . "user
ADD `vbclassified_credits` int(5) NOT NULL default '3'");

With the new changes, a site admin can change "default" so I need a way to get this value and allow the Site Admin to see what they have it set to.

I also need this to work on MYSQL 4 or newer as I can not predict what version MYSQL my users may have.

Thanks for any help or advise even if it is a reference to a page or book with general advise.

----------------------------------------
Edit - I am pretty confident the solution has to do with the MYSQL DEFAULT() function, but I am still struggling to figure out how to make it work inside of a PHP Script using the unique vBulletin code to access the database.

http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_default

[Opserty]

Set the column default of something like -1. Then when you fetch the data if the data == -1 then you set the admin default e.g. 3.

[Quarterbore]

Sorry, you lost me :confused:

My issue is that I want to create an AdminCP area that shows somethng like this:

Current Default Credits = 3
Update Default Credits to ____ [Submit]

The problem is, I am struggling to get the "3"

I am pretty sure my solution will be on this page:

http://www.php.net/manual/en/function.mysql-fetch-field.php

Here is what I am fighting with at this moment:


$credits_values = $vbulletin->db->query("
SELECT vbclassified_credits
FROM " . TABLE_PREFIX . "user
");

$meta = mysql_fetch_field($credits_values);
$default = $meta->def;
echo 'Default Credits = ' . $default;
echo '<br />';
print_r($meta);
exit();


This shows me the following:

Default Credits =
stdClass Object ( [name] => vbclassified_credits [table] => dev_user [def] => [max_length] => 4 [not_null] => 1 [primary_key] => 0 [multiple_key] => 0 [unique_key] => 0 [numeric] => 1 [blob] => 0 [type] => int [unsigned] => 0 [zerofill] => 0 )

The values in this database for this field (from PHPMyAdmin) is this:

Field: vbclassified_credits
Type: INT
Length/Values: 5
Collation:
Attributes:
Null: not null
Default: 3
Extra:
Comments:

--------------------------------------------------------------------------------------------------
I changed my code to use the sample from the PHP.net site so it looks like this:


$credits_values = $vbulletin->db->query("
SELECT vbclassified_credits
FROM " . TABLE_PREFIX . "user
");

// This checks if field exists!
$meta2 = mysql_field_seek($credits_values, 0);
echo '<br />';
echo 'vBClassified Credits Exists = ' . $meta2;
echo '<br />';



$i = 0;
while ($i < mysql_num_fields($credits_values)) {
echo "Information for column $i:<br />\n";
$meta = mysql_fetch_field($credits_values, $i);
if (!$meta) {
echo "No information available<br />\n";
}
echo "<pre>
blob: $meta->blob
max_length: $meta->max_length
multiple_key: $meta->multiple_key
name: $meta->name
not_null: $meta->not_null
numeric: $meta->numeric
primary_key: $meta->primary_key
table: $meta->table
type: $meta->type
default: $meta->def
unique_key: $meta->unique_key
unsigned: $meta->unsigned
zerofill: $meta->zerofill
</pre>";
$i++;
}

exit();


Now my Output looks like this:


vBClassified Credits Exists = 1
Information for column 0:

blob: 0
max_length: 4
multiple_key: 0
name: vbclassified_credits
not_null: 1
numeric: 1
primary_key: 0
table: dev_user
type: int
default:
unique_key: 0
unsigned: 0
zerofill: 0


So close but so confused...?

Oh yea, I confirmed I have the correct table and it sees the database and field as I edited the null value and refreshed my test script that the non tull value changed from 0 to 1. I tried changing my default value and it still shows as NULL?

Some notes as I try to figure this out:

$max_length is the length of the longest value for that field in the returned dataset, NOT the maximum length of data that column is designed to hold.

[sockwater]

Use "DESCRIBE TABLE " . TABLE_PREFIX . "user" to get information about the table and its columns from that you can see what the default value for your column is.

[Quarterbore]

I am going to share this as it may help someone else. I never did figure out why the default didn't work per the second reference but I went back to the first one and finally worked out the code to do this.

The following code works!


$user_values = $vbulletin->db->query_first("
SELECT DEFAULT(vbclassified_credits) AS defaultcredits
FROM " . TABLE_PREFIX . "user
");

echo 'Credits = ' . $user_values[defaultcredits];
exit();


I can finally finish my addition :D

Edit: sockwater - I cross posted with you as I started to post my fix and was testing before I hit reply so I had the fix before you posted. I am not sure if there is an easier way to get this but this certainly works. I do appreciate the advise and help just the same as this had me stumped for quite a few hours today!

[sockwater]

Glad you got it working. :)

[Marco van Herwaarden]

The default value for a column as set in MySQL will probably not work in many PHP scripts as these tend to provide a value for each column inserted, even if it is just a blank string (ie. ''), so the default is never used as a value was submitted.

But why not just create a vB Setting to let the admin define the default and use this when inserting the rows?

[Quarterbore]

The default value for a column as set in MySQL will probably not work in many PHP scripts as these tend to provide a value for each column inserted, even if it is just a blank string (ie. ''), so the default is never used as a value was submitted.

But why not just create a vB Setting to let the admin define the default and use this when inserting the rows?


Well, I don't have a hook off "Add Member" to give credits so the way I have been giving users credits is through the default MYSQL value for the credits field. It works fine like that but some sites want to change the default value and some people don't have access or skill to use myPHPAdmin to
update the default value.

This may well be a limited use sniped of code, but I tested it on a couple of my sites on different servers and it seems to be working so I am going to try this route for now. This should be a value that is only changed say once at most.

I use PHP to update the field attributes and that works fine, the issue was getting what the current setting was and the code above gives me that.