creating new db rows from data in array?

Question

Hey guys im trying to pass some data from an (oracle) fetch_array to a variable array and then use that array data to check if the data exists on a mysql db and create any rows that dont currently exist.. this is what i have so far.

the problem is its only checks/creates 1 entry of the array and doesn't check/created the entire array data. i think i would need to use a for loop to process all the array data concurrently

<?php




$conn = oci_connect('asdsdfsf');
$req_number = array();

if (!$conn) {
    $e = oci_error();
    trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}

$stid = oci_parse($conn, "  SELECT WR.REQST_NO                          
                            FROM DEE_PRD.WORK_REQST WR                      
                            WHERE WR.WORK_REQST_STATUS_CD = 'PLAN' AND WR.DEPT_CD ='ISNG'       

                            ");
oci_execute($stid);



while (($row = oci_fetch_array($stid, OCI_BOTH+OCI_RETURN_NULLS)) != false) {
    // Use the uppercase column names for the associative array indices

        $req_number[]= $row['REQST_NO'];


}


oci_free_statement($stid);
oci_close($conn);


//MYSQL
//Connection Variables
//connect to MYSQL  

$con = mysqli_connect($servername,$username,$password,$dbname);
if (!$con)
  {
  die('Could not connect: ' . mysqli_error());
  }
        // lets check if this site already exists in DB 
        $result = mysqli_query($con,"

                                SELECT EXISTS(SELECT 1 FROM wr_info WHERE REQST_NO = '$req_number') AS mycheck; 

                                ");

        while($row = mysqli_fetch_array($result))
            {
                    if ($row['mycheck'] == "0")  // IF site doesnt exists lets add it to the MYSQL DB
                    {
                        $sql = "INSERT INTO wr_info (REQST_NO)VALUES ('$req_number[0]')";

                        if (mysqli_query($con, $sql)) {
                            $created = $req_number." Site Created Successfully";
                        } else {
                            echo "Error: " . $sql . "<br>" . mysqli_error($con);
                        }
                    }else{ // if site is there lets get some variables if they are present... 

                            $result = mysqli_query($con,"
                                                            SELECT *
                                                            FROM wr_info
                                                            WHERE REQST_NO = '$req_number[0]'
                                                        ");

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

                        $do some stuff

                            }   

                        }
            }
mysqli_close($con);

?>

You forgot to describe the problem. – David Mar 03 '17 at 20:32 — David, Mar 03 '17 at 20:32

score 0 · Accepted Answer · answered Mar 03 '17 at 20:46

You create an array:

$req_number = array();

And loop over records to assign values to the array:

while (($row = oci_fetch_array($stid, OCI_BOTH+OCI_RETURN_NULLS)) != false) {
    $req_number[]= $row['REQST_NO'];
}

But then never loop over that array. Instead, you're only referencing the first record in the array:

$sql = "INSERT INTO wr_info (REQST_NO)VALUES ('$req_number[0]')";
// etc.

(Note: There are a couple of places where you directly reference the array itself ($req_number) instead of the element in the array ($req_number[0]), which is likely an error. You'll want to correct those. Also: You should be using query parameters and prepared statements. Getting used to building SQL code from concatenating values like that is a SQL injection vulnerability waiting to happen.)

Instead of just referencing the first value in the array, loop over the array. Something like this:

for($i = 0; $i < count($req_number); $i++) {

    // The code which uses $req_number, but
    // referencing each value: $req_number[$i]

}

thank you for the help! could you elaborate more of this "You should be using query parameters and prepared statements. Getting used to building SQL code from concatenating values like that is a SQL injection vulnerability waiting to happen" — DJRCB, Mar 04 '17 at 15:48
@DJRCB: These are good places to start with that: http://php.net/manual/en/security.database.sql-injection.php http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — David, Mar 04 '17 at 16:21

creating new db rows from data in array?

1 Answers1