Jump to content

Simple php script to update mysql table

dp2

By dp2
in PHP Coding Help

 Share

Recommended Posts

dp2 Newbie

dp2

Posted
Posted

Objective:

PHP form that fetches data from database tables and allows me to edit via a form.

 

I have tried a number of tutorials on the net but have failed to get their sample code to work mainly they are poor outdated coding versions in php3 to php4.

 

Trying to establish the current and correct way to pull data, edit and update.

 

I notice that they have been using three files to perform this

1) Form to fetch the ID row contents and display in next form

2) Displays the row for editing form and sent to the handler

3) form handler that processes the data and writes it to the database

 

Some manage to include the three files in one php file. This is where it gets complicated.

 

The following snip bit of code works by displaying the contents but I need to display the data into a form so that

I can edit the data and write it back to the database.

 

$query="SELECT * FROM regdir";
$result=mysql_query($query);
while($row=mysql_fetch_array($result,MYSQL_ASSOC))
{
   ECHO "BusinessName:{$row['BusinessName']}<br>".
        "BusinessStreet:{$row['BusinessStreet']} <br>".
         "Town:{$row['Town']} <br><br>";
}

 

Once I have a basic script working I can learn from I can build on.

 

This is what I have been working on

 

<?php
include ('dbc.php'); 
//$db=mysql_connect($dbhost, $dbuname, $dbpass);
//mysql_select_db("$dbname");
$query="SELECT * FROM regdir";
$result=mysql_query($query);
while($row=mysql_fetch_array($result,MYSQL_ASSOC))
{
   ECHO "BusinessName:{$row['BusinessName']}<br>".
        "BusinessStreet:{$row['BusinessStreet']} <br>".
         "Town:{$row['Town']} <br><br>";
}

//include 'closedb.php';


$_GET["BusinessName"]=$row["BusinessName"];
$_GET["BusinessStreet"]=$row["BusinessStreet"];
$_GET["Town"]=$row["Town"];
$_GET["PostCode"]=$row["PostCode"];
$_GET["Telephone"]=$row["Telephone"];
$_GET["joined"]=$row["joined"];
$_GET["UserEmail"]=$row["UserEmail"];
$_GET["Mobile"]=$row["Mobile"];
$_GET["user_pwd"]=$row["user_pwd"];
$_GET["Web"]=$row["Web"];
$_GET["id"]=$row["id"];
//mysql_close($db);
?>
<html>
<head>
<title>SystemsDoc Update</title>
</head>
<body bgcolor="white">
<form method="post" action="postupdate.php">
<table>
<col span="1" align="right">
<tr>
<td><font color="blue">BusinessName:</font></td>
<td><input type="text" name="BusinessName" 
value="{$row['BusinessName']}" size=100></td>
</tr>
<tr>
<td><font color="blue">BusinessStreet:</font></td>
<td><input type="text" name="BusinessStreet" 
value="<? echo $_GET["BusinessStreet"]; ?>" size=100></td>
</tr>
<tr>
<td><font color="blue">Town:</font></td>
<td><input type="text" name="Town" 
value="<? echo $_GET["Town"]; ?>" size=100></td>
</tr>
<tr>
<td><font color="blue">PostCode:</font></td>
<td><input type="text" name="PostCode" 
value="<? echo $_GET["PostCode"]; ?>" size=100></td>
</tr>
<tr>
<td><font color="blue">Telephone:</font></td>
<td><input type="text" name="Telephone" 
value="<? echo $_GET["Telephone"]; ?>" size=100></td>
</tr>
<tr>
<td><font color="blue">joined Date:</font></td>
<td><input type="text" name="joined" 
value="<? echo $_GET["joined"]; ?>" size=100></td>
</tr>
<tr>
<td><font color="blue">UserEmail:</font></td>
<td><input type="text" name="UserEmail" 
value="<? echo $_GET["UserEmail"]; ?>" size=100></td>
</tr>
<tr>
<td><font color="blue">Mobile:</font></td>
<td><input type="text" name="Mobile" 
value="<? echo $_GET["Mobile"]; ?>" size=100></td>
</tr>
<tr>
<td><font color="blue">user_pwd:</font></td>
<td><input type="text" name="user_pwd" 
value="<? echo $_GET["user_pwd"]; ?>" size=100></td>
</tr>
<tr>
<td><font color="blue">Web:</font></td>
<td><input type="text" name="Web" 
value="<? echo $_GET["Web"]; ?>" size=100></td>
</tr>
<tr>
<td><font color="blue">id:</font></td>
<td><input type="text" name="id" 
value="<? echo $_GET["id"]; ?>" size=100></td>
</tr>
<tr>
<td><input type="submit" value="Submit"></td>
</tr>
</body>
</html>

 

