pass checkboxed items from sql query to another page with a sql server query ---- checkboxed items could be 1 to many

[kat35601]

so this produce a page with a list of jobs

<?php


$connect =odbc_connect("removed");
if(!$connect) {
	exit("Connection Failed: " . $connect);
}

$sql="	
select jmpjobid,jmpPartID,jmpProductionQuantity from M1_KF.dbo.jobs where jmpProductionComplete!=-1

order by jmpPartID

 ";

$result =odbc_exec($connect,$sql);
if(!$result){
exit("Error in SQL");
}

echo "<table><tr>";
echo "<th>Job</th>";
 echo "<th>  </th>";
echo "<th>Part</th>";
 echo "<th>  </th>";

echo  "<th>Qty</th></th></tr>";

while (odbc_fetch_row($result)) {
	 
  $job=odbc_result($result,"jmpjobid");
  $Item=odbc_result($result,"jmpPartID");
  $Qty=odbc_result($result,"jmpProductionQuantity");
  $rQty=round($Qty, 0);
     
  echo"<tr>";  
  echo"<td><input type='checkbox' name='job[]' value=$job>$job</td>";
  echo"<td> &#x25c3;</td>";  
  echo "<td>$Item</td>";
  echo "<td> &#x25b9; </td>";
  echo "<td>$rQty</td></tr>";
 
}
  odbc_close($connect);

?>

the results look like see picture

the next pages guery looks like

select  jmmPartID,sum(jmmEstimatedQuantity) as QTY,Color,sides from

(SELECT          jmmPartID,jmmEstimatedQuantity,
			Color = (select top 1 immPartShortDescription from PartMaterials pm where pm.immMethodID=ja.jmaSourceMethodID and immPartShortDescription like'%PAPER%'),
			sides =(select count(*) from PartOperations po where po.imoMethodID=ja.jmapartid and po.imoWorkCenterID='HL')
From            JobMaterials Right Join      
				JobOperations on JobMaterials.jmmJobID = JobOperations.jmoJobID and  jmmJobAssemblyID = jmoJobAssemblyID and jmmRelatedJobOperationID = jmoJobOperationID right JOIN
                JobAssemblies ja On JobOperations.jmoJobID = ja.jmaJobID  and jmaJobAssemblyID = jmoJobAssemblyID left join 
               Jobs on jmpJobID = jmoJobID
                
                      
where jmmjobid in ('29316',               
'29317',               
'29318',               
'29319'               
)  and (left(jmmPartID, 3) = 'MDF' or left(jmmPartID, 3) = 'PB.')) as ctx
group by color,jmmPartID,sides

replacing the jmmjobid numbers with the results from the checkboxed items.

[Barand]

And your question is .... ?

[kat35601]

How do I pass the checkboxed items to a new page and query?

[Barand]

Your table needs to be inside <form> ... </form> tags and you need a submit button to send the form data.

In your processing page ...

if ($_SERVER['REQUEST_METHOD']=='POST') {
    $jobs = array_map('intval', $_POST['job'] ?? [0] );   // ensure they are all numbers
    
    $sql = "SELECT ... WHERE jmmjobid IN (" . join(',', $jobs) . ")";
}

 

[kat35601]

How am I doing so far?????

<?php

echo"</form>";
 echo" <form action='laminator.php' method='post'>";
  
$connect =odbc_connect("removed");
if(!$connect) {
	exit("Connection Failed: " . $connect);
}

$sql="	
select jmpjobid,jmpPartID,jmpProductionQuantity from M1_KF.dbo.jobs where jmpProductionComplete!=-1

order by jmpPartID

 ";

$result =odbc_exec($connect,$sql);
if(!$result){
exit("Error in SQL");
}

echo "<table><tr>";
echo "<th>Job</th>";
 echo "<th>  </th>";
echo "<th>Part</th>";
 echo "<th>  </th>";

echo  "<th>Qty</th></th></tr>";

