1

I have seen that in SQL Server there is a function called ROW_NUMBER, in my case I am joining 3 tables and each one has different names for the ID column (this I am doing for the jsTree plugin)

I have this code:

$sql= "(SELECT
                c1.idcarrera AS id,
                c1.nombre as text,
                null as parent_id
        FROM siiupv.carrera AS c1)";
$sql.=" UNION ALL ";
$sql.= " (SELECT
                pe.idplan_estudios AS id,
                pe.clave AS text,
                pe.idcarrera as parent_id
        FROM siiupv.plan_estudios AS pe)";
$sql.=" UNION ALL ";
$sql.= " (SELECT idcarga AS id, c2.clave AS text, idplan_estudios as parent_id FROM siiupv.carga AS c2)";
$qResults = $pdo->prepare($sql);
$qResults->execute();
$count = $qResults->rowCount();
while($m = $qResults->fetch(PDO::FETCH_ASSOC)){ 
        echo '<tr><td>&nbsp;'.$m["id"].'</td><td>'.$m["text"].'</td><td>'.$m["parent_id"].'</td></tr>';
}

It produces this output:

+------+------------------+
| id   | text | parent_id |
+------+------------------+
|    1 |  A1  | NULL      |
|    2 |  A2  | NULL      |
|    3 |  A3  | NULL      |
|    1 |  B1  |   1       |
|    2 |  B2  |   2       |
|    3 |  B3  |   1       |
|    4 |  B4  |   2       |
|    5 |  B5  |   5       |
|    1 |  C1  |   4       | Note! (Child of "B4")
|    2 |  C2  |   4       | Note! (Child of "B4")
+------+------------------+

But I want to get:

+------+------------------+
| id   | text | parent_id |
+------+------------------+
|    1 |  A1  | NULL      |
|    2 |  A2  | NULL      |
|    3 |  A3  | NULL      |
|    4 |  B1  |   1       |
|    5 |  B2  |   2       |
|    6 |  B3  |   1       |
|    7 |  B4  |   2       |
|    8 |  B5  |   5       |
|    9 |  C1  |   7       | Note! (Child of "B4")
|   10 |  C2  |   7       | Note! (Child of "B4")
+------+------------------+

Note! > The IDs are traversed and therefore must have value 7

I would like get help at least of auto increment IDs

GMB
  • 216,147
  • 25
  • 84
  • 135
Manuel Ruiz
  • 29
  • 1
  • 9
  • 1
    You could just use a incrementing variable: `$row = 0;` before the loop then `++$row` for the current row number. Note however, that there is no correlation between this id and any database row, it's just a dumb counter. – Alex Howansky Jan 09 '19 at 20:40

2 Answers2

1

One way to add a row number to each record of the resultset is to use a session variable :

SET @row_number = 0;

SELECT 
    (@row_number:=@row_number + 1) id, -- t.id
    t.text,
    t.parent_id
FROM (
    SELECT c1.idcarrera AS id, c1.nombre as text, null as parent_id FROM siiupv.carrera AS c1 ORDER BY c1.id
    UNION ALL SELECT pe.idplan_estudios AS id, pe.clave AS text, pe.idcarrera as parent_id FROM siiupv.plan_estudios AS pe ORDER BY c1.id
    UNION ALL SELECT idcarga AS id, c2.clave AS text, idplan_estudios as parent_id FROM siiupv.carga AS c2 ORDER BY c1.id
) t
GMB
  • 216,147
  • 25
  • 84
  • 135
  • it runs fine in phpMyAdmin but not in PHP but I had to enclose each SELECT between () because it marked mysql error # 1221 because of UNION & ORDER BY. your idea is coming but I think it's the session that I can not interpret with php – Manuel Ruiz Jan 10 '19 at 00:48
  • Ok, I added a variable at the end with an Alias, now everything is fine. https://stackoverflow.com/questions/41354929/how-to-print-row-number-in-mysql-in-php-application ".... ) t, (SELECT @row_number := 0) r " – Manuel Ruiz Jan 10 '19 at 01:02
  • for the column "parent_id" as I do to add the total spaces where the value is NULL, I mean, I want to change 4 to 7 (without those that are NULL stop being NULL), any ideas? – Manuel Ruiz Jan 10 '19 at 01:23
0

Not sure if I really got the issue but you could generate a new unique id by prefixing it or something.

SELECT CONCAT("A-", idcarga) AS id, c2.clave AS text, CONCAT("A-", idplan_estudios) as parent_id

sui
  • 751
  • 6
  • 10