Any ideas where I am getting wrong

 

Link to comment
Share on other sites
dp2 Newbie

dp2

Posted
  • Author
Posted

Thank you for the referal

 

This is the code which half works with errors

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/aynsley/public_html/1pw/3/edit.php on line 17

- Edit

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/aynsley/public_html/1pw/3/edit.php on line 39

 

Data from the fields are not displaying in each form box

 

<? 
//connect to mysql
//change user and password to your mySQL name and password
mysql_connect("localhost","xxxxxx","xxxxxxx"); 

//select which database you want to edit
mysql_select_db("regdir"); 

//If cmd has not been initialized
if(!isset($cmd)) 
{
   //display all the news
   $result = mysql_query("select * from regdir WHERE id = $id"); 
  } 
   {
//run the while loop that grabs all the news scripts
[color=red]while($row = mysql_fetch_array($result)){[/color]

      } 
{
      //grab the title and the ID of the news
      $BusinessName=$r["BusinessName"];//take out the title
      $id=$r["id"];//take out the id
     
 //make the title a link
      echo "<a href='edit.php?cmd=edit&id=$id'>$BusinessName - Edit</a>";
      echo "<br>";
    }

?>
<?
if($_GET["cmd"]=="edit" || $_POST["cmd"]=="edit")
{
   if (!isset($_POST["submit"]))
   {
      $id = $_GET["id"];
      $sql = "SELECT * FROM regdir WHERE id=$id";
      $result = mysql_query($sql);        
      [color=blue]$id = mysql_fetch_array($result);[/color]
      ?>
<HTML>
<body>	  
      <form action="edit.php" method="post">
      <input type=hidden name="id" value="<?php echo $id["id"] ?>">
   
      Business Name:<INPUT TYPE="TEXT" NAME="BusinessName" VALUE="<?php echo $id["BusinessName"] ?>" SIZE=30><br>
      Town:<TEXTAREA NAME="Town"COLS=30><?php echo $id["Town"] ?></TEXTAREA><br>
      Telephone:<INPUT TYPE="TEXT" NAME="Telephone" VALUE="<?php echo $id["Telephone"] ?>" SIZE=30><br>
   
      <input type="hidden" name="cmd" value="edit">
   
      <input type="submit" name="submit" value="submit">
   
      </form>
  </body>
</html>
<?php } ?>
<?php
   if ($_POST["$submit"])
   {
      $BusinessName = $_POST["BusinessName"];
  $Town = $_POST["Town"];
  $Telephone = $_POST["Telephone"];
  
  $sql = "UPDATE regdir SET BusinessName='$BusinessName',Town='$Town',Telephone='$Telephone' WHERE id=$id";
      //replace news with your table name above
      $result = mysql_query($sql);
      echo "Thank you! Information updated.";
}
}
}
?>

 

I have attempted to solve for open and closed braces with no joy

Link to comment
Share on other sites
dp2 Newbie

dp2

Posted
  • Author
Posted

Exhausted all your suggestions even reverted to this basic test script:

 

Parse error: syntax error, unexpected $end on line 50

 

<HTML>
<HEAD>
<TITLE> The Reg Directory </TITLE>
<HEAD>
<BODY>
<?php 

$dbcnx = @mysql_connect("localhost", "aynsley", "54321dkp");
if (!$dbcnx) {
  echo( "<P>Unable to connect to the" .
        "database server at this time.</P>" );
  exit();
}

//select which database you want to edit
mysql_select_db("aynsley_phpbb1",$dbcnx);

