MySQL Is Array in Multiple Columns

Where IN multiple column in mysql from php array

$post = json_decode($HTTP_RAW_POST_DATA);
$phoneNumbers = $post->phones;
$emails = $post->emails;

$ors = [];
foreach ($phoneNumbers as $i => $number) {
    $ors[] = "(phone_number = :phoneNumber{$i})";
    $params[":phoneNumber{$i}"] = $number;
}

foreach ($emails as $i => $email) {
    $ors[] = "(email = :email{$i})";
    $params[":email{$i}"] = $email;
}

$query = $pdo->prepare("SELECT phone_number, email, id FROM users WHERE ".implode(' OR ', $ors));
print_r($query);
$query->execute($params);

How to get multidimensional array grouped by multiple columns in PHP from single MySQL table?

First, I would recommend you stick with your code solution. Run a simple SQL query, and process the results, collating them into the nested array structure you want. You already have that coded, it's easier to debug and easier to change in the future if you need to change it.

I've implemented something like you describe, to create a nested structure in an SQL query. It was pretty hard, and the SQL query was complex enough that it will be a maintenance problem in the future if we ever need to modify the contents of the nested structure.

The solution I used in the SQL query was to use multiple levels of derived table subqueries, and generate aggregate JSON results at each level using JSON functions. This requires using MySQL 5.7 or later, because these JSON functions are not implemented in earlier versions of MySQL.

Demo test data:

create table if not exists mytable ( id int primary key, survey_type varchar(20), guid char(36), guid_type varchar(20), table_id  int, created_at datetime );
insert into mytable values 
(2 ,'TYPE 1','6C7251E3-2151-4754-A413-51899FAAF6C2','question','2','2022-03-20 16:14:09'), 
(3 ,'TYPE 1','EF5AFA93-C74D-4920-A13A-17A9B43239CD','question','3','2022-03-20 16:14:09'), 
(4 ,'TYPE 1','5C059148-94BE-4225-B5C2-551A81B65F16','question','4','2022-03-20 16:14:09'), 
(5 ,'TYPE 1','356B8A5C-1072-47A5-A508-D9BDCBA92CCC','answer','5','2022-03-20 16:14:09'), 
(6 ,'TYPE 1','E0CE4C26-7ABD-4162-9C8C-B4DD540AE268','answer','6','2022-03-20 16:14:09'), 
(7 ,'TYPE 1','BFBC50FC-892D-43E9-A235-D76E0D0BEF29','answer','7','2022-03-20 16:14:09'), 
(8 ,'TYPE 2','B9DCC5C1-CBFB-4589-98EF-4524F3958968','survey','8','2022-03-20 16:14:09'), 
(9 ,'TYPE 2','C98FBFF9-6FE3-414E-BB14-08EDC8281E66','survey','9','2022-03-20 16:14:09'), 
(10 ,'TYPE 2','8A780B6E-EAE0-47D6-9D05-F52B795AE617','question','10','2022-03-20 16:14:09'), 
(11 ,'TYPE 2','E3818D30-BB69-4F03-B56D-B31691F8007E','question','11','2022-03-20 16:14:09'), 
(12 ,'TYPE 2','24C81BEF-BFCE-4964-AB01-F3579251313D','answer','12','2022-03-20 16:14:09'), 
(13 ,'TYPE 3','59381701-AFBC-48F8-AECE-DB3702EE2B15','answer','13','2022-03-20 16:14:09'), 
(14 ,'TYPE 3','7F4AC694-74DC-4BEA-ACFB-D8F070769FEE','answer','14','2022-03-20 16:14:09'), 
(15 ,'TYPE 3','B5C405B9-BA7E-471A-87DD-B69D9757276F','survey','15','2022-03-20 16:14:09');

Example query:

select json_pretty(json_objectagg(survey_type, g)) as j
from (
  select survey_type, json_objectagg(guid_type, t) as g
  from (
    select survey_type, guid_type, json_objectagg(table_id, guid) as t
    from mytable
    group by survey_type, guid_type
  ) as t
  group by survey_type
) as g;

Output:

{
  "TYPE 1": {
    "answer": {
      "5": "356B8A5C-1072-47A5-A508-D9BDCBA92CCC",
      "6": "E0CE4C26-7ABD-4162-9C8C-B4DD540AE268",
      "7": "BFBC50FC-892D-43E9-A235-D76E0D0BEF29"
    },
    "question": {
      "2": "6C7251E3-2151-4754-A413-51899FAAF6C2",
      "3": "EF5AFA93-C74D-4920-A13A-17A9B43239CD",
      "4": "5C059148-94BE-4225-B5C2-551A81B65F16"
    }
  },
  "TYPE 2": {
    "answer": {
      "12": "24C81BEF-BFCE-4964-AB01-F3579251313D"
    },
    "survey": {
      "8": "B9DCC5C1-CBFB-4589-98EF-4524F3958968",
      "9": "C98FBFF9-6FE3-414E-BB14-08EDC8281E66"
    },
    "question": {
      "10": "8A780B6E-EAE0-47D6-9D05-F52B795AE617",
      "11": "E3818D30-BB69-4F03-B56D-B31691F8007E"
    }
  },
  "TYPE 3": {
    "answer": {
      "13": "59381701-AFBC-48F8-AECE-DB3702EE2B15",
      "14": "7F4AC694-74DC-4BEA-ACFB-D8F070769FEE"
    },
    "survey": {
      "15": "B5C405B9-BA7E-471A-87DD-B69D9757276F"
    }
  }
}

