Php Multi-Dimensional Array / MySql problem

Question

I am trying to write a php script that take a text file break down its contents and and insert it into a MySql database, the code is as follows:

$file = "my_file.txt";

$db = "db_name";

$link = mysql_connect("localhost","root"); 

if(!$link)  die("Connection Failed");    

mysql_select_db($db) or die("Could not open $db: ".mysql_error()."<br />");

$fp = fopen($file, 'r') or die("Could not open file");
$my_filesize = filesize($file);

while(!feof($fp)) {
  $prod_doc.=fread($fp, $my_filesize); // store the file in a variable
} 

$prod_array = explode("~",$prod_doc);   // create a array with the explode function

for($i=0; $i<count($prod_array); $i++){
  $prod_items[$i] = explode(',', $prod_array[$i]);   // create a malti-dimensional array 
}

$query = "INSERT INTO my_table(feild1, feild two, feild three)
          VALUES ('$prod_items[$i][0]','$prod_items[$i][1]','$prod_items[$i][2]')
         ";

$result = mysql_query($query);

if(!$result) die(mysql_error());            

$result = mysql_affected_rows($result);

echo $result;

mysql_close($link); `

My problem is this: Array[0], Array[1], Array[3] is what is entered into the database instead of my data. Thanks in advance, cheers.

Answer 1

To access array variable element values used inside a double-quote string need braces delimiters:

"'{$prod_items[$i][0]}','{$prod_items[$i][1]}','{$prod_items[$i][2]}') ";

Another way to code this is by concatenation (in which case you don't need the extra delimiters):

"'" . $prod_items[$i][0] . "','" . $prod_items[$i][1] . "','" . $prod_items[$i][2] . "') ";

Don't forget, if the input data is unpredictable, you need to filter out characters that can break your sequel or compromise security principles. SEE How can I prevent SQL injection in PHP?

Also, junmats's comment is correct, you are only running the query outside the for loop which doesn't make sense.

Answer 2

You have to iterate over your $prod_items array as well, then concate the values

$insert = array();
for($i=0; $i<count($prod_array); $i++){
  $prod_items[$i] = explode(',', $prod_array[$i]);   // create a malti-dimensional array
  $insert[] = '( ' .$prod_items[$i][0]. ', '.$prod_items[$i][1]. ', '. $prod_items[$i][3] .')';
}

$insert_string = implode(', ', $insert);

$query = "INSERT INTO my_table(feild1, feild two, feild three)
          VALUES" . $insert_string;

And you should use foreach insted of for.

Answer 3

Seems like you've skipped some code. After explode you'll have array of strings, not 2d array. Also it's better to update the code a bit.

$query = "INSERT INTO my_table(feild1, feild_two, feild_three) VALUES ('".$prod_items[$i][0]."','".$prod_items[$i][1]."','".$prod_items[$i][2]."') ";

Answer 4

You should use the standard concatenation(.) technique for this. PHP can only evaluate simple variables inside a string:

"$var" --> var is evaluated "$var->var" --> is not evaluated "$var[0]" --> is not evaluated

$query = "INSERT INTO my_table(feild1, feild two, feild three)
          VALUES ('".$prod_items[$i][0]."','".$prod_items[$i][1]."','".$prod_items[$i][2]".')
       ";