update multi-value attribute record in php

Sometimes, we need to update a  record in php that has multi-value attributes. At database end, the multi-value attributes are stored in a separate table, with the primary of the record against whose the multi-value attributes are stored. In php, to update multi-value attributes, we need to do some extra work.

Suppose we have file:

index.php

<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>Untitled Document</title>

<script language="javascript" type="text/javascript">
function validate()
{return true;
  var name = document.getElementById("txtname");
  var city = document.getElementById("ddlcity");
  var pref = document.getElementsByName("chkpreference");
  
  if( name.value.length == 0)
  {
    alert("Name must be entered");
    return false;
  }
  
  

  //document.write(name.value);
  
  //document.write(city.value);
  //document.write(city.selectedIndex);
  
  if(city.selectedIndex == 0)
  {
    alert("city must be selected");
    return false;
  }
  //document.write(pref[0].value + " " + pref[1].value + " " + pref[2].value);
  //document.write(pref[0].checked + " " + pref[1].checked + " " + pref[2].checked);
  
  var status = false;
  for(var i=0; i<pref.length; i++)
  {
    if(pref[i].checked == true)
    {
      status = true;
      break;
    }
  }
  
  if(status == false)
  {
    alert("Preference must be selected");
    return false;
  }
  
  return true;
}
</script>
</head>

<body>

<?php

if( isset($_POST['btnsubmit']) )
{
  echo $_POST['txtname'] . "<br>";
  echo $_POST['ddlcity'] . "<br>";
  echo sizeof($_POST['chkpreference']). "<br>";
  $chkpreference = $_POST['chkpreference'];
  
  foreach($chkpreference as $value)
  {
    echo $value . "<br>";
  }
}
?>
<form id="frm" name="frm" method="post" action="" onSubmit="return validate();">
  <table width="325" border="1">
    <tr>
      <td width="89">Name</td>
      <td width="220"><input type="text" name="txtname"  id="txtname"></td>
    </tr>
    <tr>
      <td>City</td>
      <td><select name="ddlcity" id="ddlcity">
        <option value="SELECT">SELECT</option>
        <option value="Karachi">Karachi</option>
        <option value="Lahore">Lahore</option>
        <option value="Peshawar">Peshawar</option>
        <option value="Islamabad">Islamabad</option>
      </select></td>
    </tr>
    <tr>
      <td>Preference</td>
      <td>
        
          <input type="checkbox" name="chkpreference[]" value="Sports" id="chkpreference[]">
          Sports
        <br>
        
          <input type="checkbox" name="chkpreference[]" value="Politics" id="chkpreference[]">
          Politics
        <br>
        <input type="checkbox" name="chkpreference[]" value="Business" id="chkpreference[]">
          Business
        
      </td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td><input type="submit" name="btnsubmit" id="btnsubmit" value="Submit"></td>
    </tr>
  </table>
</form>

</body>
</html>

In the above file, the chkpreference[] is a checkbox array representing a multi-valued attribute. In php, we need to declare an html object array with angular brakets [] otherwise, the php will not recognize it as array, which is unlike javascript.

The following file is edit.php that will perform the edit operation

edit.php

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>

<?php
include("db/opendb.php");
$std_id=1;
$query = "select stdid,city from stdrecord where stdid=".$std_id;

  $city = "";
    
      try
      {
                $stmt = $conn->prepare($query);	
      
      
        $stmt->execute();
        
        while($row = $stmt->fetch())
        {
          echo $row['stdid'] . " " . $row['city'];
          $city = $row['city'];
        }
      }
      catch(PDOException $e)
      {
        echo $e->getMessage();
      }

function DropDown($query, $city="")
{
  global $conn;
  $options="";
        try
      {
                $stmt = $conn->prepare($query);	
    
$stmt->execute();
        
        while($row = $stmt->fetch())
        {
          if($row['cityname'] == $city)
          $options = $options . "<option selected value=\"".$row['cityname']."\">" . $row['cityname'] . "</option>";
          else
          $options = $options . "<option value=\"".$row['cityname']."\">" . $row['cityname'] . "</option>";
  
        }
      }
      catch(PDOException $e)
      {
        echo $e->getMessage();
      }

  return $options;
}

?>

<form id="frm" name="frm" method="post" action="">
        
<select name="ddlcity" id="ddlcity">
<?php echo DropDown("select cityname, cityname from cities order by seq", $city); ?>
      </select>
<input type="submit" name="btnupdate" id="btnupdate" value="Update" />
</form>
<?php $conn=NULL; ?>
</body>
</html>

The above file as contains a function named DropDown. The purpose of this function is to create dynamic dropdown lists in PHP. This function just needs a query, and the currently selected value of option to show the dropdown list.

If you want to use the DropDown function in an “insert” form, then you just need to supply the first parameter, that i is the query.

The following is the database file used in the above PHP code files

student.sql

-- phpMyAdmin SQL Dump

--
-- Database: `student`
--

-- --------------------------------------------------------

--
-- Table structure for table `cities`
--

CREATE TABLE `cities` (
  `cityname` varchar(50) NOT NULL,
  `seq` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `cities`
--

INSERT INTO `cities` (`cityname`, `seq`) VALUES
('Islamabad', 0),
('Karachi', 0),
('Lahore', 0),
('SELECT', 0);

-- --------------------------------------------------------

--
-- Table structure for table `stdrecord`
--

CREATE TABLE `stdrecord` (
  `stdid` int(11) NOT NULL,
  `city` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `stdrecord`
--

INSERT INTO `stdrecord` (`stdid`, `city`) VALUES
(1, 'Islamabad'),
(2, 'Karachi');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `cities`
--
ALTER TABLE `cities`
  ADD PRIMARY KEY (`cityname`);

--
-- Indexes for table `stdrecord`
--
ALTER TABLE `stdrecord`
  ADD PRIMARY KEY (`stdid`),
  ADD KEY `city` (`city`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `stdrecord`
--
ALTER TABLE `stdrecord`
  MODIFY `stdid` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
--
-- Constraints for dumped tables
--

--
-- Constraints for table `stdrecord`
--
ALTER TABLE `stdrecord`
  ADD CONSTRAINT `stdrecord_ibfk_1` FOREIGN KEY (`city`) REFERENCES `cities` (`cityname`);

 

Add a Comment

Your email address will not be published. Required fields are marked *