CODEWITHSUNDEEP
phpmysql
Dustin Vicent
Dustin Vicent

Reputation: 19

Php Post to two tables in Mysql

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' ) ;
  ?>

Upvotes: 0

Views: 191

Answers (5)

Mo Adel
Mo Adel

Reputation: 1156

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

Upvotes: 0

Fabio
Fabio

Reputation: 23500

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."'

Upvotes: 2

Ajeet Singh
Ajeet Singh

Reputation: 32

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

Upvotes: 0

Jhonathan H.
Jhonathan H.

Reputation: 2713

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?

Upvotes: 0

chandresh_cool
chandresh_cool

Reputation: 11830

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.

Upvotes: 0

Related Questions