if (! @mysql_select_db("aynsley_phpbb1") ) {
  echo( "<P>Unable to locate the regdir " .
        "database at this time.</P>" );
  exit();
}
//mysql_query(<query>, <connection id>);
while ($row=mysql_fetch_array($result,MYSQL_NUM)) {
  echo("<P>" . $row["id"] . "</P>");

?>
<P> Here are all the Businesses in our database: </P>
<BLOCKQUOTE>

<?php

// Request the text of all the business names
  $result = mysql_query(
            "SELECT BusinessName FROM regdir");
  if (!$result) {
    echo("<P>Error performing query: " .
         mysql_error() . "</P>");
    exit();
  }

  // Display the text of each BusinessName in a paragraph
  while ( $row = mysql_fetch_array($result) ) {
    echo("<P>" . $row["BusinessName"] . "</P>");
  }

?>
</BLOCKQUOTE>
</BODY>
</HTML>

 

Now this is weird

 

I commented out 

  while ( $row = mysql_fetch_array($result) ) {

 

No more errors and displays the correct data pulled from the database

 

<HTML>
<HEAD>
<TITLE> The Reg Directory </TITLE>
<HEAD>
<BODY>
<?php 

$dbcnx = @mysql_connect("localhost", "aynsley", "54321dkp");
if (!$dbcnx) {
  echo( "<P>Unable to connect to the" .
        "database server at this time.</P>" );
  exit();
}

//select which database you want to edit
mysql_select_db("aynsley_phpbb1",$dbcnx);

if (! @mysql_select_db("aynsley_phpbb1") ) {
  echo( "<P>Unable to locate the regdir " .
        "database at this time.</P>" );
  exit();
}
//mysql_query(<query>, <connection id>);
//while ($row=mysql_fetch_array($result,MYSQL_NUM)) {
  echo("<P>" . $row["id"] . "</P>");

?>
<P> Here are all the Businesses in our database: </P>
<BLOCKQUOTE>

<?php

// Request the text of all the business names
  $result = mysql_query(
            "SELECT BusinessName FROM regdir");
  if (!$result) {
    echo("<P>Error performing query: " .
         mysql_error() . "</P>");
    exit();
  }

  // Display the text of each BusinessName in a paragraph
  while ( $row = mysql_fetch_array($result) ) {
    echo("<P>" . $row["BusinessName"] . "</P>");
  }

?>
</BLOCKQUOTE>
</BODY>
</HTML>

 

Any reasons why this fixes the problem?

 

I''m going to go back and test my original script to see if this is a fix

Link to comment
Share on other sites
.josh Advanced Member

.josh

Posted
Posted

because you were missing a closing bracket for that while loop.  But even if you were to put it in, your script would just be broken in a different way.  You don't have a query with $result before it, so it would have never evaluated true.  Basically it looks like that first while loop shouldn't even be there. 

 

Or maybe it should. You're trying to echo $row['id'] but you never actually run a query before that.  If you're trying to do two queries, you're missing stuff.  If you're trying to do 1 query, you have too much stuff. 

 

You said that commenting out that line makes it do what you want, so I guess you were aiming for 1 query so take out that first while (...) and take out that echo $row['id'] because it's a useless variable.

Link to comment
Share on other sites
dp2 Newbie

dp2

Posted
  • Author
Posted

Thank you for explaining the errors, I know now where the test script was failing.

 

1)I was trying to call once yet there were two calls and one was missing the $result query

2) There was a brace missing which I have corrected

 

This code now works with two calls

<HTML>
<HEAD>
<TITLE> The Reg Directory </TITLE>
<HEAD>
<BODY>
<?php 

$dbcnx = @mysql_connect("localhost", "username", "password");
if (!$dbcnx) {
  echo( "<P>Unable to connect to the" .
        "database server at this time.</P>" );
  exit();
}

//select which database you want to edit
mysql_select_db("aynsley_phpbb1",$dbcnx);

if (! @mysql_select_db("aynsley_phpbb1") ) {
  echo( "<P>Unable to locate the regdir " .
        "database at this time.</P>" );
  exit();
}

// Request the text of all the business names
  $result = mysql_query(
            "SELECT BusinessName FROM regdir");
  if (!$result) {
    echo("<P>Error performing query: " .
         mysql_error() . "</P>");
    exit();
  }
//mysql_query(<query>, <connection id>);
while ($row=mysql_fetch_array($result)) {
  echo("<P>" . $row["BusinessName"] . "</P>");
}
?>
<P> Here are all the Businesses in our database: </P>
<BLOCKQUOTE>

<?php

// Request the text of all the business names
  $result = mysql_query(
            "SELECT BusinessStreet FROM regdir");
  if (!$result) {
    echo("<P>Error performing query: " .
         mysql_error() . "</P>");
    exit();
  }

  // Display the text of each BusinessName in a paragraph
  while ( $row = mysql_fetch_array($result) ) {
    echo("<P>" . $row["BusinessStreet"] . "</P>");
  }

?>
</BLOCKQUOTE>
</BODY>
</HTML>

 

Now back to my original script.

 

What I am trying to attempt is pull the field data into a form fields to edit and then submit the edited data in the database field.

 

Ideally I'm aiming for a user to be able to edit their account details but for now I'm happy for an admin script.

 

1) A form to pull row by id

2) A form with row data for editing

3) Form handler to update or write to id row

 

I'm sure this can be achieved in one script

My first problem is echo the data into the form fields

 

 
<?PHP  echo("<P>" . $row["BusinessName"] . "</P>"); ?>
}

 

 

