I've got two data tables like below.
Table 1:
--------------------
sbstart sbend totsb
--------------------
200 205 6
Table 2:
chkNo
------
201
203
I have got a dropdown box created dynamically which contains table 1 information which is all the responses from 200 to 205. In other words that dropdown has 200,201,202...205. What I now need is to exclude the numbers in table 2 once the dropdown box is created. For instance then the dropdown should have only 200,2004 and 2005 when it is displayed.
Here is the code I've done to get all the responses between start and end number as per table 1. Can someone please tell me how do I exclude the table 2 numbers once the dropdown is created. Thanks.
$con=mysql_connect('localhost','root') or die ("Server connection failure!");
$db=mysql_select_db('regional_data',$con) or die ("Couldn't connect the database");
$SQLx="SELECT * FROM table1";
$runx=mysql_query($SQLx,$con) or die ("SQL Error");
$norx=mysql_num_rows($runx);
while ($rec = mysql_fetch_array($runx))
{
for($i=$rec['sbstart']; $i<=$rec['sbend']; $i++)
{
echo "<option id='options' value='$i'>$i<br></option>";
}
}
I would suggest leveraging array_diff() to limit the initial creation of the dropdown to only those values which don't exist in the second table, like this:
//set up the db connection
$con = mysql_connect('localhost','root') or die ("Server connection failure!");
$db = mysql_select_db('regional_data', $con) or die ("Couldn't connect the database");
//get the range of values from the first table
$SQLx = "SELECT * FROM table1";
$runx = mysql_query($SQLx, $con) or die ("SQL Error");
$rec = mysql_fetch_array($runx);
//create an array representing the range of values
$table1_array = array();
for($i = $rec['sbstart']; $i <= $rec['sbend']; $i++)
{
$table1_array[] = $i;
}
//get the values to be omitted from the second table
$SQLz = "SELECT * FROM table2";
$runz = mysql_query($SQLz, $con) or die ("SQL Error");
//create an array representing the values to be omitted
$table2_array = array();
while ($rec2 = mysql_fetch_array($runz))
{
$table2_array[] = $rec2['chkNo'];
}
//compare the arrays
//this results in an array of only the values you wish to include
$final_array = array_diff($table1_array, $table2_array);
//create the dropdown from the resulting array
foreach ($final_array as $value)
{
echo "<option id='options' value='$value'>$value<br></option>";
}
Try this. This builds an exclude list from table2 before building the drop down:
$con=mysql_connect('localhost','root') or die ("Server connection failure!");
$db=mysql_select_db('regional_data',$con) or die ("Couldn't connect the database");
$exclude = array();
$query = 'SELECT * FROM table2';
$runx=mysql_query($query,$con) or die ("SQL Error");
$norx=mysql_num_rows($runx);
while ($rec = mysql_fetch_array($runx))
{
$exclude[] = $rec['chkNo'];
}
$SQLx="SELECT * FROM table1"
$runx=mysql_query($SQLx,$con) or die ("SQL Error");
$norx=mysql_num_rows($runx);
while ($rec = mysql_fetch_array($runx))
{
for($i=$rec['sbstart']; $i<=$rec['sbend']; $i++)
{
if (!in_array($i, $exclude))
{
echo "<option id='options' value='$i'>$i<br></option>";
}
}
}
$con=mysql_connect('localhost','root') or die ("Server connection failure!");
$db=mysql_select_db('regional_data',$con) or die ("Couldn't connect the database");
$SQLx="SELECT * FROM table1";
$SQLy="SELECT * FROM table2";
$runx=mysql_query($SQLx,$con) or die ("SQL Error");
$runy=mysql_query($SQLy,$con) or die ("SQL Error");
$norx=mysql_num_rows($runx);
while ($rec = mysql_fetch_array($runx))
{
for($i=$rec['sbstart']; $i<=$rec['sbend']; $i++)
{
$exist=0;
while($rec2=mysql_fetch_array($runy)){
if($i==$rec2['chkNo']){
$exist=1;
}
}
if ($exist==0)
echo "<option id='options' value='$i'>$i<br></option>";
}
}