Jump to content

insert multiple database table row from dynamic checkboxes from db

sir_amin

By sir_amin
in PHP Coding Help

 Share

Recommended Posts

sir_amin Newbie

sir_amin

Posted
Posted

hello there

 

i have a form for submiting data into mysql table with the name of TERM_LESSON table in this form i have a sets of checkboxes that came from a table with the name GRADE these are my two tables:

TERM_LESSON

 

CREATE TABLE IF NOT EXISTS `term_lesson` (
  `lid` int(5) NOT NULL,
  `gradeid` int(1) NOT NULL,

  PRIMARY KEY (`lid`,`gradeid`)
) ENGINE=MyISAM ;


GRADE

CREATE TABLE IF NOT EXISTS `grade` (
  `gradeid` int(11) NOT NULL AUTO_INCREMENT,
  `gname` varchar(40)  NOT NULL,
  PRIMARY KEY (`gradeid`)
) ENGINE=MyISAM  AUTO_INCREMENT=0 ;

and this is my form:

 

<!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>

<form action="<?php echo $editFormAction; ?>" method="post" id="form1">

                            <table align="center">

                              <tr valign="baseline">

                                <td align="right">LID</td>

                                <td">

                               

                                <input name="lid[]" type="text" id="lid[]" value="<?php echo $row_rslesson['lid']; ?>" size="32" readonly="readonly" />

                                </td>

                             

                               

                              </tr>

                                <td >GRADE</td>

                                <td >

                                  <?php do { ?>

                                  <input name="gradeid[]" type="checkbox" id="gradeid[]" value="<?php echo $row_rstotalgrade['gradeid']; ?>" />

                                  <label for="gradeid[]"></label>

                                  <?php echo $row_rstotalgrade['gname']; ?><br />

                                <?php } while ($row_rstotalgrade = mysql_fetch_assoc($rstotalgrade)); ?></td>

                              <tr valign="baseline">

                                <td style="text-align: center"></td>

                                <td align="right"><input type="submit" value="submit" /></td>

                             

                              </tr>

                            </table>

                            <input type="hidden" name="MM_insert" value="form1" />

                          </form>

</body>

</html>

 

i want to make a TABLE ROW in TERM_LESSON for every checkboxes that is selected by user with same LID in form like: (for 2 chekbox seledted)

 

lid gradeid

11 , 1

11 , 2

 

and this is my php code for doing that:

 

<?php

if (!function_exists("GetSQLValueString")) {

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")

{

  if (PHP_VERSION < 6) {

    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  }

 

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

 

  switch ($theType) {

    case "text":

      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

      break;   

    case "long":

    case "int":

      $theValue = ($theValue != "") ? intval($theValue) : "NULL";

      break;

    case "double":

      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";

      break;

    case "date":

      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

      break;

    case "defined":

      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;

      break;

  }

  return $theValue;

}

}

$editFormAction = $_SERVER['PHP_SELF'];

if (isset($_SERVER['QUERY_STRING'])) {

  $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);

}

 

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {

 

  foreach ($_POST['gradeid'] as $i) {

 

  $insertSQL = sprintf("INSERT INTO term_lesson (lid, gradeid) VALUES (%s, %s)",

                      GetSQLValueString($_POST['lid'], "int"),

                      GetSQLValueString($i, "int"));

 

  mysql_select_db($database_register, $register);

  $Result1 = mysql_query($insertSQL, $register) or die(mysql_error());

  }

  $insertGoTo = "lesson_term_added.php";

  if (isset($_SERVER['QUERY_STRING'])) {

    $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";

    $insertGoTo .= $_SERVER['QUERY_STRING'];

  }

  header(sprintf("Location: %s", $insertGoTo));

}

 

mysql_select_db($database_register, $register);

$query_rstotalgrade = "SELECT * FROM grade";

$rstotalgrade = mysql_query($query_rstotalgrade, $register) or die(mysql_error());

$row_rstotalgrade = mysql_fetch_assoc($rstotalgrade);

$totalRows_rstotalgrade = mysql_num_rows($rstotalgrade);

?>

 

 

but when i submit the form i face to this error :

 

Warning: mysql_real_escape_string() expects parameter 1 to be string, array given in C:\wamp\www\register\admin\add_lesson_term.php on line 11

Warning: mysql_real_escape_string() expects parameter 1 to be string, array given in C:\wamp\www\register\admin\add_lesson_term.php on line 11

