PHP/MYSQL Accounting Ledger

[Doge]

I am trying to create a web-based accounting application.

 

I have decided that I will have a table that will hold the basics of each type of transaction, e.g. Sales Journal for entering invoice details, a Purchases Journal for entering purchases, and a General Journal for entering all other transactions. Once the information has been posted in one of these tables, it is also to be posted in the Ledger, which is a table of all transactions together.

 

My problem is that I have to get some of the information from (for example) the Invoice form (web page) to the Sales Journal table, but also to the Ledger table simulataneously. Now I could just setup the PHP to update both tables no problem, but I also need the Sales Journal table and the Ledger table to reference each other. How can I do this?

 

I can't create an entry in one table until the entry in the other table is completed, because I need the ID field from one table to reference to the ID field in the other table.

 

I thought about creating all the information in one large table (e.g. have a column for 'Transaction Type' where you can put 'S' for Sale, 'P' for Purchase or 'J' for journal), except then I can't have sequentially numbered lists for each type of transaction (e.g. I want to have all Sales given an equivalent invoice number starting from 1 and going upwards, then I want all General Journal entries to have a reference number starting at 1 and going up).

 

I thought another possibility is to create in PHP a function to look up the last row entered in the Ledger table and then add +1 and put this in the Sales Journal as the external reference / foreign key to the Ledger table, but what if another user enters a new entry in the time that this takes to happen... I know it's unlikely, but there must be a tidier method for doing this?

 

So how would i do this?

 

Is there a way for SQL to do it automatically, or would it definitely require PHP code?

[PFMaBiSmAd]

You cannot actually use an ID until it has been assigned (to avoid the concurrent visitor/race condition you mentioned.) See this link - http://us.php.net/manual/en/function.mysql-insert-id.php

[Catfish]

I dont know if this is what you are trying to do,but couldn't you add the entry to the first table so the ID number is assigned, then select that record from the first table using the data you used to insert it and get it's ID number, then use the ID number to make a reference to that record from the other table?

 

Something like:

mysql_query('query to insert data into first table')
// check insertion worked
mysql_query('query to select record from first table') // have to use data used for insertion to find the new record
// now we should have ID number of the record
mysql_query('query to insert data (with referencing ID) into second table')
// check insertion worked

 

I've been working on some software similar to this for years for myself (as a hoobyist) and I think you would need to have a field in each table which could be a 'reference' field. If you were to use say, an auto-incrementing field as the reference id field, you would require pretty strict control in your code to ensure those fields start synchronized and remain that way.

[Doge]

I dont know if this is what you are trying to do,but couldn't you add the entry to the first table so the ID number is assigned, then select that record from the first table using the data you used to insert it and get it's ID number, then use the ID number to make a reference to that record from the other table?

 

Something like:

mysql_query('query to insert data into first table')
// check insertion worked
mysql_query('query to select record from first table') // have to use data used for insertion to find the new record
// now we should have ID number of the record
mysql_query('query to insert data (with referencing ID) into second table')
// check insertion worked

 

I've been working on some software similar to this for years for myself (as a hoobyist) and I think you would need to have a field in each table which could be a 'reference' field. If you were to use say, an auto-incrementing field as the reference id field, you would require pretty strict control in your code to ensure those fields start synchronized and remain that way.

 

So in lieu of not having an auto-increment field in your table, how would you get a sequence of reference numbers, e.g. Invoice numbers starting at 1 and going upwards without auto-incrementing?

 

The thing is the invoice number 1 might need to be on mulitple lines (e.g. multiple sales items in one invoice), so it can't be a unique ID for each line, but I would still need to ensure invoices are numbered sequentially. Would this be something to be done in PHP?

[Doge]

What would be the code to add +1 to the previous largest number in the reference column - i have this code but it isn't working:

 

<?
$username="hidden";
$password="hidden";
$database="simplebooks";

$reference=$query1;
$date=$_POST['date'];
$narration=$_POST['narration'];
$dr_account=$_POST['dr_account'];
$dr_amount=$_POST['dr_amount'];
$cr_account=$_POST['cr_account'];
$cr_amount=$_POST['cr_amount'];

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$query1 = "SELECT reference FROM ledger";

return mysql_num_rows(mysql_query($query1)) + 1;

$query2 = "INSERT INTO ledger VALUES ('$reference','$date','$narration','$dr_account','$dr_amount','$cr_account','$cd_amount')";

mysql_query1($query1);
mysql_query($query2);

mysql_close();
?>

[Catfish]

