如何对这些数据库行进行配对?

so, I have this database, I need to know how many profile.stateX-profile.licenseX have value and also to pair them. I am able to pull them in a array but I can't find the propper php code to get what I need.

我有这个数据库，我需要知道有多少个profile.statex配置文件。被许可的人有价值，也有配对的价值。我可以将它们拖放到一个数组中，但是我找不到propper php代码来获得我需要的东西。

Again, what I need to know is this:

我需要知道的是

1: how many profile.state1/2/3/4/5-profile.license1/2/3/4/5 pairs I have with value

1:有多少profile.state1/2/3/4/5-profile。我有1/2/3/4/5双有价值的

2:output the pairs

2:输出对

This is the array returned form query:

这是数组返回的表单查询:

<pre>

array(16) {
  [0]=>
  array(2) {
    [0]=>
    string(15) "profile.address"
    [1]=>
    string(10) "Bld. Indep"
  }
  [1]=>
  array(2) {
    [0]=>
    string(21) "profile.certification"
    [1]=>
    string(7) "cert112"
  }
  [2]=>
  array(2) {
    [0]=>
    string(16) "profile.license1"
    [1]=>
    string(5) "12345"
  }
  [3]=>
  array(2) {
    [0]=>
    string(16) "profile.license2"
    [1]=>
    string(0) ""
  }
  [4]=>
  array(2) {
    [0]=>
    string(16) "profile.license3"
    [1]=>
    string(0) ""
  }
  [5]=>
  array(2) {
    [0]=>
    string(16) "profile.license4"
    [1]=>
    string(0) ""
  }
  [6]=>
  array(2) {
    [0]=>
    string(16) "profile.license5"
    [1]=>
    string(0) ""
  }
  [7]=>
  array(2) {
    [0]=>
    string(21) "profile.licensenumber"
    [1]=>
    string(7) "lice112"
  }
  [8]=>
  array(2) {
    [0]=>
    string(14) "profile.school"
    [1]=>
    string(5) "nr, 2"
  }
  [9]=>
  array(2) {
    [0]=>
    string(14) "profile.state1"
    [1]=>
    string(4) "Ohio"
  }
  [10]=>
  array(2) {
    [0]=>
    string(14) "profile.state2"
    [1]=>
    string(0) ""
  }
  [11]=>
  array(2) {
    [0]=>
    string(14) "profile.state3"
    [1]=>
    string(0) ""
  }
  [12]=>
  array(2) {
    [0]=>
    string(14) "profile.state4"
    [1]=>
    string(0) ""
  }
  [13]=>
  array(2) {
    [0]=>
    string(14) "profile.state5"
    [1]=>
    string(0) ""
  }
  [14]=>
  array(2) {
    [0]=>
    string(18) "profile.user_state"
    [1]=>
    string(8) "Roumania"
  }
  [15]=>
  array(2) {
    [0]=>
    string(11) "profile.zip"
    [1]=>
    string(3) "123"
  }
}

</pre>

I hope this makes sense.

我希望这说得通。

1 个解决方案

#1

Assuming the database is MySQL:

假设数据库为MySQL:

SELECT t1.profile_key, t1.profile_value, t2.profile_key, t2.profile_value
FROM profile AS t1
JOIN profile AS t2 ON t2.profile_key = CONCAT('profile.license', SUBSTR(t1.profile_key, 14))
WHERE t1.profile_key LIKE 'profile.state%' AND t1.profile_value != ''
AND t2.profile_key LIKE 'profile.license%' AND t2.profile_value != ''

SUBSTR(t1.profile_key, 14) gets the number after profile.state in profile_key column. Then we use CONCAT() to append that to profile.license to get the profile_key for the paired row.

SUBSTR(t1。profile_key, 14)获取配置文件后的编号。州profile_key列。然后我们使用CONCAT()将其附加到概要文件中。获取成对行的profile_key的许可。

#1