Link to comment
Share on other sites
dp2 Newbie

dp2

Posted
  • Author
Posted

This is a modified script to fetch the data and display into the form for editing then to write to the fields.

However the script is not loading the data into the form

 

<?php 
session_start();

if (!isset($_SESSION['user']))
{
header("Location: login.php");
}

include ('dbc.php'); 

$directory_tablename = 'regdir';    //  table
$link_id = 0;
$category_id_length = 3;
$root_category_id = '000';
$max_desc_length = 250;
$new_window_width = 600;
$new_window_height = 500;
$welcome_message = "Welcome to the Directory!";
$records_per_page = 3;
$num_top_sites = 5;
$num_new_sites = 5;
$admin_script = 'dir_manager.php';
$send_mail = False;
$dirmaster_email = "dirmaster<admin@xxxx>";
$MYSQL_ERRNO = '';
$MYSQL_ERROR = '';


function modify_url_form() {
   global $PHP_SELF, $id, $BusinessName, $BusinessStreet, $User_pwd;
   //directory_header();
   //if(basename($PHP_SELF) == $admin_script) $admin = 1;
   $url_info_array = get_url_info($id);
   //$category_info_array = get_category_info($url_info_array["id"]);
}
?>
<CENTER><H3>Update your PHP site!</H3></CENTER>

<FORM METHOD="POST" ACTION="<?php echo $PHP_SELF ?>">
<INPUT TYPE="HIDDEN" NAME="action" VALUE="web">
<INPUT TYPE="HIDDEN" NAME="id" VALUE="<?php echo $id ?>">
<INPUT TYPE="HIDDEN" NAME="BusinessName" VALUE="<?php echo $BusinessName ?>">
  <CENTER><TABLE BORDER="1" WIDTH="90%">
    <TR>
      <TH WIDTH="20%" NOWRAP>BusinessName</TH>
      <TD WIDTH="80%"><?php echo $url_info_array["BusinessName"] ?></TD>
    </TR>
    <TR>
      <TH WIDTH="20%" NOWRAP>user_pwd</TH>
      <TD WIDTH="80%"><INPUT TYPE="TEXT" NAME="user_pwd" 
                             SIZE="30" MAXLENGTH="150"></TD>
    </TR>    
    <TR>
      <TH WIDTH="20%" NOWRAP>BusinessStreet</TH>
      <TD WIDTH="80%"><INPUT TYPE="PASSWORD" NAME="BusinessStreet"
                             SIZE="20" MAXLENGTH="20"></TD>
    </TR>    
  
    <TR>
      <TH WIDTH="20%" NOWRAP>Town</TH>
      <TD WIDTH="80%"><INPUT TYPE="TEXT" NAME="Town" 
                             VALUE="<?php echo $url_info_array["Town"] ?>"
                             SIZE="30" MAXLENGTH="150"></TD>
    </TR>
    <TR>
      <TH WIDTH="20%" NOWRAP>UserEmail</TH>
      <TD WIDTH="80%"><INPUT TYPE="TEXT" NAME="UserEmail"
                      VALUE="<?php echo "".$UserEmail["UserEmail"];?>"
                      SIZE="30" MAXLENGTH="150"></TD>
    </TR>
    <TR>
      <TH WIDTH="20%" NOWRAP>Web<BR>
      (<?php echo $max_desc_length ?> Chars Max.)
      </TH>
      <TD WIDTH="80%"><TEXTAREA NAME="web" ROWS="5" COLS="40">
      <?php echo $url_info_array["web"] ?>
      </TEXTAREA></TD>
    </TR>
    <TR>
      <TH WIDTH="100%" NOWRAP COLSPAN="2">
      <INPUT TYPE="SUBMIT" VALUE="Modify URL" NAME="Submit">
      <INPUT TYPE="RESET" VALUE="Reset"></TH>
    </TR>
  </TABLE>
  </CENTER>
</FORM>
<?php
//   directory_footer();
//}