while (odbc_fetch_row($result)) {
	 
  $job=odbc_result($result,"jmpjobid");
  $Item=odbc_result($result,"jmpPartID");
  $Qty=odbc_result($result,"jmpProductionQuantity");

  $rQty=round($Qty, 0);
  

   
  echo"<tr>";  
  echo"<td><input type='checkbox' name='job[]' value=$job>$job</td>";
  echo"<td> &#x25c3;</td>";  
  echo "<td>$Item</td>";
  echo "<td> &#x25b9; </td>";
  echo "<td>$rQty</td></tr>";
 
}
  
  
  
    echo"<form>";
  echo"<input type='submit' value='Submit the form'/>";



odbc_close($connect);

?>
</body>
</html>

 

[Barand]

1 hour ago, kat35601 said:

How am I doing so far?????

Not too well. You have a bit of learning to do regarding html markup for forms.

[kat35601]

Laugh out loud, well I don't do a lot of HTML forms. that's why I ask for help.

[Barand]

As with all pairs of html tags (<th>..</th>, <td>..</td> etc) the closing tag contains the "/". Yet you start the page with a closing form tag. Your next line is the opening form tag but you have placed it as far as could from the rest of the form, which doen't make for great readability. You then finish the form with another opening form tag followed by the submit button. Also there is no closing </table> tag.

You want something like

<form action='laminator.php' method='post'>
    <table>
        <tr> .. headings .. </tr>
      
        while( ... ) {
           <tr> .. table content rows .. </tr>
        }
  </table>
  <input type='submit'>
</form>

 

[kat35601]

Headed in the right direction but my JOB numbers will not always be a number sometimes the will have a letter like 29216C .

 

 $jobs = array_map('intval', $_POST['job'] ?? [0] );   // ensure they are all numbers

 

[Barand]

Always 5 digits +1  optional letter?

[kat35601]

no I am finding

 

A411                
C715

002C

1000C                                         

[mac_gyver]

for an arbitrary string, you should use a prepared query. what's the actual database server type, in case it has an easy to use function that would allow a prepared query with a single input parameter instead of a separate one for each parameter?

this is a search function, the form should be a get method form, it should be 'sticky' and remember any previous choices, and it should be on the same page as the form processing code.

[Barand]

I agree a prepared query is preferred but they're a PITA with a large list of IN() values. I'm also not sure if you can do it with Sql Server without using stored procedures.

Perhaps

$jobs = array_filter($_POST['job'], fn($v) => ctype_alnum($v) && strlen($v) < 7 );

 

[kicken]

1 hour ago, Barand said:

I agree a prepared query is preferred but they're a PITA with a large list of IN() values. I'm also not sure if you can do it with Sql Server without using stored procedures.

Using SQL Server with PDO_SQLSRV I generally just do something like this for an IN list.

$placeholders = implode(', ', array_fill(0, count($_POST['jobs']), '?'));
$sql = 'select ... from ... where Id in ('.$placeholders.')';
$stmt=$pdo->prepare($sql);
$stmt->execute($_POST['jobs']);

Pretty simple.  I'd assume something similar would work with odbc_*.

 

[kat35601]

I kind of have it working with $_SESSION ARRAY. My problem if I can explain it is  I have page1 that contains my list of jobs with checkboxes. When I select boxes and submit I get an error on page2 that says "Warning: Undefined array key" but then if I use the back button and select checkboxes then the page2 will show the correct results but for the checkboxes selected the first time. So how do I fix that?

Page1

<?php

session_start();
echo"<form method='post' action='laminator.php'>";

echo"<table>";
        
$connect =odbc_connect("removed");
if(!$connect) {
	exit("Connection Failed: " . $connect);
}

$sql="	
select jmpjobid,jmpPartID,jmpProductionQuantity from M1_KF.dbo.jobs where jmpProductionComplete!=-1  

order by jmpPartID

 ";

$result =odbc_exec($connect,$sql);
if(!$result){
exit("Error in SQL");
}

echo "<table><tr>";
echo "<th>Job</th>";
echo "<th>  </th>";
echo "<th>Part</th>";
echo "<th>  </th>";

echo  "<th>Qty</th></th></tr>";

while (odbc_fetch_array($result)) {
	 
  $job=odbc_result($result,"jmpjobid");
  $Item=odbc_result($result,"jmpPartID");
  $Qty=odbc_result($result,"jmpProductionQuantity");

  $rQty=round($Qty, 0);
  
  echo"<td><input type='checkbox' name='job[]' value=$job>$job</td>";
  echo"<td> &#x25c3;</td>";  
  echo "<td>$Item</td>";
  echo "<td> &#x25b9; </td>";
  echo "<td>$rQty</td></tr>";

}
  
 echo"</table>";
 echo"<input type='submit' value='Submit' name='submit'>";
 echo"</form>";

if(isset($_POST['submit'])){

$_SESSION['myarray'] = $_POST['job'];

}


//print_r($_SESSION['myarray']);



odbc_close($connect);

?>

Page2

<?php
session_start();

$array=$_SESSION['myarray'];


$job= "('" . implode ( "', '", $array ) . "')";
echo $job;

$connect =odbc_connect("removed");
if(!$connect) {
	exit("Connection Failed: " . $connect);
}

$sql="	select * from 
(select  jmmPartID,sum(jmmEstimatedQuantity) as QTY,Color,sides from

(SELECT          jmmPartID,jmmEstimatedQuantity,
			Color = (select top 1 immPartShortDescription from PartMaterials pm where pm.immMethodID=ja.jmaSourceMethodID and immPartShortDescription like'%PAPER%'),
			sides =(select count(*) from PartOperations po where po.imoMethodID=ja.jmapartid and po.imoWorkCenterID='HL')
From            JobMaterials Right Join      
				JobOperations on JobMaterials.jmmJobID = JobOperations.jmoJobID and  jmmJobAssemblyID = jmoJobAssemblyID and jmmRelatedJobOperationID = jmoJobOperationID right JOIN
                JobAssemblies ja On JobOperations.jmoJobID = ja.jmaJobID  and jmaJobAssemblyID = jmoJobAssemblyID left join 
               Jobs on jmpJobID = jmoJobID
                
                      
where jmmjobid in $job  and (left(jmmPartID, 3) = 'MDF' or left(jmmPartID, 3) = 'PB.')) as ctx
group by color,jmmPartID,sides


) as thv

 ";

$result =odbc_exec($connect,$sql);
if(!$result){
exit("Error in SQL");
}

echo "<table><tr>";
echo "<th>Part</th>";
 echo "<th>  </th>";
echo "<th>QTY</th>";
 echo "<th>  </th>";
  echo "<th>Color</th>";
  

echo  "<th>Sides</th></th></tr>";

while (odbc_fetch_row($result)) {
	 
  $item=odbc_result($result,"jmmPartID");
  $qty=odbc_result($result,"QTY");
  $color=odbc_result($result,"Color");
  $sides=odbc_result($result,"sides");

  $rQty=round($qty, 0);
  

   
  echo"<tr>";  
  echo"<td>$item</td>";
  echo"<td> &#x25c3;</td>";  
  echo "<td>$rQty</td>";
  echo "<td> &#x25b9; </td>";
  echo "<td>$color</td>";
  echo "<td>$sides</td></tr>";
 
}

odbc_close($connect);
?>

 

[ginerjm]

Do you know that (unlike empty text boxes) un-checked checkboxes do not get returned in the POST array?  That could be the problem

[Barand]

1 ) This line

if(isset($_POST['submit'])){

belongs at the top of the second page so you only try to process the post data if any was posted. It would make more sense to check if $_POST['job'] is set.

2 ) Get rid of the SESSION code - you don't need it

[mac_gyver]

13 minutes ago, kat35601 said:

So how do I fix that?

put all this code on one page. you are creating more work for yourself creating multiple pages, when one page is all you need.

the following is an outline of the code that would do this for you -

<?php

// initialization

