Solved

Insert data from Array to Mysql

Posted on 2009-04-11
8
468 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:nguyenhoan
  • 3
  • 3
8 Comments
 
LVL 3

Expert Comment

by:Ludger Peters
ID: 24121721
ok i will have a look into it and post my find
0
 
LVL 3

Expert Comment

by:Ludger Peters
ID: 24121760
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

0
 
LVL 3

Expert Comment

by:Ludger Peters
ID: 24121860
i dont know how you want to do the ID could you tell me what you want ?
0
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
LVL 110

Expert Comment

by:Ray Paseur
ID: 24122598
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

0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 24122604
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

0
 

Author Comment

by:nguyenhoan
ID: 24125217
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
0
 
LVL 110

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 24125474
There are too many moving parts for just one question.  If you break if apart, you will get better answers.

I'll post a script that allows for multiple file uploads, then I'll sign off on this question - sometimes there is a lot of work involved in application development, and it is a matter of getting from understanding to implementation.  At EE we can help with the teaching side of the understanding, but the implementation is something that professionals get paid for, and we can't be responsible for that part of the work

Good luck with your project, ~Ray
<?php // RAY_upload_example.php
error_reporting(E_ALL);
 
// MANUAL REFERENCE PAGES
// http://docs.php.net/manual/en/features.file-upload.php
// http://docs.php.net/manual/en/features.file-upload.common-pitfalls.php
// http://docs.php.net/manual/en/function.move-uploaded-file.php
// http://docs.php.net/manual/en/function.getimagesize.php
 
// ESTABLISH THE NAME OF THE 'uploads' DIRECTORY
$uploads = 'uploads';
 
// ESTABLISH THE BIGGEST FILE SIZE WE CAN ACCEPT
$max_file_size = '8192000';  // EIGHT MEGABYTE LIMIT ON UPLOADS
 
// ESTABLISH THE KINDS OF FILE EXTENSIONS WE CAN ACCEPT
$file_exts = array('jpg', 'gif', 'png', 'txt');
 
// ESTABLISH THE NUMBER OF FILES WE CAN UPLOAD
$nf = 3;
 
 
 
// THIS IS A LIST OF THE POSSIBLE ERRORS THAT CAN BE REPORTED in $_FILES[]["error"]
$errors	= array(
	0=>"Success!",
	1=>"The uploaded file exceeds the upload_max_filesize directive in php.ini",
	2=>"The uploaded file exceeds the MAX_FILE_SIZE directive that was specified in the HTML form",
	3=>"The uploaded file was only partially uploaded",
	4=>"No file was uploaded",
	6=>"Missing a temporary folder",
	7=>"Cannot write file to disk"
);
 
 
 
// IF THERE IS NOTHING IN $_POST, PUT UP THE FORM FOR INPUT
if (empty($_POST))
{
	?>
	<h2>Upload a file</h2>
 
	<!--
		SOME THINGS TO NOTE ABOUT THIS FORM...
		NOTE THE CHOICE OF ENCTYPE IN THE HTML FORM STATEMENT
		MAX_FILE_SIZE MUST PRECEDE THE FILE INPUT FIELD
		INPUT NAME= IN TYPE=FILE DETERMINES THE NAME YOU FIND IN $_FILES ARRAY
	-->
 
	<form name="UploadForm" enctype="multipart/form-data" action="<?=$_SERVER["REQUEST_URI"]?>" method="POST">
	<input type="hidden" name="p" value="1" />
	<input type="hidden" name="MAX_FILE_SIZE" value="<?=$max_file_size?>" />
	<p>
	Find the file(s) you want to upload and click the "Upload" button below.
	</p>
 
	<?php for ($n = 0; $n < $nf; $n++)
		{
			echo "<input name=\"userfile$n\" type=\"file\" size=\"80\" /><br/>\n";
		}
	?>
	<br/>Check this box <input autocomplete="off" type="checkbox" name="overwrite" /> to <b>overwrite</b> existing files.
	<input type="submit" name="_submit" value="Upload" />
	</form>
	<?php
	die();
}
 
 
 
