dynamic forms with mysql and use of arrays for input

Question

sleign 1 Light Poster

14 Years Ago

I've been working at this most of the day, trying to figure out how I can accomplish this sort of form, even looking through countless sites for anything vaguely close to what I'm trying to do for help on logic or syntax behind this (to little to no avail, of course lol). It's a little over my head in terms of sophistication. I've done dynamic forms before, but not like this.

What I'm trying to do is this:
A user needs to update points for attendance in 3 different categories for all people registered to the site - all at once.

There are a total of 3 categories.
I can do the queries fine enough to
(a.) get the total amount of rows and
(b.) the names of all people registered within the site

...and whatever else I might need - that's pretty easy stuff for me these days.

when I do the second query (b) the form will then display the 3 categories for that person like such:
person's Name > cat1 (input text field) > cat2 (input text field) > cat3 (input text field)

and so on and so forth until all users have been populated in this form with each of the 3 categories and input text fields next to their name.

the user would then enter an integer x-xxx (default set to 0) for all users / categories and hit one submit button which would then go to the processing page, which would update the database by adding the new values to the old ones for all fields.

so if, say 5 people were registered, then all 15 values would be submitted at the same time and then updated into the db under their corresponding columns at the same time.

I'm trying to do this with 4 arrays parallel to each other that store all values so that I can put them in one or a few sessions, and transfer all the values at once to the next page.

I've done something similar to this, but not using parallel arrays and without throwing in retrieved data from a db, but since I am - the only way I'm familiar with outputting data from a database for inclusion in a form would be using a while loop like:

while($data = mysql_fetch_array($query))

I've never attempted to output information from a db, and retrieve 1 or more arrays worth of data on the same form before, and I'm finding out very quickly why - its very frustrating...

furthermore, I'm not exactly sure how I would be able to implement arrays into the form dynamically, so that it can store all the values of however many people for all 3 categories before $_POST happens, so the arrays of data are not lost.

Can anyone point me in the right direction, or give me a rough sketch of how this should work? Normally I try to post what code I have, but none of my code works, except for the layout itself including the names, and it's kind of chasing a train wreck at the moment, so I feel this is sort of a mute point to do so.

any help is appreciated.

php

2 Contributors
4 Replies
116 Views
1 Week Discussion Span
Latest Post 14 Years Ago Latest Post by sleign

All 4 Replies

Atli 182 Posting Pro

14 Years Ago

Hey.

You can set use the name attribute of an <input> just as you would an array inside PHP. So you can do stuff like:

<?php
$result = mysql_query("SELECT id, name FROM users");

