Page 1 of 1

Using tabels in MySQL

Posted: Tue May 04, 2010 7:06 am
by asai
Hi,
I am trying to make a simple web based program that gets and puts data from/into a MySQL database.
Anyone know of a good tutorial on how to get info from one tabel and use it in another from a PHP script?

Re: Using tabels in MySQL

Posted: Tue May 04, 2010 8:40 am
by mikosiko
look here viewforum.php?f=28 and read the rest of the forum for examples..... just search

Re: Using tabels in MySQL

Posted: Wed May 05, 2010 10:58 am
by asai
Ok, now I have solved some of my issues but I'm stuck... 8O

Here is my HTML code in my input file:

Code: Select all

<html>
<head>
<title>Bilagsregistrering</title>
</head>
<FONT FACE="Verdana, sans-serif">
<h2>BILAGSREGISTRERING</h2>
<form method="get" action="legginn_bilag.php">

<TABLE WIDTH=30% BORDER=0 CELLPADDING=0 CELLSPACING=0 STYLE="page-break-before: always">
<COL WIDTH=128*>
<COL WIDTH=128*>

<TR VALIGN=TOP>
<TD WIDTH=50%>
<p>Dato: </p></TD>

<TD WIDTH=50%>
<P><input type="text" size="10" maxlength="10" name="dato"></P>
</TD>
</TR>

<TR VALIGN=TOP>
<TD WIDTH=50%>
<p>Periode: </p>

<TD WIDTH=50%>
<p><input type="text" size="2" maxlength="2" name="periode"></p>
</TD>
</TR>

<TR VALIGN=TOP>
<TD WIDTH=50%>
<p>Konto: </p>

<TD WIDTH=50%>
<p><input type="text" size="4" maxlength="4" name="konto"></p>
</TD>
</TR>

<TR VALIGN=TOP>
<TD WIDTH=50%>
<p>Tekst: </p>

<TD WIDTH=50%>
<p><input type="text" size="30" maxlength="30" name="tekst"></p>
</TD>
</TR>

<TR VALIGN=TOP>
<TD WIDTH=50%>
<p>Debet: </p>

<TD WIDTH=50%>
<p><input type="text" size="10" maxlength="10" name="debet"></p>
</TD>
</TR>

<TR VALIGN=TOP>
<TD WIDTH=50%>
<p>Kredit: </p>

<TD WIDTH=50%>
<p><input type="text" size="10" maxlength="10" name="kredit"></p>
</TD>
</TR>

</TABLE>


<br>
<p><input type="submit" name="Submit" value="Skriv til database"></p>
<input type="reset" value="Nullstill" />
</form>
</FONT>
</body>
</html>
And this is my PHP file:

Code: Select all

<?php

$a = $_GET['dato'];
$b = $_GET['periode'];
$c = $_GET['konto'];
$d = $_GET['tekst'];
$e = $_GET['debet'];
$f = $_GET['kredit'];

include 'config.php';
include 'opendb.php';