function modify_url() {
  //display all the news
   $result = mysql_query("select * from BusinessName order by id"); 
   
   //run the while loop that grabs all the data scripts
   while($r=mysql_fetch_array($result)) 
   { 
      //grab the title and the ID of the news
      $title=$r["BusinessStreet"];//take out the title
      $id=$r["id"];//take out the id
     
 //make the title a link
      echo "<a href='edit.php?cmd=edit&id=$id'>$title - Edit</a>";
      echo "<br>";
    }



   if(empty($BusinessName)) error_message("Please enter the Business Name!");
   if(empty($Web)) error_message("Please enter the URL!");
   if(empty($Message1)) error_message("Please enter the description!");
   
   if(strlen($Message1) > $max_desc_length) error_message("Description too long! 150 chars max!");
   $url_info_array = get_url_info($id);
   if(!$admin && (($url_info_array["user_pwd"] != crypt($user_pwd, '.v')) || ($url_info_array["UserEmail"] != $email))) error_message("You don't have permission to modify this URL!");
   $BusinessName = addslashes($BusinessName);
   $Message1 = addslashes($Message1);
   if(!$link_id) $link_id = db_connect($default_dbname);
      
   $query = "UPDATE regdir SET BusinessName='$BusinessName', Web='$Web', Message1='$Message1' WHERE id='$id'";
   $result = mysql_query($query);
   if(!$result) error_message(sql_error());
   $num_rows = mysql_affected_rows($link_id);
   if($num_rows != 1) error_message(sql_error());
   else echo "<SCRIPT>alert(\"$BusinessName($Web) has been successfully modified!\"); 
opener.location.href='$PHP_SELF?action=show_list&id=$id&cur_page=$cur_page'; self.close();</SCRIPT>";

}

Link to comment
Share on other sites
dp2 Newbie

dp2

Posted
  • Author
Posted

Work in progress - I managed to pull data into a form

 

<HTML>
<HEAD>
<TITLE> The Edit Directory Listing</TITLE>
<HEAD>
<BODY>
<?php 

$dbcnx = @mysql_connect("localhost", "username", "password");
if (!$dbcnx) {
  echo( "<P>Unable to connect to the" .
        "database server at this time.</P>" );
  exit();
}

//select which database you want to edit
mysql_select_db("aynsley_phpbb1",$dbcnx);

if (! @mysql_select_db("aynsley_phpbb1") ) {
  echo( "<P>Unable to locate the regdir " .
        "database at this time.</P>" );
  exit();
}

// Request the text of all the business names
  $result = mysql_query(
            "SELECT BusinessName FROM regdir");
  if (!$result) {
    echo("<P>Error performing query: " .
         mysql_error() . "</P>");
    exit();
  }
//mysql_query(<query>, <connection id>);
while ($row=mysql_fetch_array($result)) {
list ($BusinessStreet, $BusinessName) = $row;
}
// Request the text of all the business names
  $result = mysql_query("SELECT * FROM regdir");
  if (!$result) {
    echo("<P>Error performing query: " .
         mysql_error() . "</P>");
    exit();
  }

  // Display the text of each BusinessName in a paragraph
while ( $row = mysql_fetch_array($result) ) {
list ($BusinessName, $BusinessStreet) = $row;
}  
?>
<html>
<body>
<form method="post"action="<?php echo $PHP_SELF ?>">
Business Name:
<input type=text name="BusinessName" value="<?PHP echo $BusinessName ?>"><br>
Business Street:
<input type=text name="BusinessStreet" value="<?PHP echo $BusinessStreet ?>"><br>

<input type=submit name="submit" value="submit">
</form>

</BLOCKQUOTE>
</BODY>
</HTML>

 

Is there a method to simplfy and condense further

Link to comment
Share on other sites
  • 2 weeks later...
dp2 Newbie

dp2

Posted
  • Author
Posted

I think we are moving towards a final solution!

 

Updating data using a php form to MYSQL table

 

I'm stuck here:

$query="SELECT * FROM systemsdoc WHERE id = \"".$formVars["id"]."\"";
$result=mysql_query($query);
$row=mysql_fetch_array($result);

 

I'm getting

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource

 

Its pointing to line 9

$row=mysql_fetch_array($result);

<?php
foreach($HTTP_POST_VARS as $varname => $value)
$formVars[$varname]=$value;
require_once("dbc1.php");
$db1=mysql_connect($dbhost, $dbuname, $dbpass);
mysql_select_db("regdir");
$query="SELECT * FROM regdirWHERE id = \"".$formVars["id"]."\"";
$result=mysql_query($query);
$row=mysql_fetch_array($result);
$formVars = array();
$formVars["BusinessName"]=$row["BusinessName"];
$formVars["BusinessStreet"]=$row["BusinessStreet"];
$formVars["Town"]=$row["Town"];
$formVars["PostCode"]=$row["PostCode"];
$formVars["Telephone"]=$row["Telephone"];
$formVars["joined"]=$row["joined"];
$formVars["UserEmail"]=$row["UserEmail"];
$formVars["Mobile"]=$row["Mobile"];
$formVars["user_pwd"]=$row["user_pwd"];
$formVars["Web"]=$row["Web"];
$formVars["id"]=$row["id"];
mysql_close($db1);
?>

 

Please could someone point me in the right direction

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.