Help Inserting Multiple Arrays

[lukep11a]

Hi, this is the first time I have used arrays in php. I have searched around many other posts but can't seem to get my head around it.

 

I am trying to process multiple arrays entered on a form. The number of entries could be different each time.

 

This is the code I have so far:

 

<?php
function submitResults($fixture_id, $ht_goals, $at_goals, $et, $h_pens, $a_pens, $postponed)
{
//Put group selections into arry in MySQL INSERT value format
$valuesAry = array();
foreach(array_keys($fixture_id) as $n)
{
$valuesAry[] = "($fixture_id[$n], $ht_goals[$n], $at_goals[$n], $et[$n], $h_pens[$n], $postponed[$n])";
}
$query = "INSERT INTO test_results (fixture_id, ht_goals, at_goals, et, pens, postponed)
VALUES ".implode(', ', $valuesAry);
$result = mysql_query($query) or die("Query: {$query}<br>Error: ".mysql_error());
return $result;
}
?>

 

It currently displays an error: Error: 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 ' , , , ), (109, , , , , ), (162, , , , , ), (316, , , , , ), (563, , , , , ), (6' at line 2

 

Any help anyone can give me would be greatly appreciated.

Edited December 10, 2012 by PFMaBiSmAd
removed micro-font formatting

[Barand]

echo $query;

 

That might be a good place to start to see the actual query that was submitted

[lukep11a]

echo $query; doesn't output anything different than the error message I was getting before

[Barand]

Well thanks so much for your assistance in an attempt to help you. Good luck.

[Psycho]

In your foreach() loop to create the "values" the arrays

foreach(array_keys($fixture_id) as $n)
{
   $valuesAry[] = "($fixture_id[$n], $ht_goals[$n], $at_goals[$n], $et[$n], $h_pens[$n], $postponed[$n])";
}

The variables $ht_goals[$n], $at_goals[$n], $et[$n], $h_pens[$n], and $postponed[$n] do not exist or do not have a value.

 

How are these arrays generated? It may help to show the form.

[lukep11a]

Each row in the form as below:

 

echo '<tr>
	 <input name="fixture_id[]" value="'.$row['fixture_id'].'" type="hidden" />
	 <td>'.date("d/m/y H:i", strtotime($row['date'])).'</td>
	 <td>'.$row['short_name'].'</td>
	 <td align="right">';
if ($row['home_id'] <= 92) echo "<a href='../stats/team-data/".str_replace($search, $replace, $row['home_team'])."' title='".$row['home_team']."Team Data'>".$row['home_team']."</a>";
else echo $row['home_team'];
echo '</td>
	 <td><input name="home_pens[]" type="checkbox" value="'.$row['home_id'].'" /></td>
	 <td><input name="home_score[]" type="text" size="2" maxlength="2" /></td>
	 <td>-</td>
	 <td><input name="away_score[]" type="text" size="2" maxlength="2" /></td>
	 <td><input name="away_pens[]" type="checkbox" value="'.$row['away_id'].'" /></td>
	 <td><input name="aet[]" type="checkbox" value="1" /></td>
	 <td>';
if ($row['away_id'] <= 92) echo "<a href='../stats/team-data/".str_replace($search, $replace, $row['away_team'])."' title='".$row['away_team']."Team Data'>".$row['away_team']."</a>";
else echo $row['away_team'];
echo '</td>
	 <td><input name="postponed[]" type="checkbox" value="1" /></td>';

[Psycho]

Well, one glaring problem I see is that some of those fields are checkboxes. Based on my understanding you have "groups" of fields and you are trying to associate the fields based upon their index. The problem is that checkboxes are not passed in the POST data if they are not checked. That means if the "home_pens" checkbox is not checked for group 1 or group 2, then the group 3 checkbox will have an index of 0 and be associated with the textboxes from group 1. You will want to manually create indexes for the array field names in your form. Just create a variable (e.g. $i) and set it to 0 before the loop, then increment it at the end of each iteration of the loop. Then use it in the form fields like this

echo "<td><input name="home_pens[$i]" type="checkbox" value="'.$row['home_id'].'" /></td>";

 

Aside from that you still haven't shown how you are creating the variables from the POST data that are then passed to the function. As I stated previously the values don't exist or are empty. Where is the code that calls the function submitResults() and, more importantly, where are you creating the variables you send to that function.

[lukep11a]

Thank you for your advice regarding manually creating indexes, i will implement this into my code. Sorry for not providing you with more information sooner. I guess I was trying to just post what i thought was causing the problem so there wasn't loads of code to go through.

 

This is the code I am using on the page to either show the form or if submit button has been pressed, call the submitResults function:

 

<?php
if (isset($_POST['submit_results'])){

if (submitResults($_POST['fixture_id'], $_POST['home_score'], $_POST['away_score'], $_POST['aet'], $_POST['home_pens'], $_POST['away_pens'], $_POST['postponed'])){

echo 'Confirmation Message';

}else {

show_enter_results();
}

} else {
// has not pressed the submit button
show_enter_results();
}
echo $query;
?>

 

The function below is used to show the form, the form successfully displays any football fixtures that have been played but not had results entered yet.

 

