Selecting multiple values from multiple tables using checkboxes

[swatisonee]

Hi,

 

I've been struggling with this for a bit but cannot seem to find a simpler, cleaner way of doing this.

 

a. I have a Table A - Customers , Table B - Contacts .

b. I have a form where a user types the first few alphabets of a customer name and then gets a list of contacts, cities.

c. The user is supposed to select multiple options from this list and then send that data to another form for processing.

 

I'm able to only pass data from Table A, not from B . Could someone please have a look at the 2 snippets of code and advise? And is there a way both these php scripts could be combined in a single one ?

Thanks so much.

 

Swati

 

Addit1.php

========

 

 

<?
include ("../include/session.php");


if(!isset($_SESSION['userid'])){

echo "<center><font face='Verdana' size='2' color=red>Sorry, Please login and use this page </font></center>";

exit;}


$userid = $_SESSION['userid'];

if(isset($_POST) && !empty($_POST["customer"]))
{


?>



<b><p><font face="Tahoma" size="2" color="#FF8000">These are the customers in your database. </b></font><p>
<table border="1" style="border-collapse: collapse" bordercolor="#111111" width="100%">
<tr bgcolor="#FF8000">




<form method="post" action="addit2.php?userid=<? echo $userid ?>">



<td><b><font size=2 face=Tahoma color=white>Select</b></td>
<td><b><font size=2 face=Tahoma color=white>Company</b></td>
<td><b><font size=2 face=Tahoma color=white>First Name</b></td>
<td><b><font size=2 face=Tahoma color=white>Last Name</b></td>
<tr>

<?php

$customer = $_POST["customer"];

$sqla = "SELECT * FROM Customers WHERE Company LIKE '%$customer%' ORDER BY `Company` asc";

$resulta = mysql_query($sqla) or die (mysql_error());


if ($myrowa = mysql_fetch_array($resulta))
{
do
{

$cid = $myrowa["CID"];

$sqlb = "SELECT * FROM Custcontact WHERE CID = '$cid'";

//echo $sqlb;
$resultb = mysql_query($sqlb) or die(mysql_error());

if ($myrowb= mysql_fetch_array($resultb))
{
do
{
$fn =$myrowb["Firstname"];
$ln =$myrowb["Lastname"];
$ccid=$myrowb["CCID"];

echo"<font face='Verdana' size='2' color=red>";
//echo $ccid;

    printf("<tr><td><input type=\"checkbox\" name=\"choice[]\" value=%d><td>
    <font size=2 face=Tahoma color=white>%s<td>
    <font size=2 face=Tahoma color=white>%s<td>
    <font size=2 face=Tahoma color=white>%s<td>
     </tr>",
$myrowa["CID"],
$myrowa["Company"],
$fn,
$ln);

  } while ($myrowb = mysql_fetch_array($resultb));
  }

  } while ($myrowa = mysql_fetch_array($resulta));
  }
?>



</table>

<input type=hidden name="ccid"  value="<? echo $ccid ?>" size="5">

<p><input type="submit" value="Select">
</form>
<?php
}
else
{
  showForm();
}


function showForm()
{

$userid = $_GET["userid"];



?>


<form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
<input type="hidden" name="userid" value="<? echo $userid ?>">

<font face="Tahoma" size="3" color="#C0C0C0">Type a few alphabets of the company name.   <P>

<font face="Tahoma" size="2" color="#FFF000">Company:<input type="text" size="12" name="customer">

<input type="submit" value="Search">

</form>



<?php
}
?>

 

Addit2.php

========

 

<?
include ("../include/session.php");


if(!isset($_SESSION['userid'])){

echo "<center><font face='Verdana' size='2' color=red>Sorry, Please login and use this page </font></center>";

exit;}


$userid = $_SESSION['userid'];


?>

<?php

$customer = $_POST['choice'];

$comp = implode($customer,",");     // works correctly



$ccid = $_POST['ccid'];

$ccid1 = implode($ccid,",");  // lists only the last value from Table B


//This is what i wish to do .

$sqla = "INSERT INTO Table X (CID,CCID) VALUES ($cid,$ccid) ";

//which will contain all the CIDs and CCIDs selected by the user.
?>

[Barand]

After the line outputting the checkbox, have a hidden field for the ccid

 

<?php
    printf("<tr><td><input type=\"checkbox\" name=\"choice[]\" value=%d><td>
    <font size=2 face=Tahoma color=white>%s<td>
    <font size=2 face=Tahoma color=white>%s<td>
    <font size=2 face=Tahoma color=white>%s<td>
     </tr>",
$myrowa["CID"],
$myrowa["Company"],
$fn,
$ln);

echo "<input type='hidden' name='ccid[]'  value='$ccid' >";

[swatisonee]

Thanks Barand..i tried to expand the scope of the multiple table using joins but now i'm stuck. I wanted to select all $customers having the same / similar contacts and addresses but obviously the where clause in the following statement is wrong as it cannot identify all $customers sharing similar contacts and/or addresses.The table structure is given below

 

<?php

$customer = $_POST["customer"];

$sqla = "SELECT * FROM Customers c INNER JOIN Custcontact a USING(CID) INNER JOIN Custaddress t USING(CID) INNER JOIN Custaddress w USING(AID)  INNER JOIN Cities t USING(Cityid) WHERE c.Company LIKE '%$customer%' ORDER BY Company asc";

$resulta = mysql_query($sqla) or die (mysql_error ());
while ($myrowa=mysql_fetch_array($resulta)) {

$aid= $myrowa['AID']; // its a many to many relationship with $cid. ie multiple companies can share the same address.
$ccid = $myrowa["CCID"]; // its a many to many relationship with $cid. ie multiple contacts can share the same address in the same company or in multiple companies.

$cid = $myrowa["CID"];
$cityid = $myrowa["Cityid"];
.....and so on ...
?>

<form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
<input type="hidden" name="userid" value="<? echo $userid ?>">

<font face="Tahoma" size="3" color="#C0C0C0">Type a few alphabets of the company name.   <P>

<font face="Tahoma" size="2" color="#FFF000">Company:<input type="text" size="12" name="customer">

<input type="submit" value="Search">

</form>

 

Table Customers :

CID || Company

 

Table Custcontact :

CCID || CID || AID || FN || LN

 

Table Custaddress :

CAID (auto increment) || AID || CID || Cityid || Address

 

How can I rectify the $sql ?

 

Thanks,

Swati