else // WE HAVE GOT SOMETHING IN $_POST
{
 
// THERE IS POST DATA - PROCESS IT
	echo "<h2>Results: File Upload</h2>\n";
 
// ACTIVATE THIS TO SEE WHAT IS COMING THROUGH
//	echo "<pre>"; var_dump($_FILES); var_dump($_POST); echo "</pre>\n";
 
// ITERATE OVER THE CONTENTS OF $_FILES
	foreach ($_FILES as $my_uploaded_file)
	{
 
// SKIP OVER EMPTY SPOTS - NOTHING UPLOADED
		$error_code	= $my_uploaded_file["error"];
		if ($error_code == 4) continue;
 
// SYNTHESIZE THE NEW FILE NAME
		$f_type	= trim(strtolower(end    (explode( '.', basename($my_uploaded_file['name'] )))));
		$f_name	= trim(strtolower(current(explode( '.', basename($my_uploaded_file['name'] )))));
		$my_new_file	= getcwd() . '/' . $uploads . '/' . $f_name .'.'. $f_type;
		$my_file	= $uploads . '/' . $f_name .'.'. $f_type;
 
// OPTIONAL TEST FOR ALLOWABLE EXTENSIONS
		if (!in_array($f_type, $file_exts)) die("Sorry, $f_type files not allowed");
 
// IF THERE ARE ERRORS
		if ($error_code != 0)
		{
			$error_message = $errors[$error_code];
			die("Sorry, Upload Error Code: $error_code: $error_message");
		}
 
// GET THE FILE SIZE
		$file_size	= number_format($my_uploaded_file["size"]);
 
// MOVE THE FILE INTO THE DIRECTORY
// IF THE FILE IS NEW
		if (!file_exists($my_new_file))
		{
			if (move_uploaded_file($my_uploaded_file['tmp_name'], $my_new_file))
			{
				$upload_success = 1;
			}
			else
			{
				$upload_success = -1;
			}
 
// IF THE FILE ALREADY EXISTS
		}
		else
		{
			echo "<br/><b><i>$my_file</i></b> already exists.\n";
 
// SHOULD WE OVERWRITE THE FILE? IF NOT
			if (empty($_POST["overwrite"]))
			{
				$upload_success = 0;
 
// IF WE SHOULD OVERWRITE THE FILE, TRY TO MAKE A BACKUP
			}
			else
			{
				$now	= date('Y-m-d');
				$my_bak = $my_new_file . '.' . $now . '.bak';
				if (!copy($my_new_file, $my_bak))
				{
					echo "<br/><b>Attempted Backup Failed!</b>\n";
				}
				if (move_uploaded_file($my_uploaded_file['tmp_name'], $my_new_file))
				{
					$upload_success = 2;
				}
				else
				{
					$upload_success = -1;
				}
			}
		}
 
// REPORT OUR SUCCESS OR FAILURE
		if ($upload_success == 2) { echo "<br/>It has been overwritten.\n"; }
		if ($upload_success == 1) { echo "<br/><b><i>$my_file</i></b> has been saved.\n"; }
		if ($upload_success == 0) { echo "<br/><b>It was NOT overwritten.</b>\n"; }
		if ($upload_success < 0)  { echo "<br/><b>ERROR <i>$my_file</i> NOT SAVED - SEE WARNING FROM move_uploaded_file() COMMAND</b>\n"; }
		if ($upload_success > 0)
		{
			echo "$file_size bytes uploaded.\n";
			if (!chmod ($my_new_file, 0755))
			{
				echo "<br/>chmod(0755) FAILED: fileperms() = ";
				echo substr(sprintf('%o', fileperms($my_new_file)), -4);
			}
			echo "<br/><a href=\"$my_file\">See the file $my_file</a>\n";
		}
// END ITERATOR
	}
}
?>

Open in new window

0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
This article discusses four methods for overlaying images in a container on a web page
Learn the basics of lists in Python. Lists, as their name suggests, are a means for ordering and storing values. : Lists are declared using brackets; for example: t = [1, 2, 3]: Lists may contain a mix of data types; for example: t = ['string', 1, T…
Learn the basics of modules and packages in Python. Every Python file is a module, ending in the suffix: .py: Modules are a collection of functions and variables.: Packages are a collection of modules.: Module functions and variables are accessed us…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question