I have a variable of type array and I want to store it as it is in database(mysql) and when I apply tinyblob that I usually use in java for display it return abnormal output.
So what is the correct datatype for the array in php.
You define the column in the
CREATE TABLE statement as
SET('USA', 'France', 'Germany', ...) NOT NULL (not null optional), and use queries like:
INSERT INTO myTable SET countries="USA,Germany";
So you define a
id_country|name ----------+----------- 1 |USA 2 |Germany 3 |France
and define a pivot table that links your main object (f.ex. "users") to the countries, thus esabilishing a many-to-many relationship.
users_countries table would link
users table to
countries table in this way:
id_user|id_country ------------------ 1 |1 1 |2 2 |1
In this example, user 1 is linked to countries with id 1 and 2 (USA and Germany), user 2 is linked to USA only, and so on
This involves using the server-side language (in your case PHP) to concat the values with some separator (such as comma
, or pipe
$countries = array("USA", "Russia", "Iran"); $countriesString = implode (",", $countries); // $countriesString = "Usa,Russia,Iran" $query = "INSERT INTO mytable SET countries = '" . $countriesString . "'";
(Note: the string value is not escaped for example purpose, but it is not a good practice)
When you retrieve the value from the db, you retrive the string you put into, and obtain again an array with
$array = explode(",", $retrievedValueFromDb), providing the same separator.
You can rely also on the
find_in_set(needle, field) function, that returns an integer greater than zero if the string is present in a comma-separated value field.
So if you use approach 1) and have a string field like
"USA,Germany", you can obtain the columns in which
USA is present by using:
SELECT * FROM myTable WHERE find_in_set('USA', countries) <> 0
Approach 1) works if you have few fixed values, also very easy on queries. Downside is that you are limited to 64 values, that the values must be fixed, and the cannot contain a comma (,) as its used as a separator.
Approach 2) is the most standardized and correct, it's also normalized and query-efficient
Approach 3) is easy but requires extra processing and is not so easy for SQL
As stated elsewhere, there is no "array" datatype in the dbms, you have to use some workaround.