function show_enter_results()
{
$self= htmlspecialchars($_SERVER['PHP_SELF']);
echo '<form action="'.$self.'" method="post">
<table>
<tr>
<td>Date</td>
<td>Comp</td>
<td align="right">Home Team</td>
<td>P</td>
<td></td>
<td></td>
<td></td>
<td>P</td>
<td>AET</td>
<td>Away Team</td>
<td>Postponed?</td>
</tr>';

$query="SELECT home.team_id AS home_id, home.team_name AS home_team, away.team_id AS away_id, away.team_name AS away_team, fixtures.fixture_id, fixtures.date, competitions.short_name
FROM fixtures
LEFT JOIN teams home
ON fixtures.ht_id = home.team_id
LEFT JOIN teams away
ON fixtures.at_id = away.team_id
LEFT JOIN results
ON fixtures.fixture_id = results.fixture_id
LEFT JOIN competitions
ON fixtures.comp_id = competitions.comp_id
WHERE results.fixture_id IS NULL AND fixtures.date <= CONVERT_TZ(NOW(), '-08:00', '+00:00')
GROUP BY fixtures.fixture_id
ORDER BY DATE(fixtures.date) ASC, competitions.comp_id ASC, fixtures.date ASC, home_team ASC";
$numresults = mysql_query($query);
$numrows = mysql_num_rows($numresults);
if ($numrows == 0) {
echo "<p class='normal'>All results are up to date.</p>";
}
$result = mysql_query($query) or die(mysql_error());
$i = 0;
while($row = mysql_fetch_assoc($result)) {
$search = array(' & ', ' ');
$replace = array('-and-', '-');

echo '<tr>
<input name="fixture_id[$i]" value="'.$row['fixture_id'].'" type="hidden" />
<td>'.date("d/m/y H:i", strtotime($row['date'])).'</td>
<td>'.$row['short_name'].'</td>
 <td align="right">';
 if ($row['home_id'] <= 92) echo "<a href='../stats/team-data/".str_replace($search, $replace, $row['home_team'])."' title='".$row['home_team']."Team Data'>".$row['home_team']."</a>";
 else echo $row['home_team'];
 echo '</td>
<td><input name="home_pens[$i]" type="checkbox" value="'.$row['home_id'].'" /></td>
<td><input name="home_score[$i]" type="text" size="2" maxlength="2" /></td>
<td>-</td>
<td><input name="away_score[$i]" type="text" size="2" maxlength="2" /></td>
<td><input name="away_pens[$i]" type="checkbox" value="'.$row['away_id'].'" /></td>
<td><input name="aet[$i]" type="checkbox" value="1" /></td>
<td>';
 if ($row['away_id'] <= 92) echo "<a href='../stats/team-data/".str_replace($search, $replace, $row['away_team'])."' title='".$row['away_team']."Team Data'>".$row['away_team']."</a>";
 else echo $row['away_team'];
 echo '</td>
<td><input name="postponed[$i]" type="checkbox" value="1" /></td>';
$i++;
}
echo '</table>
<input name="submit_results" type="submit" value="Submit" />
</form>';


}

 

 

If I haven't properly explained how I am creating the variables from the POST data, or where are I am creating the variables that are sent to the function then I apologise. I get a bit lost with the technical terms used as I am still relatively new to PHP.

[Psycho]

So, you are sending the explicit $_POST[fieldname] variables to the function. So, as stated before you will need to define the index value to be the same for each "set" of field or else they will not be grouped together. Then to solve your problem you need to ensure that you are checking if each field has a value. If it doesn't you need to set an appropriate value for the INSERT query.

 

So, let's say that for the first set of fields the 'home_pens' checkbox was not set. In your insert query you are trying to insert a value for that field for that record. Since the user did not check that checkbox the field doesn't even exist in the post data. So, you need to put 'something' for that value in the insert query. In fact you should validate/sanitize all of the data. Here is an example:

foreach(array_keys($fixture_id) as $n)
{
   $id = intval($fixture_id[$n]);
   $htGoals = isset($ht_goals[$n]) ? intval($ht_goals[$n]) : 0;
   $atGoals = isset($at_goals[$n]) ? intval($at_goals[$n]) : 0;
   $etVal = isset($et[$n]) ? intval($et[$n]) : 0;
   $hPens = isset($h_pens[$n]) ? intval($h_pens[$n]) : 0;
   $post= isset($postponed[$n]) ? intval($postponed[$n]) : 0;
   $valuesAry[] = "($id, $htGoals, $atGoals,$etVal, $hPens, $post)";
}

[lukep11a]

Thanks, the code now executes but it is only inserting the very last row into the table, do you know how I can insert every row?

[Psycho]

Hmm, are you creating an array of the values and concatenating them after the loop or are you overwriting the a "value" variable on each iteration of the loop and only running the query on that value?

 

Echo your query to the page.

[lukep11a]

Hi, I hadn't correctly named the fields so I guess they were all overwriting each other like you said, i had labelled them like this

 

<input name="home_pens[$i]"

 

and as I was using php to echo the form i needed to concatenate the $i variable, if that makes sense, like this:

 

<input name="home_pens['.$i.']"

 

It now works, thanks for all your help, you've been very patient with me!! Next thing is to work out how to ignore rows that have no scores entered for them

Edited December 12, 2012 by lukep11a