PHP - Update Using A Pre-populated Form

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

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:
Quote

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:


Code: [Select]
<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:


Code: [Select]
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);   
   }
}

hello

i have a db with name which populates a form dropdown list
there are various variables like this in the form.
the user selects their choices and then submits the form to a new db table


at the moment it does not capture the dropdown data to the insert sql

help please

here is the code:

===============================

<?php
include ('connect.php');

// Insert any new image into database
if(isset($_POST['xsubmit']) && $_FILES['imagefile']['name'] != "") {
  $fileName = $_FILES['imagefile']['name'];
  $fileSize = $_FILES['imagefile']['size'];
  $fileType = $_FILES['imagefile']['type'];
  $content  = addslashes (file_get_contents($_FILES['imagefile']['tmp_name']));
  $jeweltype = $_POST['jeweltype'];
  $jewelsize = $_POST['jewelsize_in'];
  $jewelcolour = $_POST['jewelcolour_in'];
  $jewelmaterial = $_POST['jewelmaterial_in'];
  $jewelgender = $_POST['jewelgender_in'];

  if(!get_magic_quotes_gpc()) {
    $fileName = addslashes($fileName);
  }

  // Checking file size
  if ($fileSize < 150000) {
    mysql_query ("INSERT into jewel_images (name,size,type,content,jeweltype,jewelsize,jewelcolour,jewelmaterial,jewelgender) "
   . "values ('$fileName','$fileSize','$fileType','$content','$jeweltype','$jewelsize','$jewelcolour','$jewelmaterial','$jewelgender')");
  } else {
    $err = "The Image file to too large!";
  }
}

// Find out about latest image

$gotten = mysql_query("select * from jewel_images order by row_id desc");
$row    = mysql_fetch_assoc($gotten);
$bytes  = $row['content'];

// If this is the image request, send out the image

 if ($_REQUEST['pic'] == 1) {
   header("Content-type: $row[type];");
 print $bytes; 
 }
?>
<html>
<head>
<title>Upload an image to a database</title>
</head>
<body>
  <font color="#FF3333"><?php echo $err ?></font>
  <table>
    <form name="Upload" enctype="multipart/form-data" method="post">
      <tr>
        <td>Upload <input type="file" name="imagefile"><br />
            Jewelery Type:
               <select>
               <?php
               $sql="SELECT jeweltype FROM jeweltypes";
               $result =mysql_query($sql);
               while ($data=mysql_fetch_assoc($result)){
               ?>
               <option value ="jeweltype" ><?php echo $data['jeweltype'] ?></option>
               <?php } ?>
               </select>
               <br />
            Jewelery Size:
               <select>
               <?php
               $sql="SELECT jewelsize FROM jewelsizes";
               $result =mysql_query($sql);
               while ($data=mysql_fetch_assoc($result)){
               ?>
               <option value ="jewelsize" ><?php echo $data['jewelsize'] ?></option>
               <?php } ?>
               </select>
               <br />
            Jewelery Colour:
               <select>
               <?php
               $sql="SELECT jewelcolour FROM jewelcolours";
               $result =mysql_query($sql);
               while ($data=mysql_fetch_assoc($result)){
               ?>
               <option value ="jewelcolour_in" ><?php echo $data['jewelcolour'] ?></option>
               <?php } ?>
               </select>
               <br />
            Jewelery Material:
               <select>
               <?php
               $sql="SELECT jewelmaterial FROM jewelmaterials";
               $result =mysql_query($sql);
               while ($data=mysql_fetch_assoc($result)){
               ?>
               <option value ="jewelmaterial_in" ><?php echo $data['jewelmaterial'] ?></option>
               <?php } ?>
               </select>
               <br />
            Jewelery Gender:
               <select>
               <?php
               $sql="SELECT jewelgender FROM jewelgenders";
               $result =mysql_query($sql);
               while ($data=mysql_fetch_assoc($result)){
               ?>
               <option value ="jewelgender_in" ><?php echo $data['jewelgender'] ?></option>
               <?php } ?>
               </select>
               <br />
        <input type="submit" name="xsubmit" value="Upload">
        </td>
      </tr>
      <tr>
       <td>Latest Image</td>
      </tr>
      <tr>
        <td><img src="?pic=1"></td>
      </tr>
    </form>
 </table>
</body>
</html>

hey all. i previously posted about how i lost all my data so im trying to recreate my form again. after not many replies from my desperate call for help i tried to figure it out on my own, i spent hours and hours going through this forum and others but im just running into brick walls because of my minor knowledge of php (though i do have to say i am picking it up a little!)  my form is short and simple so this shouldnt be too hard but this is what i have come up with.

