Solved

Help needed | Mysql listbox to filter results

Posted on 2014-04-30
16
227 Views
Last Modified: 2014-05-06
I'm trying to m ake the listbox vaules filter the (WHERE) option within the sql query.  

<html>
		<h4><i class="fa fa-users"></i> Please select your Sub-contractor</h4>
			<select class="form-control input-lg" name="score_subcontractor_fk">
				<?php include 'get_subcontractor.php'; ?>
			</select>
</html>


<?php
$con=mysqli_connect("10.10.10.1","web212-navicat","blank","blank");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

$result = mysqli_query($con,"
SELECT `score_pk` , `score_date` , `score_timelydelivery` , `score_GoodAttitudeDisplayed` , `score_ProgrammePerformance` , `score_FinishedProduct` , `score_TidyWorkingArea` , `score_MaterialsProtected` , `score_RiskAssessments` , `score_GoodHousekeeping` , `score_SigningIn` , `score_PPE` , `score_FuelStorageUse` , `score_WasteManagement` , `score_PollutionPrevention` , `score_contract_fk` , `score_subcontractor_fk` , `score_presenceofsupervisor` , `score_comments` , b.contract_pk, b.contract_name, c.subcontractor_name
FROM `scores` 
INNER JOIN contracts b ON score_contract_fk = contract_pk
INNER JOIN subcontractor c ON score_subcontractor_fk = subcontractor_pk
WHERE score_subcontractor_fk=7");

echo "<div class='table-responsive visible-lg'><table class='table table-striped'>
<tr>
<th>Date</th>
<th>Contract<br/>Name</th>
<th>Subcontractors<br/>Name</th>
<th>timely<br/>delivery</th>
<th>Good<br/>Attitude<br/>Displayed</th>
<th>Programme<br/>Performance</th>
<th>Finished<br/>Product</th>
<th>Tidy<br/>Working<br/>Area</th>
<th>Materials<br/>Protected</th>
<th>Risk<br/>Assessments</th>
<th>Good<br/>Housekeeping</th>
<th>Signing<br/>In</th>
<th>PPE</th>
<th>Fuel<br/>Storage<br/>Use</th>
<th>Waste<br/>Management</th>
<th>Pollution<br/>Prevention</th>
<th>presence<br/>of<br/>supervisor</th>
<th>comments</th>
</tr>";

while($row = mysqli_fetch_array($result))
  {
  echo "<tr>";
echo "<td>" . $row['score_date'] . "</td>";
echo "<td>" . $row['contract_name'] . "</td>";
echo "<td>" . $row['subcontractor_name'] . "</td>";
echo "<td>" . $row['score_timelydelivery'] . "</td>";
echo "<td>" . $row['score_GoodAttitudeDisplayed'] . "</td>";
echo "<td>" . $row['score_ProgrammePerformance'] . "</td>";
echo "<td>" . $row['score_FinishedProduct'] . "</td>";
echo "<td>" . $row['score_TidyWorkingArea'] . "</td>";
echo "<td>" . $row['score_MaterialsProtected'] . "</td>";
echo "<td>" . $row['score_RiskAssessments'] . "</td>";
echo "<td>" . $row['score_GoodHousekeeping'] . "</td>";
echo "<td>" . $row['score_SigningIn'] . "</td>";
echo "<td>" . $row['score_PPE'] . "</td>";
echo "<td>" . $row['score_FuelStorageUse'] . "</td>";
echo "<td>" . $row['score_WasteManagement'] . "</td>";
echo "<td>" . $row['score_PollutionPrevention'] . "</td>";
echo "<td>" . $row['score_presenceofsupervisor'] . "</td>";
echo "<td>" . $row['score_comments'] . "</td>";
  echo "</tr>";
  }
echo "</table></div>";

mysqli_close($con);
?> 

Open in new window

0
Comment
Question by:Geektank
  • 9
  • 7
16 Comments
 
LVL 35

Expert Comment

by:Kimputer
ID: 40031727
Any problems ? If so, can you show the rendered source file ?
0
 

Author Comment

by:Geektank
ID: 40031792
do you mean 'get_subcontractor.php'
0
 

Author Comment

by:Geektank
ID: 40032156
It needs to work like the following

http://labs.mysql.com/
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 35

Expert Comment

by:Kimputer
ID: 40032321
Currently, I cannot see how it works, if it works, and if it doesn't, what needs to be fixed. You should load the page, then save as html and attach here.
0
 

Author Comment

by:Geektank
ID: 40032361
Hi thanks for getting back to me, well the basic listbox pulls the vaules from the database.

and the code below shows a query run on the database with the WHEN clause, however I don't no how to make the listbox 'become the query vaule'

WHERE score_subcontractor_fk=7");

Open in new window

0
 
LVL 35

Expert Comment

by:Kimputer
ID: 40032439
So you want a dropdown list with what ? 1 through 7 ? 1 through 100 ?
0
 

Author Comment

by:Geektank
ID: 40032447
the dropdown list calls data from another table the number refers to it's foreign key / Primary Key
0
 
LVL 35

Expert Comment

by:Kimputer
ID: 40032455
Then I need that query first. Or if it's POSTed, which string/variable holds it.
0
 

Author Comment

by:Geektank
ID: 40032461
Sorry the answer to that was yes but i already have this working... It's the ability to link it in with the query's clause.
0
 

Author Comment

by:Geektank
ID: 40032517
ok the get_subcontractor.php is below.

<?php
$con=mysqli_connect("10.10.10.1","web212-navicat","blank","blank");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

$result = mysqli_query($con,"SELECT * FROM subcontractor");



while($row = mysqli_fetch_array($result))
  {
  echo '<option value="';
  echo $row['subcontractor_pk'];
  echo '">';
  echo $row['subcontractor_name'];
  echo '</option>';

  }
  
mysqli_close($con);

?>

Open in new window

0
 
LVL 35

Expert Comment

by:Kimputer
ID: 40033439
Then it should be something like this:

<html>

<script type="text/javascript" src="http://code.jquery.com/jquery-1.11.0.min.js"></script>
	<h4><i class="fa fa-users"></i> Please select your Sub-contractor</h4>
	<select class="form-control input-lg" name="score_subcontractor_fk" id="score_subcontractor_fk">
		<?php include 'get_subcontractor.php'; ?>
	</select>
	
	<script type="text/javascript">
		$('#score_subcontractor_fk').click(function(){
				var value = $( "#score_subcontractor_fk" ).val();
				var form = $('<form action="index.php" method="post">' +
		'<input type="hidden" name="id" value="' +  value + '" />' +
		'</form>');
		$('body').append(form);
		$(form).submit();
			  });

</script>


</html>


<?php


if( $_SERVER['REQUEST_METHOD'] == 'POST') {

	if(isset($_POST['id'])){
			$id = $_POST['id'];
			$con=mysqli_connect("10.10.10.1","web212-navicat","blank","blank");
			// Check connection
			if (mysqli_connect_errno())
			  {
			  echo "Failed to connect to MySQL: " . mysqli_connect_error();
			  }

			$result = mysqli_query($con,"
			SELECT `score_pk` , `score_date` , `score_timelydelivery` , `score_GoodAttitudeDisplayed` , `score_ProgrammePerformance` , `score_FinishedProduct` , `score_TidyWorkingArea` , `score_MaterialsProtected` , `score_RiskAssessments` , `score_GoodHousekeeping` , `score_SigningIn` , `score_PPE` , `score_FuelStorageUse` , `score_WasteManagement` , `score_PollutionPrevention` , `score_contract_fk` , `score_subcontractor_fk` , `score_presenceofsupervisor` , `score_comments` , b.contract_pk, b.contract_name, c.subcontractor_name
			FROM `scores` 
			INNER JOIN contracts b ON score_contract_fk = contract_pk
			INNER JOIN subcontractor c ON score_subcontractor_fk = subcontractor_pk
			WHERE score_subcontractor_fk=$id");

			echo "<div class='table-responsive visible-lg'><table class='table table-striped'>
			<tr>
			<th>Date</th>
			<th>Contract<br/>Name</th>
			<th>Subcontractors<br/>Name</th>
			<th>timely<br/>delivery</th>
			<th>Good<br/>Attitude<br/>Displayed</th>
			<th>Programme<br/>Performance</th>
			<th>Finished<br/>Product</th>
			<th>Tidy<br/>Working<br/>Area</th>
			<th>Materials<br/>Protected</th>
			<th>Risk<br/>Assessments</th>
			<th>Good<br/>Housekeeping</th>
			<th>Signing<br/>In</th>
			<th>PPE</th>
			<th>Fuel<br/>Storage<br/>Use</th>
			<th>Waste<br/>Management</th>
			<th>Pollution<br/>Prevention</th>
			<th>presence<br/>of<br/>supervisor</th>
			<th>comments</th>
			</tr>";

			while($row = mysqli_fetch_array($result))
			  {
			  echo "<tr>";
			echo "<td>" . $row['score_date'] . "</td>";
			echo "<td>" . $row['contract_name'] . "</td>";
			echo "<td>" . $row['subcontractor_name'] . "</td>";
			echo "<td>" . $row['score_timelydelivery'] . "</td>";
			echo "<td>" . $row['score_GoodAttitudeDisplayed'] . "</td>";
			echo "<td>" . $row['score_ProgrammePerformance'] . "</td>";
			echo "<td>" . $row['score_FinishedProduct'] . "</td>";
			echo "<td>" . $row['score_TidyWorkingArea'] . "</td>";
			echo "<td>" . $row['score_MaterialsProtected'] . "</td>";
			echo "<td>" . $row['score_RiskAssessments'] . "</td>";
			echo "<td>" . $row['score_GoodHousekeeping'] . "</td>";
			echo "<td>" . $row['score_SigningIn'] . "</td>";
			echo "<td>" . $row['score_PPE'] . "</td>";
			echo "<td>" . $row['score_FuelStorageUse'] . "</td>";
			echo "<td>" . $row['score_WasteManagement'] . "</td>";
			echo "<td>" . $row['score_PollutionPrevention'] . "</td>";
			echo "<td>" . $row['score_presenceofsupervisor'] . "</td>";
			echo "<td>" . $row['score_comments'] . "</td>";
			  echo "</tr>";
			  }
			echo "</table></div>";

			mysqli_close($con);
	}
}


?> 

Open in new window


Please note the mentioning of index.php. That's because I assume that's what this code is for. If not, put in the filename where you will use this code.
0
 

Author Comment

by:Geektank
ID: 40044022
Yeah this is more like what i'm after, howerever the 'on click' function only pulls the first record within the list... Should this have a submit button and if so how should i begin to code it in.

Thanks in advance your on the right lines. :)
0
 
LVL 35

Expert Comment

by:Kimputer
ID: 40044047
Can you try this, click on the arrow next to the dropdown list. Without letting go of the mousebutton, continue to select an item more down the list, only now will you release the button. This is how you select another item, other than the first record.

If you still want the submit button, it's also okay of course, I will code it after you reply.
0
 

Author Comment

by:Geektank
ID: 40044067
Yeah if you wouldn't mind the submit button would be better for uses.
0
 
LVL 35

Accepted Solution

by:
Kimputer earned 500 total points
ID: 40044092
New code (click button to submit):

<html>

<script type="text/javascript" src="http://code.jquery.com/jquery-1.11.0.min.js"></script>
	<h4><i class="fa fa-users"></i> Please select your Sub-contractor</h4>
	<select class="form-control input-lg" name="score_subcontractor_fk" id="score_subcontractor_fk">
		<?php include 'get_subcontractor.php'; ?>
	</select>
	
	<button id="btn1">submit</button>
	
	<script type="text/javascript">
		$('#btn1').click(function(){
				var value = $( "#score_subcontractor_fk" ).val();
				var form = $('<form action="index.php" method="post">' +
		'<input type="hidden" name="id" value="' +  value + '" />' +
		'</form>');
		$('body').append(form);
		$(form).submit();
			  });

</script>


</html>


<?php


if( $_SERVER['REQUEST_METHOD'] == 'POST') {

	if(isset($_POST['id'])){
			$id = $_POST['id'];
			$con=mysqli_connect("10.10.10.1","web212-navicat","blank","blank");
			// Check connection
			if (mysqli_connect_errno())
			  {
			  echo "Failed to connect to MySQL: " . mysqli_connect_error();
			  }

			$result = mysqli_query($con,"
			SELECT `score_pk` , `score_date` , `score_timelydelivery` , `score_GoodAttitudeDisplayed` , `score_ProgrammePerformance` , `score_FinishedProduct` , `score_TidyWorkingArea` , `score_MaterialsProtected` , `score_RiskAssessments` , `score_GoodHousekeeping` , `score_SigningIn` , `score_PPE` , `score_FuelStorageUse` , `score_WasteManagement` , `score_PollutionPrevention` , `score_contract_fk` , `score_subcontractor_fk` , `score_presenceofsupervisor` , `score_comments` , b.contract_pk, b.contract_name, c.subcontractor_name
			FROM `scores` 
			INNER JOIN contracts b ON score_contract_fk = contract_pk
			INNER JOIN subcontractor c ON score_subcontractor_fk = subcontractor_pk
			WHERE score_subcontractor_fk=$id");

			echo "<div class='table-responsive visible-lg'><table class='table table-striped'>
			<tr>
			<th>Date</th>
			<th>Contract<br/>Name</th>
			<th>Subcontractors<br/>Name</th>
			<th>timely<br/>delivery</th>
			<th>Good<br/>Attitude<br/>Displayed</th>
			<th>Programme<br/>Performance</th>
			<th>Finished<br/>Product</th>
			<th>Tidy<br/>Working<br/>Area</th>
			<th>Materials<br/>Protected</th>
			<th>Risk<br/>Assessments</th>
			<th>Good<br/>Housekeeping</th>
			<th>Signing<br/>In</th>
			<th>PPE</th>
			<th>Fuel<br/>Storage<br/>Use</th>
			<th>Waste<br/>Management</th>
			<th>Pollution<br/>Prevention</th>
			<th>presence<br/>of<br/>supervisor</th>
			<th>comments</th>
			</tr>";

			while($row = mysqli_fetch_array($result))
			  {
			  echo "<tr>";
			echo "<td>" . $row['score_date'] . "</td>";
			echo "<td>" . $row['contract_name'] . "</td>";
			echo "<td>" . $row['subcontractor_name'] . "</td>";
			echo "<td>" . $row['score_timelydelivery'] . "</td>";
			echo "<td>" . $row['score_GoodAttitudeDisplayed'] . "</td>";
			echo "<td>" . $row['score_ProgrammePerformance'] . "</td>";
			echo "<td>" . $row['score_FinishedProduct'] . "</td>";
			echo "<td>" . $row['score_TidyWorkingArea'] . "</td>";
			echo "<td>" . $row['score_MaterialsProtected'] . "</td>";
			echo "<td>" . $row['score_RiskAssessments'] . "</td>";
			echo "<td>" . $row['score_GoodHousekeeping'] . "</td>";
			echo "<td>" . $row['score_SigningIn'] . "</td>";
			echo "<td>" . $row['score_PPE'] . "</td>";
			echo "<td>" . $row['score_FuelStorageUse'] . "</td>";
			echo "<td>" . $row['score_WasteManagement'] . "</td>";
			echo "<td>" . $row['score_PollutionPrevention'] . "</td>";
			echo "<td>" . $row['score_presenceofsupervisor'] . "</td>";
			echo "<td>" . $row['score_comments'] . "</td>";
			  echo "</tr>";
			  }
			echo "</table></div>";

			mysqli_close($con);
	}
}


?> 

Open in new window

0
 

Author Closing Comment

by:Geektank
ID: 40044129
Works perfectly , and can be addapted to suite.  Thank you.
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Help with SQL Server Polygon 2 32
SQL Syntax 5 34
what are the unique tables in SQL master database 5 58
Query group by data in SQL Server - cursor? 3 29
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question