Multiple Rows Output into Variables in MySQL

Store multiple rows from mysql database into one single variable

Each time through the loop, add the desired value to an array. Then, after the loop, use the implode() function to create your desired output.

while($row = mysql_fetch_array($mysql_commandJc)){

$followI = $row['industry'];

$resultArray[] = $followI;
}

$groupConcat = implode(" OR ", $resultArray);

echo $groupConcat;

Note that you should really stop using the mysql library and instead use mysqli or PDO.

Query multiple mysql rows and separate into variables after?

mysql_fetch_* will only fetch one row. I think what you want is this:

$result = mysql_query("SELECT url, image, placement FROM advert WHERE user='1'") 
or die(mysql_error());

$adverts = array();

while(($row = mysql_fetch_assoc($result))) {
$adverts[$row['placement']] = $row;
}

It will create an array like this:

Array(
'sideadtop' => Array(
'url' => ...,
'image' => ...,
'placement' => ...
),
'sideadmiddle' => Array(...),
'sideadbottom' => Array(...)
)

You can access the individual adverts with $adverts['sideadtop'], $adverts['sideadmiddle'], etc.

Imo this is a better approach than creating a variable for each element.

SELECT INTO multiple @variables MySQL

You can join with the same table and ensure that each join will provide a new id, something like (eg. for two ids, but you will get the point):

SELECT a1.id, a2.id INTO @photo1, @photo2
FROM album a1
inner join album a2 on a2.scene=a1.scene and a2.upload=a1.upload and a2.id>a1.id
WHERE a1.uploaded = @time AND a1.scene_id = NEW.id;

See SqlFiddle for a complete sql and test case.

MYSQL select into multiple rows

Actually you can't. Select Into is used when has just one row as result. Use cursor instead. For example:

DELIMITER $$
CREATE TRIGGER notify_winner
AFTER UPDATE ON Auctions
FOR EACH ROW

BEGIN
DECLARE done INT DEFAULT FALSE;

DECLARE cur1 CURSOR FOR
SELECT A.winner, A.vin
FROM Auctions AS A
WHERE A.winner != 'NONE'
AND A.winner IS NOT NULL;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur1;

read_loop: LOOP
FETCH cur1 INTO @buyer, @vin;

IF done THEN
LEAVE read_loop;
END IF;

INSERT
INTO Wins (winner,vin)
values (@buyer, @vin);

END LOOP;

CLOSE cur1;

END$$
DELIMITER ;

For more information take a look at this link: https://dev.mysql.com/doc/refman/5.7/en/cursors.html

Hope it helps!

MySQL query for selecting multiple rows as arrays of those rows data

You could do it with this query:

SELECT a1.id, a2.id
FROM (SELECT *, @rownum1:=@rownum1+1 AS rownum
FROM (SELECT id
FROM `ct_product`
LIMIT 3
) art
JOIN (SELECT @rownum1 := 0) r
) a1
JOIN (SELECT *, @rownum2:=@rownum2+1 AS rownum
FROM (SELECT id
FROM `ct_product`
LIMIT 3, 3
) art
JOIN (SELECT @rownum2 := 0) r
) a2
ON a1.rownum = a2.rownum

Output:

id  id  
1 4
2 5
3 6

This query works by creating two new tables with artificially generated row numbers (@rownum1 and @rownum2) from the first 3 and the second 3 rows in the original table. They are then JOINed on matching row numbers to get the desired result.



Related Topics



Leave a reply



Submit