my tables
----------------
Movies: ID(P_id),Title,Category (F_K),URL

Categories: ID (P_Id),Category


All i am needing my form to do is enter records into my movies table, therefore it goes like this:

Title: "textbox"
URL: "textbox"
Category: "dropdown  menu"
  "submit"

now the thing here is that the dropdown menu needs to be populated with data from Category.Categories table so i can add that info into category in the movies table. so pretty much it will be sending the ID from Categories table into Category in the Movies and yes they both have the same datatype.

so here is what i have so far.

my form.php

Code: [Select]
   <html>
      <form id="form1" name="Update" method="post" action="add.php">
   
      <label>
   
      First Name: <input type="text" name="Title" id="textfield" />
   
      </label>
   
      <br />
   
      <label>
   
      Last Name: <input type="text" name="URL" id="textfield2" />
   
      </label>

<select name='Category'>
<?php
$con = mysql_connect("localhost", "root", "");
if (!$con)
{
die('Could not connect to DB: ' . mysql_error() );
}

mysql_select_db ("mydb", $con);
$query = "SELECT Category from Catgories";
$result = mysql_query($query) OR DIE ("There was an error .mysql_error());
while ($line = mysql_fetch_array($result,MYSQL_ASSOC)){
$categoryid = $line["playerid"];
$category = $line["category"];

echo "<option value='$categoryid'>$category</option>\n";

}
php?>

</select>

   
      <input name="" type="submit" value="send" />
 
      </form>
</html>
and then my add.php

Code: [Select]
<?php
$con = mysql_connect("localhost", "root", "");
if (!$con)
{
die('Could not connect to DB: ' . mysql_error() );
}

mysql_select_db ("mydb", $con);

$sql="INSERT INTO movies (title, url)
VALUES ('$_POST[title]','$_POST[url]')";

if (!mysql_query($sql,$con))
{
die ('Error: ' . mysql_error());
}
echo "Record added";

so first things first, although i have tested the code its throwing me a variable error i think on line 30 so i figured that instead of focusing on just the error i should just have everything looked over since i dont have anyone i can turn to about this stuff to make sure i didnt forget any " or { etc..

i am also not sure if the while loop is even needed in form.php, i kind of taylored this code to my own needs. THEN i have the add.php, granted that by some miracle that the form.php is correct i dont know what else i would need to add to it for my dropdown.

i really need this bad and i hope someone can help me you know. if anyone has any ideas for a more simple code please, enlighten me but just give me some good examples because i really am a beginner.  thanks alot.

Hi i have this simple update form and scrip but somehow it doesnt seem to be update the field on the database can someone help out please.
The html form is the second form bellow where the action send to status_update.php
HTML FORM
Code: [Select]
<?php                include("../header.html");            ?>
<?php                include("header_news.html");            
extract($_REQUEST,EXTR_SKIP);
?><?php            
/*                print("sfilm_refnum = $sfilm_refnum<BR>");                
print("sfilm_addr01 = $sfilm_addr01<BR>");                
print("sfilm_postcode = $sfilm_postcode<BR>");                
print("Film Client = $fclient<BR>");            */             
?>           
<form id="search" action="list.php" method="post" name="search">               
<table width="780" border="0" cellspacing="0" cellpadding="4" bgcolor="#eeeeee">                   
<tr>                       
<td align="right" width="140"></td>                       
<td width="320"><span class="hofblack10">                           
</span>                           
</td>                       
<td align="center" width="100"><input type="hidden" name="lstart" value="<?php print("$lstart"); ?>" /><input type="hidden" name="lend" value="<?php print("$lend"); ?>" /><input type="hidden" name="lamount" value="<?php print("$lamount"); ?>" /></td>                        <td align="center" width="100"></td>                       
<td align="right"></td>                   
</tr>               
</table>           
</form>           
<table width="780" border="0" cellspacing="0" cellpadding="4" bgcolor="#4050c4">               
<tr>                 
<td width="60" class="hofwhite10">action</td>                   
<td width="140"><span class="hofwhite14">DATE</span></td>                   
<td width="80"><span class="hofwhite14">ID</span></td>                   
<td><span class="hofwhite14">News Titile</span></td>                   
<td width="100"><span class="hofwhite14">Status</span></td>               
</tr>               
<tr height="0">                 
<td bgcolor="white" width="60"></td>                   
<td bgcolor="white" width="140" height="0"></td>                   
<td bgcolor="white" width="80" height="0"></td>                   
<td bgcolor="white" height="0"></td>                   
<td bgcolor="white" width="100" height="0"></td>               
</tr>           
</table><table width="780" border="0" cellspacing="0" cellpadding="4"><tr>                   
<td width="60"></td>                 
<td width="80"></td>                   
<td><a class="blueullrg" href="add.php">Add News</a></td>                   
<td align="right" width="120"></td>               
</tr>               
<tr height="0">                   
<td width="60" height="0"></td>                 
<td width="80" height="0"></td>                   
<td height="0"></td>                   
<td align="right" width="120" height="0"></td>               
</tr>           
</table>  <?php          
//get the DB connection variables    
include("../../../includes/config.php");        
//connect to DB        
$connection = @mysql_connect($db_address,$db_username,$db_password) or die("Couldn't CONNECT.");        
$db = @mysql_select_db($db_name, $connection) or die("Couldn't select DATABASE.");                
$query2="SELECT * FROM news WHERE !(news_status='deleted')";        
$result2 = mysql_query($query2) or die("Couldn't execute QUERY - Select NEWS Qty");        
$fqty = mysql_num_rows($result2);            
//SELECT or FIND the same USERNAME        
$query3="SELECT * FROM news WHERE !(news_status='deleted') ORDER BY news_id DESC";        
$result3 = mysql_query($query3) or die("Couldn't execute QUERY - Select NEWS");        
while ($row = mysql_fetch_array($result3))            
{                
$news_id                 = $row['news_id'];                
$news_title                = $row['news_title'];                
$news_story                = $row['news_story'];                
$news_image                = $row['news_image'];                
$news_image_caption        = $row['news_image_caption'];                
$news_image_link        = $row['news_image_link'];                
$news_date_day            = $row['news_date_day'];                
$news_date_month        = $row['news_date_month'];                
$news_date_year            = $row['news_date_year'];                
$news_status            = $row['news_status'];                
$news_website            = $row['news_website'];                
$news_date_created        = $row['news_date_created'];        
        $news_date_modified        = $row['news_date_modified'];    ?>           
        <table width="780" border="0" cellspacing="0" cellpadding="4" bgcolor="#eeeeee">               
        <tr>                 
        <td width="60"><span class="hofblack10">                   
         <?php                    
         if($news_status=="deleted"){                        
         print("<a class='hifblack10'>deleted</span>");                    
         }ELSE{                        
         print("<a class='blueul' href='edit.php?id=$news_id'>edit</a>");                    
         }                    
         ?>                 
          </span></td>                   
          <td width="140"><span class="titlegrey12">                     
          <?php                      
          if(!$news_date_day) 
          { echo "00"; }
          else{ echo $news_date_day; }                      
          echo "/";                     
           if(!$news_date_month) { echo "00"; }else{ echo $news_date_month; }                      
           echo "/";                      
           if(!$news_date_year) { echo "0000"; }else{ echo $news_date_year; }                    
           ?>                   
           </span></td>                   
           <td width="80"><span class="titlegrey12"><?php                    print("$news_id");                    ?></span>
           </td>                   
           <td><?php                    
           if($news_status=="deleted")
           {                        
           print("<class='hofblack10'>$news_title</span>");                    
           }ELSE{                        
           print("<a class='blueul' href='edit.php?id=$news_id'>$news_title</a>");                    
           }                    
           ?></td>                   
           <td width="100">                       
           <form id="list_update" action="status_update.php" method="post" name="list_update">                           
        <select name="newnstatus" size="1">                               
           <option  <?php if($row['news_status'] == "") 
           {
            print("selected");
           } ?>
           selected="selected" value="">Status...</option>                               
           <option  <?php if($row['news_status'] == "on")
           {
            print("selected");
           } ?> value="on">On</option>
               <option  <?php if($row['news_status'] == "off")
           {
            print("selected");
           } ?> value="off">Off</option>
            <option  <?php if($row['news_status'] == "deleted")
           {
            print("selected");
           } ?> value="deleted">Delete</option>
        </select>
         <input type="hidden" name="nstatus" value="<?php echo $row[news_status]; ?>" />                           
           <input type="hidden" name="id" value="<?php echo $row[news_id]; ?>" />                       
           <input type="submit" name="update" value="update" />                       
           </form>                     
            </td>               
            </tr>               
            <tr height="0">                 
            <td bgcolor="white" width="60"></td>                   
            <td bgcolor="white" width="140" height="0"></td>                   
            <td bgcolor="white" width="80" height="0"></td>                   
            <td bgcolor="white" height="0"></td>                   
            <td bgcolor="white" width="100" height="0"></td>               
            </tr>           
            </table>
            <?php            }        mysql_close($connection);//}?>           
            <table width="780" border="0" cellspacing="0" cellpadding="4">         
            <tr>                   
            <td width="60"></td>                 
            <td width="80"></td>                   
            <td><a class="blueullrg" href="add.php">Add News</a></td>                   
            <td align="right" width="120"></td>               
            </tr>           
            </table><?php                // include("list_navigation.html");            ?>
            <?php                include("../footer.html");            ?>
            </div></body></html>
The action script php
Code: [Select]
<?php
/*    echo "fstatus: ".$fstatus."<BR>";    
echo "id: ".$id."<BR>";    
echo "fclient: ".$fclient."<BR>";    
echo "newfstatus: ".$newfstatus."<BR>";*/    
//set the date of agreement        
$timestamp = date('l jS \of F Y h:i:s A');    
//get the DB connection variables        
include("../../../includes/config.php");
//connect to DB        
$connection = @mysql_connect($db_address,$db_username,$db_password) or die("Couldn't CONNECT.");        
$db = @mysql_select_db($db_name, $connection) or die("Couldn't select FILMS DATABASE.");    
// All appears well, so enter into database                
$query= "UPDATE news SET news_status = '$newnstatus' WHERE news_id='$id'";

$result = mysql_query($query) or die("could not execute query - Update FILMS Record to DB");    
//setup an email to the Admin @ hof, w/o attachment            
$emailto="xx@xxx.co.uk";        
$emailfrom="no-reply@xxxx.co.uk";        
$emailsubject="xx Record Updated";        
$emailmessage="Hello Registrar\n\n";        
$emailmessage.="News ID: ".$id."\n";        
$emailmessage.="Updated on: ".$timestamp."\n\n";        
$emailmessage.="Status was: ".$nstatus."\n";        
$emailmessage.="Status now: ".$newfnstatus."\n";        
$emailmessage.="Thank you,\n\n";        
$emailmessage.="Web Site ROBOT\n";        
$emailmessage.="(Administrator)\n";        
$emailmessage.="xxx.co.uk | xxx.biz\n";        
$emailmessage.="----------------------------------------------\n";        
$emailmessage.="e. http://www.xxx.co.uk/contact.php\n";        
$emailmessage.="w. http://www.xxx.co.uk\n";        
$emailheader="From: xxx.co.uk<$emailfrom>";
$emailheader  .= 'Cc: xxx@xxx.co.uk'."\r\n";
$emailheader  .= 'Bcc: xxx@xxxxx.co.uk'."\r\n";        
$ok=mail($emailto,$emailsubject,$emailmessage,$emailheader);        
mysql_close($connection);            
if ($ok)
 {        
 header("Location: list.php"); 
 /* Redirect browser */        
 exit;    
 } else {        
 $errmsg="There was a problem, please try later or telephone us direct.";        
 $errsta="1";        
 include("edit_error.html");        
 //echo "<p>Mail could not be sent. Sorry!</p>";        
 exit;    
 }        
 ?> Thanks in advance

Good morning. I am looking fo some help with an update script to update all rows in a table.

This is what I have for a form and all looks well. form.php

<?php

// run the query and put the results in an array variable called $result 
$result = mysql_query("SELECT * FROM table ORDER BY 'id', 'title', 'text', 'number'"); 


print "<form method='post' action='update.php'> 
<table width='100%' border='0' cellspacing='1' cellpadding='1'><tr> 

<td align='center'><strong>ID</strong></td> 
<td align='center'><strong>Title</strong></td> 
<td align='center'><strong>text</strong></td> 
<td align='center'><strong>Number</strong></td> 
</tr>\n"; 

// start a loop to print all of the courses with their book information 
// the mysql_fetch_array function puts each record into an array. each time it is called, it moves the array counter up until there are no more records left 
while ($Update = mysql_fetch_array($result)) { 


  // start displaying the info; the most important part is to make the name an array (notice bookinfo[$i]) 
  print "<td align='center'><p>{$Update['id']}</p></td>\n"; 
  print "<td align='center'><input type='text' name='title' value='{$Update['title']}' /></td>"; 

  print "<td align='center'><input type='text' size='40' name='text' value='{$Update['text']}' /></td>\n"; 
  print "<td align='center'><input type='text' size='40' name='number' value='{$Update['number']}' /></td>\n"; 
print "</tr>\n"; 
// add 1 to the count, close the loop, close the form, and the mysql connection 

} 
print "<tr> 
<td colspan='4' align='center'><input type='submit' value='submit' />"; 
print "</td> 
</tr> 
</table> 
</td> 
</tr> 
</form> 
</table>"; 

print "</tr>\n"; 
?><br /><br />

 

My question is. How do I update this info into the database with the proper info. ie. Update.php?