[FIXED] How to pair those database rows?

Issue

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.

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

2:output the pairs

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.

Solution

Assuming the database is 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.

Answered By - Barmar