[RESOLVED] Use Multiple Selects To Compare Data In a Single Table

cerd_web

Hello all, I am a little stuck with the initial stage of a small project.

I'm hoping that you maybe able to point me in the right direction or give me some thoughts on how I best could accomplish my goals with PHP.

In our database we have 10 columns of different kinds of apples. Our rows are arranged by weights, 1lb to tons and each cell has a price.

Something like this

Weight	Apple	Orange	Lime 	Banana	Alfalfa	Straw
1lb	$0.22	$0.23	$0.46	$0.22	$1.05	$1.06
2.2k	$0.22	$0.23	$0.46	$0.22	$1.05	$1.06
Gross	$22.00	$23.00	$46.00	$22.00	$105.00	$106.00
Bushel	na	na	na	na	$6.22	$6.24
Bale	na	na	na	na	$4.89	$4.90
10lbs	$2.20	$2.30	$4.60	$2.20	$10.50	$10.60
22k	$2.20	$2.30	$4.60	$2.20	$10.50	$10.60
50lbs	$11.00	$11.50	$23.00	$11.00	$52.50	$53.00
110k	$11.00	$11.50	$23.00	$11.00	$52.50	$53.00

I am going to build a page with 10 different Check boxes (1 for each apple (fruit type)). Then I am going to have a multi-select with the different weight types.

The goal:
The buyers will be able to check different types of apples then select 1 or more different weight types and compare them side by side in a table that is generated on submit.

Where I am lost at is how do I, do these multiple selects and get the data into a table which is based off of their choices?

I have coded other pages to get single data points ie "Select apples from fruits where weight =1lb" and I have even done the multi-select that gets all the weights "Select apples from fruits where weight=$t" then loop through the results in rows for the table. I just have no good idea on how to select results for each fruit and weight at the same time.. hope this makes since.

Any help is appreciated.

cerd_web

This is what I have so far maybe it will help.

Box[]= The Fruit Checkboxes
Test[]= The weights selected from multi-select box

<?php

    if (isset($_POST['test']) && !empty($_POST['test']))
        {

        foreach ($_POST['test'] as $t){

        echo "<tr>" . "<td width='64'>" . $t."." . "</td>";
		foreach ($_POST['box'] as $b) {
        $sql="SELECT $b FROM fruits WHERE weight='$t' LIMIT 0, 20";
        $result=mysql_query($sql2);
        while ($row=mysql_fetch_array($result2)) {
        $price=$row['$b'];
        echo "<td width='58' align='center' class='std_text'>" . $price . "</td>" . "</tr>";

                     }
                                }
                                     } 
		}

?>

I think I'm jumbling up my foreach loops.

sneakyimp

It's not clear to me what sort of checkboxes are on your form or what the structure of your table is or what the $_POST variables refer to. It's therefore a little hard to comment or offer advice.

Res

as sneakyimp said, it's a little difficult to decode what you're trying to do here. Looking at what you've put up here though, I imagine things are almost working but you are getting an empty row for the price? At least that's one error I was able to pick out.

Take a look at where you assign the price

$price=$row['$b'];

You are using the string literal '$b', when you most likely want to use the variable value of $b. Remember single quotes will skip the php parser, so variables come out how they are put in.

Try removing your single quotes, this will at least get you the value of that column.

$price=$row[$b];

Hope that's all it is 😉

cerd_web

Sorry I did my best to explain. Here is the code

<?php session_start();
?>
<head>
<title>Select Multiple</title>
<?php
include 'dbc.php';
mysql_select_db(customer_info); 

$sql="SELECT * FROM chemicals";
$result=mysql_query($sql); 
$options="";

while ($row=mysql_fetch_array($result)) {
$fluid=$row["chemical"];
$options.="<OPTION VALUE=\"$fluid\">".$fluid.'</option>';
}
?>
<script type="text/javascript">
function checkSelected(s)
{
maxSelections = 20;
selectedCount = 0;
lastSelected = '';
for (x=0;x<s.length;x++)
{
if (s[x].selected)
	{
	selectedCount++;
	lastSelected = s[x];	
	}

}
if (selectedCount > maxSelections) 
	{
	alert('Please select only '+maxSelections+' options');
	lastSelected.selected = false;
	s.focus();
	}
}
</script>