mysql_query("INSERT INTO Bilagsregister 
(Dato, Periode, Konto, Tekst, Debet, Kredit) VALUES('$a', '$b', '$c', '$d', '$e', '$f' ) ") 
or die(mysql_error());  

$result = mysql_query("SELECT $c FROM Kontoplan") 
or die(mysql_error());
while($row = mysql_fetch_array( $result ))

$g = $row['Saldo'];
$saldo = $g + $e;
$saldo2 = $g - $f;

mysql_query("INSERT INTO Kontoplan 
(Saldo) VALUES('$saldo' ) ") 
or die(mysql_error());


include 'closedb.php';
?>
I'm trying to take the value in the Debit and Kredit rows og the table Bilagsregistrering and fetch the row with Saldo from the Kontoplan table.
Then put the sum of this back to the Saldo row in Kontoplan table.
With this code the amount from Debet is put into a new Konto 0...
Any suggestions?

Re: Using tabels in MySQL

Posted: Wed May 05, 2010 12:38 pm
by mikosiko
all of this :

Code: Select all

$result = mysql_query("SELECT $c FROM Kontoplan") 
or die(mysql_error());
while($row = mysql_fetch_array( $result ))

$g = $row['Saldo'];
$saldo = $g + $e;
$saldo2 = $g - $f;

mysql_query("INSERT INTO Kontoplan 
(Saldo) VALUES('$saldo' ) ") 
or die(mysql_error());
is incorrect... have multiple errors.... the select is wrong... the insert is wrong and both unnecessary according to your objectives... you need more reading :wink:

all the above should be done with an UPDATE sentence.

in addition... which is the PK (Primary Key) of your table Bilagsregister ?, how this table is related with your table Kontoplan? you need to know it to be able to select or update the appropriated recod

Re: Using tabels in MySQL

Posted: Thu May 06, 2010 6:47 am
by asai
I have done some reading... ;)

This code work almost like i want it to:

Code: Select all

<?php

$a = $_GET['dato'];
$b = $_GET['periode'];
$c = $_GET['konto'];
$d = $_GET['tekst'];
$e = $_GET['debet'];
$f = $_GET['kredit'];

include 'config.php';
include 'opendb.php';

mysql_query("INSERT INTO Bilagsregister 
(Dato, Periode, Konto, Tekst, Debet, Kredit) VALUES('$a', '$b', '$c', '$d', '$e', '$f' ) ") 
or die(mysql_error());  

$result = mysql_query("SELECT * FROM Kontoplan") 
or die(mysql_error());

$g = $row['Saldo'];

$saldo = $g + ($e - $f);

$result = mysql_query("UPDATE Kontoplan SET Saldo='$saldo' WHERE Kontonummer='$c'") 
or die(mysql_error());

include 'closedb.php';
?>
Theres only one thing that doesn't work as expected:
The sum of Saldo and Debet/Kredit.
I get to update the Saldo record in Kontoplan, but only with the number input from the field Debet or Kredit.
So lets say I have 10000 in Saldo in the Kontoplan table and then put in 1500 in the Debet field in Bilagsregistrering, the Saldo should be updated to 11500.
And with 1500 in the Kredit instead, it should read 8500 after the update.
Now it reads either 1500 or -1500...

The primary key in Bilagsregistrering is Bilagsnummer. This is a auto increment number.
I haven't found any understandable on relations between tables. Any qlues?

Re: Using tabels in MySQL

Posted: Thu May 06, 2010 9:01 am
by mikosiko
yes I have a clue.... you need MORE reading!! :)

As I told you in previous post.... you don't need this

Code: Select all

$result = mysql_query("SELECT * FROM Kontoplan") 
or die(mysql_error());
and even if you decide to use it (I can't imagine why you will do that)... the next line

Code: Select all

$g = $row['Saldo'];
doesn't mean anything.... from where are you getting this value $row['Saldo'] ?..... is not present in your code

just delete those 2 lines and work a little more with your UPDATE clause.

but pay attention to that update... if you have only 1 record in your table Kontoplan it will work... if you have more than one record... do you want to update every single record there or only one specific record (dor that your need to use the PK)

Re: Using tabels in MySQL

Posted: Fri May 07, 2010 12:34 am
by asai
I will do some more reading. :oops:

The table Kontoplan is not only one record. With my code i was able to update one specific record, the only trouble is adding the sum of Debet/Kredit from the HTML input file to Saldo in Kontoplan.

Re: Using tabels in MySQL

Posted: Fri May 07, 2010 8:08 am
by mikosiko
well... I didn't read your update fully... my fault now :)

just replace this:

Code: Select all

$saldo = $g + ($e - $f);
$result = mysql_query("UPDATE Kontoplan SET Saldo='$saldo' WHERE Kontonummer='$c'") 
for this:

Code: Select all

$result = mysql_query("UPDATE Kontoplan SET Saldo= Saldo + '$e' - '$f' WHERE Kontonummer='$c'")
once you have your code working... you must incorporate some validations to be sure that you are using "clean" values from your $_GET's