Using tabels in MySQL

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
asai
Forum Commoner
Posts: 43
Joined: Tue May 04, 2010 6:24 am
Location: Norway

Using tabels in MySQL

Post 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?
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Using tabels in MySQL

Post by mikosiko »

look here viewforum.php?f=28 and read the rest of the forum for examples..... just search
asai
Forum Commoner
Posts: 43
Joined: Tue May 04, 2010 6:24 am
Location: Norway

Re: Using tabels in MySQL

Post 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?
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Using tabels in MySQL

Post 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
asai
Forum Commoner
Posts: 43
Joined: Tue May 04, 2010 6:24 am
Location: Norway

Re: Using tabels in MySQL

Post 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?
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Using tabels in MySQL

Post 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)
asai
Forum Commoner
Posts: 43
Joined: Tue May 04, 2010 6:24 am
Location: Norway

Re: Using tabels in MySQL

Post 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.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Using tabels in MySQL

Post 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
Post Reply