Welcome to the Java Programming Forums


The professional, friendly Java community. 21,500 members and growing!


The Java Programming Forums are a community of Java programmers from all around the World. Our members have a wide range of skills and they all have one thing in common: A passion to learn and code Java. We invite beginner Java programmers right through to Java professionals to post here and share your knowledge. Become a part of the community, help others, expand your knowledge of Java and enjoy talking with like minded people. Registration is quick and best of all free. We look forward to meeting you.


>> REGISTER NOW TO START POSTING


Members have full access to the forums. Advertisements are removed for registered users.

Results 1 to 10 of 10

Thread: Improving the SQL statement

  1. #1
    Forum VIP
    Join Date
    Jul 2010
    Posts
    1,621
    Thanks
    25
    Thanked 322 Times in 300 Posts

    Default Improving the SQL statement

    I have a SQL statement which I would like to optimize. This code is in php, but I'm just worrying about the sql part, so I figured some people on this forum might have some advice.

    Basically, I have a table of user accounts called: Company. For this portion of the code, we only care that the table has columns: Name and Money.
    I am attempting to do a basic money transfer from one user to another (imaginary money). The php code is queried with parameters in the url which I use to determine who is transferring to who and how much. In order to do that transfer, I am performing two database queries. I wanted to know if there was a way to make it a single query.
    This is my current code. It is pretty straightforward.
    /* connect to the db */
    $link = connect();
     
    /* get fields from url */
    $sender = $_GET['sender'];
    $receiver = $_GET['receiver'];
    $amount = $_GET['amount'];
    /* subtract from sender */
    $firstQuery = 'UPDATE Company SET Money = Money-'.$amount.' WHERE Name=\''.$sender.'\'';
    $firstResult = mysql_query($firstQuery,$link);
    /* add to receiver */
    $query = 'UPDATE Company SET Money = Money+'.$amount.' WHERE Name=\''.$receiver.'\'';
    ...
    $result = mysql_query($query,$link);
    ...
    NOTE TO NEW PEOPLE LOOKING FOR HELP ON FORUM:

    When asking for help, please follow these guidelines to receive better and more prompt help:
    1. Put your code in Java Tags. To do this, put [highlight=java] before your code and [/highlight] after your code.
    2. Give full details of errors and provide us with as much information about the situation as possible.
    3. Give us an example of what the output should look like when done correctly.

    Join the Airline Management Simulation Game to manage your own airline against other users in a virtual recreation of the United States Airline Industry. For more details, visit: http://airlinegame.orgfree.com/


  2. #2
    Forum VIP
    Join Date
    Jun 2011
    Posts
    305
    My Mood
    Bored
    Thanks
    45
    Thanked 85 Times in 71 Posts
    Blog Entries
    4

    Default Re: Improving the SQL statement

    I'm sure there is a way to combine the two statements (above my paygrade, perhaps someone else can help) however I fail to see the advantage. They are two discrete operations and should probably be kept that way.

    More of a concern is that this is vulnerable to SQL injection attacks. Consider using mysqli prepared statements or at the very least mysql_real_escape_string to sanitize the parameters.

  3. #3
    Forum VIP
    Join Date
    Jul 2010
    Posts
    1,621
    Thanks
    25
    Thanked 322 Times in 300 Posts

    Default Re: Improving the SQL statement

    My primary reason is because it sort of "breaks the design" of the rest of the file (this file handles a bunch of different requests for interacting with the Company table in the database). I also do not know what happens to the database if half of a query fails. If a half failure undoes all the changes the query did, then that would be a very strong argument for why it should be one statement (as it currently stands, if the first query succeeds and the second fails, the money the sender transfers would just vanish into thin air).

    I am aware that I am using some deprecated and potentially vulnerable php functions. I have across-the-board plans in my backlog for updating all my php files to conform with current best practices, but my primary concern right now is getting my overall project functional (the php server-side is a small part of the overall project). 6 months ago, I didn't know any php code, and I've been sort of learning it as I need to. Once I'm in a situation where my users can use my program properly, I intend on going back and investing the time into learning some better php habits (my current timetable has me hoping for mid-January, depending on how much Oracle's java update gets in my way).
    NOTE TO NEW PEOPLE LOOKING FOR HELP ON FORUM:

    When asking for help, please follow these guidelines to receive better and more prompt help:
    1. Put your code in Java Tags. To do this, put [highlight=java] before your code and [/highlight] after your code.
    2. Give full details of errors and provide us with as much information about the situation as possible.
    3. Give us an example of what the output should look like when done correctly.

    Join the Airline Management Simulation Game to manage your own airline against other users in a virtual recreation of the United States Airline Industry. For more details, visit: http://airlinegame.orgfree.com/

  4. #4
    Administrator copeg's Avatar
    Join Date
    Oct 2009
    Location
    US
    Posts
    5,264
    Thanks
    176
    Thanked 821 Times in 764 Posts
    Blog Entries
    5

    Default Re: Improving the SQL statement

    Agree with the above advice...your code is susceptible to injection and a PreparedStatement could alleviate that danger, help with the updates, and allow you to rollback changes if one fails. You could write a stored procedure to accomplish this task. I might also recommend removing the SQL code and encapsulating it elsewhere

  5. #5
    Forum VIP
    Join Date
    Jul 2010
    Posts
    1,621
    Thanks
    25
    Thanked 322 Times in 300 Posts

    Default Re: Improving the SQL statement

    What do you mean by: "removing the SQL code and encapsulating it elsewhere"?
    The code in this php file is a webservice (one of twelve webservices) I am using to communicate with my server.
    NOTE TO NEW PEOPLE LOOKING FOR HELP ON FORUM:

    When asking for help, please follow these guidelines to receive better and more prompt help:
    1. Put your code in Java Tags. To do this, put [highlight=java] before your code and [/highlight] after your code.
    2. Give full details of errors and provide us with as much information about the situation as possible.
    3. Give us an example of what the output should look like when done correctly.

    Join the Airline Management Simulation Game to manage your own airline against other users in a virtual recreation of the United States Airline Industry. For more details, visit: http://airlinegame.orgfree.com/

  6. #6
    Administrator copeg's Avatar
    Join Date
    Oct 2009
    Location
    US
    Posts
    5,264
    Thanks
    176
    Thanked 821 Times in 764 Posts
    Blog Entries
    5

    Default Re: Improving the SQL statement

    Quote Originally Posted by aussiemcgr View Post
    What do you mean by: "removing the SQL code and encapsulating it elsewhere"?
    The code in this php file is a webservice (one of twelve webservices) I am using to communicate with my server.
    I mean create a PHP class (or some alternative) which is function based that delegates the SQL calls for the client. Thus, all the web service file does is create an instance of the class and call its functions (a similar type of MVC design one might use, say, in a java Swing application, allowing code-reuse and easier maintenance).

  7. #7
    Forum VIP
    Join Date
    Jul 2010
    Posts
    1,621
    Thanks
    25
    Thanked 322 Times in 300 Posts

    Default Re: Improving the SQL statement

    Well that is essentially all the webservices do. My program sends parameters to the php url. Then, based on the parameters used and all sorts of other stuff, it creates the SQL statement, sends the query, retrieves the result set, and replies back to my program with an xml document.
    I don't think you can "create an instance" of a php file. You can import another php file's methods, but I think that's really all you can do. Regardless, I think that would create a lot of unnecessary overhead for a simple database query.
    NOTE TO NEW PEOPLE LOOKING FOR HELP ON FORUM:

    When asking for help, please follow these guidelines to receive better and more prompt help:
    1. Put your code in Java Tags. To do this, put [highlight=java] before your code and [/highlight] after your code.
    2. Give full details of errors and provide us with as much information about the situation as possible.
    3. Give us an example of what the output should look like when done correctly.

    Join the Airline Management Simulation Game to manage your own airline against other users in a virtual recreation of the United States Airline Industry. For more details, visit: http://airlinegame.orgfree.com/

  8. #8
    Administrator copeg's Avatar
    Join Date
    Oct 2009
    Location
    US
    Posts
    5,264
    Thanks
    176
    Thanked 821 Times in 764 Posts
    Blog Entries
    5

    Default Re: Improving the SQL statement

    Fully your decision...just my .02 on the matter.

    I don't think you can "create an instance" of a php file. You can import another php file's methods, but I think that's really all you can do.
    Php5 and above allows for Object Oriented programming
    PHP: Classes and Objects - Manual

  9. #9
    Forum VIP
    Join Date
    Jun 2011
    Posts
    305
    My Mood
    Bored
    Thanks
    45
    Thanked 85 Times in 71 Posts
    Blog Entries
    4

    Default Re: Improving the SQL statement

    I was thinking about this today. Perhaps a temporary table is worth considering? Create a temp table, insert the sender/receiver/amount, two updates to the company table as above then finally delete the temp table. It'll provide some protection from extreme cases where the server goes down mid query. You could later extend on the design and keep a table of all transaction. Just a thought.

    Also, Copeg's advice is genius. Encapsulating queries decouples the database from the client and it allows you to unit test without messing around with URL POSTS.

  10. #10
    Forum VIP
    Join Date
    Jul 2010
    Posts
    1,621
    Thanks
    25
    Thanked 322 Times in 300 Posts

    Default Re: Improving the SQL statement

    The php is not the client. It is a webservice the client uses to access the database because my webhost does not allow remote access to the database. The php code runs on the server, so it has local access to the database. I use the php webservices to allow my client to communicate with the database.

    I have a table of transactions. The way it works (currently), is:
    1. User 1 specifies that they would like to loan X amount of money to any other user. The loan information is then added to a database table.
    2. User 2 comes along and decides they like the look of User 1's loan, so they accept it. The loan information in the database is updated.
    3. User 1 then comes back, sees that User 2 would like to accept the loan. User 1 can then accept User 2's request, or reject it. If it is accepted, the loan information in the database is updated, and the funds are transferred from User 1 to User 2. Then, every "iteration" of the program/game/simulation (whatever it would be called), User 2 transfers a small amount back to User 1 (as payments) until the loan, plus interest, is paid off.

    So I do have a table of transactions, but my main concern is getting those initial funds transferred after both parties have accepted (fake funds, obviously, I'm not playing around with real money). The things I've asked on this thread are all related to #3.
    NOTE TO NEW PEOPLE LOOKING FOR HELP ON FORUM:

    When asking for help, please follow these guidelines to receive better and more prompt help:
    1. Put your code in Java Tags. To do this, put [highlight=java] before your code and [/highlight] after your code.
    2. Give full details of errors and provide us with as much information about the situation as possible.
    3. Give us an example of what the output should look like when done correctly.

    Join the Airline Management Simulation Game to manage your own airline against other users in a virtual recreation of the United States Airline Industry. For more details, visit: http://airlinegame.orgfree.com/

Similar Threads

  1. passing jtable row values to an sql statement while looping through it
    By greeninho in forum What's Wrong With My Code?
    Replies: 3
    Last Post: May 30th, 2013, 04:40 PM
  2. [SOLVED] How to modify core Java interface java.sql.Statement.execute(String sql)?
    By amughost in forum Java Theory & Questions
    Replies: 6
    Last Post: June 9th, 2012, 04:31 PM
  3. Replies: 1
    Last Post: June 14th, 2011, 11:08 AM
  4. Improving the code to produce the same program
    By u-will-neva-no in forum Java Theory & Questions
    Replies: 13
    Last Post: April 8th, 2011, 09:41 PM
  5. If Statement in SQL String
    By Steffi1013 in forum Loops & Control Statements
    Replies: 0
    Last Post: March 30th, 2010, 03:25 PM