homer.favenir Posted January 26, 2009 Share Posted January 26, 2009 hi, can anyone please help me. i want to create a view table in phpmyadmin of my webhosting. but when i execute the query it gives me an error my code is: DROP VIEW IF EXISTS `mafeegro_mafeedb`.`loanspaidregister`; CREATE ALGORITHM=UNDEFINED DEFINER=`mafeegro_root`@`192.168.2.7` SQL SECURITY DEFINER VIEW `mafeegro_mafeedb`.`loanspaidregister` AS select `pawnshop_tbl`.`PawnTicketNo` AS `PT No`,concat(`pawnshop_tbl`.`LastName`,_latin1' ',`pawnshop_tbl`.`FirstName`) AS `Name`,`pawnshop_tbl`.`DateLoan` AS `Date Loan`,if((`pawnshop_tbl`.`ServiceType` = _latin1'Renew'),`pawnshop_tbl`.`DateLoan`,_utf8'') AS `Date Renew`,`pawnshop_tbl`.`DateRedeem` AS `Date Redeem`,if(((to_days(`pawnshop_tbl`.`ExpiryDate`) - to_days((curdate() + 3))) < 0),`pawnshop_tbl`.`ExpiryDate`,_utf8'') AS `Date Auction`,_utf8'' AS `Date Sold`,if((`pawnshop_tbl`.`Principal` between 100 and 500),`pawnshop_tbl`.`Principal`,_utf8'') AS `100 - 500`,if((`pawnshop_tbl`.`Principal` between 501 and 1999),`pawnshop_tbl`.`Principal`,_utf8'') AS `501 - 1999`,if((`pawnshop_tbl`.`Principal` >= 2000),`pawnshop_tbl`.`Principal`,_utf8'') AS `2000 - UP`,if((`pawnshop_tbl`.`ServiceType` = _latin1'Redeem'),`pawnshop_tbl`.`Principal`,_utf8'') AS `Principal Paid`,if(((`pawnshop_tbl`.`ServiceType` = _latin1'Redeem') or (`pawnshop_tbl`.`ServiceType` = _latin1'Renew')),format((`pawnshop_tbl`.`Principal` * `pawnshop_tbl`.`Interest`),2),_utf8'') AS `Interest Paid`,if((`pawnshop_tbl`.`ServiceType` = _latin1'Redeem'),(`pawnshop_tbl`.`Principal` + format((`pawnshop_tbl`.`Principal` * `pawnshop_tbl`.`Interest`),2)),if((`pawnshop_tbl`.`ServiceType` = _latin1'Renew'),format((`pawnshop_tbl`.`Principal` * `pawnshop_tbl`.`Interest`),2),_utf8'')) AS `Cash Received`,`pawnshop_tbl`.`Description` AS `Description`,`pawnshop_tbl`.`Branch` AS `branch`,_utf8'' AS `Gain/Loss` from `pawnshop_tbl` union all select `sold_tbl`.`idpawnshop_tbl` AS `PT no`,concat(`sold_tbl`.`LastName`,_latin1' ',`sold_tbl`.`FirstName`) AS `Name`,`sold_tbl`.`DateLoan` AS `Date Loan`,_utf8'' AS `Date Renew`,_utf8'' AS `Date Redeem`,_utf8'' AS `Date Auction`,`sold_tbl`.`DateSold` AS `Date Sold`,if((`sold_tbl`.`AuctionPrice` between 100 and 500),`sold_tbl`.`AuctionPrice`,_utf8'') AS `100 - 500`,if((`sold_tbl`.`AuctionPrice` between 501 and 1999),`sold_tbl`.`AuctionPrice`,_utf8'') AS `501 - 1999`,if((`sold_tbl`.`AuctionPrice` >= 2000),`sold_tbl`.`AuctionPrice`,_utf8'') AS `2000 - UP`,`sold_tbl`.`Principal` AS `Principal Paid`,_utf8'' AS `Interest Paid`,`sold_tbl`.`AuctionPrice` AS `Cash Received`,`sold_tbl`.`Description` AS `Description`,`sold_tbl`.`Branch` AS `branch`,(`sold_tbl`.`AuctionPrice` - `sold_tbl`.`Principal`) AS `Gain/Loss` from `sold_tbl`; #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE ALGORITHM=UNDEFINED DEFINER=`mafeegro_root`@`192.168.2.7` SQL SECURITY DE' at line 2 Quote Link to comment https://forums.phpfreaks.com/topic/142443-solved-create-view-in-phpmyadmin-is-error/ Share on other sites More sharing options...
Mchl Posted January 26, 2009 Share Posted January 26, 2009 Try CREATE VIEW `mafeegro_mafeedb`.`loanspaidregister` AS... Quote Link to comment https://forums.phpfreaks.com/topic/142443-solved-create-view-in-phpmyadmin-is-error/#findComment-746377 Share on other sites More sharing options...
homer.favenir Posted January 26, 2009 Author Share Posted January 26, 2009 ok, thanks Quote Link to comment https://forums.phpfreaks.com/topic/142443-solved-create-view-in-phpmyadmin-is-error/#findComment-746440 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.