mysql - create stored procedure for multiple update queries -

select id ids challenges expire_date > today 

i need loop multiply ids (foreach in php)

foreach (ids)       update challenges set status = 'expired' id = ids      update user_challenge set challenge_status = 'expired' challenge_status = 'pending' , challenge_id = ids      update user_challenge set challenge_status = 'failed' challenge_status = 'accepted' , challenge_id = ids   end each; 

can please create stored procedure or single query perform this, thanks

your single update query in mysql might this

update user_challenge u inner join        challenges c on u.challenge_id = c.id    set c.status = 'expired',        u.challenge_status = case when u.challenge_status = 'pending' 'expired'                                   when u.challenge_status = 'accepted' 'failed' end  c.expire_date < curdate() 

here sqlfiddle example

i believe condition expiration make sense should expire_date < curdate() (meaning expiration date should less today's date) reflected in query.