Solved

Insert data from Array to Mysql

Posted on 2009-04-11
8
456 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
Comment Utility
ok i will have a look into it and post my find
0
 
LVL 3

Expert Comment

by:Ludger Peters
Comment Utility
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
Comment Utility
i dont know how you want to do the ID could you tell me what you want ?
0
Easy Project Management (No User Manual Required)

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
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 108

Accepted Solution

by:
Ray Paseur earned 500 total points
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
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 …
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now