</head>

<body>
<div id="Main">
  <p>&nbsp;</p>
  <p>&nbsp;</p>
  <p>&nbsp;</p>
  <p>&nbsp;</p>
</div>
<div id="Intro">
  <div id="apDiv5">
    <form id="form1" name="frm1" method="post" action="chemical_lookup.php">
     <table width="391" border="1">
        <tr>
          <td width="28"><input type="checkbox" name="box[]" id="Nitrile" /></td>
          <td width="99">Nitrile</td>
          <td width="23"><input type="checkbox" name="box[]" id="Fluorosilicone" />
          <label for="Fluorosilicone"></label></td>
          <td width="102">Fluorosilicone</td>
        </tr>
        <tr>
          <td><input type="checkbox" name="box[]" id="HNBR" /></td>
          <td>HNBR</td>
          <td><input type="checkbox" name="box[]" id="Silicone" />
          <label for="Silicone"></label></td>
          <td>Silicone</td>
        </tr>
        <tr>
          <td><input type="checkbox" name="box[]" id="EPDM" /></td>
          <td>EPDM</td>
          <td><input type="checkbox" name="box[]" id="Polyacrylate" />
          <label for="Polyacrylate"></label></td>
          <td>Polyacrylate</td>
        </tr>
        <tr>
          <td><input type="checkbox" name="box[]" id="Viton" /></td>
          <td>Fluorocarbon</td>
          <td><input type="checkbox" name="box[]" id="SBR" />
          <label for="SBR"></label></td>
          <td>SBR</td>
        </tr>
        <tr>
          <td><input type="checkbox" name="box[]" id="Neoprene" /></td>
          <td>Neoprene</td>
          <td><input type="checkbox" name="box[]" id="NaturalRubber" /></td>
          <td>Natural Rubber</td>
        </tr>
        <tr>
          <td><input type="checkbox" name="box[]" id="Butyl" /></td>
          <td>Butyl</td>
          <td colspan="2">&nbsp;</td>
        </tr>
      </table>

  </div>
  <div id="apDiv8">
   <table width="100%" border="0">
    <form method="post" action="chemical_lookup.php">
        <tr>
          <th width="350" scope="row"><div align="left">
            <select name="test[]" size="7" multiple="multiple" id="items" onChange="checkSelected(this)" 
			<?php 
			if (isset($_SESSION['user_id'])) { 
			echo "style='width: 370px;'>";
			 } else { 
			 echo "style='color:#cccccc; font:Arial; width:370px; background-color:#E1E1E1;'" ;
			 echo "disabled='true'>"; 
			 } ?>
              </option>
                <?php echo $options?>
            </select>
          </div></th>
        </tr>
        <tr>
          <th width="350" scope="row"><div align="right">
            <span class="stdText_Bold">Quick Find:</span> 
            <input type="text" id="txt" value="Chemical Name?" onfocus="this.value==this.defaultValue?this.value='' :null" onkeyup="searchSel(this.value);" style="color:#000000; font:Arial; font-size:12px; background-color:#e1e1e1;" 
            <?php if (isset($_SESSION['user_id'])) { 
			echo ">";
			 } else { 
			 echo "disabled='true'>"; 
			 } ?>
            <input type=button onClick="location.href=chemical_lookup.php'" value='Reset' 
            <?php if (isset($_SESSION['user_id'])) { 
			echo ">";
			 } else { 
			 echo "disabled='true'>"; 
			 } ?>
            <input type="submit" value="Submit" name="submit" 
            <?php if (isset($_SESSION['user_id'])) { 
			echo ">";
			 } else { 
			 echo "disabled='true'>"; 
			 } ?>

      </div>
</table>
<script type="text/javascript">
<!--

var optList;
var optsValue = new Array();
var optsText = new Array();

