Member Avatar for Amaina

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?

  • 3 Contributors
  • 6 Replies
  • 19K Views
  • 2 Years Discussion Span
  • Latest Post Latest Post by flashx4u
Member Avatar for pritaeas

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?

Member Avatar for Amaina

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
Member Avatar for Amaina

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
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.