Not sure what type of loop to use

AzzyDude24601 · 05-30-2012, 04:03 PM

Hi everyone, I've got this code set up where basically it 'while loops' out this list of people in a database (first name, second name, etc.) and the idea is that you can organize these people into groups.

So this translates to having a form field for whatever you want to call the group, then you tick the boxes next to each person you want in this new group. And when you click SAVE, it updates the "group" column for each of those people in the database with the new group name.

So I think I understand how to do this in theory. This is the PHP MySQL I have written so far:

mysql_query("UPDATE `Participant` SET `group`='".$_POST["group"]."' WHERE `idParticipant`='".$_POST["check"]."';");

So I believe I need some kind of loop so that it runs this query for each POST'd idParticipant so it updates each one in turn. I'm just not sure which loop to use and what the syntax would look like. I'm gonna continue researching it online but if you have any advice, please let me know.

Fou-Lu · 05-30-2012, 04:07 PM

No don't loop it like this; limit SQL to as few requests as you possibly can. In the case of an UPDATE, you can use an IN clause for the WHERE condition.
What is the $_POST['group'] to $_POST['check'] relationship? As in, it sounds like you have many perticipant's available, and potentially many groups, so what does that look like structurally from your HTML?

AzzyDude24601 · 05-30-2012, 04:13 PM

Quote:

Originally Posted by Fou-Lu

No don't loop it like this; limit SQL to as few requests as you possibly can. In the case of an UPDATE, you can use an IN clause for the WHERE condition.
What is the $_POST['group'] to $_POST['check'] relationship? As in, it sounds like you have many perticipant's available, and potentially many groups, so what does that look like structurally from your HTML?

This is the body of my HTML:

<body>

<form name="myform" id="myform" action="" method="POST">

<label for="group" id="group_label">Group Name: </label>
<input type="text" name="group" maxlength="45" id="group" value=""/>
<br><br>

Member List:<br><br>

<?php

$query = mysql_query('SELECT * FROM `Participant`');
if (!$query)
{
die('Query failure: '.mysql_error());
}

while($rows = mysql_fetch_array($query)):

$id = $rows['idParticipant'];
$fname = $rows['firstName'];
$sname = $rows['secondName'];
$facility = $rows['facility'];

echo "<input type='checkbox' name='check' id='$id' value='$id' /><br>";
echo "First Name: $fname<br>";
echo "Second Name: $sname<br>";
echo "Facility: $facility<br><br>";

endwhile;

?>

<input type="submit" name="submit" value="Save New Group">

</form>

<form>

<input type="button" value="Back" onclick="window.location.href='home.php'">

</form>

</body>

Not sure what you mean by the relationship between $_POST['group'] and $_POST['check']? We need the $_POST['group'] so we know what to insert into the group column of the chosen participants in the list. And we need the $_POST['check'] so we know which participants need to be updated. That make sense?

AzzyDude24601 · 05-30-2012, 04:28 PM

Maybe I've got this all wrong in my head >_< ...what's the RIGHT way to do this?

Fou-Lu · 05-30-2012, 04:46 PM

No this is sufficient. The relationship is many participants to one group from your form.
The query will be very simple. Use the checkbox array to create an IN clause:

PHP Code:


			
if (isset($_POST['check']) && count($_POST['check']) > 0)
{
    $sGroup = mysql_real_escape_string($_POST['group']);    
    $aParticipants = array_map('mysql_real_escape_string', $_POST['check']);
    $sIn = implode("', '", $aParticipants);
    $sQry = "UPDATE Participant SET group = '$sGroup' WHERE idParticipant IN ('$sIn')";
    mysql_query($sQry);
}

You may want to comment out that query and print the $sQry to verify that it looks correct first. But that looks like it will work.

AzzyDude24601 · 05-30-2012, 04:55 PM

Hey thanks for getting back to me. My PHP knowledge is quite inferior to yours so I apologize that I can't debug this myself. The following two lines returned the following errors respectively:

$aParticipants = array_map('mysql_real_escape_string', $_POST['check']);
$sIn = implode("', '", $aParticipants);

Warning: array_map() [function.array-map]: Argument #2 should be an array in C:\wamp\www\CURRENT\process_group.php on line 8
Warning: implode() [function.implode]: Invalid arguments passed in C:\wamp\www\CURRENT\process_group.php on line 9

As I said you know more than me...but I think the problem is it's expecting an array but all it's getting is some POST data. Would we not have to convert all the POST data into an array first...?

Fou-Lu · 05-30-2012, 05:46 PM

Change the HTML for the input name to 'check[]'. That should fix that.

AzzyDude24601 · 05-30-2012, 06:14 PM

Quote:

Originally Posted by Fou-Lu

Change the HTML for the input name to 'check[]'. That should fix that.

Yeah, that did the trick! But now it doesn't seem to like our MySQL syntax. It said the following:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group = 'test123' WHERE idParticipant IN ('4', '7')' at line 1

This is good news; I think we're super close. However I don't have much experience using the IN clause. Can you find a mistake there?

Fou-Lu · 05-30-2012, 06:18 PM

Did I spell the property names correctly?
Its also possible that your SQL is strict. Since there are numbers, they should be treated as such:

PHP Code:


			
    $sIn = implode(", ", $aParticipants);
    $sQry = "UPDATE Participant SET group = '$sGroup' WHERE idParticipant IN ($sIn)";
    mysql_query($sQry) or die(mysql_error());

Edit:
Lol, the problem is that GROUP is a reserved word (ie: GROUP BY). Add backticks around the group word.

AzzyDude24601 · 05-30-2012, 06:26 PM

Quote:

Originally Posted by Fou-Lu

Edit:
Lol, the problem is that GROUP is a reserved word (ie: GROUP BY). Add backticks around the group word.

LOL, yes, that was it. Perfect. Thanks for all your help! ^_^