//when the page loads get the original options values and text and store them in arrays
window.onload = function() {
optList = document.getElementsByTagName("option");
for(var i=0; i<optList.length; i++) {
     optsValue[i] = optList[i].value;
     optsText[i] = optList[i].text.toLowerCase();    

}    

}

function searchSel(txtSearch) {
//clear all the current options
document.getElementById("items").options.length = 0;
var count = 0;
for(var i=0; i < optsValue.length; i=i+1) {
    if(optsText[i].indexOf(txtSearch.toLowerCase()) == 0) {  //match found
         //add this option to the select list options
         var newOpt = new Option(optsValue[i],optsText[i],false,false);
         document.getElementById("items").options[count] = newOpt;
         count = count+1;
    }
    }
}
//-->
</script></div>
  <div id="apDiv11">
  <table class="tableone" summary="Nitrile O-Ring Compatability With Selected Chemicals">
      <thead>
        <tr>
          <th class="th1"><div align="left">Chemical </div></th> 
          <th class="th2"><div align="left">NBR</div></th> 
          <th class="th3"><div align="left">HNBR</div></th> 
          <th class="th4"><div align="left">EPDM</div></th> 
          <th class="th5"><div align="left">FKM</div></th> 
          <th class="th6"><div align="left">CR</div></th> 
          <th class="th7"><div align="left">IIR</div></th> 
          <th class="th8"><div align="left">VMQ</div></th> 
          <th class="th9"><div align="left">PVMQ</div></th>
          <th class="th10"><div align="left">ACM</div></th>
          <th class="th11"><div align="left">SBR</div></th>
          <th class="th12"><div align="left">NR</div></th> 
          <th class="th13"><div align="left">&nbsp;</div></th> 
        </tr>
      </thead>
<tbody>
<tr><td colspan="12">
   <div class="innerb"> 
	  <table class="tabletwo">
<?php if (!isset($_POST['test']) && empty($_POST['test'])) { ?>
      <tr class="dk">
        <td class="td1">&nbsp;</td>
        <td class="td2">&nbsp;</td>
        <td class="td3">&nbsp;</td>
        <td class="td4">&nbsp;</td>
        <td class="td5">&nbsp;</td>
        <td class="td6">&nbsp;</td>
        <td class="td7">&nbsp;</td>
        <td class="td8">&nbsp;</td>
        <td class="td9">&nbsp;</td>
        <td class="td10">&nbsp;</td>
        <td class="td11">&nbsp;</td>
        <td class="td12">&nbsp;</td>
      </tr>
      <?php } ?>
     <?php
        $color_array = array("x" => "stdText_Bold", "1" => "cell_green_bold", "2" => "cell_blue_bold", "3" => "cell_yellow_bold", "4" => "cell_red_bold" );
          if (isset($_POST['test']) && !empty($_POST['test']))
            {

        foreach ($_POST['test'] as $t){

        echo "<tr>" . "<td width='64'>" . $t."." . "</td>";
        foreach ($_POST['box'] as $b) {
        $sql="SELECT $b FROM chemicals WHERE chemical='$t' LIMIT 0, 20";
        $result=mysql_query($sql2);
        while ($row=mysql_fetch_array($result2)) {
        $price=$row['$b'];
        echo "<td width='58' align='center' class='std_text'>" . $price . "</td>" . "</tr>";

                     }
                                }
                                     }
        } 
?> 
</table>
 </div>
</td></tr>
</tbody>
</table>
</form></div>
</html>

cerd_web

the test page is located at

http:// www.moldedrubberseals. com/chemical_lookup.php
Remove spaces

This is test site so directory is protected
temp login=guest pass:1x2x3x

Res

Your form is disabled so we can't submit what you have to see the error in the display.

cerd_web

Ya sorry you'll also have to login to start a session 😐 which you can't do from that page if you hit one of the by material's page (nitrile_orings.php) it'll ask you to login

test1
open4me

then come back to the chemical_looup.php to how it doesn't work.

cerd_web

I was just trying to get the code working before I actually built the page sorry for the loops to actually help I know that can be frustrating.

Res

