MySQL Stored procedure with multiple SELECT FROM statements from the same table

Question

I have a table called "thumb" with relative paths to images and other data related with the images like their titles and descriptions. Each page of the website I'm working on represents different category of the images. For now, I have separate stored procedures for each page(category) to select different images according to their IDs. Is it possible to have one stored procedure for each page(category) which would select different images or do I need to create separate stored procedures for each range of images?

Now it is like this: Procedure for the 'All works' page:

BEGIN
SELECT * FROM thumb ORDER BY id ASC;
END

Procedure for "Paintings":

BEGIN
SELECT * FROM thumb WHERE id BETWEEN 12 AND 15 ORDER BY id ASC;
END

Procedure for "Drawings":

BEGIN
SELECT * FROM thumb WHERE id BETWEEN 16 AND 19 ORDER BY id ASC;
END

Etc.

Can a stored procedure listen to where the calling comes from and through if statements decide which SELECT * FROM statement to apply?

Can a stored procedure listen to where the calling comes Procedure cannot "listen" at all. But you may call it from different places with different parameter(s), and select according query by the parameter value. — Akina
– Akina, Commented May 6, 2020 at 12:41

Akina · Accepted Answer · 2020-05-06 12:44:27Z

1

You need something like

CREATE PROCEDURE get_images (IN pagetype TEXT)
SELECT * 
FROM thumb 
WHERE CASE pagetype WHEN 'Paintings' THEN id BETWEEN 12 AND 15
                    WHEN 'Drawings'  THEN id BETWEEN 16 AND 19 
                                     ELSE TRUE
                                     END
ORDER BY id ASC;

answered May 6, 2020 at 12:44

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

yendrrek Over a year ago

Many thanks, works great in PHP MyAdmin. I just can't figure out how to call this procedure in my PHP code to display images. I've tried $pagetype = 'Paintings'; $result = $dbc->query('CALL get_images("$pagetype")'); but it gives the same results on all pages. How do I call the parameters?

Akina Over a year ago

$result = $dbc->query("CALL get_images('$pagetype')");

yendrrek Over a year ago

The quotes! :D Thank you very much again! большое спасибо!

Collectives™ on Stack Overflow

MySQL Stored procedure with multiple SELECT FROM statements from the same table

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related