Inserting multiple values into mysql

[Slowie]

Hi People

 

i'm a newb at this so bare with me but i currently have a php file called Newkpi.php which has a select statement in. this selects data from a table called "StaffList".

this then populates the page with a html table with 14 records (this will increase/decrease over time) i then have some extra text boxes to enter more detail into like service amaount service date and so on.

when i click the submit button i want it to cycle through each row and insert the data into a separate table called "Services". however i cannot for the life of me get this to work and need some help with it. people find attached the code for Newkpi and see if you can help me with this.

 

in total i want it to take the names from stafflist and populate Services with the names and the extra detail which is entered on the page

 

Much Thanx in advance

 

SLOWIE

 

[attachment deleted by admin]

[George Botley]

You want to assign each field as an array like this:

 

 

<input type="text" name="$price[]" />

 

 

And then loop through with a for loop inserting them into the database along the lines of this:

 

 

foreach($price as $key => $value) {


$query = "INSERT INTO services (price) VALUES ('{$key['$value']}')";
$query = mysql_query($query);


}

 

 

Been a while since I've used a loop in this way but I'll post my most recent method as a working example once I find it.

 

 

[Slowie]

You'll be a life saver if you could i have been banging my head against a wall over this.

 

just courious, would this work correctly for pulling the information ( in this case the staff member) and inserting it into another table

 

  <td name="user_name" value=><?php echo $rrows['full_name'];?></td>

 

so if i do a mysql query of

 

INSERT INTO Services (user_name) VALUES ($_POST('user_name'));

 

would it pull the staff member from the form into the services table. as im sure i have done this incorrectly

[George Botley]

Hello Slowie,

 

 

Based on the code you just provided, it wouldn't do anything. All you are doing there is showing the Full Name in a table on a webpage, not actually inserting it into another table. Disregard this if you intended to do that. The INSERT query however would insert whatever the posted user_name field was into the Services table and it correct, but needs to be wrapped in a query like this and needs a small change to the $_POST tag.

 

 

INSERT INTO Services (user_name) VALUES ($_POST['user_name']);

 

 

Use [] tags instead of (). To make things easier in the future, perhaps use variables to store the value of the posted information. It makes things easier should you need to change the value of the inserted data. See below. I have commented the information.

 

 

<?php


//Set Variables
$user_name = $_POST['user_name'];


//Prepare Query 
$query = "INSERT INTO Services (user_name) VALUES ($_POST['user_name'])";


//Run Query
$query = mysql_query($query) or die(mysql_error());


?>

 

 

Hope that helps... Now, for the code I mentioned.

 

 

---

I created this code a while back for a youth football club to submit match reports, detailing the scorers.

 

 

I have stripped the form to its minimum so you can see the array's in use.

 

 

Here's the form:

 

 

 

<form action="#" method="POST">


<input type="text" name="scorer[]">


<input type="submit" value="Submit">


</form>

 

 

And here is the PHP that handles it:

 

 

<?php


//Set Variables
$scorers = $_POST["scorer"];
$rand = rand("1", "200");


//Run through array storing values

foreach($scorers as $scorer) {


        //Prepare Query
        $query = "INSERT INTO match_report_scored (scorer, report_id) VALUES ('$scorer', '$rand')";


        //Run Query
$query = mysql_query($query) or die(mysql_error());	


}


?>

 

 

By setting the name of the text field to an array we are allowing there to be multiple fields with the same name. In the PHP we then set a variable with the name of the fields, in this case scorer. Essentially that scorer array would look like this if there were three fields.

 

 

Array (


[0] => John Doe
[1] => Jane Doe
[2] => Tom Doe


)

 

 

Hopefully that helps you see what an Array is. We want to tell PHP to go through each item of this array and do something with them. This is where the "foreach" function comes in useful.

 

 

What we tell PHP to do is the following:

 

 

"For each value that exists in the array with variable $scorers, see each array item as $scorer and loop through running the below command". In PHP this is written as

 

 

foreach($scorers as $scorer) { }

 

 

Simple enough really, do you agree? Within the curly brackets you can then write what command you want using $scorer as the variable for the current item within the array that PHP is dealing with. As our array has three items the foreach would actually create something like this as we told it to insert the value into the database.

 

 

mysql_query("INSERT INTO match_report_scored (scorer, report_id) VALUES ('John Doe', '123')");
mysql_query("INSERT INTO match_report_scored (scorer, report_id) VALUES ('Jane Doe', '183')");
mysql_query("INSERT INTO match_report_scored (scorer, report_id) VALUES ('Tom Doe', '25')");

 

 

I hope this has helped answer your query. Let me know.