I still can't really figure out what's going on in there. Start adding echo's to everything, including your sql statements. You have an error in there when the form is submitted, try and figure out what's causing it by echoing all your values you out as they are built, this should give you a good indicator of what isn't working. Once you know specifically which portion isn't working it's much easier to fix.

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /hermes/bosweb/web164/b1644/ipw.columbia1/public_html/moldedrubberseals/chemical_lookup.php on line 445

Res

It looks like you're really close, but the second query that gets the resistance/value(?) of a rubber against a chemical appears to be failing. Take a look at that sql statement specifically, and the variables that go into it.

Maybe try a...

echo "<pre>";
print_r($_POST);
echo "</pre>";

to get an idea of what all your input variables are looking like.

cerd_web

443 $sql="SELECT $b FROM chemicals WHERE chemical='$t' LIMIT 0, 20";
444 $result=mysql_query($sql2);
445 while ($row=mysql_fetch_array($result2)) {
446 $price=$row[$b];

I notice the error on line 445. If I change the $b to an actually material like Nitrile and let it search it will actually find the results. So I am thinking that the problem is that I can't or I'm doing something wrong with trying to search using $b which is an array. Thus I tried to make a foreach loop, but that is also in the $t loop.

I'm far from good at PHP and a little confused as to how I can get multiple data points and display them in the same table.

But you see what I mean I hope, you have checkboxes, and a multi-select now the goal is to get all the data points that the user selects and put them in nice neat table.

cerd_web

Res;10969020 wrote:
It looks like you're really close, but the second query that gets the resistance/value(?) of a rubber against a chemical appears to be failing. Take a look at that sql statement specifically, and the variables that go into it.

Maybe try a...
echo "<pre>";
print_r($_POST);
echo "</pre>";
to get an idea of what all your input variables are looking like.

Good Idea,

Now I see that

 [box] => Array
        (
            [0] => on
            [1] => on
        )

which can't be right, so I need to set the value to them? I didn't make a value tag in the html could that be the issue?

Res

Correct. Sorry I didn't catch that in the html. Assign a value to them then look at your sql, it should start working at that point as it will look for the product rather than 'on'

sneakyimp

It's not clear to us what columns you have in your table called 'chemicals' but I can't help but wonder if the field 'chemical' is one of them.

You may find it helpful to use [man]print_r[/man] on the variables in your code in order to understand what's in them. For example, you use 'box[]' as the name for each and every one of your checkboxes but you neglect to give them a value. I'd be willing to bet your $_POST array will not be very helpful in accomplishing what you want.

cerd_web

Okay makes sense, seems like progress 🙂

mysql_fetch_array(): supplied argument is not a valid MySQL result resource line 447

Line 447 = $price=$row[$b];

I'm not sure what to try next.

sneakyimp

Each and every time you run a query, you need to make sure it did not return an error:

$result=mysql_query($sql2);

You didn't bother to check if the query was valid.

cerd_web

sneakyimp;10969026 wrote:
It's not clear to us what columns you have in your table called 'chemicals' but I can't help but wonder if the field 'chemical' is one of them.

You may find it helpful to use [man]print_r[/man] on the variables in your code in order to understand what's in them. For example, you use 'box[]' as the name for each and every one of your checkboxes but you neglect to give them a value. I'd be willing to bet your $_POST array will not be very helpful in accomplishing what you want.

Table name is chemicals, there is a "column" for "chemical" which is all the chemical in the select box, and then a column for each of the materials in the checkboxes, and each chemical has a number rating in each column which directly relates to the material vs chemical (compatibility).

cerd_web

Okay gotcha

if ($result=mysql_query($sql2)) {
}else{
echo mysql_error();
}

Get query was empty.

Res

So we've fixed what we think might be the bad source data. We're still getting that error, which means we're still not getting the query right (or something above it that effects it).

Easiest thing to do in this situation is echo out the sql and see what it looks like.

$sql="SELECT $b FROM chemicals WHERE chemical='$t' LIMIT 0, 20"; 
//add this before mysql_query
echo $sql."<br/>";

That will let us see what is running through mysql, and let us know what's up with $t, as I suspect that has issues.