PHP Process MySQL reponse Array into Array of unique records

Question

In PHP i run this query and get array with 2 records having 2 keys each now from this array i need to extract unique records is there some more professional way to build this query and then extract unique records from array than what i did below?

P.S. position1 and positions2 tables are identical. so position1 one of them can be removed.

$get_positions = "SELECT positions1.pos_id1,
                         positions1.pos_name1,
                         positions2.pos_id2,
                         positions2.pos_name2
                      FROM employees
                          LEFT JOIN positions1 ON positions1.pos_id1 = employees.position1
                          LEFT JOIN positions2 ON positions2.pos_id2 = employees.position2
                              WHERE employees.status IN (1,2)
                                  GROUP BY pos_name2
                                      ORDER BY pos_id2 ASC";
$positions_res = $sql->RunSQL($get_positions, "select");

Dump of $positions_res

array(2) {
  [0]=>
  array(8) {
    [0]=>
    string(1) "4"
    ["pos_id1"]=>
    string(1) "4"
    [1]=>
    string(27) "Driver"
    ["pos_name1"]=>
    string(27) "Driver"
    [2]=>
    string(1) "2"
    ["pos_id2"]=>
    string(1) "2"
    [3]=>
    string(9) "Cook"
    ["pos_name2"]=>
    string(9) "Cook"
  }
  [1]=>
  array(8) {
    [0]=>
    string(2) "19"
    ["pos_id1"]=>
    string(2) "19"
    [1]=>
    string(23) "Guard"
    ["pos_name1"]=>
    string(23) "Guard"
    [2]=>
    string(2) "19"
    ["pos_id2"]=>
    string(2) "19"
    [3]=>
    string(23) "Guard"
    ["pos_name2"]=>
    string(23) "Guard"
  }
}

code continues

$pos_list = array();
$i = 0;
for ($n = 0; $n < count($positions_res); $n++) {

    if ($positions_res[$n]["pos_name1"] == $positions_res[$n]["pos_name2"]) {
        $pos_list[$i]["id"] = $positions_res[$n]["pos_id1"];
        $pos_list[$i]["name"] = $positions_res[$n]["pos_name1"];
        $i++;
    } else {
        $pos_list[$i]["id"] = $positions_res[$n]["pos_id1"];
        $pos_list[$i]["name"] = $positions_res[$n]["pos_name1"];
        $i++;
        $pos_list[$i]["id"] = $positions_res[$n]["pos_id2"];
        $pos_list[$i]["name"] = $positions_res[$n]["pos_name2"];
        $i++;
    }
}

var_dump($pos_list);

dump of final result

array(3) {
  [0]=>
  array(2) {
    ["id"]=>
    string(1) "4"
    ["name"]=>
    string(27) "Driver"
  }
  [1]=>
  array(2) {
    ["id"]=>
    string(1) "2"
    ["name"]=>
    string(9) "Cook"
  }
  [2]=>
  array(2) {
    ["id"]=>
    string(2) "19"
    ["name"]=>
    string(23) "Guard"
  }
}

EDIT:

I need get list of unique positions (pos_id, pos_name) of all positions that assigned to employees position1 and position2 with (for employees with status 1 and 2).

For example employee

John has position1=Driver, position2=Driver, employee Sam has Poition1=Driver, Position2=Guard employee Mike has Position1=Cook, Position2=Driver so in this case i need to select

List:

4,  Driver
19, Guard
2,  Cook

