Using tabels in MySQL
Moderator: General Moderators
Using tabels in MySQL
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?
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
look here viewforum.php?f=28 and read the rest of the forum for examples..... just search
Re: Using tabels in MySQL
Ok, now I have solved some of my issues but I'm stuck...
Here is my HTML code in my input file:
And this is my PHP file:
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?
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>
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';
?>
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
all of this :
is incorrect... have multiple errors.... the select is wrong... the insert is wrong and both unnecessary according to your objectives... you need more reading
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
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());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
I have done some reading... 
This code work almost like i want it to:
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?
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';
?>
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
yes I have a clue.... you need MORE reading!! 
As I told you in previous post.... you don't need this
and even if you decide to use it (I can't imagine why you will do that)... the next line
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)
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());Code: Select all
$g = $row['Saldo'];
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
I will do some more reading.
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.
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
well... I didn't read your update fully... my fault now 
just replace this:
for this:
once you have your code working... you must incorporate some validations to be sure that you are using "clean" values from your $_GET's
just replace this:
Code: Select all
$saldo = $g + ($e - $f);
$result = mysql_query("UPDATE Kontoplan SET Saldo='$saldo' WHERE Kontonummer='$c'") Code: Select all
$result = mysql_query("UPDATE Kontoplan SET Saldo= Saldo + '$e' - '$f' WHERE Kontonummer='$c'")