populating mysql using php

[anfetienne]

hi this question is linked to this post ive made in the mysql section.

http://bytes.com/topic/mysql/answers/871536-populating-table#post3499253

simply i want to use a form to populate a table with the locations of images, thumbnails and their corresponding captions. i want to populate the table like this:

id image location thumbnail location caption

1 folder/image1.jpg folder/t/image1.jpg just a test
2 folder/image2.jpg folder/t/image2.jpg just the test
3 folder/image3.jpg folder/t/image3.jpg just to test

is it possible to loop php to insert data into more than one row in a mysql table like shown above?

[anfetienne]

ok i have solved how to insert multiple rows in sql but is it possible to loop this in php so it inserts the correct amount of rows for the amount of images?

[Dormilich]

consider using a Prepared Statement (it's faster than multiple queries)

[pdkadam]

yes its possible

yes you can loop it exactly the no. of times the amount of images you have.
for that purpose u will have to set a variable to count the images. this variable will first check the no. of images and will restrict the loop to execute that many no. of times.
now how that can be done? u need to tell whr r the images stored & fetched from.

[anfetienne]

cant find anything on multiple insert with prepared statements

[anfetienne]

and pdkadam....i want to do this on upload not once its stored it will be alot easier that way because i can get this image and thumbnail locations once the code has been run in vars and then just add them into the sql (which is the last thing to run within the php script).

i think i will be using a foreach loop, i will set a array in the form called id[] but wont store this into the database as the project number will be stored as an id for later and easier retrieval. the images are already an array so therefore i have the captions to change to an array and then write my foreach loop....

i will be back with the results...thank s for the help guys

[Dormilich]

cant find anything on multiple insert with prepared statements

then you should read a bit more about prepared statements...

it basicly goes like this

// ...
$ps->prepare($sql);
foreach (...)
{
    // ...
    $ps->execute($values);
}

[anfetienne]

Dormilich i dont appreciate the manner or tone of your last message.....sim ple fact is the link you sent explained prepared statements i read the whole thing and nowhere on that page did it give an example or state anything about multiple inserts. moderator or not you just rude....your answer wasnt needed especially in that manner as i previously stated that i found my way that i will use and i will return the results later.

thanks but no thanks for the answer!

[anfetienne]

and i also searched it on google and for tutorials but it kept coming up with javascript!

[Dormilich]

and i also searched it on google and for tutorials but it kept coming up with javascript!

I'd have thought you could have had a look at the PHP manual (eventually you need a PHP function to use Prepared Statements (just my train of thoughts)).

manual entry (further examples in the function discussions)

my google search

moderator or not you just rude....

1) I'm not modding PHP
2) I can't have a good day everytime. I'm a human after all and are bound to make mistakes
3) I'm sorry if I upset you

* – someone once told me that "reading the manual" is a rare skill nowadays…

[dlite922]

Dorm,

Don't worry, when i was learning PHP, if you had slapped me in the face then gave me the wrong answer, I'd still would have loved you for it :)

But that's just me, I love learning,



Dan

[Dormilich]

yea, sometimes I wish I had a teacher for PHP… anyways, thanks Dan

Dorm the Worm *harhar*

[anfetienne]

apology accepted....bei ng new to php doesn't exactly help me in any case.

thanks for the tip, ill give it a try as well

[anfetienne]

ok ive done a test using prepared statements..... nice and fast but one problem....i dont know whether my loop is correct for this, it has worked when i done a multiple INSERT but only inserts 1 row with the prepared statements..

here is my code

<?php error_reporting(E_ALL);

$returnURL = $_POST['returnURL'];

if(isset($_POST['create_xml'])){

// configuration 
$dbtype     = "sql"; 
$dbhost     = "localhost"; 
$dbname     = "theau10_resources"; 
$dbuser     = "theau10_tawUser"; 
$dbpass     = "auction10"; 
 
// database connection 
$conn = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser,$dbpass); 
 
// query 
$sql = "INSERT INTO flashGallery (id,imageLoc,thumbLoc,imageCap) VALUES (:id,:imageLoc,:thumbLoc,:imageCap)"; 
$q = $conn->prepare($sql); 

foreach ($_POST['random_digit'] as $key => $random_digit)
{
  $q->execute(array(':id'=>$random_digit,
  					':imageLoc'=>$_POST['imageT'][$key],
					':thumbLoc'=>$_POST['thumbnailT'][$key], 
                    ':imageCap'=>$_POST['captionT'][$key])); 
  
}

}
?>

[anfetienne]

problem solved....just had to change id to tempID for the project number and add a column id to auto increment

here is the final code

<?php error_reporting(E_ALL);

$returnURL = $_POST['returnURL'];
$random_digit = $_POST['random_digit'];

// configuration 
$dbtype     = "sql"; 
$dbhost     = "localhost"; 
$dbname     = "theau10_resources"; 
$dbuser     = "theau10_tawUser"; 
$dbpass     = "auction10"; 
 
// database connection 
$conn = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser,$dbpass); 

// query 
$sql = "INSERT flashGallery (imageNo,tempID,imageLoc,thumbLoc,imageCap) VALUES (:imageNo,:tempID,:imageLoc,:thumbLoc,:imageCap)"; 
$q = $conn->prepare($sql); 

foreach ($_POST['picT'] as $key => $value)
{
  $q->execute(array(':tempID'=>$random_digit,
			        ':imageNo'=>$value,
  				':imageLoc'=>$_POST['imageT'][$key],
				':thumbLoc'=>$_POST['thumbnailT'][$key], 
                                ':imageCap'=>$_POST['captionT'][$key])); 
  
}
?>