Warning: mysql_real_escape_string() expects parameter 1 to be string, array given in C:\wamp\www\register\admin\add_lesson_term.php on line 11

Warning: mysql_real_escape_string() expects parameter 1 to be string, array given in C:\wamp\www\register\admin\add_lesson_term.php on line 11

Warning: mysql_real_escape_string() expects parameter 1 to be string, array given in C:\wamp\www\register\admin\add_lesson_term.php on line 11

Warning: mysql_real_escape_string() expects parameter 1 to be string, array given in C:\wamp\www\register\admin\add_lesson_term.php on line 11

Warning: mysql_real_escape_string() expects parameter 1 to be string, array given in C:\wamp\www\register\admin\add_lesson_term.php on line 11

Warning: mysql_real_escape_string() expects parameter 1 to be string, array given in C:\wamp\www\register\admin\add_lesson_term.php on line 11
Column 'lid' cannot be null

 

i appreciate any idea.

 

with prior thanks

sir_amin Newbie

sir_amin

Posted
  • Author
Posted

sorry i putted them in code block but..

 

<!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>
<form action="<?php echo $editFormAction; ?>" method="post" id="form1">
                            <table align="center">
                              <tr valign="baseline">
                                 <td align="right">LID</td>
                                <td">
                                
                                <input name="lid[]" type="text" id="lid[]" value="<?php echo $row_rslesson['lid']; ?>" size="32" readonly="readonly" />
                                </td>
                               
                                
                              </tr>
                                <td >GRADE</td>
                                <td >
                                  <?php do { ?>
                                  <input name="gradeid[]" type="checkbox" id="gradeid[]" value="<?php echo $row_rstotalgrade['gradeid']; ?>" />
                                  <label for="gradeid[]"></label>
                                  <?php echo $row_rstotalgrade['gname']; ?><br />
                                <?php } while ($row_rstotalgrade = mysql_fetch_assoc($rstotalgrade)); ?></td>
                              <tr valign="baseline">
                                <td style="text-align: center"></td>
                                <td align="right"><input type="submit" value="submit" /></td>
                               
                              </tr>
                            </table>
                            <input type="hidden" name="MM_insert" value="form1" />
                          </form>
</body>
</html>

 

?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  if (PHP_VERSION < 6) {
    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
  }

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}
$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
  $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {

  foreach ($_POST['gradeid'] as $i) {

  $insertSQL = sprintf("INSERT INTO term_lesson (lid, gradeid) VALUES (%s, %s)",
                       GetSQLValueString($_POST['lid'], "int"),
                       GetSQLValueString($i, "int"));

  mysql_select_db($database_register, $register);
  $Result1 = mysql_query($insertSQL, $register) or die(mysql_error());
  }
  $insertGoTo = "lesson_term_added.php";
  if (isset($_SERVER['QUERY_STRING'])) {
    $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
    $insertGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $insertGoTo));
}

mysql_select_db($database_register, $register);
$query_rstotalgrade = "SELECT * FROM grade";
$rstotalgrade = mysql_query($query_rstotalgrade, $register) or die(mysql_error());
$row_rstotalgrade = mysql_fetch_assoc($rstotalgrade);
$totalRows_rstotalgrade = mysql_num_rows($rstotalgrade);
?>

 

help me plz

jcbones Newbie

jcbones

Posted
Posted

Remove the array from the following input element.  It isn't in a while loop so there is no need for it, and it should cure your problems.

<input name="lid[]" type="text" id="lid[]" value="<?php echo $row_rslesson['lid']; ?>" size="32" readonly="readonly" />

Change to:

<input name="lid" type="text" id="lid" value="<?php echo $row_rslesson['lid']; ?>" size="32" readonly="readonly" />

Psycho Prolific Member

Psycho

Posted
Posted

This part of the code is very inefficient:

  foreach ($_POST['gradeid'] as $i) {

  $insertSQL = sprintf("INSERT INTO term_lesson (lid, gradeid) VALUES (%s, %s)",
                       GetSQLValueString($_POST['lid'], "int"),
                       GetSQLValueString($i, "int"));

  mysql_select_db($database_register, $register);
  $Result1 = mysql_query($insertSQL, $register) or die(mysql_error());
  }

 

You have a loop to do multiple INSERTs individually. Running queries in loops is always a bad idea. Even so, there is no need to select the database each time or to rerun the validation for $_POST['lid'] each time. That code can be much more efficient.

 

  $lid = GetSQLValueString($_POST['lid'], "int");
  $insertValues = array();
  foreach ($_POST['gradeid'] as $gradeid)
  {
    $insertValues[] = sprintf("(%s, %s)", $lid, GetSQLValueString($gradeid, "int"));
  }

  mysql_select_db($database_register, $register);
  //Create ONE query to insert all the records
  $insertSQL = "INSERT INTO term_lesson (lid, gradeid) VALUES " . implode(', ', $insertValues);
  $Result1 = mysql_query($insertSQL, $register) or die(mysql_error());

 

sir_amin Newbie

sir_amin

Posted
  • Author
Posted

another question:

 

how can i avoid from inserting duplicated data ?

 

i changed my TERM_LESSON table to:

 

CREATE TABLE IF NOT EXISTS `term_lesson` (
  `lid` int(5) NOT NULL,
  `tid` int(5) NOT NULL,
  `termid` int(3) NOT NULL,
  `gradeid` int(1) NOT NULL,
  `display` int(1) NOT NULL,
  PRIMARY KEY (`lid`,`tid`,`termid`,`gradeid`)
) ENGINE=MyISAM ;

 

and this code worked for me :

 

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {

  $lid = GetSQLValueString($_POST['lid'], "int");
  $tid =GetSQLValueString($_POST['tid'], "int");
  $termid =GetSQLValueString($_POST['termid'], "int");
  $display =GetSQLValueString($_POST['display'], "int");
  $insertValues = array();
  foreach ($_POST['gradeid'] as $gradeid)
  {
    $insertValues[] = sprintf("(%s, %s, %s, %s, %s )", $lid,$tid,$termid,$display,GetSQLValueString($gradeid, "int"));
  }

  mysql_select_db($database_register, $register);
  //Create ONE query to insert all the records
  $insertSQL = "INSERT INTO term_lesson (lid, tid, termid, display, gradeid) VALUES " . implode(', ', $insertValues);
  $Result1 = mysql_query($insertSQL, $register) or die(mysql_error());

 

i was avoiding duplicate data in database by (redirecting user to a page to show a message for duplicating data inserted) :

 

// *** Redirect if row(s) exists
$MM_flag="MM_insert";
if (isset($_POST[$MM_flag])) {
  $MM_dupKeyRedirect="lesson_term_exist.php";
  $loginUsername = $_POST['lid'];
  $lesson_tid = $_POST['tid'];
  $lesson_term = $_POST['termid'];
  $lesson_grade = $_POST['gradeid'];
  $LoginRS__query = sprintf("SELECT lid FROM term_lesson WHERE lid=%s AND tid=%s AND termid=%s ", GetSQLValueString($loginUsername, "int"), GetSQLValueString($lesson_tid, "int"), GetSQLValueString($lesson_term, "int"));
  mysql_select_db($database_register, $register);
  $LoginRS=mysql_query($LoginRS__query, $register) or die(mysql_error());
  $loginFoundUser = mysql_num_rows($LoginRS);

  //if there is a row in the database,  can not add the requested data
  if($loginFoundUser && $lesson_tid && $lesson_term && $lesson_grade ){
    $MM_qsChar = "?";
    //append the data to the redirect page
    if (substr_count($MM_dupKeyRedirect,"?") >=1) $MM_qsChar = "&";
    $MM_dupKeyRedirect = $MM_dupKeyRedirect . $MM_qsChar ."requsername=".$loginUsername;
    header ("Location: $MM_dupKeyRedirect");
    exit;
  }
}

 

is there any way for this code to check this ROWS OF DATA that is inserted?

 

 

Psycho Prolific Member

Psycho

Posted
Posted

I was thinking about that when you stated that these were checkboxes. I wasn't sure if this was a one-time operation or if you wanted to be able to edit the selections.

 

If these are to be editable, then you should have the page create the page to create all the checkboxes and automatically have the ones checked where there are corresponding records. Now, when the page is submitted you should do a two-step process. First DELETE all the associated records for the lid (lesson ID?) then INSERT new records for the checkboxes that are checked.

sir_amin Newbie

sir_amin

Posted
  • Author
Posted

NO these are NOT to be editable,but all that i want is if user tried to insert a row that is in the database alert user and say: YOU CAN NOT ADD THESE INFORMATION TWICE.

 

my table primary keys are:

 

PRIMARY KEY (`lid`,`tid`,`termid`,`gradeid`),

 

i want a select query with where clause for return this if exist with inserted data in form.

 

note: this is a INSERT page and is not intended to edit data.

 

very grateful for your fast replies

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.