SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Zealot
    Join Date
    Jan 2006
    Posts
    169
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Want a conditional insert function? Is it possible?

    I'm writing a back-end script that queries a large number of records and works through them over a period of an hour or so.

    Each record is processed and may generate a financial transaction that needs to be added to the customer's account. The customer's account balance is returned in the original result set, but they may have several transactions in the processing queue and the sum of those transactions may allow their account balance to go negative if I rely on the account balance in the result set.

    What I want to do is create a statement that only inserts the new transaction if the current account balance is greater than the transaction amount. I want to know if the insert happened or not so I can take appropriate action.

    Code:
    IF(account_balance>$txn_amount)
    THEN(INSERT INTO transactions (txn_amount, timestamp, account_balance, user_id) VALUES($txn_amount, NOW(), $new_account_balance, $user_id))
    If such a function exists, how would I determine whether the insert happened or not?

    I'm trying to avoid doing a database query to check the balance for each transaction, as it would likely bog the server down.

  2. #2
    Smart programmer silver trophy M.Zeb Khan's Avatar
    Join Date
    Jan 2004
    Location
    Luton, Beds
    Posts
    1,791
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Why not use the scripting language to check the balance before inserting? let's say if you are using PHP as a scriptling language, you just do like this;

    PHP Code:
    if(mysql_num_rows(mysql_query("SELECT txn_id FROM transactions WHERE account_balance < 1 ")) < 1) { 
         
    mysql_query("INSERT INTO transactions (txn_amount, timestamp, account_balance, user_id) VALUES($txn_amount, NOW(), $new_account_balance$user_id) ");


  3. #3
    SitePoint Zealot
    Join Date
    Jan 2006
    Posts
    169
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm running thousands of transactions and was thinking it would be less server intensive to do it in one database call. Maybe it's not a big deal. I'll give it a try.

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    using mysql_num_rows() to find out if a row exists is a really bad idea. don't do it. use count(*) instead, or use a static value and a test for NULL.

    a better approach in your situation is a temporary staging table. create a temp table, load the data in to the temp table, then run a query with a JOIN to figure out which rows you want to insert and which you don't. you can also use a DELETE statement with a JOIN to remove the rows you don't want to insert, and follow up with an UPDATE or an INSERT ... SELECT statement to get the rows in to the target table.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  5. #5
    SitePoint Zealot
    Join Date
    Jan 2006
    Posts
    169
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks. Just curious... Why is mysql_num_rows() a no-no? I've been using it years and didn't realize it was a problem. So you'd do:

    Code:
    $result=mysql_query("SELECT COUNT(*) as num_results FROM mytable WHERE user_id=6");
    $row=mysql_fetch_array($result);
    if($row['num_results']){
    }
    versus:

    Code:
    $result=mysql_query("SELECT field as num_results FROM mytable WHERE user_id=6");
    if($mysql_num_rows($result)){
    }

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the difference is like this --

    suppose you and i are sitting in our office in new york, and i ask you to find out how many people are in los angeles

    do you...

    a. go to los angeles, get everybody on a plane, bring them all to new york, and count them as the get off the plane

    or

    b. go to los angeles, count all the people, and come back with the answer?

    that's the difference

    the planes required are the number of blocks of data transferred between the database (LA) and the front-end app (NY)

    granted, the parallel isn't quite so stark as counting ~everybody~ in LA, because you are getting transactions only for a particular user

    that would be more like counting only the people who live on hollywood boulevard

    you still wouldn't send them all over and count them as they got off the plane
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot
    Join Date
    Jan 2006
    Posts
    169
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Gotcha. Makes a lot of sense.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •