LinuxQuestions.org

LinuxQuestions.org (/questions/)
-   Programming (http://www.linuxquestions.org/questions/programming-9/)
-   -   MySql and a simple Bank Account DB (http://www.linuxquestions.org/questions/programming-9/mysql-and-a-simple-bank-account-db-839465/)

air4time 10-21-2010 01:39 AM

MySql and a simple Bank Account DB
 
OK here is what I have


~$ php -v
PHP 5.2.10-2ubuntu6.4 with Suhosin-Patch 0.9.7 (cli) (built: Jan 6 2010 22:41:56)

mysql Ver 14.14 Distrib 5.1.37, for debian-linux-gnu (i486)

Server version: Apache/2.2.12 (Ubuntu)
Server built: Mar 9 2010 21:20:44

Ubuntu 9.10

I want to set up a simple account program. I need to know how to have a total funds that when I input a payment and/or deposit it updates . I have tried to find a way but have not found one yet. I have tried phpMyAdmin and MySQL administrator . I have used Seagate Database Software to pull data from data bases so I have a little under standing on how it works. I need 3 tables to have relations. "Total" being the "main" one and deposit and/or payment being the other two. I even bought a dummies book on the subject but can't seem to find the answer there.

Thanks

yancek 10-21-2010 01:20 PM

Do you have the server installed, Ubuntu, Apache, Mysql, php/perl? If not, this site explains it.

https://help.ubuntu.com/community/ApacheMySQLPHP

If that doesn't help, try googling "ubuntu lamp server howto". You should get a number of sites. Have you configured mysql/php/apache? What point are you at and what problems have you had. Creating tables and scripts for what you say you want to do should not be that difficult. I don't really understand where the problem is?

air4time 10-21-2010 02:29 PM

Thanks yancek for your help

Quote:

What point are you at and what problems have you had
Ok I have the DB set up with 1 table and 9 fields and the 3 of fields (Balance-withdraw-payments) is where I am having trouble. I know what I want them to do but am unsure of how to do it. I thought this could be done in one of the GUIs I mention but could not find how to make ( I think this is what it is called) the relation between the 3 of them. Decrease the bal with payment and increase with deposit. But if it can not be done in a GUI I am fine with. Just when I started making this sites said to use "Phpmyadmin" said that was the way for beginners.

Quote:

Creating tables and scripts for what you say you want to do should not be that difficult.
That is what I thought at first but here we are.

Quote:

Do you have the server installed, Ubuntu, Apache, Mysql, php/perl?
Yes

Quote:

Have you configured mysql/php/apache?
Yes

Quote:

If that doesn't help, try googling "ubuntu lamp server howto".
Am doing that now Thanks

FYI I can access a website and phpmyadmin hosted on this pc from any pc within my local network.

yancek 10-23-2010 07:12 PM

I used PhpMyAdmin a little and it seemed to work well. Not really that familiar with it and haven't used it for a while. I'm wondering if GnuCash might be a better solution. It might be too much as it seems to be created for more complicated situations.

if you have your table, call it acctg and fields balance, withdraw, payment you can go to a terminal and log in to mysql, at the prompt: mysql> SELECT SUM(payment-withdraw) from acctg;

This should give you a balance. I'm not sure if this is what you are looking for because it will total all the payments in the column, total all the withdraw in the columns, then subtract withdraw from payments and give you a balance. Obviously, it doesn't insert the result into the balance columnd so you will then need an Insert statement to insert the balance in your table. If you just want to keep an ongoing total of balance, this should work. If you want to just keep the current entry in balance, you should use the update command.

This is actually a little more complicated than I thought as I guess I did not read your initial post thoroughly. I have little experience at mysql or php so don't know that I will be of any further help.

Good Luck and if you haven't, check out GnuCash.

air4time 10-23-2010 07:41 PM

Thanks yancek

But I am wanting to do more with MySQL. I think if I can get this working I would have a better understanding of MySQL for the other stuff. I have (I think) all the basic stuff working. I have a simple website running that can be seen from any pc on my network and I can work on MySQL from any pc with Phpmyadmin. Just not sure how to config the DB to do what I want. I need it to be where I can read and write to it via a php website but before I even get to the website design I have no ideal what to do with the fields as far as set up, at least the 3(bal,payment and deposit). My goal is to be able to logged in a site (local) and post to the DB and it update and give me the bal.



Maybe I am starting something to advance if so does anyone have a suggestion of a easier DB to do first?

Thanks

yancek 10-24-2010 12:53 AM

Quote:

at least the 3(bal,payment and deposit)
Is that a typo? Earlier you said balance, payment withdrawal?
Do you have any php scripts to insert the initial data to payment, withdrawal?

air4time 10-25-2010 02:04 AM

Thanks yancek

Quote:

Is that a typo? Earlier you said balance, payment withdrawal?
No sry Withdraw was.

Quote:

Do you have any php scripts to insert the initial data to payment, withdrawal?
No I was thinking needed to get the DB set up right first but again I do not know what I am doing :-).

Thanks Lonnie

Mark1986 10-25-2010 10:17 AM

It's easier to test your database with some data in it already.

You could download the Northwind database and convert it to MySQL. Then you have a sample database with some data in it. At least, this is what you can do if you are just practising.

Can you be a bit more specific what you want to do? E.g. I want deposit - withdrawal into balance, etc. If you want, you can use the Mysql Workbench. That's a free tool in which you can not only model your database, query it but also administrate it. I like using it for one.

If you need help with advancing in queries, just let me know so I can guide you in the right direction(s).

air4time 10-25-2010 10:10 PM

Thanks Mark1986


Quote:

If you want, you can use the Mysql Workbench.
I am not where I can install that but will check it out.

Quote:

You could download the Northwind database and convert it to MySQL. Then you have a sample database with some data in it.
Great ideal while trying to find that I found this I am able to install the DB but will have to wait to do the PHP stuff, I am in another city and do not have full access to the pc with MySQL installed via internet.

Quote:

Can you be a bit more specific what you want to do? E.g. I want deposit - withdrawal into balance, etc.
Sure,
I am unsure how to set up a DB that can keep a running total of how much money is available after either or both a withdraw and / or deposit. Should it be 3 tables with at least 2 fields or can it be in one table with many fields? I thought this would be easy but when I started the DB was way more options then I expected.Like field type?Collation type?Attributes type? AUTO_INCREMENT?to name a few.

Like I said before I even got a dummy book but it is not much help for what I want to do. Know of a good book for both PHP and MySQL? Or one for each?

Quote:

At least, this is what you can do if you are just practising.
Yes that is all I am wanting to do is practice. I would like in the end be able to make a POS DB but that is a ways off as I am having trouble with what some say is a simple thing to do. :-)

Thanks Lonnie

yancek 10-26-2010 12:49 AM

First you create the database. You can do this in a terminal by logging in to mysql once you have it set up:

mysql -u air4time -p (you would need to change air4time to whatever user you actually have, the -p is if you are using a password, after entering this, you are prompted for the password, enter it and hit the enter key. When you enter the password, nothing shows on screen, no asterisks)

You should then see something like this. I'm using version 5.0.45 on PCLinuxOS so yours will be different:
Quote:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2611
Server version: 5.0.45 PCLinuxOS - MySQL Standard Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>
At the mysql> prompt, you first create the database, then at the mysql prompt type use database name (whatever name you used) then create the table with the three fields you wanted (balance,withdrawal,payment). The links below explain how to do this and give good examples.

When you have this done, you will be able to insert information. Again, the sites below give good examples/explanations.

Two sites that are good for beginners for both mysql and php are linked below:

http://www.w3schools.com/
http://www.tizag.com/

tizag also has a forum: http://forums.tizag.com/

Another good source is php.net, they have a lot of sample code.

Setting up the tables/fields should not be difficult. I think the problem will be with the php scripts. The entry I posted above works from the mysql prompt to get and display a balance as I explained it. It's definitely not as easy as I initially thought it would be, at least for someone with minimal experience which includes myself. Good Luck.

Mark1986 10-26-2010 02:54 AM

Eeehm, here's what I would do.

Create a table deposit with fields id (primary key, auto-numbering), amount, date (datetime), source (who or what gave you that amount).
Create a table balance with fields id (primary key, auto-numbering), amount, date (datetime).

Now you can use the table deposit for both deposits and withdrawals. In case of a withdrawal just put a '-' in front of the amount.

For practice, try writing a script that randomly enters 30 records in the first table. Keep 1 date per upload, at max 5 different sources and let the amount be of decimal with 3 digits after the comma.(This will learn you to use INSERT INTO.)

Next, try writing a script that rounds the amounts into decimals with 2 digits. (This will learn you the UPDATE statement.)

Next, write a stored procedure to update the balance every hour or 30 minutes. (This will learn you stored procedures.)

Next, try to write a script that deletes all rows from deposit older than a month. (This will learn you the DELETE statement.) Note: In my field of work you are just not allowed to delete rows. You're supposed to add a field 'deleted' and mark it as 1. This way you never lose data.

And last but not least, check the size of your database and how much time your queries take. See what you can do to trim both down, but keep in mind that for example compressing your database will increase the time your queries need to run.

If you need help, I can show you a PHP script that inserts some random values into a table. If you wanted something else or something more particular or advanced, please let me know that too.

air4time 10-26-2010 03:23 PM

ok Mark1986

Quote:

Create a table deposit with fields id (primary key, auto-numbering), amount, date (datetime), source (who or what gave you that amount).
Create a table balance with fields id (primary key, auto-numbering), amount, date (datetime).
mysql> describe balance;
+--------+--------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+-------------------+----------------+
| id | mediumint(9) | NO | PRI | NULL | auto_increment |
| amount | int(11) | NO | | NULL | |
| date | timestamp | NO | | CURRENT_TIMESTAMP | |
+--------+--------------+------+-----+-------------------+----------------+
3 rows in set (0.01 sec)

mysql> describe deposit;
+--------+--------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+-------------------+----------------+
| id | mediumint(9) | NO | PRI | NULL | auto_increment |
| amount | int(11) | NO | | NULL | |
| date | timestamp | NO | | CURRENT_TIMESTAMP | |
| source | int(11) | NO | | NULL | |
+--------+--------------+------+-----+-------------------+----------------+
4 rows in set (0.00 sec)

mysql>

Does that look right?

Quote:

For practice, try writing a script that randomly enters 30 records in the first table. Keep 1 date per upload, at max 5 different sources and let the amount be of decimal with 3 digits after the comma.(This will learn you to use INSERT INTO.)
Ok this went straight over my head.

Quote:

If you need help, I can show you a PHP script that inserts some random values into a table.
Yes that would help thanks.

Quote:

http://www.w3schools.com/
http://www.tizag.com/

tizag also has a forum: http://forums.tizag.com/

Another good source is php.net, they have a lot of sample code.

.
Thanks yancek I went to them sites.

Quote:

Setting up the tables/fields should not be difficult. I think the problem will be with the php scripts.
I agree.

Mark1986 10-26-2010 05:14 PM

Here's an example:

PHP Code:

<?php

error_reporting
(-1);

function 
generate_name($length) {
    
srand(((double)microtime()) * 1000000);
    
$string='';
    
    
$chars='abcdefghijklmnopqrstuvwxyz';
    
$chars.='ABCDEFGHIJKLMNOPQRSTUWXYZ';
    
    for(
$i=0$i $lengte$i++) {
        
$string.=$chars{rand(0, (strlen($chars) - 1))};
        }
    return 
$string;
    } 

function 
generate_pass($lengte) {
    
srand(((double)microtime()) * 1000000);
    
$string='';
    
    
$chars='abcdefghijklmnopqrstuvwxyz';
    
$chars.='ABCDEFGHIJKLMNOPQRSTUWXYZ';
    
$chars.='01234567890123456789';
    
    for(
$i=0$i $lengte$i++) {
        
$string.=$chars{rand(0, (strlen($chars) - 1))};
        }
    return 
$string;
    } 

$db_conn mysql_connect("localhost"xxxxxx) or die("Could not connect to database."); 

mysql_select_db("ik"$db_conn) or die("Uh oh!");

$i=1;

