POST multiple queries at once

[lukep11a]

Hi, I am trying to insert some code that will submit multiple queries in one go. What I want is for a user to make 12 selections from 12 different groups. I then want them to be stored in a table called 'selections' which only has two columns, 'userid' and 'teamname', so I want to submit each selection onto a new row on the table, I am not even sure if this is possible but this is the code I have so far that doesn't seem to be working at present:

 

<?php

	require_once "header.php";

	$userid = $_POST['userid'];
	$teamname = $_POST['group1'];
	$teamname = $_POST['group2'];
	$teamname = $_POST['group3'];
	$teamname = $_POST['group4'];
	$teamname = $_POST['group5'];
	$teamname = $_POST['group6'];
	$teamname = $_POST['group7'];
	$teamname = $_POST['group8'];
	$teamname = $_POST['group9'];
	$teamname = $_POST['group10'];
	$teamname = $_POST['group11'];
	$teamname = $_POST['group12'];

	$query1="INSERT INTO selections (userid, group1)VALUES ('".$userid."','".$teamname."')";
	$query2="INSERT INTO selections (userid, group2)VALUES ('".$userid."','".$teamname."')";
	$query3="INSERT INTO selections (userid, group3)VALUES ('".$userid."','".$teamname."')";
	$query4="INSERT INTO selections (userid, group4)VALUES ('".$userid."','".$teamname."')";
	$query5="INSERT INTO selections (userid, group5)VALUES ('".$userid."','".$teamname."')";
	$query6="INSERT INTO selections (userid, group6)VALUES ('".$userid."','".$teamname."')";
	$query7="INSERT INTO selections (userid, group7)VALUES ('".$userid."','".$teamname."')";
	$query8="INSERT INTO selections (userid, group8)VALUES ('".$userid."','".$teamname."')";
	$query9="INSERT INTO selections (userid, group9)VALUES ('".$userid."','".$teamname."')";
	$query10="INSERT INTO selections (userid, group10)VALUES ('".$userid."','".$teamname."')";
	$query11="INSERT INTO selections (userid, group11)VALUES ('".$userid."','".$teamname."')";
	$query12="INSERT INTO selections (userid, group12)VALUES ('".$userid."','".$teamname."')";

	mysql_query($query1, $query2, $query3, $query4, $query5, $query6, $query7, $query8, $query9, $query10, $query11, $query12) or die ('Error updating database');

	?>

 

[mrgrammar]

Make the teams an array and then use a foreach loop to insert them.

 

	$userid = $_POST['userid'];

	$teamname = array();
	$teamname[0] = $_POST['group1'];
	$teamname[1] = $_POST['group2'];
	$teamname[2] = $_POST['group3'];
	$teamname[3] = $_POST['group4'];
	$teamname[4] = $_POST['group5'];
	$teamname[5] = $_POST['group6'];
	$teamname[6] = $_POST['group7'];
	$teamname[7] = $_POST['group8'];
	$teamname[8] = $_POST['group9'];
	$teamname[9] = $_POST['group10'];
	$teamname[10] = $_POST['group11'];
	$teamname[11] = $_POST['group12'];

	foreach($teamname AS $t){
		$query = "INSERT INTO selections (userid, group1)VALUES ('".$userid."','".$t."')";
		mysql_query($query) or die();
	}

[xyph]

You want to do something like this

 

