0

I have been cracking my head on this mater in the past couple of days. I am faced with a situaton where i need to write a MySQL equivalent of PHP code to loop through sql select results. The Php code looks like this

$query = "SELECT pubid, author FROM refs";
$sql=$con->prepare($query);
$sql->execute();
$sql->setFetchMode(PDO::FETCH_ASSOC);

    while ($row=$sql->fetch()){
        $id= $row['pubid'];
        $author = $row['author'];
        $name = explode('., ', $author);


            }

How can one write sql/mysql query that does the same?

2
Contributors
5
Replies
17
Views
1 Year
Discussion Span
Last Post by pritaeas
0

You can write a stored procedure that uses a cursor to loop through your query result. Is that what you are looking for? What exactly is your requirement?

0

Yes, that is what i am looking for...here is the snippet

CREATE PROCEDURE getDistinctAuthors()

BEGIN

DECLARE pubid INT(11);
DECLARE author VARCHAR(255);

-- cursor

DECLARE authorlist_cursor CURSOR FOR

SELECT pubid, author FROM refs;


DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

OPEN authorlist_cursor;

get_authors: LOOP

Edited by Amaina

0

Thank you @pritaeas for this wonderful link. However, what i want is to loop through the returned strings a loop similar to PHP's foreach loop.

When i call this procedure i get the following results:

mysql> SET @author_list = "";
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> CALL unitpublications.build_author_list(@author_list);
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> 
mysql> SELECT @author_list;
+-------------------------------------------------------------------------------------------------------+
| @author_list                                                                                          |
+-------------------------------------------------------------------------------------------------------+
| Elliott, A. M., Luo, N., Tembo, G., Halwiindi, B., Steenbergen, G., Machiels, L., Pobee, J., Nunn, P; |
+-------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

My question is what is the mysql equivalent of a foreach loop that can loop each string delimited by '., ' ?

Edited by Amaina

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.