echo '<form action="?" method="post"><table>'
while($row = mysql_fetch_assoc($result) 
{
    echo <<<HTML
    <tr>
        <td>{$row['name']}</td>
        <td><input type="text" name="cat[{$row['id']}][1]" ></td>
        <td><input type="text" name="cat[{$row['id']}][2]" ></td>
        <td><input type="text" name="cat[{$row['id']}][3]" ></td>
    </tr>
HTML;
}
echo '<tr><td rowspan="3"><input type="submit" /></td></tr>';
echo '</table></form>';
?>

And use that to update the users like so:

<?php
foreach($_POST['cat'] as $_user_id => $_cats)
{
    foreach($_cats as &$_cat) {
        // Manditory securiy measures, even in example snippets :)
        $_cat = mysql_real_escape_string($_cat);
    }
    $sql = "UPDATE user SET
                cat1 = '{$_cats[1]}',
                cat2 = '{$_cats[2]}',
                cat3 = '{$_cats[3]}'
            WHERE id = {$_user_id}
            LIMIT 1";
    mysql_query($sql) or trigger_error("Failed to update cats for user {$_user_id}"):
}
?>

See what I mean?

Edited 14 Years Ago by Atli because: Typos.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

sleign 1 Light Poster · Answer 1 · 2009-11-20T17:50:17+00:00

sorry its taken awhile to get back to you. I'm in my last week of school so I've been studying for finals n such...

Anyways, I checked out your code and have been playing with it a little bit. I hate copy/pasting other peoples' code because it doesn't really give me a full understanding of what's going on and why.

So I've tried to sort of write it in my own way, the syntax throws me off a little bit, but I understand the jist of what's being done. It seems that a few parallel arrays are being used such that:

name array
-> cat1 parallel to name
-> cat2 parallel to name
-> cat3 parallel to name

at least this is my understanding of the approach.

I wasn't really able to get far, so worst case scenario I resort back to exactly what you put down. I had to hash out a few typos but the code will put it into arrays and post on the next page, however, I'm noticing some problems and I don't really see where they're coming from.

It seems that every 3rd element the array will skip one, and about the 4th row or so that it does, it seems that it's trying to make up for the skipped row and bunch a few into the same array when they should be split up and put into separate arrays.

At first I thought that maybe it's because for the arrays you started at [1] then [2] and [3] - and maybe it should start at [0] thru [2]. I realize, however, this is not the case. It seems to neglect the first array altogether when I do this. I'm thinking that these indexes are for numbering the arrays themselves, not the actual indices of each array.

To see what I have working in action you can view it here

I'm sort of stumped as to why for every row it skips 1. I get this result by starting with 1 and incrementing by 1 for all 18 fields, so you end up with 1-18. I did this to make sure the arrays were getting input/output right before I did any updating to the database.

The only other problem I have is that I can't seem to get the name field to carry across either. It shows up fine on the form, but on the next page the name is neglected. I've tried a few different ways to catch the name to no avail. I'm using a hidden field right now to try and post it to the next page.

My code I have is as follows:

First Page:

<?php //connect to db info here ?>

//header stuff here

<body>
<div id="contents">

<?php
$result = mysql_query("SELECT * from users where userAccess='Registered' or
			userAccess='Admin'");

echo '<form action="receive.php" method="post"><table style="border: none;">';
while($row = mysql_fetch_assoc($result))
{
    echo <<<HTML
    <tr>
        <td class="hideIt">{$row['userName']}</td>
	
        <td class="hideIt"><input type="text" class="inputPts" name="cat[{$row['seaPoints']}][1]" ></td>
        <td class="hideIt"><input type="text" class="inputPts" name="cat[{$row['skyPoints']}][2]" ></td>
        <td class="hideIt"><input type="text" class="inputPts" name="cat[{$row['dynPoints']}][3]" ></td>
	<td class="hideIt"><input type="hidden" class="inputPts" name="cat[{$row['userName']}][4]"></td>
    </tr>
HTML;
}
echo '<tr><td rowspan="3"><input type="submit" value="Update" class="button" /></td></tr>';
echo '</table></form>';
?>

</div>
</body>
</html>

Second Page

<?php //connect to db info here ?>

//header stuff here

<body>
<div id="contents">
<?php


foreach($_POST['cat'] as $_user_id => $_cats)
{
    foreach($_cats as $_cat) {
        $_cat = mysql_real_escape_string($_cat);
    }

	echo "Sea:{$_cats[1]}<br/>";
	echo "Sky:{$_cats[2]}<br/>";
	echo "Dyn:{$_cats[3]}<br/>";
	echo "Name:{$_cats[4]}<br/>___________<br/>";
}
?>
</div>
</body>
</html>

Any ideas?

Atli 182 Posting Pro · Answer 2 · 2009-11-20T18:37:59+00:00

Ahh yea, sorry. I should have explained that a little better.

You can use the <input> elements just like you would a normal variable within PHP, and once it is submitted, it will appear as an element inside the $_POST super-global.

What you did wrong in your code was that you effectively used the value of the variable as the variable name, and left the value empty.

This line:

<td class="hideIt"><input type="text" class="inputPts" name="cat[{$row['seaPoints']}][1]" ></td>

Once execute by PHP would appear something like:

<td class="hideIt"><input type="text" class="inputPts" name="cat[<row_seaPoints_value>][1]" ></td>

Which creates an element of "cat" where the value of $row['seaPoints'] is the name of the new element, and it is left without a value.

Which would leave you with a $_POST['cat'] array looking like:

$_POST['cat'] = Array(
    'SeaPoint Value #1' => Array( 
        1 => ""
    ),
    'SeaPoint Value #2' => Array( 
        1 => ""
    ),
    'SeaPoint Value #3' => Array( 
        1 => ""
    ),
);

What you wanted to do is create an element under "cat" named "seaPoints" and pass the value as the value. Also, because you have a series of 'seaPoints' values you want to pass, you need to further identify it by the 'id' (or name) of the row it is meant for.

The goal would be to create an $_POST['cat'] array looking like:

$_POST['cat'] = Array(
    'seaPoints' => Array(
        [1] => 'SeaPoint Value #1',
        [2] => 'SeaPoint Value #2',
        [3] => 'SeaPoint Value #3'
    )
);

So you would want the PHP to create a HTML row that looks something like:

<td class="hideIt"><input type="text" class="inputPts" name="cat['seaPoints'][<row_id>]" value="<row_value>" ></td>

(Where, <row_id> and <row_value> would be replaced by actual ID's and values)

Then, once the form is submitted to PHP, you could fetch it from the $_POST array like:

echo $_POST['seaPoints'][<row_id>];

And, because you would have a number of them, each with a different <row_id>, you could loop through them like so:

foreach($_POST['seaPoints'] as $_rowID => $_rowValue)
{
    echo $_rowID, " = ", $_rowValue, "<br />";
}

I hope that helps to explain this a bit better.
Let me know is something is unclear.

sleign 1 Light Poster · Answer 3 · 2009-11-26T07:18:08+00:00

I think we were coming from two different perspectives on this, but I managed to solve this problem.

I wasn't exactly trying to pull any other information out of the database with the exception of the name and the ID that corresponds to the name - the values would default to 0.

The user inputs the value, if any, and that value would add to the current value of what is stored in the corresponding column.

This probably isn't the best approach to this, however, it works so if it ain't broke don't fix it lol.

Anyways, my code is:

1st page:

//db connect info, header, etc etc

//form
<?php
while($row = mysql_fetch_assoc($result))
{
    ?>
    <tr>
	<td class="hideIt">
	<input type="hidden" name="name[name][]" value="<?php echo $row['userName'];?>">
	<input type="hidden" name="name[userID][]" value="<?php echo $row['userID'];?>">

	<?php echo $row['userName'];?></td>
	<td class="hideIt"><input type="text" class="inputPts" name="name[sea][]" value="0" maxlength="3"></td>
	<td class="hideIt"><input type="text" class="inputPts" name="name[sky][]" value="0" maxlength="3"></td>
	<td class="hideIt"><input type="text" class="inputPts" name="name[dyn][]" value="0" maxlength="3"></td>
	<?php if($row['lastUpdate']=="0000-00-00") { echo "<td class='hideIt'><i>Member account not approved</i></td>"; }?>
    </tr>
	<?php 
}

echo '<tr><td colspan="4" style="text-align: right" class="hideIt"><input type="submit" value="Update" class="button" /></td></tr>';
echo '</table></form>';
}
else
	echo "<i>There are no pending users that need their points linked.</i>";
?>

and then to process the code:
2nd page:

//db connect info, header, variables, etc etc

$seaPts = array();
$skyPts = array();
$dynPts = array();
$getCurrentSeaPoints=mysql_query("select seaPoints, skyPoints, dynPoints from users where linkNeed=1 order by userID asc");

while($points = mysql_fetch_array($getCurrentSeaPoints))
{
	$seaPts[] = $points['seaPoints'];
	$skyPts[] = $points['skyPoints'];
	$dynPts[] = $points['dynPoints'];
}
####################


####################
# ADD TOTALS AND UPDATE TO DB
####################
for($i=0; $i<$cap; $i++)
{
	$personID = $people['userID'][$i];
	$addSea = $people['sea'][$i];
	$addSky = $people['sky'][$i];
	$addDyn = $people['dyn'][$i];

	$newSeaTotal = 0;
	$newSkyTotal = 0;
	$newDynTotal = 0;

	$newSeaTotal = $addSea + $seaPts[$i];
	$newSkyTotal = $addSky + $skyPts[$i];
	$newDynTotal = $addDyn + $dynPts[$i];

	$update = mysql_query("update users set seaPoints='$newSeaTotal', skyPoints='$newSkyTotal', dynPoints='$newDynTotal', lastUpdate=now(), linkNeed=0 where '$personID' = userID");
	if(!$update) {
		echo "Error updating database. <br/>";
		mysql_close();
		exit();  }

}
mysql_close();

this seems to work very well

what it will basically do is this:
a user signs up for the site
admin must verify / approve the account

if the user is a current member a flag is set to display this user in the above code, otherwise if the person is a prospective member / interested person then there is no flag set because this person would have no points

once the current member's account is approved, and the flag is set to display this person in the above code, an admin can add points to the user in any combination of the 3 categories, using the ID to link the name and 3 categories in a multidimensional array (I think its technical term is associative array?)

the second page will parse that array for the ID and 3 categories - user name is pretty much neglected at this point. each of the 3 categories is added to the total value in each of the 3 columns in the database, and then updated to the new total

You have given me some insight and led me in the direction I needed to go on, so I thank you for that, tho I sorta had to branch off from your solution.

I didn't want to leave you hanging tho as to whether or not I got it, so I figured I'd let u know and throw u the code for what I did.

Thank you again for your help :)

dynamic forms with mysql and use of arrays for input

Recommended Answers Collapse Answers

All 4 Replies

Recommended Answers