INSERT INTO `table` ( `col1`, `col2 ) VALUES ( 'data1', 'data2' ), ( 'data3', 'data4' ), ( 'data5', 'data6' )

 

etc.

 

You probably want to use the team ID and not the team name, though.

[Psycho]

I have to second xyph's advice. Do NOT run queries in loops. It is terribly inefficient. It may not be a problem with only 12 records and with your expected site traffic, but it does not scale. So, if you try running many queries in loops (e.g. reporting pages) or if you have high site traffic you will run in to problems very fast. It is better to follow good programming techniques for the smaller solutions as well as the larger ones.

 

Here is an example of how you can accomplish all the INSERTs with a single query:

 

First off, as xyph suggested,  change your field names in the form so they will be interpreted as an array by PHP. The following example is text fields, but you can do the same with multiple select lists, checkboxes, etc.

Group 1: <input type="text" name="groups[]" />
Group 2: <input type="text" name="groups[]" />
Group 3: <input type="text" name="groups[]" />
...

 

Then your PHP processing code would look something like this:

//Process user input
$userid = mysql_real_escape_string(trim($_POST['userid']));
//Put group selections into arry in MySQL INSERT value format
$valuesAry = array();
foreach($_POST['groups'] as $group)
{
    $group = mysql_real_escape_string(trim($group));
    if($group != '')
    {   //Only add if not empty
        $valuesAry[] = "('{$userid}', '{$group}')";
    }
}

//Create ONE INSERT query
$query = "INSERT INTO table VALUES " . implode(', ', $valuesAry);

 

The resulting query will look like xyph showed

INSERT INTO table (`user_id`, `group`)
VALUES ('1', 'Group1Value'), ('1', 'Group2Value'), ('1', 'Group3Value')
       ('1', 'Group4Value'), ('1', 'Group5Value'), ('1', 'Group6Value')
       ('1', 'Group7Value'), ('1', 'Group8Value'), ('1', 'Group9Value')
       ('1', 'Group10Value'), ('1', 'Group11Value'), ('1', 'Group12Value')

[lukep11a]

Thankyou for your response, I will give this a go and get back to you

[mrgrammar]

mjdamato,  I was just thinking of something quick and easy for a small task.  You are right though.  It's best to follow good programming techniques all the time.

[xyph]

Your solution isn't wrong by any means, there are just more efficient ways of doing it. It's a learning experience for all

[Psycho]

mjdamato,  I was just thinking of something quick and easy for a small task.

 

I understand what you are saying, but performance issues aside, running the queries in a loop is really no more "quick and easy" to code than what I provided. Instead of running individual queries in the loop I added the values to the array in the loop - then ran the one query after the loop. So, it was one additional line of code. Well, two lines if you count the line where I instantiated the $valuesAry variable.

[lukep11a]

Hi, I have made the changes you sugested mjdamato, but I used radio groups instead of text fields and now it will only let you make one selection in total, rather than one selection from each group. I guess that's because each radio input has name="groups[]" so it thinks they are all one big group. Any suggestions on how I separate them back into 12 groups?

[xyph]

Why would use use radios? Use checkboxes

[Psycho]

Hi, I have made the changes you sugested mjdamato, but I used radio groups instead of text fields and now it will only let you make one selection in total, rather than one selection from each group. I guess that's because each radio input has name="groups[]" so it thinks they are all one big group. Any suggestions on how I separate them back into 12 groups?

 

Why would use use radios? Use checkboxes

 

Well, I might agree with xyph depending on the actual implementation. If you have 12 groups all with the exact same options, then a single list of checkboxes is a superior solution. You could implement some JavaScript code to handle restriction of 12 options. You would have to handle that validation in the PHP logic as well, but that is the case no matter what solution you go with. I would just process up to the first 12 selections (if there were more than 12) and not process the rest. Alternatively, you could use 12 select fields - again you still need to validate for more than 12 values being passed in the POST data.

 

However, if you have 12 groups of options all with different values to select from then you can either use a set of select fields or radio groups as you have done. But, as you discovered if you give all the radio buttons the same then it is treated as one giant radio group. That is easily solved by giving each group it's own array index

Group 1:
<input type="radio" name="groups[0]" value="A" /> A
<input type="radio" name="groups[0]" value="A" /> A
<input type="radio" name="groups[0]" value="A" /> A

Group 2:
<input type="radio" name="groups[1]" value="1" /> 1
<input type="radio" name="groups[1]" value="2" /> 2
<input type="radio" name="groups[1]" value="3" /> 3

[xyph]

From what I gather over the several posts he's made, he wants to use checkboxes.

[lukep11a]

Thanks for your help, yes all 12 groups have different values, that solved the issue of only being able to select 1 from each group and when I clicked submit everything seemed to work fine, but after checking my table in phpmyadmin no data had been submitted. I don't know if I have missed something. This is the beginning part of my form data:

 

<form name="form1" method="post" action="submitselections.php">
    <input name="userid" type="hidden" value="<?= $_SESSION['userid'] ?>">
    
    <div id="selection1">
    	<h3 class="heading_3">Group 1</h3>
      	<p class="radio"><input type="radio" name="groups[0][]" value="Man Utd" checked="checked">Man Utd (CL)</p>
        <p class="radio"><input type="radio" name="groups[0][]" value="Chelsea">Chelsea (CL)</p>
        <p class="radio"><input type="radio" name="groups[0][]" value="Man City">Man City (CL)</p>
        <p class="radio"><input type="radio" name="groups[0][]" value="Arsenal">Arsenal (CL)</p>
        <h3 class="heading_3">Group 2</h3>
        <p class="radio"><input type="radio" name="groups[1][]" value="Tottenham" checked="checked">Tottenham (EL)</p>
        <p class="radio"><input type="radio" name="groups[1][]" value="Liverpool">Liverpool</p>
        <p class="radio"><input type="radio" name="groups[1][]" value="Everton">Everton</p>

 

And this is the processing code on submit:

 

<?php
require_once "header.php";

//Process user input
$userid = mysql_real_escape_string(trim($_POST['userid']));
//Put group selections into arry in MySQL INSERT value format
$valuesAry = array();
foreach($_POST['groups'] as $group)
{
    $group = mysql_real_escape_string(trim($group));
    if($group != '')
    {   //Only add if not empty
        $valuesAry[] = "('{$userid}', '{$group}')";
    }
}

//Create ONE INSERT query
$query = "INSERT INTO selections VALUES " . implode(', ', $valuesAry);

	?>

 

I'm really sorry to keep bugging you about this.

[Psycho]

That's my fault. I noticed a problem with the sample radio fields I last posted and I made a quick update thinking I could fix it before you read the post. But, based on the code you just posted it looks like you used the code before I updated it. Remove the "[]" at the end of the field name.

 

Also, when having problems with database issues such as this it is always a good idea to echo the query to the page to see what it contains. When you say there is no data, do you mean no records were created? I would think that there would be records with a user ID but no value.

[lukep11a]

I have changed the field name, but it's still not submitting any records to the database, how do I echo the query to the page?

[Psycho]

I have changed the field name, but it's still not submitting any records to the database, how do I echo the query to the page?

 

Um,

echo $query;

 

But you should have some debugging code 9at least in development) to see the errors. Try running your query like this

$result = mysql_query($query) or die("Query: {$query}<br>Error: ".mysql_error());

[lukep11a]

Sorry, realised how simple that was after i re-read it.

 

It comes up with this which looks right to me, just don't understand why it's not adding it to the database..

 

INSERT INTO selections VALUES ('4', 'Man Utd'), ('4', 'Tottenham'), ('4', 'Stoke City'), ('4', 'Birmingham'), ('4', 'Millwall'), ('4', 'Barnsley'), ('4', 'Preston'), ('4', 'Rochdale'), ('4', 'Oldham'), ('4', 'Dag & Red'), ('4', 'Rotherham'), ('4', 'Cheltenham')

 

 

[xyph]

Your INSERT syntax is wrong. You _SHOULD_ define the columns you plan on inserting to.

 

INSERT INTO selections ( `userid`, `group` ) VALUES ('4', 'Man Utd') ... etc

 

Again, you should use the team's ID rather than name.

[lukep11a]

Thanks to you both it works now, I also had userid set to a primary key so think that's why it wasn't accepting the same value for multiple entries! Thanks again