Grab info from multiple sql tables and put into one using php

Question

At the moment this code updates the user info in MYSQLTABLE1. What I also want done is user info to be copied to WMYSQLTABLE which I can do but I also want a code from MYSQLTABLE2 to be copied over into a column in WMYSQLTABLE as well. Here is the part I need changing:

   $sql_insert2 = "INSERT INTO ".WMYSQLTABLE2."(ip,date,address)values
    ('','$ip','$date','$address')";      
    $res_insert2 = mysql_query($sql_insert2)or die(mysql_error());
//Need to also insert code from first row from      column named 'codes' in MYSQLTABLE2.

Actual code, it's a bit messy at the moment and the if/else statements do the exact same at the moment. It works but I get the error " Column count doesn't match value count at row 1" because I cannot fill the last column with the code from MYSQLTABLE2.

<?php
include("includes/config.php");
include("includes/mysql.php");
include("amount.php");

if(isset($_POST['func']))

    {

        $address        = $_POST['address'];
        $ip             = $_POST['ip'];
        $date           = $_POST['date'];
        $time           = $_POST['time'];
        $price      = $_POST['price'];
        $increment              = $_POST['increment'];
        $id             = $_POST['id'];
        $num = 0;



        $sql_check_address = "SELECT * FROM ".MYSQLTABLE1." WHERE address='$address'";
        $res_check_address = mysql_query($sql_check_address)or die(mysql_error());
        $num               = mysql_num_rows($res_check_address);
        $row               = mysql_fetch_assoc($res_check_address);


        if($num > 0)
            {   
                $address        = $row['address'];
                $ip             = $row['ip'];
                $date           = $row['date'];
                $oldprice   = $row['price'];
                $id             = $row['id'];

                $newprice   = $oldprice - $payAmount*200;
                $newinc        = $increment - 200;

                $sql_update1 = "UPDATE ".MYSQLTABLE1." SET ip='$ip',date='$date',price='$newprice',increment='$newinc',address='$address' WHERE id='$id'";  
                $res_update1 = mysql_query($sql_update1)or die(mysql_error());

                /////////////////Insert user info and copy code from MYSQLTABLE2 to WMYSQLTABLE2

                $sql_insert2 = "INSERT INTO ".WMYSQLTABLE2."(ip,date,address)values
                ('','$ip','$date','$address')";
                $res_insert2 = mysql_query($sql_insert2)or die(mysql_error());

            }
        else{
            $address        = $row['address'];
            $ip             = $row['ip'];
            $date           = $row['date'];
            $oldprice   = $row['price'];
            $id             = $row['id'];

            $newprice   = $oldprice - $payAmount*200;
            $newinc        = $increment - 200;

            $sql_update = "UPDATE ".MYSQLTABLE1." SET ip='$ip',date='$date',price='$newprice',increment='$newinc',address='$address' WHERE id='$id'";   
            $res_update = mysql_query($sql_update)or die(mysql_error());

            /////////////////Insert user info and copy code from MYSQLTABLE2 to WMYSQLTABLE2

            $sql_insert2 = "INSERT INTO ".WMYSQLTABLE2."(ip,date,address)values
                ('','$ip','$date','$address')";
            $res_insert2 = mysql_query($sql_insert2)or die(mysql_error());

            e
                }

    }

Any help will be greatly appreciated.

Your code is vulnerable to SQL injection. See [How can I prevent SQL injection in PHP?](http://stackoverflow.com/q/60174). Also, [**Please, don't use `mysql_*` functions in new code**](http://bit.ly/phpmsql). They are no longer maintained [and are officially deprecated](http://j.mp/XqV7Lp). Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. If you choose PDO, [here is a good tutorial](http://j.mp/PoWehJ). — Madara's Ghost, Mar 15 '14 at 10:57
`$sql_insert2 = "INSERT INTO ".WMYSQLTABLE2."(ip,date,address)values ('','$ip','$date','$address')";` See carefully you tiring to pass 4 values for 3 columns... ['' is extra before '$ip') — CyberBoy, Mar 15 '14 at 11:14
User input is limited and has to adhere to rules but yes I should probably change it as it is a learning process for me. Can sql injections be executed in otherways other than an input box? Look like I've got much more work to do ^^ — user3345992, Mar 15 '14 at 11:20

score 0 · Answer 1 · answered Mar 15 '14 at 11:01

The general form of the query would be:

$sql_insert = "INSERT INTO " . WMYSQLTABLE2 . "(code, ip, date, address)
               SELECT code, '$ip', '$date', '$address'
               FROM OtherTable
               WHERE <put something here to select the row>";

Grab info from multiple sql tables and put into one using php

1 Answers1