CODEWITHSUNDEEP
phpmysqlwordpress
Shmack
Shmack

Reputation: 2321

MySQL Text datatype acting like Int datatype

So I have an interesting problem. I created a table whose column is clearly defined as a text datatype (The name column). When I insert a string into it, it returns a 0. When I simply input an integer, it will store that integer. So long story short it is treating that column as an integer instead of a text field. This is the create statement (I am using php and inserting into a wordpress database).

global $wpdb;
$charset_collate = $wpdb->get_charset_collate();
require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
$sql = "CREATE TABLE IF NOT EXISTS $table_name (
    oid INT NOT NULL AUTO_INCREMENT,
    user_id INT NOT NULL,
    name TEXT NOT NULL,
    date DATE NOT NULL,
    total FLOAT NOT NULL,
    email TEXT NOT NULL,
    phone TEXT NOT NULL,
    PRIMARY KEY (oid)
    ) $charset_collate;";
    dbDelta( $sql );

and this is the insert statement

global $wpdb;
$data = array('user_id' => $_POST['uid'], 'name' => 965, 'date' => $_POST['CurrentDay'], 'total' => $_POST['Total'], 'email' => $_POST['Email'], 'phone' => $_POST['Phone']);
    $format = array('%s','%d');
    $wpdb->insert($table, $data, $format);
    $my_id = $wpdb->insert_id;

You can ignore the 965 for the 'name' column (which returns 965 for that column and row)... I tried it as a string... 'name' => 'Shane',... Every time I put a string, it returns a 0 in that column.

Upvotes: 0

Views: 331

Answers (1)

Akina
Akina

Reputation: 42622

wpdb->insert method accepts 3 parameters. 2nd parameter specifies the values with must be inserted, 3rd parameter positionally specified the formats of the values to be inserted.

In your code the format parameter contains 2 values - i.e. it specifies the format for first 2 parameters. All another parameters are treated as "the type is not specified", so they are transferred as string type ones (the most common type which may transfer all another types) unless otherwise specified in wpdb->$field_types.

1st parameter is 'user_id' => $_POST['uid'], and the format specification for it is '%s' which means "string".

2nd parameter is 'name' => 965, and the format specification for it is '%d' which means "integer". So inserting a string type value into this parameter causes the convertion to the integer data type, and the result is zero.

In your case I do not see the necessity to specify the data types at all. Simply skip 3rd (optional) parameter.

Upvotes: 1

Related Questions