Thread: 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);
...

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).

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

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.

Originally Posted by aussiemcgr

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).

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.

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

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.