2

First, take a look at this table, basically I need to UPDATE (+1) a ticket WHERE the Username equals to Jondoe AND Money is greater than 200.

enter image description here

Now, I know I can execute 3 queries, first to check if the actual user has Jondoe as Username, then check if he has enough Money and if these results are true, then UPDATE (+1) a ticket finally.

I'm learning SQL and i'm working over a mssql database, what's the proper way to do such a conditional query/update? Can I avoid using that many queries?

I'm also working with laravel framework, I don't know if it comes with any "tool" to accomplish what I want.

Thanks in advance

Improve this question
3
  • 1
    You could have a trigger that stopped insertion of invalid values. Commented Jun 30, 2016 at 9:54
  • 2
    If you have update record where username = 'Jondoe' you want to use update query instead of insert. Commented Jun 30, 2016 at 9:54
  • Yes, my bad. I just updated my question. Thank you @NikhilVaghla Commented Jun 30, 2016 at 9:56

5 Answers 5

Reset to default
2

What you are looking for is to use the UPDATE statement and the WHERE statement to seive out what you require, and this is how you do it. Parse the command through laravel and it will update accordingly.

UPDATE TableName Set Tickets=Tickets+1 WHERE USERNAME='JonDoe' AND Money > 200

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

SET Tickets=Tickets+1 may be more akin to what the OP was asking for.
I was basing this on his previous question, didn't follow-ed on his updated question.
I'm testing it right now, having in mind I'm using PHP, is there any way I can "catch" the error back, if either Username or Money didn't match the condition?
Probably not so familliar with the laravel context, but for generic PHP a try and catch would be good enough. Give me a moment to get that up.
@Frondor, It depends on the database, but I think you can't in this case. You are asking the database to update one field of a row if some conditions are met, if you get no updates where needed, you know some condition wasn't met, but you can't say which one was. But in this case, as you are asking for money >= quantity, as apokryfos pointed out you have only two options: the user doesn't exist or the money is not enough.
2

Using Eloquent:

\App\User::where('username','JonDoe')->where('Money','>',200)->increment('Tickets');
Improve this answer

2 Comments

I think Eloquent is my best bet this time, gotta check. Thank you!
If you have a user model already then eloquent is better than fluent (it's actually built on top of it).
2

I'm not expert with Laravel, but as many other frameworks, it should have an ORM system ( Object Relational Mapping : https://en.wikipedia.org/wiki/Object-relational_mapping ), this systems helps you to 'speak' to databases in an object oriented fashion ( with methods like getFirstByPrimaryKey( $primaryKey ) ).

This said, I think that kind of systems are better when you already know how to do it manually.

In the case you are proposing, you have to think first what you need, and then think what's the better approach to accomplish it.

What you need:

Insert a ticket for one user if that user has more or equals some quantity.

Let me suppose, perhaps you're going to detry that quantity from money field after a successful ticket creation, right ?

So I think we're selling a ticket, and the normal steps should be:

  • Check if user is logged. If it's logged, we probably have the user id in session or a similar system. It's much better to search in indexed numeric fields, if possible.

  • Once we have the user_id, we update the row if that user id has enough money for this action:


    //pseudo code
        $userId = 12;
        $cost = 200;
        $query = "update ticketstable set tickets=tickets+1, money=money-$cost 
    where id='$userId' and money >= $cost";
        $result = $db->query($query);

        if($result){
          //row with id = $userId has been updated
        }else{
          //There's not a row with $userId, or that row's money was less than $cost
        }

Take in account when dealing with money operations that you should make all related queries as a transaction ( a transaction in database argot means several sql sentences executed together, in a way that if some fails, all others are reverted, to avoid inconsistency ).

If you divide my proposal in two queries:

1) check if user with id=x has money >= $cost;

2) update money -= $cost;

You can find in a situation where the user or other automated process takes some money from the same row between the steps 1 and 2, and then you end with negative money, and allowing an operation you shouldn't.

Improve this answer

Comments

1

Laravel has the fluent query builder which you can use (more or less) like below:

$affectedRows = DB::table('TableName')
    ->where("Username", "Jondoe")
    ->where("Money",">",200)
    ->increment("Tickets");

You can also use $affectedRows to check if there were any updates made.

Edit:

If you have a user model (e.g. named User) then you can use the same syntax but with Eloquent.

Example:

User::where("Username", "Jondoe")
    ->where("Money",">",200)
    ->increment("Tickets");

Eloquent is Laravel's ORM (Object-Relational model) which provides a mapping between relational databases and PHP objects (i.e. the model part of the MVC framework). It's not a query builder itself but it does use fluent in the background to build queries.

Reference https://laravel.com/docs/5.0/queries

Improve this answer

6 Comments

Thank you! I was aware of that. Sadly, I think that means 2 queries + the increment at the end... Isn't that kind of a "overkill" in comparision to UPDATE TableName Set Tickets=Tickets+1 WHERE USERNAME='JonDoe' AND Money > 200?
Fluent is a query builder. It builds a single query based on the chain of functions. Functions like where,whereIn etc just add segments to the query and functions like increment,update,insert,get,first etc build and execute the query.
That's cool info @apokryfos, but what if it doesn't find the user, or he/she hasn't enough money? How can I "catch" those errors? I mean, to return the proper error
Technically speaking, not updating any user because the conditions don't match is not an error. However, in my updated response there's the $affectedRows variable which would tell you how many users were updated. If the username is unique then the value will be either 0 (meaning that either the user doesn't exist or doesn't have enough money, if you want to know which one is the issue you need to run another query) or 1 (which means everything went ok).
Got it! I wish I can choose this, and the one about Eloquent as the best answer, but I can't, sadly.
|
1

Try This Command

UPDATE TableName Set Tickets=Tickets+1 WHERE USERNAME='JonDoe' AND Money > 200

Hopefully This will solve your problem

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.