Php array to mysql field

Adding an array to a MySQL column in SELECT

I was wondering if, when you are retrieving the user data from a MySQL table, you can add an extra column with the karma array and order by that extra_column:

 $query = 'SELECT user.*, > WHERE user.id IN '.(implode(',',$some_array['user_id'])).' ORDER BY extra_column'; 

Do you want that extra column to be only in your result of the SELECT? The values in the array are not in the database?

I would have to agree with Eric. Maybe there is another solution where you can use a table to store some data or do some sort of calculation after the sql results are returned. Are you trying to associate the array with the result, or individual array items to row numbers?

actually, just the individual array to the row numbers. and @True Soft, these karma calculations change according to the user who is viewing them and when he is viewing them, thats why I don’t see why I should store it.

3 Answers 3

SELECT id, ELT(FIELD(id, 4, 3, 5, 1), 129, 87, 13, 20) AS karma FROM ( SELECT 4 AS id UNION ALL SELECT 3 AS id UNION ALL SELECT 5 AS id UNION ALL SELECT 1 AS id ) q WHERE id IN (4, 3, 5, 1) ORDER BY karma 

it worked! but somehow after I removed the (SELECT. UNION ALL) stuff. before it was showing all the users in the user_table.

Not really (at least not without going through some really nasty dynamic sql generation).

You would have to have the karma in a column on the user table (or some other table that you could join to the user table). It seems like that should be stored in the database anyways though right?

If the values in the array have to be provided by the script (i.e., don’t exist in the database in any way you could join them, which is the ideal case), then I see two possibilities:

1. Create a temporary table for your karma, insert all karma values, and then join your query with your karma array
2. Use a case statement in your query. This is an untenable solution if the number of users is large.

The code for generating the case statement could look something like this:

$query = 'SELECT *, ' . GenerateCaseStatement($some_array) . ' AS extra_column FROM user WHERE user.id in (' . implode(',', $some_array['user_id']) . ' ORDER BY extra_column'; function GenerateCaseStatement($some_array) < $str = 'CASE id '; for (i=0; i$str .= ' ELSE NULL END'; return $str; > 

Источник

Save array in mysql database

I want to save extra information before sending the total order to Paypal. For each item I have created a single column in my MySQL database where I want to store it. Now I was thinking to save it as an array which I can read later for creating a PHP page. Extra fields are taken from input form fields. By using an array can I be sure not to mixup information?

storing an array in a single mysql field defeats the purpose of a database. What does your current code look like?

MySQL 5.7.8 and higher has now native JSON support, meaning you can put a JSON string directly in a table field in the database.

7 Answers 7

You can store the array using serialize / unserialize . With that solution they cannot easily be used from other programming languages, so you may consider using json_encode / json_decode instead (which gives you a widely supported format). Avoid using implode / explode for this since you’ll probably end up with bugs or security flaws.

Note that this makes your table non-normalized, which may be a bad idea since you cannot easily query the data. Therefore consider this carefully before going forward. May you need to query the data for statistics or otherwise? Are there other reasons to normalize the data?

Also, don’t save the raw $_POST array. Someone can easily make their own web form and post data to your site, thereby sending a really large form which takes up lots of space. Save those fields you want and make sure to validate the data before saving it (so you won’t get invalid values).

Источник

Array into mysql database field [closed]

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the help center.

The items in the array were posted in the form of ingredients[] . How could i concatenate all the values and put them into one field of a row in a database An example of a mysql code would be perfect. Thanks

$ingredient_method = $_POST['conValue']; $ingredient_type = $_POST['OneWordDescription']; $ingredient_element = $_POST['ingredients']; $sql="INSERT INTO tbl_Recipe (recipe_id, ingredient_method, ingredient_type, ingredient_element) VALUES ('',$_POST[conValue]','$_POST[OneWordDescription]','$_POST[ingredients]')"; if (!mysql_query($sql)) < die('Error: ' . mysql_error()); >echo "1 record added"; 

This is currently to vague to answer.. They way you describe it you want to just concatenate all the values and put them into one field of a row in a database.

Being that you are seeking code, it all depends.. Like I said, this is vague.. Which way are you interacting with MySQL? do you really just want a string that says «Salt Sugar Water»?

yes for the time being, I can code them into a separate table at a later date. I am performing a full text search on it you see.

2 Answers 2

1. Use PHP’s serialize function to convert the array into a string before putting it into the database, and then unserialize when you pull the data out.
2. Use PHP’s implode to turn the data into a string and then explode when you pull the data out.
3. Create another table in MySQL, insert each value into the database as a separate row, and then use something like INNER JOIN or LEFT JOIN in your query pull the data out.

Now that you’ve posted code, I have to recommend you do NOT use mysql_* functions. They are deprecated. Use either mysqli or PDO. Also, ALWAYS use either mysqli_real_escape_string or PDO::quote on values you receive from the user to prevent SQL injection attacks.

Here is an example using serialize and PDO:

//get the array $ingredient_method = $_POST['conValue']; $ingredient_type = $_POST['OneWordDescription']; $ingredient_elements = $_POST['ingredients']; if(!is_array($ingredient_elements)) < //do some error handling >//connect to the database $dsn = 'mysql:dbname=testdb;host=127.0.0.1'; $user = 'dbuser'; $pass = 'dbpass'; try < $pdo = new PDO($dsn, $user, $pass); >catch (PDOException $e) < echo 'Connection failed: ' . $e->getMessage(); > //serialize the string $elements_str= serialize($ingredient_elements); //prepare sql $sql = 'INSERT INTO tbl_Recipe '. '(recipe_id, ingredient_method, ingredient_type, ingredient_element) '. 'VALUES ( '. '"",'. //recipe_id $pdo->quote($ingredient_method).','. $pdo->quote($ingredient_type ).','. $pdo->quote($elements_str). ')'; // insert into database $con->query($sql); 

Источник