Solved

Insert data from Array to Mysql

Posted on 2009-04-11
8
466 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 109

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 109

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 109

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

Easy, flexible multimedia distribution & control

Coming soon!  Ideal for large-scale A/V applications, ATEN's VM3200 Modular Matrix Switch is an all-in-one solution that simplifies video wall integration. Easily customize display layouts to see what you want, how you want it in 4k.

Question has a verified solution.

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

Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
This article discusses how to create an extensible mechanism for linked drop downs.
Learn the basics of if, else, and elif statements in Python 2.7. Use "if" statements to test a specified condition.: The structure of an if statement is as follows: (CODE) Use "else" statements to allow the execution of an alternative, if the …
The viewer will learn how to count occurrences of each item in an array.

839 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