php insert sql issue while loop / foreach

Question

The following code from my data between tables, but for some reason only one value is inserted to database

<?php

    DEFINE("DB_SERVER", "localhost"); //LOCALHOST
    DEFINE("DB_USER", "user");
    DEFINE("DB_PASS", "pass");
    DEFINE("DB_NAME", "table");

    $connect = mysql_connect(DB_SERVER,DB_USER,DB_PASS) or die("connect issue". ' ' .  mysql_error());
    mysql_query('SET NAMES utf8'); 

    $db = mysql_select_db(DB_NAME,$connect) or die("connect issue". ' ' . mysql_error());
    mysql_query('SET NAMES utf8');

    if (!$db){
        echo "connect issue";
    }

    $sql = "SELECT id, column2 FROM tablea";
    $result = mysql_query($sql) or die(mysql_error());

    $set = date("Y-m-d H:i:s", time());

    while ($row = mysql_fetch_array($result)) {

        $id = $row['id'];
        $list = $row['column2'];

        echo "user_id: $id";
        echo "<br/><br/>";

        $makes = explode (";", $row['column2']);

        $i = 0;
        foreach ($makes as $make) {

            $sql2 = "SELECT url FROM tableb WHERE id = '$make'";
            $result2 = mysql_query($sql2) or die(mysql_error());

            while ($row2  = mysql_fetch_array($result2)) {

                echo $row2[0];
                echo $row2[1];
                echo $row2[2];
                echo $row2[3];
                echo $row2[4];
                echo $row2[5];
                echo $row2[6];
                echo $row2[7];

                $m1 = $row2[0];
                $m2 = $row2[1];
                $m3 = $row2[2];
                $m4 = $row2[3];
                $m5 = $row2[4];
                $m6 = $row2[5];
                $m7 = $row2[6];
                $m8 = $row2[7];

                echo "<br/>";

            }

            if (++$i == 8) break;
        }

        $sql3 = "INSERT INTO tablec (partner_id, make1, make2, make3, make4, make5, make6, make7, make8, saved) VALUES ('$id', '$m1', '$m2', '$m3', '$m4', '$m5', '$m6', '$m7', '$m8', '$set')";
        $result3 = mysql_query($sql3) or die(mysql_error());

        var_dump($sql3);
        var_dump($result3);

        if($result3) {

            echo "done";

        }

        else {

            echo "fail";

        }       

        echo "<pre>";
            var_dump($row);
        echo "</pre>";


        echo "<hr/>";

    }

?>

var_dump sql:

string(186) "INSERT INTO tablec (partner_id, make1, make2, make3, make4, make5, make6, make7, make8, saved) VALUES ('75', 'opel', '', '', '', '', '', '', '', '2014-02-06 18:20:14')"

it works fine, but the only 1 variable inserted the table "$row2[0];"

whats the problem? thank you for your help!

Can you explain more? Which variable is inserted, which isn't, how did you check this, what have you tried to fix this so far? — Mark
– Mark, Commented Feb 6, 2014 at 17:53

Marc B · Accepted Answer · 2014-02-06 17:30:57Z

1

You only ever fetch one field from your tableb:

        $sql2 = "SELECT url FROM tableb WHERE id = '$make'";
                        ^^^^---- here

If you want more fields, you need to specify them:

SELECT url, field1, field2, etc...

answered Feb 6, 2014 at 17:30

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Marc B Over a year ago

That too, but that's up to the OP to figure out... running nested queries like they're attempting is highly inefficient anyways.

Collectives™ on Stack Overflow

php insert sql issue while loop / foreach

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related