So, what you are getting is all the positions that are held by any employee who has a status of 1 or 2? If the same employee holds 2 positions, then you want both of them in your results. Your server-side logic is hinting to me that you could probably do the same reduction in your SQL. In your PS you are saying that position1 and position2 are identical? Do they hold the same data or just the same structure? Also, what is the DBO you are using. I am not familiar with one that has a RunSQL method. — ghbarratt, Jul 28 '12 at 1:43
By DBO you mean Storage Engine? I use default MyISAM. Yes both tables positions1 and positions1 and data in them identical. I need get list of unique positions (pos_id, pos_name) of all positions that assigned to employees position1 and position2 with (for employees with status 1 and 2). — John Smith, Jul 30 '12 at 21:34
I wanted to know what the name of the database object or library you are using is. Common ones are PDO, mysqli_, MDB2, etc. But I am unfamiliar with one with a RunSQL method. — ghbarratt, Jul 30 '12 at 21:37
Ok i use mysql library (not PDO or MySQLi) and RunSQL() is just name of function i created - it access database with mysql_query(); — John Smith, Jul 30 '12 at 21:44

Michael Berkowski · Answer 1 · 2012-07-28 01:37:59Z

Look into the source for $sql->RunSQL(), and I'll bet it calls mysql_fetch_array() without the second parameter. The default behavior of that function is to return both associative and numeric keys unless the constant MYSQL_NUM or MYSQL_ASSOC is passed as the second parameter.

Replace that with a call to mysql_fetch_assoc() or mysql_fetch_array($the_result_resource_variable, MYSQL_ASSOC) to return only associative keys back instead of both associative and numeric keys.

I dont see how associative and numeric array solves my problem, by default i get multidimensional array that i printed above. — John Smith, Jul 30 '12 at 21:49

ghbarratt · Answer 2 · 2012-07-30 22:17:55Z

Assuming that positions1 and positions2 are copies of one positions table, I would change the query to look like this:

SELECT 
    p.pos_id,
    p.pos_name,
    CAST(GROUP_CONCAT(e.name,'(',e.id,')' SEPARATOR ', ') AS CHAR) AS employees
FROM
    positions p
    JOIN employees e ON e.position1 = p.pos_id OR e.position2 = p.pos_id
WHERE e.status IN (1,2)
GROUP BY p.pos_id
ORDER BY p.pos_id

Then if the employees table looked like this:

mysql> select * from employees;
+----+---------+--------+-----------+-----------+
| id | name    | status | position1 | position2 |
+----+---------+--------+-----------+-----------+
|  1 | Glen    |      1 |         1 |         5 |
|  2 | John    |      2 |         7 |         6 |
|  3 | Michael |      1 |         2 |         1 |
|  4 | David   |      0 |         3 |         4 |
|  5 | Albert  |      2 |         2 |         0 |
|  6 | Edward  |      1 |         0 |         3 |
|  7 | Ben     |      1 |         1 |         3 |
+----+---------+--------+-----------+-----------+

and the positions table looked like this:

mysql> select * from positions;
+--------+--------------+
| pos_id | pos_name     |
+--------+--------------+
|      1 | Janitor      |
|      2 | Guard        |
|      3 | Shift Leader |
|      4 | Driver       |
|      5 | Cook         |
|      6 | Manager      |
|      7 | Owner        |
+--------+--------------+

the result of this query would look like this:

+--------+--------------+-----------------------------+
| pos_id | pos_name     | employees                   |
+--------+--------------+-----------------------------+
|      1 | Janitor      | Michael(3), Ben(7), Glen(1) |
|      2 | Guard        | Michael(3), Albert(5)       |
|      3 | Shift Leader | Ben(7), Edward(6)           |
|      5 | Cook         | Glen(1)                     |
|      6 | Manager      | John(2)                     |
|      7 | Owner        | John(2)                     |
+--------+--------------+-----------------------------+

asked	1 year ago
viewed	138 times
active	1 year ago

current community

more stack exchange communities

PHP Process MySQL reponse Array into Array of unique records

2 Answers

Your Answer

Not the answer you're looking for? Browse other questions tagged php mysql or ask your own question.

Community Bulletin

Hot Network Questions

current community

more stack exchange communities

PHP Process MySQL reponse Array into Array of unique records

2 Answers

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged php mysql or ask your own question.

Community Bulletin

Related

Hot Network Questions