PHP failing to insert values in Mysql ENUM columns

[dbarasuk]

Hi
I recently had a PHP script to insert my daily expenses in Mysql.

I retrieved with PHP into Internet Explorer values from a single- ENUM type column table to be able to insert values in my database using directly a form embedded in the browser (Just to avoid all the typing from the Mysql client).

The ENUM values were 4 in number and were about the payment means ('Bancontact', 'Proton', 'Cash', 'Other'). I then added one more value ('Visa') to the list. While I could insert any value successfully with the four previous ones, I always have been getting an error for any attempt to insert a payment means using the newly added 'visa' value.

I echoed the values being sent to the database and there is no error.
How can I solve this mystery? Is this due to the fact of having altered the table by adding a new value? All the values being inserted are legal values since coming directly from a table to the form and there is no option to choose a value that has not been defined to be part of the ENUM column.

For the moderator, please don't move this thread since the problem is occuring at the php level not at the database one.(since by doing that operation using the Mysql client, the task succeeds.)

Thanks for your advice.

[coopster]

How can I solve this mystery?

You could start by posting the specific error you are getting in regards to this ...

I always have been getting an error for any attempt to insert a payment means using the newly added 'visa' value.

[dbarasuk]

I just had avoided to post the 100 lines of code just to make the life of the reader more easy.

To answer your question, the error I am getting is the one forseen in the code, i.e, in case the INSERT Query fails. It's one line as follows: "Cannot insert the expense values, please try again" . THis is on line 87. and I don't see why the query fails since sending the query to the server using the mysql client, the query succeeds with no problem.

The full source code below:

$query = "INSERT INTO expenses VALUES (null, '$date', '$amount', '$payment_reason', '$payment_means', '$location')"; 
$result = mysqli_query($connection_id_1, $query); 
if($result) { 
 echo "Expense values successfully inserted, thank you.".'<br>'; 
 header('Location: [localhost...]  
 exit(); 
} else { 
 echo"<p>Cannot insert the expense values, please try again.</p>".'<br>'; 
 // this is outputting bool(false) in case of data entry failure. 
 var_dump($result).'<br>'; 
}

[edited by: coopster at 9:59 pm (utc) on Mar. 20, 2008]
[edit reason] Removed unnecessary code [/edit]

[coopster]

hehe, sorry for the misunderstanding. I meant the mysql error. You are checking the $result and then dumping out your own message as well as dumping the $result variable, but you should also dump the specific MySQL error that is being thrown. See mysqli_errno [php.net] and mysqli_error [php.net] for details and add a couple statements dumping them out to see what you get.

[dbarasuk]

Sorry,
for my first assertion I said that inserting values using the Mysql client succeeds, but it's not true for the said problematic "Visa" value.

I tried using the query on Mysql client like this:

INSERT INTO expenses VALUES (null, "2008-03-21", 40, "Food", "Visa", "Supermarket");

That "expenses" table has columns (expense_id, date, amount, expense_reason, payment_means, location).

I got an error as : "ERROR 1265 (01000): Data truncated for column 'payment_means' at row 1 ".

This means that PHP would also fail since it's the same query. The column that causes the query to fail is the one where the value "Visa" would be inserted. This is really beyond of what I can understand since if I run the same query replacing the value "visa" with the other optional values "Bancontact", "Cash" ... the query succeeds with no problem. (The data is not truncated in any other value)

Hope this is a bit clear and points an expert to identify the right solution.

I am waiting for a possible solution

[dbarasuk]

I added one line code in the source like:
printf("Error Code: %d '<br>'", mysqli_errno($connection_id_1));

and I got "Error Code: 1265"

How to solve it?

thks.

[coopster]

I always dump the error number and the error message. Then it's off to the documentation [dev.mysql.com] if I am uncertain of the message. What is your table structure? If you do a "DESCRIBE myTable"; query you can see the structure of the table.

[coopster]

One more thing. Every time I dump the errors, I always dump the query statement too. You can often see your issue right away when you do this. I'm guessing perhaps you have an empty value for that variable.

[dbarasuk]

I identified the error, but i still have some minor problem.

In fact I was retrieving ENUM values from a table name payment_means that has a single colum (means) ENUM ("Bancontact", "Proton", "Visa", "Cash", "Cash And Proton", "Library Card", "Other") and I was submitting one of those values into a table (expenses) in its column (payment_means) which has the same name as the first table (don't mind) but defined as ENUM with different values.

NOw I have changed the definition of the first table (payment_means), in its only one column (means) to look like the second table. The result of the ENUM columns of both tables is like the one mentionned above.

However, if I select for instance "Visa Card" to be submitted and echo the value being submitted (shown in the above code), I expect to get "Visa Card" BUT GET INSTEAD only "Visa". Definitely, the value was truncated which corresponds to the error "Data truncated I am getting"

I tried to use the htmlentities() function to keep the empty space in the value but could not fix that problem.

WHich function should help me the keep the entirety of the "Visa Card" value instead of it being truncated?

I hope that function could solve the problem

Thank you for your solution