UPDATE using a pre-populated form

[Jim R]

Hopefully I can explain this well.  I have a list, which is linked here:

 

http://hoosierhoopsreport.com/test-11-rank/

 

When I'm done, I'm trying to set up the list I, as ADMIN, can make changes to the list without having to go into my database.  I'm mostly concerned with the # column, but once we get through this, I can apply what I've learned. 

 

I'm setting the form to print # and school as text fields.  I'm getting id, nameFirst and nameLast as hidden values to pass to my form handler. 

 

When I hit submit, I'm getting this 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 'WHERE id='172'' at line 5

Query: UPDATE wp_playerRank_copy SET rankClass='10', hschool='Avon HS', WHERE id='172'

 

 

I'm pretty sure it's just filtering through the information to most last piece of data handled, which isn't what I want, but I also don't understand why I'm getting the error. 

 

 

Here is the code dealing with the form:

 

 

<form id="class2011" action="/resources/form/dbplayerUpdate.php" method="post">

<table class="playerTable" border="0" width="100%">
<thead>
<tr>
<th class="num">#</th>
<th class="top">Top 10</th>
<th class="height">HT</th>
<th class="pos">POS</th>';

if (current_user_can("access_s2member_level4")){
echo '<th class="level">Level</th>';
}
echo '<th class="school">City (School)</th>';

if (current_user_can("access_s2member_level4")){
echo '<th class="summer">Summer Team</th>';
}
echo '<th class="college">College</th>
</tr>
</thead>

<tfoot>
<tr>
<td colspan="8" align="right">(+) moved up; (d) debut; (s) switched from another position / <b>Colleges in bold print means committed</b></td>
</tr>
<tr>
<td><input id="Submit" name="Submit" type="submit" value="Submit" /></td>
</tr>
</tfoot>

<tbody>';$query = 'SELECT * FROM wp_playerRank_copy WHERE year="2011" ORDER BY rankClass ASC';
$results = mysql_query($query);
while($line = mysql_fetch_assoc($results)) {

if ($line['rankClass'] != 0)  {

echo '
<tr>
<td>
<input type="hidden" name="db_id" value="' . $line['id'] . '"size="2">
<input type="text" name="rankClass" value="' . $line['rankClass'] . '"size="2">' . $line['devClass'] .'</td>
<td>';

if ($line['wpID'] > '0') {
echo '
<input type="hidden" name="nameFirst" value="' . $line['nameFirst'] . '"size="2">
<input type="hidden" name="nameLast" value="' . $line['nameLast'] . '"size="2">
<a href="/tag/' . $line['nameFirst'] . '-' . $line['nameLast'] .'">'. $line['nameFirst'] . ' ' . $line['nameLast'] . '</a>';
}
else
{
echo  $line['nameFirst'] . ' ' . $line['nameLast'];
}
echo '</td><td>'. $line['height'] . '</td>
<td>'. $line['position'] . '</td>';

if (current_user_can("access_s2member_level4")){
echo '<td>'. $line['level'] . '</td>';
}
echo '<td><input type="text" name="hschool" value="' . $line['hschool'] .'"size="30"></td>';

if (current_user_can("access_s2member_level4")){
echo '<td>'. $line['summer'] . '</td>';
}

if ($line['committed'] == 'y') {
echo '
<td><strong>'. $line['college'] . '</strong></td>
';}


echo '</tr>
';}

}
echo '

</tbody></table></form>

 

 

 

Here is the form handler:

 

 

if(!$con = mysql_connect("localhost","jwrbloom_","redcoach")) {
   die("Could not connect to database: ".mysql_error());
}
mysql_select_db("jwrbloom_wpHHR", $con);

$nameFirst = $_POST['nameFirst'];
$nameLast = $_POST['nameLast'];
$db_id = $_POST['db_id'];
$rankClass = $_POST['rankClass'];
$hschool = $_POST['hschool'];

//$grade = $_POST['grade'];
//$coachSchool = $_POST['coachSchool'];
//$feet = $_POST['feet'];
//$inches = $_POST['inches'];

/*
search for existing row
*/
$sql = "SELECT id FROM wp_playerRank_copy WHERE nameFirst='".mysql_real_escape_string($nameFirst)."' AND nameLast='".mysql_real_escape_string($nameLast)." '";
if(!$result = mysql_query($sql)) {
   die(mysql_error()."<br />Query: ".$sql);   
}
if(mysql_num_rows($result)) {
  $row = mysql_fetch_assoc($result);
  /*
  update existing row
  */
  $sql = "UPDATE wp_playerRank_copy SET 
                  rankClass='".mysql_real_escape_string($rankClass)."', 
                  hschool='".mysql_real_escape_string($hschool)."', 

                  WHERE id='".$row['id']."'";
   if(!$result = mysql_query($sql)) {
      die(mysql_error()."<br />Query: ".$sql);   
   }
}

[mikosiko]

I didn't read all your code, therefore I don't know if it has more issues... but regarding to the error that you posted I see a ","  that should not be there:

SET rankClass='10', hschool='Avon HS', WHERE id='172'

[Jim R]

Ok...that helped, thank you.

 

But as expected, it's just noticing the last row of data.  I need it to recognize any/all of the changes.

[fenway]

You need a while loop.

[Jim R]

You need a while loop.

 

What?  Where?  When?  : )

 

 

[fenway]

You're assuming the result set has a single row.

[Jim R]

Not sure I understand, but I'll have anywhere from 10-30 rows of data on the screen.

[fenway]

Right -- you need to iterate through the result set.

[Jim R]

Where would it go?  I tried putting it before the Update, but it just creates a loop that eventually times out.

[Jim R]

The following code produced this error:

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/jwrbloom/public_html/resources/form/dbplayerUpdate.php on line 47

 

$sql = "SELECT id FROM wp_playerRank_copy WHERE nameFirst='".mysql_real_escape_string($_POST['nameFirst'])."' AND nameLast='".mysql_real_escape_string($_POST['nameLast'])." '";
if(!$result = mysql_query($sql)) {
   die(mysql_error()."<br />Query: ".$sql);   
}
if(mysql_num_rows($result)) {
while ($row = mysql_fetch_array($result))
  /*
  update existing row
  */
{  
  $sql = "UPDATE wp_playerRank_copy SET 
                  rankClass='".mysql_real_escape_string($_POST['rankClass'])."', 
                  hschool='".mysql_real_escape_string($_POST['hschool'])."'

                  WHERE id='".$row['id']."'";
   if(!$result = mysql_query($sql)) {
      die(mysql_error()."<br />Query: ".$sql);   
   }
  }
}