while (
$i 21) {

    
$name=generate_name(6);
    
$pass=generate_pass(6);
    
$md5ww=md5($ww);
    
    
$sql="INSERT INTO user (profiel, ww, email, actief, ww_temp) VALUES ('".$name."', '".$pas."', 'temp@email.com', '0', '".$ww."')";
    
    
mysql_query($sql) or die("Query could not be executed - "mysql_error());
    
    
$i++;
}

mysql_close($db_conn);

echo 
'Done executing queries';

?>

Change the number in the line
PHP Code:

while ($i 21) { 

to any number you like to enter that many recods minus one.

This example should help you to get a query made for your table.

If there is anyone who has improvements, all advice is welcome.

For the OP: what I always did at the start was creating the SQL statements first. Once I had those, I started creating a PHP file to fill the query. Or add the query to an existing PHP file.

Good luck, let me know if you need more explaining or something.

Edit: you did create the right tables. All looks fine to start with.

air4time 10-26-2010 06:11 PM

thanks Mark1986

Ok tried it but did not work
Code:

$db_conn = mysql_connect("localhost", xxx, xxx) or die("Could not connect to database.");

mysql_select_db("ik", $db_conn) or die("Uh oh!");

Had to edit that

Code:

function generate_name($length) {
    srand(((double)microtime()) * 1000000);
    $string='';
   
    $chars='abcdefghijklmnopqrstuvwxyz';
    $chars.='ABCDEFGHIJKLMNOPQRSTUWXYZ';
   
    for($i=0; $i < $lengte; $i++) {

Changed "lengte" to "length" to match with above it

Code:

while ($i < 21) {

    $name=generate_name(6);
    $pass=generate_pass(6);
    $md5ww=md5($ww);
   
    $sql="INSERT INTO user (profiel, ww, email, actief, ww_temp) VALUES ('".$name."', '".$pas."', 'temp@email.com', '0', '".$ww."')";

I think this part is wrong for my DB? Here is the errors I get

Quote:

Notice: Undefined variable: ww in /xxx/xxx/xxx//www/trytest.php on line 42

Notice: Undefined variable: pas in /xxx/xxx/xxx//www/trytest.php on line 44

Notice: Undefined variable: ww in /xxx/xxx/xxx/www/trytest.php on line 44
Query could not be executed - Table 'testchecking.user' doesn't exist
/xxx/xxx/xxx/ is what I put to hide the actual path. Trytest.php is the name of the file. I do not have a Table named user so I think that is the problem.I can comment out and remove some stuff there but still will give me this error
Quote:

Query could not be executed - Table 'testchecking.user' doesn't exist

Here is my DB
Quote:

mysql> describe balance;
+--------+--------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+-------------------+----------------+
| id | mediumint(9) | NO | PRI | NULL | auto_increment |
| amount | int(11) | NO | | NULL | |
| date | timestamp | NO | | CURRENT_TIMESTAMP | |
+--------+--------------+------+-----+-------------------+----------------+
3 rows in set (0.01 sec)

mysql> describe deposit;
+--------+--------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+-------------------+----------------+
| id | mediumint(9) | NO | PRI | NULL | auto_increment |
| amount | int(11) | NO | | NULL | |
| date | timestamp | NO | | CURRENT_TIMESTAMP | |
| source | int(11) | NO | | NULL | |
+--------+--------------+------+-----+-------------------+----------------+
4 rows in set (0.00 sec)

mysql>
Thanks Lonnie

Mark1986 10-27-2010 02:32 AM

Yes, you are right. I am Dutch and most of the variables and other names are in Dutch. I figured to change them to English before posting. Apparently, not my best work ever ;).

Here is the changed bit, so it should work.

PHP Code:

while ($i 21) {

    
$name=generate_name(6);
    
$pass=generate_pass(6);
    
$md5ww=md5($pass);
    
    
$sql="INSERT INTO user (profiel, ww, email, actief, ww_temp) VALUES ('".$name."', '".$md5ww."', 'temp@email.com', '0', '".$pass."')";
    
    
mysql_query($sql) or die("Query could not be executed - "mysql_error());
    
    
$i++;


This should work now. Can't test this at the moment, since I am at work.

I hope this will help you.


All times are GMT -5. The time now is 06:11 PM.