Fetch that result into your client app. It's a single long string, so I hope your data is not longer than MySQL's max_allowed_packet length.

Convert the string into your desired nested array with json_decode().

mysql is array in multiple columns

Use the FIND_IN_SET function:

WHERE (   FIND_IN_SET(events.name, mysql_real_escape_string($string)) > 0
       OR FIND_IN_SET(events.cities, mysql_real_escape_string($string)) > 0
       OR FIND_IN_SET(events.counties, mysql_real_escape_string($string)) > 0)

MySQL select where two fields are not in array at the same time

Actually you can run NOT IN / IN mysql condition.

You can gather all available variants from array and run delete with pair not in condition.

Let's say we have black-xl and white-sm variants, so query will be:

delete from variants where (color, size) not in (('black', 'xl'), ('white', 'sm'));

example on fiddle with select (to know what's going to be deleted): http://sqlfiddle.com/#!9/d6623c/5

How storing array values in different columns of table using php

Here the Code after making it work properly

$month = array('red','green','red');
$values = array();
foreach($month as $dataset)
{
  $values[] = "'{$dataset}'";
}

$columns = implode(", ",array_keys($values));
$escaped_values = array_values($values);
$valu  = implode(", ", $escaped_values);

$sql = "INSERT INTO abc (col1,col2,col3) VALUES ($valu);";

Converting multiple columns from mysql recordset into Array

In the end I did it this way. It worked although it is more complicated than I expected. If someone has a better solution. Feel free to post it and I will tick your answer after testing.

 val rs1 = statement.executeQuery("" +
    "select BBK,AAA from OPER_MEMB_AAAD order by BBK")

while (rs1.next()){
  strBBKArrayRs1+=rs1.getString(1)
  strAAAArrayRs1+=rs1.getString(2)
}

val rs2 = statement.executeQuery("" +
  "select BBK,AAB from MIDD_MEMB_AAAJ order by BBK")

while (rs2.next()){
  strBBKArrayRs2+=rs2.getString(1)
  strAAAArrayRs2+=rs2.getString(2)
}

val strRs1Array=ArrayBuffer(strBBKArrayRs1,strAAAArrayRs1)
val strRs2Array=ArrayBuffer(strBBKArrayRs2,strAAAArrayRs2)

How to save multiple columns to bash arrays with 1 select in mysql

Assuming the objective is to read a grid of data into multiple arrays (with each column being loaded into a separate array) ...

For dynamically generating/populating arrays using bash namerefs take a look at this answer.

If the number of arrays (and their names) are predefined ...

First some data to simulate the OP's output:

$ cat mysql.out
somemail1@gmail.com 2 2z7bhxb55d3
somemail2@gmail.com 2 we3cq3micu9cn
somemail3@gmail.com 1 we3cq1dicu9cn

NOTE: The following code assumes the column data does not include white space, eg, the above file has 3 columns and not 1 column with embedded spaces; otherwise OP will need to insure the stream of input has a well-defined column delimiter that can be used by the while loop

One bash loop idea:

unset      data1 data2 data3
typeset -a data1 data2 data3

i=0
while read -r col1 col2 col3
do
    (( i++ ))
    data1[${i}]="${col1}"
    data2[${i}]="${col2}"
    data3[${i}]="${col3}"
done < mysql.out                                     # replace this with ...
# done < <(mysql ... SELECT data1,data2,data3 ...)   # this to have mysql results fed directly into 'while' loop

This gives us:

$ typeset -p data1 data2 data3
declare -a data1=([1]="somemail1@gmail.com" [2]="somemail2@gmail.com" [3]="somemail3@gmail.com")
declare -a data2=([1]="2" [2]="2" [3]="1")
declare -a data3=([1]="2z7bhxb55d3" [2]="we3cq3micu9cn" [3]="we3cq1dicu9cn")

If you don't mind the indices starting @ 0 ...

unset      data1 data2 data3
typeset -a data1 data2 data3

while read -r col1 col2 col3
do
    data1+=("${col1}")
    data2+=("${col2}")
    data3+=("${col3}")
done < mysql.out