Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    90
    Rep Power
    3

    mysql transaction error


    Hi,

    I am trying to use php to get results from a query that uses user-defined variables.
    the ordinary syntax of the query is
    SET @num = 1, @pattern = 0, @grpname = "" ;
    SELECT @num := IF(@pattern = rPattern AND @grpname = groupname, @num + 1, 1) AS num,
    dudes.name AS Name1, @pattern:=rPattern AS Pattern, @grpname:=groupname AS GName
    FROM rpatterns LEFT JOIN dudes ON rtdudeD=dudeid
    LEFT JOIN groups ON rGroupID=groupID
    WHERE rpattern.rid= 54 AND rIsActive = "1" AND dudeisactive=1 AND LEFT(rPattern,1)<>0
    ORDER BY rpattern, rGroupID, rPriority DESC;
    the query itself works.
    because php can only do one query at a time, i'm trying to combine these 2 queries (the SET and the SELECT) using a transaction as below:
    PHP Code:
    $query = array();
    $query  'START TRANSACTION; SET @num = 1, @pattern = 0, @grpname = "" ;';
    $query .= 'SELECT @num := IF(@pattern = rtpattern AND @grpname = routinggrpname, @num + 1, 1) AS num, ';
    $query .= 'tmname AS Terminator, @pattern:=rtPattern AS Pattern, @grpname:=routinggrpname AS RGname';
    $query .= 'FROM routes LEFT JOIN terminations ON rtTerminationID=tmid ';
    $query .= 'LEFT JOIN routinggroups ON rtGroupID=routinggrpID';
    $query .= 'WHERE  rthsid= ' $id ' AND rtIsActive = "1"  AND tmisactive=1 AND LEFT(rtPattern,1)<>0 ';
    $query .= 'ORDER BY rtpattern, rtGroupID, rtPriority DESC ; ROLLBACK';
    $result2 mysql_query($query); 
    when i run this i get an error
    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 'SET @num = 1, @pattern = 0, @grpname = "" ;SELECT @num := IF(@pattern = rpattern' at line 1
    setting aside the fact that the code does not use mysqli_query() , what am i doing wrong.?
    Last edited by bobert123; July 31st, 2014 at 12:16 PM.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2003
    Posts
    3,553
    Rep Power
    595
    This looks to me like a MySQL question. While there are many experts here you might get a quicker reponse on the MySQL forum. However, you have a major PHP problem in that you are using the deprecated (for more than a decade) MySQL extensions and need to switch to PDO. Your code is probably open to injection attacks and will break with the next release of PHP anyway.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    90
    Rep Power
    3
    Originally Posted by gw1500se
    This looks to me like a MySQL question. While there are many experts here you might get a quicker reponse on the MySQL forum. However, you have a major PHP problem in that you are using the deprecated (for more than a decade) MySQL extensions and need to switch to PDO. Your code is probably open to injection attacks and will break with the next release of PHP anyway.
    as i said, the query itself works. i run it in mysqlyog without issues.
    regarding the deprecated code, please see my response here: adding a fifth column to an existing array
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2003
    Posts
    3,553
    Rep Power
    595
    How did you verify that it works? Did you 'echo $query' then copy and paste it?

    P.S. Why did you create $query as an array then CAST it to a string?
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  8. #5
  9. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,908
    Rep Power
    6352
    The PHP mysql driver does not support multiple commands within a single call to mysql_query. You are attempting at least 3 commands in a single call. This is one very good reason why you should switch to mysqli or PDO (as mentioned in your other thread). mysqli supports rollback but with mysql you'd have to issue the commands separately. This giant block of multiple standalone commands all being issued at once may work when you copy/paste them into your query software, but doesn't work via PHP due to security handling.
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    90
    Rep Power
    3
    Originally Posted by gw1500se
    How did you verify that it works? Did you 'echo $query' then copy and paste it?

    P.S. Why did you create $query as an array then CAST it to a string?
    i verified the works in sqlyog, not in php.
    query doesn't need to be an array. that was something from the legacy code, and i have no idea why it was there. i'm happy to remove it if it's not needed.

    the PHP mysql driver does not support multiple commands within a single call to mysql_query. You are attempting at least 3 commands in a single call.
    that's the point of using a transaction. it should all be treated as one command.
    as i wrote in the other post, if it would make a significant difference, i'm happy to put in a new mysqli_connect() for just this section of code. could someone please show me the correct syntax for doing this with mysqli?
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2003
    Posts
    3,553
    Rep Power
    595
    Then you really don't know if the query you generate from PHP works or not because you do not know exactly what that the code outputs. Transactions do not work that way from a PHP perspective. Yes, they make multiple queries look like a single query (over simplified) as far as the db is concerned but you still need to make the PHP calls individually. You don't really need to use transactions here, just call the queries separately.

    Now an array makes sense. Each query is an element of the array and then you use a 'foreach' to run the queries.

    Comments on this post

    • bobert123 agrees
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  14. #8
  15. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,908
    Rep Power
    6352
    Transactions in MySQL (and all SQL drivers) are by connection not by command. If I log into my oracle environment right now and do an update command, then go home for the weekend and issue a COMMIT; command Monday morning, it will be the same transaction (and my DBA would murder me).

    The point of transactions is not to issue one gigantic multi-step process in a single massive command. Begin the transaction when your business logic determines it needs to begin, perform all your operations, and at the END of your script issue either a rollback or a commit. If you're doing everything in one huge command there's not much reason for transactions anyway because you put commit right at the end and that blows away the purpose of transactions: you are incapable of rolling back.

    Comments on this post

    • bobert123 agrees
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,469
    Rep Power
    1752
    Is it still the case that only the InnoDB engine supports transactions in mySQL?

    Comments on this post

    • ManiacDan agrees : I have no idea. This is a good question.
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  18. #10
  19. No Profile Picture
    I haz teh codez!
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Dec 2003
    Posts
    2,552
    Rep Power
    2337
    Another option here is to make the whole sequence a stored procedure.
    I ♥ ManiacDan & requinix

    This is a sig, and not necessarily a comment on the OP:
    Please don't be a help vampire!
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    90
    Rep Power
    3
    i may be missing something here, but from what you guys are saying it sounds like i can set the variable in one query and continue using it in the next query without a transaction. as long as i don't close the mysql connection. If that's the case, then that's great, i just need to run 2 separate mysqli_query() commands. one for the SET and one for the SELECT. i'll give that a try tomorrow. thanks.
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2003
    Posts
    3,553
    Rep Power
    595
    Correct.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  24. #13
  25. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,908
    Rep Power
    6352
    How did that work out for you?
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Dec 2004
    Posts
    3,008
    Rep Power
    376
    while you can issue two separate queries and get things done, bear in mind transactions are usually used for when you want different queries to complete fully or not at all..
  28. #15
  29. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,908
    Rep Power
    6352
    Right, OP wasn't using transaction properly at all. Transactions are for when you want all your queries to be treated as a single activity, so if one of them fails they all fail. That's why you start a transaction, perform a bunch of business logic, and then make the COMMIT/ROLLBACK decision based on the status of each individual activity.
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo