many to many insert/update help??

john6767

i'm trying to do a many to many insert/update and cannot find any information on how this is done in php? I have done this in asp before but am not familiar enough with php to get a working version.

Basically i have a table of areas and a table of customers, the customer may have many areas that they are interested in and as such i have another table areas_customers that is a joining table. I can display the areas as checkboxes on the customer add page but cannot get the the areas to be added into the database.

does anyone have any simple working examples they can post that i can learn off? I cannot find any examples, tutorials or anything on the web at all and this would be a common thing for php programmers to be doing..

here is the asp code that i have used in the past for an insert..

<%
set customersRS = Server.CreateObject("ADODB.Recordset")
customersRS.ActiveConnection = MM_connLMYC_STRING
customersRS.Source = "SELECT top 1 *  FROM customerRegister  order by customerID desc"
customersRS.CursorType = 0
customersRS.CursorLocation = 2
customersRS.LockType = 3
customersRS.Open()
customersRS_numRows = 0
%>
<%
If Request("selected") Then
customersRS.addnew
customersRS("Name")=Request("customerName")
customersRS("address")=Request("address")
customersRS("suburb")=Request("suburb")
customersRS("postcode")=Request("postcode")
customersRS("homePhone")=Request("homePhone")
customersRS("fax")=Request("fax")
customersRS("mobilePhone")=Request("mobilePhone")
customersRS("workPhone")=Request("workPhone")
customersRS("email")=Request("email")
customersRS("preferredContactMethod")=Request("preferredContactMethod")
customersRS.update
customersRS.requery
customerID=customersRS("customerID")


' append the query string to the redirect URL
Dim MM_editRedirectUrl
MM_editRedirectUrl = "customer_register.asp"
If (Request.QueryString <> "") Then
  If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0) Then
    MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
  Else
    MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
  End If
End If
customersRS.requery
customerID=customersRS("customerID")
		  'the insert script for lmyc
		  'this is the positions insert script
		  Set MM_Cmd = Server.CreateObject("ADODB.Command")
	  MM_Cmd.ActiveConnection = MM_connLMYC_STRING
	myarray=split(request("positionID"),",")
	for i=0 to ubound(myarray)
	  MM_Cmd.CommandText = "INSERT INTO positionsCustomers (customerID,positionID) VALUES (" & customerID & "," & myarray(i) & ")"
	  MM_Cmd.Execute
	next
	'end of insert multiple script...
Response.Redirect("customer_register.asp")
end if
%>

pete_bisby

That doesn't sound like a many-to-many relationship - one customer has many areas is one-to-many.

If you have the area checkboxes within the form defined as an array ...

// Assume that the database returns the list of areas
for ($i=0; $i < mysql_num_rows($result); $i++) {
  $row = mysql_fetch_row($result);
  echo "<checkbox name=\"area[]\" value=\"" . $row[0] . "\">\n";
}

... you can then simply loop through the array that is posted, and insert this information within the table ...

foreach ($_POST["area"] as $areaname) {
  $ary = "INSERT INTO area_customer VALUES (" . $customerID . ", '" . $areaname . "')";
// execute query
}

john6767

ok, that sounds sensible. How would i go about an update page where the checkboxes would be checked if they are chosen or unchecked if not chosen, then check the new boxes and update the area_customer table as needed?

pete_bisby

Create a LEFT JOIN query that will bring all of the checkboxes, and a marker to say which ones should be checked. For example (PostgreSQL query):

SELECT areas.areaname, COALESCE(area_customer.areaname, '') AS "checked"
FROM areas LEFT JOIN area_customer ON areas.areaname=area_customer.areaname;

From this query you are then able to see which checkbox should be checked or not. If you're using MySQL, just check the syntax of the COALESCE command - I'm unsure whether this is present in MySQL, you might be able to get away with an IF statement.

john6767

ok, i have been playing with this but have not got anywhere, i have made a test site to get this concept going, here is the add page code, i have not got the checkboxes working so i cannot test the insert, but i applied the code, where have i gone wrong..

<?php require_once('Connections/cnnTest.php'); ?>
<?php
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $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"] == "balls_add_form")) {
  $insertSQL = sprintf("INSERT INTO balls (ball_name, ball_desc) VALUES (%s, %s)",
                       GetSQLValueString($_POST['txt_ball_name'], "text"),
                       GetSQLValueString($_POST['txt_ball_desc'], "text"));

  mysql_select_db($database_cnnTest, $cnnTest);
  $Result1 = mysql_query($insertSQL, $cnnTest) or die(mysql_error());
//standard dreamweaver insert code above
//my insert for sizes below
foreach ($_POST["size"] as $sizeID) { 
  $ary = "INSERT INTO balls_sizes (ball_id, size_id VALUES (" . $ball_id . ", '" . $sizeID . "')"; 
// execute query 
	mysql_select_db($database_cnnTest, $cnnTest);
  $Result2 = mysql_query($ary, $cnnTest) or die(mysql_error());
} 

//end of my insert for sizes


  $insertGoTo = "index.php";
  if (isset($_SERVER['QUERY_STRING'])) {
    $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
    $insertGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $insertGoTo));
}

mysql_select_db($database_cnnTest, $cnnTest);
$query_rsSizes = "SELECT * FROM sizes ORDER BY size_name ASC";
$rsSizes = mysql_query($query_rsSizes, $cnnTest) or die(mysql_error());
$row_rsSizes = mysql_fetch_assoc($rsSizes);
$totalRows_rsSizes = mysql_num_rows($rsSizes);
?><!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=iso-8859-1" />
<title>Balls add page</title>
</head>

<body>
<p>Balls add page</p>
<form id="balls_add_form" name="balls_add_form" method="POST" action="<?php echo $editFormAction; ?>">
  <table width="80%" border="0" cellpadding="0" cellspacing="0">
    <tr>
      <td>Ball Name </td>
      <td><input name="txt_ball_name" type="text" id="txt_ball_name" /></td>
    </tr>
    <tr>
      <td>Ball Desc </td>
      <td><input name="txt_ball_desc" type="text" id="txt_ball_desc" /></td>
    </tr>
    <tr>
      <td>Sizes</td>
      <td>
	  <?php 
	  // Assume that the database returns the list of areas 
		for ($i=0; $i < mysql_num_rows($rsSizes); $i++) { 
		  $row = mysql_fetch_row($rsSizes); 
		  echo "<input type=\"checkbox\" name=\"size[]\" value=\"" . $row[0] . "\">\n"; 
		} 

  ?>
  <input type="checkbox" name="checkbox" value="checkbox" /></td>
</tr>
<tr>
  <td>&nbsp;</td>
  <td><input type="submit" name="Submit" value="Add Ball" /></td>
</tr>
  </table>
  <input type="hidden" name="MM_insert" value="balls_add_form">
</form>
</body>
</html>
<?php
mysql_free_result($rsSizes);
?>

tjohnson_nb

Having trouble following this code but isn't there a bracket missing in this line after size_id??

$ary = "INSERT INTO balls_sizes (ball_id, size_id VALUES (" . $ball_id . ", '" . $sizeID . "')";

Also I write my queries without all those concatenation operators like this;

$ary = "INSERT INTO balls_sizes (ball_id, size_id) VALUES ('$ball_id ', '$sizeID')";

I find it much easier to read!

john6767

thanks, i didn't know you could do it without the concatenation!

one of the errors i believe is where the checkboxes are displayed.. the checkboxes aren't showing for every size, i have never done this before so i think its something simple..

<?php 
      // Assume that the database returns the list of sizes 
        for ($i=0; $i < mysql_num_rows($rsSizes); $i++) { 
          $row = mysql_fetch_row($rsSizes); 
          echo "<input type=\"checkbox\" name=\"size[]\" value=\"" . $row[0] . "\">\n"; 
        } 

  ?>

tjohnson_nb

Not sure what you mean, can you be more specific? Do you mean some boxes are checked and some are not or no boxes at all??

pete_bisby

Your post with all of the coding on it (Oct 7th) - part of the way through you have mysql_fetch_assoc($rsSizes), which is going to return the first row of your recordset.

Then you have a for loop, which is going to return the rest of the recordset. If you're missing the first record for your checkboxes, remove the mysql_fetch_assoc and leave the for loop in.