0

while creating a procedure in MYSQL i am facing a problem as, condition runs 'IF' statement and runs 'ELSE' statements too. just because an insert statement inside 'IF'.

insert into Userverified  (UserID, Mobile, PIN,uname,unameid)           
select NextUserID(),Mobile,PIN,null,null from UserNotVerified where Mobile=p_Mobile;

always results--- "result 00" ---why?

if i remove this

insert into Userverified  (UserID, Mobile, PIN,uname,unameid)           
select NextUserID(),Mobile,PIN,null,null from UserNotVerified where Mobile=p_Mobile;

works fine.

im using PhpMyAdmin and new with MYsql. code attached here as---

 CREATE procedure test      
(      
p_Mobile  varchar(13),      
p_LastOTP varchar(6)      
)          
begin   
 if ((select count(*) from UserVerified where mobile=p_Mobile)=0)      
 then 
  insert into Userverified  (UserID, Mobile, PIN,uname,unameid)           
  select NextUserID(),Mobile,PIN,null,null from UserNotVerified where Mobile=p_Mobile;

  if((select count(*) from uwallet where mobile=p_Mobile)=0) 
  then
  insert into UserWallet (WalletID,Mobile,JoinAmount,WinAmount,ReferAmount,TotalAmount,DateModified)
  select nextwalletid(),p_Mobile,0,0,0,0,now();
  end if;

  select 1 as result;      
 else      
select '00' as result;       
 end if;            
end;
GMB
  • 216,147
  • 25
  • 84
  • 135
Shiv Pratap Singh Yadav
  • 1
  • i would use exist instead count(*) = 0 but hen it doesn't work it is because your insert has a flaw. So check teh slect first and then the insert, that can nobody check. – nbk Apr 26 '20 at 21:18
  • Could you clarify if you get the error a) while *creating* your procedure or b) while *executing* it? If a) the problem is 99.999% because you do not use a [delimiter](https://stackoverflow.com/q/10259504) or alternatively don't use the procedure tab of phpmyadmin (which takes care of this for you). If b), my best guess would be that you call your code twice somehow: the first time it inserts, the second time it returns 00 (as it has already inserted it the first time). A strong giveaway for that would be if the user is actually verified despite returning 00, check that. – Solarflare Apr 26 '20 at 21:58
  • Actually as u told to use procedure tab of phpmyadmin for execution that works .. results 1 and 00 ... But with mysql command still showing 00 as result after insertion – Shiv Pratap Singh Yadav Apr 27 '20 at 08:03
  • Unfortunately, it is still unclear what you are doing *exactly*. Your procedure looks ok, e.g. it could work and not show the problem that you describe if you would do what you describe. You don't. Somehow. But we don't know where it differs. *But with mysql command still showing 00 as result after insertion* is unfortunately not enough to understand what you did *exactly*. E.g.: did you check if you actually created the procedure (and how)? How (and where) did you call your procedure with what parameters? What are the exact messages? Did you check if the user is in `UserVerified` afterwards? – Solarflare Apr 27 '20 at 09:45
  • I'd urge you to look at some phpmyadmin tutorial to see what you do and what you need to do where to achieve what you want to do. I made you a [fiddle](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=b9d1ee14f9580b99892244a20c1a37b5) that does what you described that you did (I just replaced your `nextwalletid()` and `NextUserID()` functions by autoincrement ids, it should not change your logic), and from what I understand it gives you the expected result. You now have to figure out where that differs from what you actually did in phpmyadmin/console/your app (or if your data/tables are different). – Solarflare Apr 27 '20 at 09:51

0 Answers0