$connect =odbc_connect("removed");
if(!$connect) {
	exit("Connection Failed: " . $connect);
}
// note: don't tell hackers or other visitors to your site that a connection error occurred
// also, $connect is a false value in this case and outputting it won't show anything
// when learning, developing, and debugging, you should display database statement errors
// when on a live/public server, you should log database statement errors
// regardless of which database server type you are using, switching to the more modern PDO extension will allow you to easily do this, by using exceptions for errors and in most cases simply let php catch and handle the exception for you


// post method form processing - none


// get method business logic - get/produce data needed to display the page

// get job data, for producing search checkboxes
$sql="	
select jmpjobid,jmpPartID,jmpProductionQuantity
 from M1_KF.dbo.jobs
 where jmpProductionComplete!=-1
 order by jmpPartID
 ";
$job_result = odbc_exec($connect,$sql);
if(!$job_result){
	exit("Error in SQL");
}
// same comments as above about displaying/logging database statement errors

// i recommend that you pre-fetch the data from any select query into an appropriately named php variable,
// then test/use that variable in the html document
// this moves the database specific code out of the html document

// apparently the main data retrieval query 'requires' a job id search input?
// if so, it needs to be conditionally executed based on a non-empty search
if(isset($_GET['job']))
{
	// at least one checkbox is checked - assuming no programming mistake or nefarious activity
	$ph = implode(',',array_fill(0,count($_GET['job']),'?'));
	$sql = "SELECT ... WHERE jmmjobid IN ($ph)"; // put the full query here...
	$data_result = odbc_prepare($connect, $sql);
	odbc_execute($data_result, $_GET['job']);
	// you can use odbc_fetch_row/odbc_result to loop/fetch the result from this query
	// wondering if odbc_fetch_array is present for the database server type
	// also, switching the more modern PDO extension will make fetching data easier
}


// html document
?>


<?php
// output the search form
if(empty($job_result))
{
	echo "<p>No Job data to search amongst</p>";
}
else
{
	?>
	<form>
	<table>
	<tr>
	<th>Job</th>
	<th> </th>
	<th>Part</th>
	<th> </th>
	<th>Qty</th>
	</tr>
	<?php
	while(odbc_fetch_row($job_result))
	{
		$job=odbc_result($job_result,"jmpjobid");
		$Item=odbc_result($job_result,"jmpPartID");
		$Qty=odbc_result($job_result,"jmpProductionQuantity");
		$rQty=round($Qty, 0);
		// pre-check any existing choices
		$chk = isset($_GET['job']) && in_array($job,$_GET['job']) ? 'checked' : '';
		echo"<tr>";  
		echo"<td><input type='checkbox' name='job[]' value='$job' $chk>$job</td>";
		echo"<td> &#x25c3;</td>";  
		echo "<td>$Item</td>";
		echo "<td> &#x25b9; </td>";
		echo "<td>$rQty</td></tr>";
	}
	?>
	</table>
	<input type='submit' value='Submit the form'>
	<form>
	<?php
}
?>

<?php
// output the search result
if(!isset($_GET['job']))
{
	echo "<p>Please select Job Id(s)</p>";
}
else
{
	if(empty($data_result))
	{
		echo "<p>Search did not match any data</p>";
	}
	else
	{
	?>

	<?php
	while(odbc_fetch_row($data_result))
	{

	}
	?>

	<?php
	}
}
?>

 

 

[kat35601]

I really would like to keep the two pages separate because the second page is a phpspreadsheet page and I will have 14 of them each for a different machine.

I can make it work by opening both pages in the browser then go to the first page and select the checkboxes and submit but not call the second page. Then I can go to the second page and refresh

it and it works. But if I call the second page it errors"Warning: Undefined array key "myarray" in /var/www/html/production/laminator.php on line 54"

 

[Barand]

First page

Run query
create form from results

when form is submitted, second page loads

Second page

if (isset($_POST['job'])) {
    $job = "'" . join("','", $_POST['job']) . "'";
}
else reload first page

Run query WHERE ... IN ($job)

Show output.

No SESSION needed!