Computer ForumsComputers  

Go Back   Computer Forums > Programmers Lounge > Programming Discussions

Reply
 
LinkBack Thread Tools Display Modes
Old 06-06-2006, 12:21 PM   #1 (permalink)
msaunders
Newb Techie
 
Join Date: Jul 2005
Posts: 13
Default update records using php and mysql

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.
msaunders is offline   Reply With Quote
Old 06-06-2006, 02:59 PM   #2 (permalink)
office politics
Dope Tech
 
Join Date: Jan 2004
Posts: 3,589
Send a message via ICQ to office politics Send a message via AIM to office politics Send a message via Yahoo to office politics
Default

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), 04) == "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); 
?>
Attached Files
File Type: txt agtform1.html.txt (26.0 KB, 17 views)
__________________
Tech IMO.com | ExtremeTech.com | ASP Free.com | SysOpt.com | Tech Support Guy.org
DB Forums.com | Cyber Tech Help.com | Lazy Forums.com | Warrior Nation.net

'If you don't stand for somethin you'll fall for anything' - Dr. Dre Been there, done that
office politics is offline   Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT. The time now is 07:09 AM.


Powered by vBulletin® Version 3.7.1
Copyright ©2000 - 2008, Jelsoft Enterprises Ltd.
Content Relevant URLs by vBSEO 3.0.0 RC8