Link to home
Start Free TrialLog in
Avatar of nguyenhoan
nguyenhoanFlag for Viet Nam

asked on

Insert data from Array to Mysql

I have a table IMAGES with 3 Fields: Id, Position, Filename.
And I have an Array in the Code Field below.

I need you to help me make the query to insert the data  from the array, such as:

{
insert '111' to field 'Position';
insert 'asd.jpg' to field 'Filename';
$Id = $db->last_id();
} ...and go on for the whole loop

Please help !

Array
(
    [0] => Array
        (
            [0] => 111
            [1] => asd.jpg
        )
    [1] => Array
        (
            [0] => 222
            [1] => fgh.gif
        )
    [2] => Array
        (
            [0] => 333
            [1] => jkl.jpg
        )
// ... and go on...
)

Open in new window

Avatar of Ludger Peters
Ludger Peters
Flag of South Africa image

ok i will have a look into it and post my find
thats the PHP code for just generating the SQL string
<?PHP
$arraydata = array();//the data array
$sql = "INSERT INTO `database`.`table` (`id`, `position`, `filename`) VALUES";
$id = 0;
foreach($arraydata as $data)
{
	$sql .= " ('".$id."', '".$data[0]."', '".$data[1]."'),";//adds the value
	$id++;//increments the id
}
$sql = substr($sql, 0, -1);//takes away the extra , at the end
$sql .= ";";//adds the ending
?>

Open in new window

i dont know how you want to do the ID could you tell me what you want ?
The "ID" Field is probably an AUTO_INCREMENT index in the data base table.  In that case, you do not need to set it separately or even pay any attention to it during the INSERT process, unless you need it for other processing in the script at the same time you are inserting the records.

This code snippet will insert each row and give you back each ID.  It assumes that you are somehow connected to the data base correctly.  I will post another script to illustrate that process.

HTH, ~Ray
<?php // RAY_temp_hoan.php
error_reporting(E_ALL);
 
/* // SAMPLE DATA FROM THE OP
Array
(
    [0] => Array
        (
            [0] => 111
            [1] => asd.jpg
        )
    [1] => Array
        (
            [0] => 222
            [1] => fgh.gif
        )
    [2] => Array
        (
            [0] => 333
            [1] => jkl.jpg
        )
// ... and go on...
)
*/ // END OF DATA FROM THE OP
 
 
// GENERATE SOME TEST DATA
$dat = array(
          array('111', 'asd.jpg'),
          array('222', 'fgh.jpg'),
          array('333', 'jkl.jpg'),
          array('444', 'xyz.jpg')
       );
// DOES THE TEST DATA LOOK OK?
// print_r($dat); // YES, FINE
 
// ITERATE OVER THE ARRAY, TAKE OUT EACH SUB-ARRAY AND MAKE A QUERY
foreach ($dat as $my_data)
{
// ESCAPE THE VALUES BEFORE INSERTING
   $position = mysql_real_escape_string($my_data[0]);
   $filename = mysql_real_escape_string($my_data[1]);
 
// CREATE THE INSERT QUERY
   $sql      = "INSERT INTO IMAGES (    Position,      Filename   ) ";
   $sql     .= "VALUES             ( \"$position\", \"$filename\" ) ";
 
// EXECUTE THE INSERT QUERY AND TEST FOR SUCCESS
   $res      = mysql_query($sql);
   if (!$res)
   {
      echo "<br/>QUERY FAIL $sql \n";
      $err = mysql_errno() . ' ' . mysql_error();
      die($err);
   }
 
// GET THE INSERT ID (IF NEEDED)
   $my_id    = mysql_insert_id($res);
 
// SHOW THE RESULTS OF THE EFFORT
   echo "<br/>$my_id $position $filename \n";
}

Open in new window

The top lines of this script (6 ~ 26) shows how to connect to a server and select a data base.

best regards, ~Ray
<?php // RAY_mysql_example.php
error_reporting(E_ALL);
 
 
// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";
 
// CONNECT TO THE DATA BASE SERVER
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>NO DB CONNECTION: ";
   echo "<br/> $errmsg <br/>";
}
 
// SELECT THE DATA BASE
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>NO DB SELECTION: ";
   echo "<br/> $errmsg <br/>";
   die('NO DATA BASE');
}
 
 
 
// MAKING A QUERY AND TESTING THE RESULTS
$sql = "SELECT id FROM my_table WHERE username='$username'";
$res = mysql_query($sql);
 
// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>QUERY FAIL: ";
   echo "<br/>$sql <br/>";
   die($errmsg);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESOURCE-ID IN $res SO WE CAN NOW USE $res IN OTHER MYSQL FUNCTIONS
 
 
 
// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num = mysql_num_rows($res);
if (!$num)
{
   echo "<br/>QUERY FOUND NO DATA: ";
   echo "<br/>$sql <br/>";
}
else
{
   echo "<br/>QUERY FOUND $num ROWS OF DATA ";
   echo "<br/>$sql <br/>";
}
 
 
 
// ITERATE OVER THE RESULTS SET TO SHOW WHAT WE FOUND
echo "<pre>\n"; // MAKE IT EASY TO READ
while ($row = mysql_fetch_assoc($res))
{
   var_dump($row);
}

Open in new window

Avatar of nguyenhoan

ASKER

It's mean I have a multiple input file, each input file have an position (from a selectbox).  (Please see the Code Snippet below).After I selected the files, and selected a position for each file, I press submit.

And the file have to do these following actions:

1- INSERT the data (position, filename) to table "profileimgs". Each File with the Position is in Each Record. Record is the next Auto Increment ID in the table "profileimgs".

2- UPLOAD all of the file that I have selected to a folder in the server, such as "images/profiles/"

The upload form is look like this:
--------------------------------
[Add more Image]

Image Position1: ___1___ - File1: __asd.gif__ [Browse]
Image Position2: ___2___ - File2: __fgh.jpg__ [Browse]
Image Position3: ___1___ - File3: __abc.png_ [Browse]
....(add more if you want)

[SUBMIT]
--------------------------------

And the record I want look like this:
-------------------------------------------------
Table "profileimgs"
[ID]  ---------|--- [POSITION] --|---[FILENAME]
.....
lastID -------|-------- 1 --------|----- asd.gif
lastID+1-----|-------- 2 --------|----- fgh.jpg
lastID+2 ----|-------- 1 --------|----- abc.png
..... (as much as your upload form submited)

And Yes ! I want my upload is look like Expert Upload Files in this Comment with a description for each file, and with multiple file. Please help.
<?
/*	if ($_POST['action']=='upload')
	{
        $array_position = $_POST[[position];
        $array_image = $_FILES[imgfile][name];
 
 // HOW IS THE PROCESS WORKING ?
	
		header("Location: profileimg.php?result=success");
		exit();
	}*/		
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Upload Multi Record</title>
<script type="text/javascript">
var counter = 0;
var counterPosition = 0;
var counterImage = 0;
 
function addMore(divName){
     var newdiv = document.createElement('div');
         newdiv.innerHTML = "<strong>Position &raquo;</strong> <select name='position[" + (counterPosition + 1) + "]'><option value=\"1\">option1</option><option value=\"2\">option2</option></select> <strong>Image &raquo;</strong> <input type='file' name='imgfile[" + (counterImage + 1) + "]'>";
     counter++;
     counterPosition++;
     counterImage++;
     document.getElementById(divName).appendChild(newdiv);
}
</script>
</head>
<body>
 
<!-- CONTAINER -->
<div style=" width:500px; margin:0px; padding: 0px;">
  <table width="100%" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td align="center" valign="top">
	<div id="table">
<?
	if ($_GET['result']=='success')
	{
?>	
	<div class="title" align="center">Images Added Successfully !</div>
<?
	}
?>
	  <table width="100%" border="0" cellpadding="4" cellspacing="1" bgcolor="#e5e5e5" align="center">
 
        <tr bgcolor="#FF0000">
          <td colspan="2" bgcolor="#2981c4" class="white_bold" style="margin-right: 7px;">MULTIPLE UPLOAD</td>
        </tr>                                      		
        <tr class="row1">
          <td colspan="2" style="margin-right: 7px;">
			  <form name="uploadform" enctype="multipart/form-data" method="POST">
			  <table cellpadding="5" cellspacing="5" border="0">
				  <tr>
                      <td align="left"><input type="button" value="Add more image" onClick="addMore('multiple');"></td>
					  <td align="left"><input name="over" type="checkbox" id="over" value="true"> Overwrite</td>
                  </tr>
                  <tr>
					  <td colspan="2">
					  <div id="multiple" style="padding:10px 0;">
					  <div><strong>Position &raquo;</strong> <select name='position[0]'><option value="1">option1</option><option value="2">option2</option></select> <strong>Image &raquo;</strong> <input type='file' name='imgfile[0]'></div>
					  </div>
					  <input name="Submit" type="submit" class="button" value="Upload"> 
					  <input type="hidden" name="action" value="upload">
					  </td>
                  </tr>
			  </table>
			  </form>
		  </td>	
        </tr>	
      </table>
 
	</div></td>
  </tr>
</table>
<div class="clear"></div>
</div>
 
<!-- END CONTAINER -->
</body>
</html>

Open in new window

code.txt
ASKER CERTIFIED SOLUTION
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial