insert/update into two tables with loop
hi,
I have a form, and I have it looping as the current page loops athrough all the football teams that play each other.
I have added a field for the data and added the ADDT date picker but it was not adding itself to the other date fields
I need to know how I can insert the data into two tables from the list, and not create any doubles of matches
I have removed the data picker as it does not add itself to each
<input type="text" name="date" id="date" tabindex="1" />
I have a form, and I have it looping as the current page loops athrough all the football teams that play each other.
I have added a field for the data and added the ADDT date picker but it was not adding itself to the other date fields
I need to know how I can insert the data into two tables from the list, and not create any doubles of matches
I have removed the data picker as it does not add itself to each
<input type="text" name="date" id="date" tabindex="1" />
This is the query getting the paired teams
mysql_select_db($database_db, $db);
$query_match_fixtures = "select m.match_id, date_format(m.date, '%W %D %M %Y') as mDate, m.time, t1.division, m.report, t1.team_name as team1_name, s1.score as score1, t2.team_name as team2_name, s2.score as score2, v.venue_name, r.fname, r.sname
from matches m left join (matchscores s1 left join team t1 on t1.team_id = s1.team) on (s1.match_id = m.match_id) left join (matchscores s2 left join team t2 on t2.team_id = s2.team) on (s2.match_id = m.match_id)
LEFT JOIN referee r ON r.ref_id = m.referee_id LEFT JOIN venue v ON v.venue_id = m.venue_id
where s1.team <> s2.team
group by match_id
order by m.match_id";
$match_fixtures = mysql_query($query_match_fixtures, $db) or die(mysql_error());
$row_match_fixtures = mysql_fetch_assoc($match_fixtures);
$totalRows_match_fixtures = mysql_num_rows($match_fixtures);
This is the table where the list of matches is shown.
I have modified it to include a form, but I am not sure how to get this part working
<h2><?php echo $row_match_fixtures['mDate']; ?></h2>
</div>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td><table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td height="25" bgcolor="#000000" class="tableTextHeading"><div align="center">Date</div></td>
<td height="25" bgcolor="#000000" class="tableTextHeading"><div align="center">Home</div></td>
<td height="25" bgcolor="#000000" class="tableTextHeading"><div align="center">Score</div></td>
<td height="25" bgcolor="#000000" class="tableTextHeading"><div align="center">Away</div></td>
<td height="25" bgcolor="#000000" class="tableTextHeading"><div align="center">Kick-Off</div></td>
<td height="25" bgcolor="#000000" class="tableTextHeading"><div align="center">Venue</div></td>
<td height="25" bgcolor="#000000" class="tableTextHeading"><div align="center">Referee</div></td>
</tr>
<?php do { ?>
<tr>
<form action="" method="post" name="fixtures">
<td><div align="center"><span id="sprytextfield1">
<label>
<input type="text" name="date" id="date" tabindex="1" />
<span class="textfieldRequiredMsg">A value is required.</span> </label>
</span></div></td>
<td><div align="center"><?php echo $row_match_fixtures['team1_name']; ?></div></td>
<td><div align="center"><?php echo $row_match_fixtures['score1']; ?> v <?php echo $row_match_fixtures['score2']; ?></div></td>
<td><div align="center"><?php echo $row_match_fixtures['team2_name']; ?></div></td>
<td><div align="center"><?php echo $row_match_fixtures['time']; ?></div></td>
<td><div align="center"><?php echo $row_match_fixtures['venue_name']; ?></div></td>
<td><div align="center"><?php echo $row_match_fixtures['fname']; ?> <?php echo $row_match_fixtures['sname']; ?></div></td>
<td>
</td>
</form>
</tr>
<?php } while ($row_match_fixtures = mysql_fetch_assoc($match_fixtures)); ?>
</table></td>
</tr>
</table>
ASKER
This is page with 3 update statements in.
It will not update the date textbox or the other input boxes
fixtures-edit.txt
It will not update the date textbox or the other input boxes
fixtures-edit.txt
>> I have removed the data picker as it does not add itself to each
The datepicker is a javascript object so it requires a unique ID to work properly. Merely adding a loop around a form will produce multiple objects with the same ID and that's why that doesn't work.
Overall, when you add a repeating region around a form tag, you create multiple forms but only one is submitted.
IOW, you have:
<form>
...
</form>
<form>
...
</form>
<form>
...
</form>
when you want:
<form>
...
...
...
</form>
One set of form tags and one submit button is needed. Once you have a single form, you can then take the data submitted and use it to populate two insert statements. You may need to convert your form fields to arrays, though.
The datepicker is a javascript object so it requires a unique ID to work properly. Merely adding a loop around a form will produce multiple objects with the same ID and that's why that doesn't work.
Overall, when you add a repeating region around a form tag, you create multiple forms but only one is submitted.
IOW, you have:
<form>
...
</form>
<form>
...
</form>
<form>
...
</form>
when you want:
<form>
...
...
...
</form>
One set of form tags and one submit button is needed. Once you have a single form, you can then take the data submitted and use it to populate two insert statements. You may need to convert your form fields to arrays, though.
ASKER
Hi,
I have added one submit to each row
It is still playing up as It will not update the fields.
So if I create an array, how will it know which record to update?
do I create the array as
<input name="s2" type="text" value="<?php echo $row_match_fixtures['score
Do I need to add anything else?
I have added one submit to each row
It is still playing up as It will not update the fields.
So if I create an array, how will it know which record to update?
do I create the array as
<input name="s2" type="text" value="<?php echo $row_match_fixtures['score
Do I need to add anything else?
<?php do { ?>
<form action="<?php echo $editFormAction; ?>" method="POST" name="fixtures">
<tr>
<td><div align="center"><span id="sprytextfield1">
<label>
<input name="date" type="text" class="date" size="8" style="text-align:center" value="<?php echo $row_match_fixtures['shortDate']; ?>" />
<span class="textfieldRequiredMsg">A value is required.</span>
</label>
</span></div></td>
<td><div align="center"><a href="match-player.php?id=<?php echo $row_match_fixtures['match_id']; ?>&team=<?php echo $row_match_fixtures['team1_id']; ?>"><?php echo $row_match_fixtures['team1_name']; ?></a></div></td>
<td><div align="center"><input name="s1" type="text" value="<?php echo $row_match_fixtures['score1']; ?>" size="1" style="text-align:center" />
v <input name="s2" type="text" value="<?php echo $row_match_fixtures['score2']; ?>" size="1" style="text-align:center" />
</div></td>
<td><div align="center"><a href="match-player.php?id=<?php echo $row_match_fixtures['match_id']; ?>&team=<?php echo $row_match_fixtures['team2_id']; ?>"><?php echo $row_match_fixtures['team2_name']; ?></a></div></td>
<td><div align="center"><span id="sprytextfield2">
<label>
<input name="time" type="text" size="3" value="<?php echo $row_match_fixtures['time']; ?>" style="text-align:center" />
</label>
<span class="textfieldInvalidFormatMsg">Invalid format.</span><span class="textfieldRequiredMsg">A value is required.</span></span></div></td>
<td><div align="center"><span id="spryselect1">
<label>
<select name="venue">
<?php
do {
?>
<option value="<?php echo $row_venue['venue_id']?>"<?php if (!(strcmp($row_venue['venue_id'], $row_venue['venue_name']))) {echo "selected=\"selected\"";} ?>><?php echo $row_venue['venue_name']?></option>
<?php
} while ($row_venue = mysql_fetch_assoc($venue));
$rows = mysql_num_rows($venue);
if($rows > 0) {
mysql_data_seek($venue, 0);
$row_venue = mysql_fetch_assoc($venue);
}
?>
</select>
</label>
<span class="selectRequiredMsg">Please select an item.</span></span></div></td>
<td><div align="center"><span id="spryselect2">
<label>
<select name="referee">
<?php
do {
?>
<option value="<?php echo $row_referee['ref_id']?>"<?php if (!(strcmp($row_referee['ref_id'], $row_referee['sname']))) {echo "selected=\"selected\"";} ?>><?php echo $row_referee['fname']?> <?php echo $row_referee['sname']?></option>
<?php
} while ($row_referee = mysql_fetch_assoc($referee));
$rows = mysql_num_rows($referee);
if($rows > 0) {
mysql_data_seek($referee, 0);
$row_referee = mysql_fetch_assoc($referee);
}
?>
</select>
</label>
<span class="selectRequiredMsg">Please select an item.</span></span></div></td>
<td><div align="center"><input name="Submit" type="submit" id="Submit" value="Save" /></div></td>
</tr>
<input name="match_id" type="hidden" value="<?php echo $row_match_fixtures['match_id']; ?>" />
<input name="mscore1" type="hidden" value="<?php echo $row_match_fixtures['mscore1']; ?>" />
<input name="mscore2" type="hidden" value="<?php echo $row_match_fixtures['mscore2']; ?>" />
<input name="team1_id" type="hidden" value="<?php echo $row_match_fixtures['team1_id']; ?>" />
<input name="score1" type="hidden" value="<?php echo $row_match_fixtures['score1']; ?>" />
<input name="team2_id" type="hidden" value="<?php echo $row_match_fixtures['team2_id']; ?>" />
<input name="score2" type="hidden" value="<?php echo $row_match_fixtures['score2']; ?>" />
<input type="hidden" name="MM_update" value="fixtures" />
</form>
<?php } while ($row_match_fixtures = mysql_fetch_assoc($match_fixtures)); ?>
ASKER
I have fixed the datepicker
Form fields that post to an array look like this:
<input name="s2[<?php echo $i ?>]" type="text" value="<?php echo $row_match_fixtures['score
In addition to adding the [] to the name of the field, you also initiate a counter variable before the loop opens
$i=0;
and increment it before the loop closes:
$i++;
while {condition};
Now you can use that to easily group the repeated fields and also give a unique ID to each field.
In order for the update to know which is which you will also have to echo the primary key as a repeating hidden field and then adapt this script:
http://www.phpeasystep.com/mysql/10.html
You can't really do it with the built-in behaviors.
<input name="s2[<?php echo $i ?>]" type="text" value="<?php echo $row_match_fixtures['score
In addition to adding the [] to the name of the field, you also initiate a counter variable before the loop opens
$i=0;
and increment it before the loop closes:
$i++;
while {condition};
Now you can use that to easily group the repeated fields and also give a unique ID to each field.
In order for the update to know which is which you will also have to echo the primary key as a repeating hidden field and then adapt this script:
http://www.phpeasystep.com/mysql/10.html
You can't really do it with the built-in behaviors.
ASKER
So do I have to put $i on each input?
And will I have to change the mysql statement?
And will I have to change the mysql statement?
ASKER
is there anyway I can get this to update the tables/?
ASKER
jason1178: I have used your method, but it still does not update to the database, it just ignores the query.
Show the current state of your code, please.
ASKER
Hi,
I have disabled two of the queries, so that I know atleast one works before adding the rest
I have disabled two of the queries, so that I know atleast one works before adding the rest
$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
$editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}
if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "fixtures")) {
if($Submit){
for($i=0;$i<$count;$i++){
mysql_select_db($database_db, $db);
$updateSQL1 = "UPDATE matches SET date='$date[$i]', time='$time[$i]', referee_id='$referee[$i]', venue_id='$venue[$i]' WHERE match_id=$match_id[$i]";
//do first query
/* mysql_query($updateSQL1, $db) or die(mysql_error());
$updateSQL2 = sprintf("UPDATE matchscores SET match_id=%s, team=%s, score=%s WHERE matchscores_id=%s",
GetSQLValueString($_POST['match_id'], "int"),
GetSQLValueString($_POST['team1_id'], "int"),
GetSQLValueString($_POST['score1'], "int"),
GetSQLValueString($_POST['mscore1'], "int"));
mysql_query($updateSQL2, $db) or die(mysql_error());
$updateSQL3 = sprintf("UPDATE matchscores SET match_id=%s, team=%s, score=%s WHERE matchscores_id=%s",
GetSQLValueString($_POST['match_id'], "int"),
GetSQLValueString($_POST['team2_id'], "int"),
GetSQLValueString($_POST['score2'], "int"),
GetSQLValueString($_POST['mscore2'], "int"));
mysql_query($updateSQL3, $db) or die(mysql_error());*/
}
}
/* mysql_select_db($database_db, $db);
$Result1 = mysql_query($updateSQL, $db) or die(mysql_error());*/
$updateGoTo = "fixtures-edit.php";
if (isset($_SERVER['QUERY_STRING'])) {
$updateGoTo .= (strpos($updateGoTo, '?')) ? "&" : "?";
$updateGoTo .= $_SERVER['QUERY_STRING'];
}
header(sprintf("Location: %s", $updateGoTo));
}
<?php // Count table rows
$count=mysql_num_rows($match_fixtures);
?>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td><table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td height="25" bgcolor="#000000" class="tableTextHeading"><div align="center">Date</div></td>
<td height="25" bgcolor="#000000" class="tableTextHeading"><div align="center">Home</div></td>
<td height="25" bgcolor="#000000" class="tableTextHeading"><div align="center">Score</div></td>
<td height="25" bgcolor="#000000" class="tableTextHeading"><div align="center">Away</div></td>
<td height="25" bgcolor="#000000" class="tableTextHeading"><div align="center">Kick-Off</div></td>
<td height="25" bgcolor="#000000" class="tableTextHeading"><div align="center">Venue</div></td>
<td height="25" bgcolor="#000000" class="tableTextHeading"><div align="center">Referee</div></td>
<td height="25" bgcolor="#000000" class="tableTextHeading"><div align="center">Update</div></td>
</tr>
<?php do { ?>
<?php $id[]=$row_match_fixtures['match_id']; ?>
<form action="<?php echo $editFormAction; ?>" method="POST" name="fixtures">
<tr>
<td><div align="center"><span id="sprytextfield1">
<label>
<input name="date[]" type="text" class="date" id="date" size="8" style="text-align:center" value="<?php echo $row_match_fixtures['shortDate']; ?>" />
<span class="textfieldRequiredMsg">A value is required.</span>
</label>
</span></div></td>
<td><div align="center"><a href="match-player.php?id=<?php echo $row_match_fixtures['match_id']; ?>&team=<?php echo $row_match_fixtures['team1_id']; ?>"><?php echo $row_match_fixtures['team1_name']; ?></a></div></td>
<td><div align="center"><input name="s1[]" type="text" value="<?php echo $row_match_fixtures['score1']; ?>" size="1" style="text-align:center" id="s1" />
v <input name="s2[]" type="text" value="<?php echo $row_match_fixtures['score2']; ?>" size="1" style="text-align:center" id="s2" />
</div></td>
<td><div align="center"><a href="match-player.php?id=<?php echo $row_match_fixtures['match_id']; ?>&team=<?php echo $row_match_fixtures['team2_id']; ?>"><?php echo $row_match_fixtures['team2_name']; ?></a></div></td>
<td><div align="center"><span id="sprytextfield2">
<label>
<input name="time[]" type="text" size="3" value="<?php echo $row_match_fixtures['time']; ?>" style="text-align:center" id="time" />
</label>
<span class="textfieldInvalidFormatMsg">Invalid format.</span><span class="textfieldRequiredMsg">A value is required.</span></span></div></td>
<td><div align="center"><span id="spryselect1">
<label>
<select name="venue[]" id="venue">
<?php
do {
?>
<option value="<?php echo $row_venue['venue_id']?>"<?php if (!(strcmp($row_venue['venue_id'], $row_venue['venue_name']))) {echo "selected=\"selected\"";} ?>><?php echo $row_venue['venue_name']?></option>
<?php
} while ($row_venue = mysql_fetch_assoc($venue));
$rows = mysql_num_rows($venue);
if($rows > 0) {
mysql_data_seek($venue, 0);
$row_venue = mysql_fetch_assoc($venue);
}
?>
</select>
</label>
<span class="selectRequiredMsg">Please select an item.</span></span></div></td>
<td><div align="center"><span id="spryselect2">
<label>
<select name="referee[]" id="referee">
<?php
do {
?>
<option value="<?php echo $row_referee['ref_id']?>"<?php if (!(strcmp($row_referee['ref_id'], $row_referee['sname']))) {echo "selected=\"selected\"";} ?>><?php echo $row_referee['fname']?> <?php echo $row_referee['sname']?></option>
<?php
} while ($row_referee = mysql_fetch_assoc($referee));
$rows = mysql_num_rows($referee);
if($rows > 0) {
mysql_data_seek($referee, 0);
$row_referee = mysql_fetch_assoc($referee);
}
?>
</select>
</label>
<span class="selectRequiredMsg">Please select an item.</span></span></div></td>
<td><div align="center"><input name="Submit" type="submit" id="Submit" value="Save" /></div></td>
</tr>
<input name="match_id[]" type="hidden" id="match_id" value="<?php echo $row_match_fixtures['match_id']; ?>" />
<input name="mscore1[]" type="hidden" id="mscore1" value="<?php echo $row_match_fixtures['mscore1']; ?>" />
<input name="mscore2[]" type="hidden" id="mscore2" value="<?php echo $row_match_fixtures['mscore2']; ?>" />
<input name="team1_id[]" type="hidden" id="team1_id" value="<?php echo $row_match_fixtures['team1_id']; ?>" />
<input name="score1[]" type="hidden" id="score1" value="<?php echo $row_match_fixtures['score1']; ?>" />
<input name="team2_id[]" type="hidden" id="team2_id" value="<?php echo $row_match_fixtures['team2_id']; ?>" />
<input name="score2[]" type="hidden" id="score2" value="<?php echo $row_match_fixtures['score2']; ?>" />
<input type="hidden" name="MM_update" value="fixtures" />
</form>
<?php } while ($row_match_fixtures = mysql_fetch_assoc($match_fixtures)); ?>
</table></td>
</tr>
</table>
ASKER
anyone?
I'm still looking at it but the code is kind of messy and I see a couple of weird lines like 66:
<?php $id[]=$row_match_fixtures[
What's going on there?
<?php $id[]=$row_match_fixtures[
What's going on there?
ASKER
ASKER
Can I update the tables one at a time without using an array?
ASKER
Have one form but do two tasks, have two submit buttons:
1 will submit into the matches table
and the other to insert into the matchscores table
1 will submit into the matches table
and the other to insert into the matchscores table
>> Can I update the tables one at a time without using an array?
If you are adding or updating multiple records, you need the array. Otherwise, you could make each record its own form and have that form do a single update followed by a single insert.
If you are adding or updating multiple records, you need the array. Otherwise, you could make each record its own form and have that form do a single update followed by a single insert.
ASKER
Basically I need 1 update and two inserts
I cannot seem to get this array form working, I have tried your method and other methods and I am really struggling with this.
I think this will be the last job I take on.
I cannot seem to get this array form working, I have tried your method and other methods and I am really struggling with this.
I think this will be the last job I take on.
ASKER
This does not work either,
$limit = count($match_id);
for($i=0;$i<$limit;$i++) {
if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "fixtures")) {
$updateSQL = sprintf("UPDATE matches SET `date`=%s, `time`=%s, referee_id=%s, venue_id=%s WHERE match_id=%s",
GetSQLValueString($_POST['date[]'], "date"),
GetSQLValueString($_POST['time[]'], "text"),
GetSQLValueString($_POST['referee[]'], "int"),
GetSQLValueString($_POST['venue[]'], "int"),
GetSQLValueString($_POST['match_id[]'], "int"));
mysql_select_db($database_db, $db);
$Result1 = mysql_query($updateSQL, $db) or die(mysql_error());
$updateGoTo = "fixtures-edit.php";
if (isset($_SERVER['QUERY_STRING'])) {
$updateGoTo .= (strpos($updateGoTo, '?')) ? "&" : "?";
$updateGoTo .= $_SERVER['QUERY_STRING'];
}
header(sprintf("Location: %s", $updateGoTo));
}
}
One update and two inserts from one set of form fields or from a repeated region? The former is easy enough...the data from the one form submission can be used multiple times after submission.
If you are presenting data in a repeating region and need to do multiple actions from that, you need to either master the sample I showed you or (if you want to throw money at a solution) purchase the DataAssist extension from WebAssist.com.
With that, you can create via DW behaviors a repeating region that will automatically work in array form with the DataAssist Update Multiple Records and Insert Multiple Records behaviors.
If you are presenting data in a repeating region and need to do multiple actions from that, you need to either master the sample I showed you or (if you want to throw money at a solution) purchase the DataAssist extension from WebAssist.com.
With that, you can create via DW behaviors a repeating region that will automatically work in array form with the DataAssist Update Multiple Records and Insert Multiple Records behaviors.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.
ASKER
CREATE TABLE IF NOT EXISTS `matches` (
`match_id` int(8) NOT NULL auto_increment,
`date` date default NULL,
`time` varchar(5) default NULL,
`report` longtext,
`referee_id` int(8) NOT NULL,
`season_id` int(8) NOT NULL,
`venue_id` int(8) NOT NULL,
PRIMARY KEY (`match_id`),
KEY `referee_id` (`referee_id`),
KEY `venue_id` (`venue_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=21 ;
CREATE TABLE IF NOT EXISTS `matchscores` (
`matchscores_id` int(8) NOT NULL auto_increment,
`match_id` int(8) NOT NULL,
`team` int(8) NOT NULL,
`score` int(8) default NULL,
PRIMARY KEY (`matchscores_id`),
KEY `match_id` (`match_id`,`team`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=39 ;
Open in new window