Php Post to two tables in Mysql

Question

I'm trying to POST to two tables at the same time. I'm trying to get the DonorID to display in to another table under $description. I'm able to just write any text in the $description, but I need it to be dynamic not static, which is what the text is. I have two tables; the first is accounting and the second is donations. I'm trying to alter the $description='Donation from Donor'; and have the donor that made the transaction be listed where the Donor is. Any suggestions would be greatly appreciated.

Here is my code:

<?php

  $dbserver = "localhost";
  $dblogin = "root";
  $dbpassword = "";
  $dbname = "";

  $date=$_POST['date'];
  $firstname=$_POST['firstname'];
  $lastname=$_POST['lastname'];
  $middleinitial=$_POST['middleinitial'];
  $organization=$_POST['organization'];
  $donorid=$_POST['donorid'];
  $paymenttype=$_POST['paymenttype'];
  $nonmon=$_POST['nonmon'];
  $event=$_POST['event'];
  $Income=$_POST['Income'];
  $account='Revenue';
  $description='Donation from Donor';
  $transactiontype='Income';
  $Expense='0.00';

  $con = mysql_connect("$dbserver","$dblogin","$dbpassword");
    if (!$con)
  {
  die('Could not connect to the mySQL server please contact technical support 
           with the following information: ' . mysql_error());
  }

  mysql_select_db("$dbname", $con);


  $sql = "INSERT INTO donations (date, firstname, middleinitial, lastname, 
            organization, donorid, paymenttype, nonmon, Income, event)  

  Values        
            ('$date','$firstname','$middleinitial','$lastname','$organization',  
             '$donorid','$paymenttype','$nonmon','$Income','$event')";


  $sql2 = "INSERT INTO accounting (date, transactiontype, account, 
              description, Income, Expense)

  VALUES ('$date','$transactiontype','$account','$description','$Income','$Expense')";

  mysql_query($sql2);


    if (!mysql_query($sql,$con))
  {

    die('Error: ' . mysql_error());

  }
  echo "1 record added";

  mysql_close($con);

  header( 'Location: http://localhost/donations.php' ) ;
  ?>

you are not executing query `$sql` also i would not use `mysql_` for new project, please switch to `mysqli` or `PDO` — Fabio, Apr 12 '13 at 05:25

Fabio · Answer 1 · 2013-04-12T05:53:50.967

As i said i would personaly use mysqli for new project, here a sample of you code with mysqli:

       $dbserver = "localhost";
       $dblogin = "root";
       $dbpassword = "";
       $dbname = "";

       $date=$_POST['date'];
       $firstname=$_POST['firstname'];
       $lastname=$_POST['lastname'];
       $middleinitial=$_POST['middleinitial'];
       $organization=$_POST['organization'];
       $donorid=$_POST['donorid'];
       $paymenttype=$_POST['paymenttype'];
       $nonmon=$_POST['nonmon'];
       $event=$_POST['event'];
        $Income=$_POST['Income'];
       $account='Revenue';
       $description='Donation from Donor';
       $transactiontype='Income';
       $Expense='0.00';

        //opening connection
        $mysqli = new mysqli($dbserver, $dblogin, $dbpassword, $dbname);
        if (mysqli_connect_errno()) 
        {
            printf("Connection failed: %s\n", mysqli_connect_error());
            exit();
        }

        $sql = "INSERT INTO `donations` (`date`, `firstname`, `middleinitial`, `lastname`, `organization`, `donorid`, `paymenttype`, `nonmon`, `Income`, `event`) Values  ('$date','$firstname','$middleinitial','$lastname','$organization', '$donorid','$paymenttype','$nonmon','$Income','$event')";

        $sql2 = "INSERT INTO `accounting` (`date`, `transactiontype`, `account`, `description`, `Income`, `Expense`) VALUES ('$date','$transactiontype','$account','$description','$Income','$Expense')";

        $query1 = $mysqli->query($sql) or die($mysqli->error.__LINE__);
        $query2 = $mysqli->query($sql2) or die($mysqli->error.__LINE__);

        //closing connection
        mysqli_close($mysqli);

        header( 'Location: http://localhost/donations.php' ) ;

UPDATE you can add donorid simply placing both vars in the query like:

        $sql2 = "INSERT INTO `accounting` (`date`, `transactiontype`, `account`, `description`, `Income`, `Expense`) VALUES ('".$date."','".$transactiontype."','".$account."','".$donorid . " " . $description."','".$Income."','".$Expense."')";

this way i just separate donorid and description with a space but you can add anything you want to in plain text:

'".$donorid . " - " . $description."'

OK, I've made the change, but do you have a suggestion on getting the donorid to appear in the description? — Dustin Vicent, Apr 12 '13 at 05:41

score 0 · Answer 2 · answered Apr 12 '13 at 05:25

0

After this

$sql = "INSERT INTO donations (date, firstname, middleinitial, lastname, 
        organization, donorid, paymenttype, nonmon, Income, event)  

        Values        
        ('$date','$firstname','$middleinitial','$lastname','$organization',  
         '$donorid','$paymenttype','$nonmon','$Income','$event')";

put

mysql_query($sql);

Please execute the query.

answered Apr 12 '13 at 05:25

chandresh_cool

11,753
3
30
45

Ok, I added the mysql_query($sql); but how to do I make the Description dynamic and make sure that the donorid gets put there? What syntax would I Use? – Dustin Vicent Apr 12 '13 at 05:39
then you to run a select query on donor id and then use that donation id and add it to your decription variable. – chandresh_cool Apr 12 '13 at 05:45

score 0 · Answer 3 · edited May 23 '17 at 11:49

0

Things I see is ..

First your just executing your $sql2 but not the other $sql statement

Another is while inserting you declared some columns name that is a mysql reserved word (date column)

you should have `` backticks for them..

Refer to this link MYSQL RESEERVED WORDS

additional note: Your query is also vulnerable to sql injection

SQL INJECTION

How to prevent SQL injection in PHP?

edited May 23 '17 at 11:49

Community

1
1

answered Apr 12 '13 at 05:28

Jhonathan H.

2,734
1
19
28

I've done a bit of research on SQL Injection.. i've ran the '); drop table donations ; -- and nothing happened. Except that it said that there was a Mysql error statement. Can you show me what type of injection I'm vulnerable to? – Dustin Vicent Apr 14 '13 at 01:42

Mo Adel · Answer 4 · 2013-04-12T18:00:07.233

You will have to split $sql2 to 2

1st :-

 $sql2 = "INSERT INTO accounting (description) SELECT * FROM donations WHERE donorid='$donorid'"

then another one

"UPDATE accounting SET date='', transactiontype='', account ='', Income='', Expense ='' WHERE description=(SELECT * FROM donations WHERE donorid='$donorid')"

that will take all the information from donoation for the given donorid and list it under description in accounting

score 0 · Answer 5 · answered Apr 12 '13 at 05:44

0

Just write after insert on trigger on first table to insert data into another table.

answered Apr 12 '13 at 05:44

Ajeet Singh

32
2

Php Post to two tables in Mysql

5 Answers5