If you want to find the largest value of the field 'reference' in the database table 'ledger', you would need to change the SQL query ($query1) to select the largest value of reference. Something like: SELECT reference FROM ledger ORDER BY reference DESC LIMIT 1

should return the largest value of reference. if you need to increment that value, you would have to use mysql_fetch_assoc() or mysql_fetch_row() on the result of the mysql_query call to get to the value. eg:

 

<?php
if ($dbLink = mysql_connect(localhost,$username,$password))
{
  @mysql_select_db($database) or die( "Unable to select database");

  $query1 = "SELECT reference FROM ledger ORDER BY reference DESC LIMIT 1";
  if ($dbResult = mysql_query($query1))
  {
    $row = mysql_fetch_assoc($dbResult);
    print_r($row); // just outputs what is in row (for your debugging)
    $nextReference = $row['reference'] + 1;
  }
  else
    print('Could not select data from database. Error given: '.mysql_error().'<br/>."\n");
}
else
  print('Could not connect to database. Error given: '.mysql_error().'<br/>."\n");
?>

 

I just thought of other option which might be easier - you could select the largest value of reference using the SQL but might also be able to increment the value in the SQL query so it removes the need to do that in PHP.

 

In your previous code, mysql_num_rows(), retrieves the number of rows from a result set. So say you searched for records where reference was > 0 and < 9, and there is enough data in the table to be beyond record #9, mysql_num_rows would return a value of 8 because there are 8 records or results returned by the query. You need to look up mysql_fetch_ functions (there's a few of them that differ slightly).

[PFMaBiSmAd]

On service/interrupt driven multitasking operating systems, you cannot guarantee the order in which concurrent queries will be executed due multiple visitors on your site. Nothing that attempts to get the current highest row number/id can guarantee that it will be the value that was just inserted or that adding one to it will result in an unused value by the time a query is executed, which is why the mysql_insert_id() function that I posted in the first reply exists, because it WILL return the ID of the row that was just inserted by the current invocation of your script.

 

 

[atl_andy]

Here's another option for using one table, that holds all transactions together.  Have a master transaction number that auto increments, JOU0001, JOU0002, etc. along with the transaction ID.  Then you have a sequence number table for each transaction type, Sales sequence, Invoice sequence, which would populate a FK field to each table.  SAL0001 linked to JOU0001, INV0001 linked to JOU0002, SAL0002 linked to INV0003.  I work on a ERP system that uses this type of model for journal entries.  It's quite a bit more complex since it has an inventory function that writes to the GL as well.  Your best bet for data integrity would be to use one large table, IMHO.

[Doge]

Here's another option for using one table, that holds all transactions together.  Have a master transaction number that auto increments, JOU0001, JOU0002, etc. along with the transaction ID.  Then you have a sequence number table for each transaction type, Sales sequence, Invoice sequence, which would populate a FK field to each table.  SAL0001 linked to JOU0001, INV0001 linked to JOU0002, SAL0002 linked to INV0003.  I work on a ERP system that uses this type of model for journal entries.  It's quite a bit more complex since it has an inventory function that writes to the GL as well.  Your best bet for data integrity would be to use one large table, IMHO.

 

I like this idea, the only problem I can see though is that if you want to add lots of information about sales invoices, e.g. multiple items, customer information, etc... you would get a very large table???

 

 

 

 

[shawnz]

Smaller off the shelf (QuickBooks/Peachtree) accounting systems use loose references not implementing foreign keys.  Relations are logic implemented, but this is not recommended in the database world.

 

I did a general ledger system (double entry) where debits were + and credits were -.  I had a transaction record that had many child transaction entry records.  Each record referenced and account, which was flagged for each account type (asset, liability, revenue, expense, and equity (-2 thru 2).  This was done using foreign keys.

 

To reference invoices, I added columns that had foreign keys pointing to the respective document headers and row.  The document headers and rows just augmented what was in the journal to create a highly efficient database model (less reproduction).

 

For concurrency issues, you can date/time stamp the row with the user.  If the record snapshot your viewing does not match the databases, the you dont have a current copy.  Other accounting systems (QuickBooks and Peachtree) include a record version number that is incremented on each write.

 

You can do fun things with foreign keys: one to one relationships allow for an "object style view of data".  For example, you have contacts, and then customers extend contacts.  The ID for the customer has a foreign key to the ID for the contact.  This ensures that only one customer per contact, and that the contact exists for the customer to exists.

 

Also, make sure to use transactions, if an entry fails when updating the multiplicity of tables, the changes made can be rolled back ensuring a consistent data set.

 

Shawn Zernik

www.internetworkconsulting.net