My Procedure: (It's a scheme)
delimiter //
CREATE PROCEDURE get_random_data()
BEGIN
Declare Count_a INT;
SELECT Floor(Count(`id_x`) * Rand()) INTO Count_a From `x_data` Where `ok_w` = 1 And `active` = 2 And `time` >= Unix_timestamp();
# Now We select Some Rows
SELECT `id_x` From `x_data` Where `spec` = 1 Limit Count_a,3;
# And if Count of this select is small than < 3
IF(row_count() < 3) then
SELECT `id_anonsu` From `x_data` Where `spec` = 0 Limit Count_a,2;
END IF ;
END//
delimiter ;
// Target
How to get the results From BOTH queries? // One from query before if statement
// Second from query in statement, if first query count is smaller than 3
Ok, I found the answer.
The original code:
IF(row_count() < 3) then
SELECT `id_anonsu` From `x_data` Where `spec` = 0 Limit Count_a,2;
END IF ;
Was replaced for:
IF(FOUND_ROWS() < 3) THEN SELECT `id_anonsu` From `x_data` Where `spec` = 0 Limit Count_a,2;
END IF ;
Better Version:
Work Good with query (Who use LIMIT) And we need know number of returned rows by (Query)in Store Procedure
Version with show count
DROP PROCEDURE IF EXISTS test;
Delimiter //
CREATE PROCEDURE test(OUT w INT)
BEGIN
Declare Counter_a INT DEFAULT 0;
Set @id_counter = 0;
SELECT Floor(Count(`id_x`) * Rand()) INTO Counter_a From `x_data` Where `ok_w` = 1 And `active` = 2 And `time` >= Unix_timestamp();
SELECT @id_counter := @id_counter + 1, `id_x` From `x_data` Where `spec` = 1 Limit Counter_a,3;
IF @id_counter < 3 THEN
Set Counter_a = Counter_a + 1;
SELECT `id_x` From `x_data` Limit Counter_a,2;
END IF;
Set w = @id_counter;
END //
Delimiter ;
Output like this:
mysql> Call test(@z); Select @z;
+--------------------------------+-----------+
| @id_counter := @id_counter + 1 | id_x |
+--------------------------------+-----------+
| 1 | 7 |
| 2 | 8 |
+--------------------------------+-----------+
2 rows in set (0.00 sec)
+-----------+
| id_x |
+-----------+
| 8 |
+-----------+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
+------+
| @z |
+------+
| 2 |
+------+
1 row in set (0.00 sec)
Version with hidden count
DROP PROCEDURE IF EXISTS test;
Delimiter //
CREATE PROCEDURE test(OUT w INT)
BEGIN
Declare Counter_a INT DEFAULT 0;
Set @id_counter = 0;
SELECT Floor(Count(`id_x`) * Rand()) INTO Counter_a From `x_data` Where `ok_w` = 1 And `active` = 2 And `time` >= Unix_timestamp();
SELECT `id_x` From `x_data` Where @id_counter := @id_counter + 1 And `spec` = 1 Limit Counter_a,3;
IF @id_counter < 3 THEN
Set Counter_a = Counter_a + 1;
SELECT `id_x` From `x_data` Limit Counter_a,2;
END IF;
Set w = @id_counter;
END //
Delimiter ;
Output like this:
mysql> Call test(@z); Select @z;
+-----------+
| id_x |
+-----------+
| 7 |
| 8 |
+-----------+
2 rows in set (0.00 sec)
+-----------+
| id_x |
+-----------+
| 8 |
+-----------+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
+------+
| @z |
+------+
| 2 |
+------+
1 row in set (0.00 sec)
@TIP
@id_counter := @id_counter + 1
It's Must be before all conditions in where.
For example:
[.....] Where @id_counter := @id_counter + 1 And `field_2` > 1 LIMIT 0, 5;
Will return always 1...
Correct Version
[.....] Where `field_2` > 1 And [Other conditions] And @id_counter := @id_counter + 1 LIMIT 0, 5;