update records using php and mysql

[msaunders]

Hi All,

haven't been here in awhile, good to be back..lol..anyways. I stuck on trying to updated a database on MYSQL server. I have a application database, just a simple one, I just want to learn the language first. Anyways, I have five columns in the table and I'm using a HTML form to retrieve the data from the mysql server. How would I retrieve the data from the mysql database and display the results in the forms, so I can modify what's in the table through the form. and sent it back to the mysql database. I just need a general idea of how to do it.

[office politics]

i started a project that was similar but never finished. I got as far as having a user fill out a html form and have the data inserted into the mysql db. I started this project before studying for my dba so please bare with me.

The php script starts out with a setup for the server parameters and connects to the db. Next, we see some functions which we'll get back to. It grabs all the POST data in the foreach loop. Stack has fieldname and then the data pushed onto it. This script then echos the values out so you can see them. Now we have our data, we want to prepare for insertion (lol). Determine the top of the stack with array_slice. Use insert() for values going into a table as a single row; specify how many values to grab, the "sliced" stack, and the table name. Or we use multi_add() for multiple entries into a single table; for instance a list of people representing their agency.

i attached the html form.

PHP Code:

<?php 

//table field lengths

//server info
$location = "127.0.0.1:3306"; 
$username = "fos_cs"; 
$password = "f3"; 
$database = "fosalem_agtappt"; 

//Connect to the DB
$conn = mysql_connect("$location","$username","$password"); 
if (!$conn) die ("Could not connect MySQL"); 
mysql_select_db($database,$conn) or die ("Could not open database"); 
echo "db connected
";//Report DB connected

$stack = array();//create list for receiveing data
$agtname = $_POST["agtname"];//save for multiuse

function insert($x, $stack, $table) { 
$query = "insert into ".$table." set ";//move data into table
for ($i=0;$i<$x;$i+=2) {
  if (substr(pos($stack), 0, 4) == "com_") // is field name prefixed with com_?
    $query = $query.substr_replace(pos($stack),"",0,4)."='";//Shave com_ & Add field name to the query
  else 
    $query = $query.pos($stack)."='";//Get field name & add it to the query
  next($stack);//Move to data entry
  if(is_array(pos($stack))) { //Is data entry from checkboxes?
    foreach(pos($stack) as $element) // Each checkbox entry
      $query = $query.$element.","; //gets added to the query
    $query= $query."',";//finish query
  }  
  else 
    $query= $query.pos($stack)."',";//Get data entry & add it to the query
  next($stack);//Get next field name
  //echo $i."
";
}
$query = substr($query,0,(strlen($query)-1));//shave last comma
return $query;//Send query back to program
}

function exequery($query) {//Use function to execute queries
  //echo $query."

";//Show execute string
  $sqlresult = mysql_query($query);//Execute
  //Check For Errors
  if (!$sqlresult) 
    echo "Error running query 
".$query."

".mysql_errno().": ".mysql_error()."

";
  else
    echo "query executed
".$query."

";
}


function multi_add($slice, $names, $how_many, $table, $agtname) {
next($slice);//Skip field name
for ($i=0;$i<$how_many;$i++) {
  if (pos($slice) != "") {
    $query = "insert into ".$table." set ";//Put data in this table
    $query = $query."agtname='".$agtname."',";//Define agency
    foreach($names as $field) {
      $query = $query.$field."='".pos($slice)."',";//Get data entry
      next($slice);next($slice);//Move To Next Field Name; Skip field name
    }
  $query = substr($query,0,(strlen($query)-1));//shave last comma
  if ($table == "persinfo") // For the persinfo query
    $query = $query.",indexed='".md5($query)."'"; // Add unique value to query
  exequery($query);//Execute
  } else {
    foreach($names as $field) {
      next($slice);next($slice);//Move To Next Field Name; Skip field name
    }
  }
  //echo pos($slice)."

";
} }

$count = 0;
foreach ($_POST as $key => $value) { //take name&data pair 
  array_push($stack, $key, $value);//and put it on into two save piles
  echo $count.": ".$key." - ".$value."
";// Show data entry
  $count++;
}

echo "
";
reset($stack);//point to first in both save piles

//INFO
$slice = array_slice($stack, 0);
$query = insert(28, $slice, "info");
exequery($query);//Execute

//AGENCY PRINCIPALS
$slice = array_slice($stack, 28);
$fields = array("name", "title", "dob", "yearsagy");
multi_add($slice, $fields, 3, "principals", $agtname);

//MARKETING
$slice = array_slice($stack, 52);
$query = insert(50, $slice, "marketing");
$query = $query.",agtname='".$agtname."'";
exequery($query);//Execute

//PERSONEL INFO
$slice = array_slice($stack, 102);
$fields = array("keyperson", "title", "hiredate", "yearsinforce", "prof_desg", "lic_num");
multi_add($slice, $fields, 10, "persinfo", $agtname);

//OFFICE
$slice = array_slice($stack, 222);
$query = insert(22, $slice, "office");
$query = $query.",agtname='".$agtname."'";
exequery($query);//Execute

//PERSONAL LINES INFO
$slice = array_slice($stack, 244);
$query = insert(46, $slice, "plinfo");
$slice = array_slice($stack, 291);
$terr = "";
for($i=0;$i<3;$i++) {
  $terr = $terr.pos($slice).",";
  next($slice);next($slice);
}
$terr = substr($terr,0,(strlen($terr)-1));//shave last comma
$query = $query.",agtname='".$agtname."'";
$slice = array_slice($stack, 397);//Get discontiued
$query = $query.",discontinued='".pos($slice)."',primary_terr='".$terr."',pc='p',indexed='".md5($query)."'";
exequery($query);//Execute

//PERSONAL LINES CO'S
$slice = array_slice($stack, 296);
$fields = array("company", "years", "volume", "3year", "pc");
multi_add($slice, $fields, 10, "company", $agtname);

//COMMERICAL LINES INFO
$slice = array_slice($stack, 398);
$query = insert(46, $slice, "plinfo");
$slice = array_slice($stack, 445);
$terr = "";
for($i=0;$i<3;$i++) {
  $terr = $terr.pos($slice).",";
  next($slice);next($slice);
}
$terr = substr($terr,0,(strlen($terr)-1));//shave last comma
$query = $query.",agtname='".$agtname."'";
$slice = array_slice($stack, 551);//Get discontiued
//echo md5($query);
$query = $query.",discontinued='".pos($slice)."',primary_terr='".$terr."',pc='c',indexed='".md5($query)."'";
exequery($query);//Execute

//COMMERICAL LINES CO'S
$slice = array_slice($stack, 450);
$fields = array("company", "years", "volume", "3year", "pc");
multi_add($slice, $fields, 10, "company", $agtname);


//AUTOMATION
$slice = array_slice($stack, 552);
$query = insert(30, $slice, "automation");
$query = $query.",agtname='".$agtname."'";
exequery($query);//Execute


/*//Get names of columns in table
$field_names = array();
$res = mysql_query("SHOW COLUMNS FROM info");
for($i=0;$i<mysql_num_rows($res);$i++){
   array_push($field_names,mysql_result($res, $i));
} 
foreach($field_names as $value) {
  echo array_pop($field_names)."
";
}*/

//mysql_query($query); 
?>