Checking the value of a dropdowns in sql php

sarojthapa60 · September 12, 2016

require_once('include/database.php');

?>
<!DOCTYPE html>
<html>
<head>
</head>
<body>

<section>
<table id="myTable" class="tablesorter">
<thead>
<tr>
<th>Capacity</th>
<th>Ownership</th>
<th>Action</th>
</tr>
</thead>

<tbody>
<?php
$stmt = $conn->prepare("SELECT * FROM MATRIX ORDER BY OBJECTID ASC");
$stmt ->execute();
$result = $stmt->fetchAll();
foreach($result as $row) {
?>

<tr>
<td><?=$row['Ownership'];?></td>
<td><?=$row['Capacity'];?></td>
<td><a href="edit.php?id=<?=$row['OBJECTID'];?>">Edit </a> </td>

</tr>
</tbody>

<?php
}
?>

cyberRobot · September 12, 2016

I'm not sure what you are asking. Are you looking to incorporate a dropdown into the code above? Is there a dropdown somewhere else on your website that connects to the above code?

maxxd · September 12, 2016

There's neither a question nor a drop-down in your post...

ginerjm · September 12, 2016

Besides the atrocious arrangement of your html and php code intermixed which is to be avoided, your code is broken. Have you looked at how your output page appears in your browser yet? How many </tbody> tags do you see?

There are so many cleaner and easier ways to do what you are doing but you insist on mixing html and php code together. Please help yourself by doing some reading and change things around.

An example of cleaner php code:

In place of this:

$stmt ->execute();
$result = $stmt->fetchAll();
foreach($result as $row) {
?>

<tr>
<td><?=$row['Ownership'];?></td>
<td><?=$row['Capacity'];?></td>
<td><a href="edit.php?id=<?=$row['OBJECTID'];?>">Edit </a> </td>

</tr>
</tbody>

<?php
}

Try this:

$stmt ->execute();
while($row=$stmt->fetch())
{
echo "<tr>";
echo "<td>{$row['Ownership']}</td>";
echo "<td>{$row['Capacity']}</td>";
echo "<td><a href='edit.php?id='".$row['OBJECTID']."'>Edit</a></td>";
echo "</tr>";
echo "</tbody>";
}

Of course that ending tbody tag does NOT belong there but that's your problem.

Note that you s/b checking the result of the execute statement to be SURE that your query actually ran.

sarojthapa60 · September 12, 2016

There's neither a question nor a drop-down in your post...

I have dropdowns in my "Edit.php" file. When the edit option in index.php is clicked, it brings to the form. Some of the input for the forms have dropdowns queried from a table called matrix_dropdowns. What I am trying to achieve is: "Capacity" field has dropdowns namely GREEN, AMBER, RED, and BLACK. If an initial value is GREEN or the user chooses GREEN option, I want the background to be green, AMBER ->yellow color. The codes for edit.php is shown below:

<?php
require_once('include/database.php');
 
if (isset($_POST['btn_submit'])) {
    if (isset($_POST['txt_id'])) {
        $id = $_POST['txt_id'];
    } else {
        $id = '';
    }
 
    if (isset($_POST['txt_capacity'])) { ////It has dropdowns, namely GREEN, AMBER, RED, BLACK. 
        $capacity = $_POST['txt_capacity'];
    } else {
        $capacity = 0;
    }
 
    if (isset($_POST['txt_ownership'])) { 
        $ownership = $_POST['txt_ownership'];
    } else {
        $ownership = '';
    }
    
    try {
        $stmt = $conn->prepare("UPDATE MATRIX SET Capacity=:capacity,
        Ownership=:ownership
        WHERE OBJECTID =:id");
 
        $stmt->execute(array(':capacity' => $capacity, ':ownership'=>$ownership, ':id' => $id));
 
        if ($stmt) {
            header('Location:index.php');
            exit();
        }
    } catch (PDOException $e) {
        echo $e->getMessage();
    }
}
 
$object_id='';
$capacity = '';
$ownership = '';
 
if (isset($_GET['id'])) {
    $id = $_GET['id'];
    $stmt = $conn->prepare("SELECT * FROM MATRIX WHERE OBJECTID=:id");
    $stmt->execute(array(':id' => $id));
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    $object_id = $row['OBJECTID'];
    $capacity = $row['Capacity'];
    $ownership = $row['Ownership'];
}
?>
 
<!DOCTYPE html>
<html>
<head>
<title>Edit the Data</title>
</head>
<body>
<h2>Edit the records</h2>
 
<form action="" method="post">
<table border="3px" cellpadding="5px"> 
 
<tr>
<td>Capacity</td>
<td><label>
<select name="txt_capacity" class="textfields" id="capacity">
<option id="0">Select One</option>
<?php
require_once('include/database.php');
$stmt = $conn->prepare("SELECT * FROM MATRIX_DROPDOWNS");
$stmt ->execute();
$result = $stmt->fetchAll();
foreach($result as $row){
    ?>
    <option id="<?=$row['OBJECTID'];?>"><?=$row['colors']?></option>
    <?php
}
?>
</select>
</label>
</td>
</tr>
 
<tr>
<td>Ownership</td>
<td><label>
<select name="txt_ownership" class="textfields" id="ownership">
<option id="0">Select One</option>
<?php
require_once('include/database.php');
$stmt = $conn->prepare("SELECT * FROM MATRIX_DROPDOWNS");
$stmt ->execute();
$result = $stmt->fetchAll();
foreach($result as $row){
    ?>
    <option id="<?=$row['OBJECTID'];?>"><?=$row['colors']?></option>
    <?php
}
?>
</select>
</label>
</td>
</tr>
 
<tr>
<td><label>
<input type="hidden" name="txt_id" value="<?= $object_id; ?>">
</label>
</td>
<td><label><input type="submit" name="btn_submit" value="Submit">
</label>
</td>
</tr>
 
</table>
</form>

Edited September 12, 2016 by cyberRobot
added [code][/code] tags

Jacques1 · September 12, 2016

Dude. Stop posting your entire application in every single reply to every single person. We can all see it.

sarojthapa60 · September 12, 2016

Dude. Stop posting your entire application in every single reply to every single person. We can all see it.

Sorry about that. didn't know everyone could see that.

benanamen · September 12, 2016

Depending on the name of a button to be submitted for your script to work will completely fail in certain circumstances. You need to use

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

cyberRobot · September 12, 2016

Let's deal with the GET variable from index.php first. You are utilizing the variable here:

$object_id='';
$capacity = '';
$ownership = '';
 
if (isset($_GET['id'])) {
    $id = $_GET['id'];
    $stmt = $conn->prepare("SELECT * FROM MATRIX WHERE OBJECTID=:id");
    $stmt->execute(array(':id' => $id));
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    $object_id = $row['OBJECTID'];
    $capacity = $row['Capacity'];
    $ownership = $row['Ownership'];
}

And it sounds like $capacity is the variable that indicates which color was selected, correct? If so, are you able to incorporate $capacity into the following query?

$stmt = $conn->prepare("SELECT * FROM MATRIX_DROPDOWNS");

Does $capacity match the values stored in your "colors" column in the MATRIX_DROPDOWNS table?

cyberRobot · September 12, 2016

If an initial value is GREEN or the user chooses GREEN option, I want the background to be green, AMBER ->yellow color.

I just re-read Reply #5.

Are you looking to limit the number of options displayed in the dropdown based on the color selected in index.php? Or are you just trying to change the background color of the dropdown?

Edited September 12, 2016 by cyberRobot

maxxd · September 12, 2016

Possibly more importantly, there is a lot of redundant and inefficient code in what you've posted. First and foremost, don't simply 'SELECT *' from a table when you only need three columns. Specify the columns you need. Also, you're running the same query twice. Don't. Run the query once to get the values, store the values in an array, and loop through the array twice. On top of that, you're including the database.php file three separate times. You are at lease using require_once(), but why take the time to do the extra typing? Include the external file at the top of this file and you're good to go.

I'm in the same boat as cyberRobot here - until you explain a bit more thoroughly what exactly you're trying to do, we're not going to be able to help you find the correct answer.

sarojthapa60 · September 12, 2016

Let's deal with the GET variable from index.php first. You are utilizing the variable here:
$object_id='';
$capacity = '';
$ownership = '';
 
if (isset($_GET['id'])) {
    $id = $_GET['id'];
    $stmt = $conn->prepare("SELECT * FROM MATRIX WHERE OBJECTID=:id");
    $stmt->execute(array(':id' => $id));
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    $object_id = $row['OBJECTID'];
    $capacity = $row['Capacity'];
    $ownership = $row['Ownership'];
}
And it sounds like $capacity is the variable that indicates which color was selected, correct? If so, are you able to incorporate $capacity into the following query?
$stmt = $conn->prepare("SELECT * FROM MATRIX_DROPDOWNS");
Does $capacity match the values stored in your "colors" column in the MATRIX_DROPDOWNS table?

Yes, $capacity indicates which color is selected. I was able to incorporate $capacity into the query and it matches the "colors" column in the matrix_dropdowns. I am trying a different approach now. In the form of edit.php, I created the dropdowns like:

<tr>

<td>Capacity</td>

<option id="0">Select One</option>

<option id="1"> GREEN</option>

<option id="2">AMBER</option>

<option id="4">BLACK</option>

I am trying to create a javascript function to check the values and change the background color dynamically.

Thanks.

</select>

</label>

</td>

</tr>

sarojthapa60 · September 12, 2016

I just re-read Reply #5.

Are you looking to limit the number of options displayed in the dropdown based on the color selected in index.php? Or are you just trying to change the background color of the dropdown?

I have only four options in the dropdowns. I am trying to change the background color of the cell of the table, not the background color of the dropdown.

mac_gyver · September 12, 2016

<option ... ></option> tags need value='...' attributes, not id's.

i recommend that you write and test one small section of code at a time and only go onto the next section when you have determined that previous section works correctly.

maxxd · September 13, 2016

I am trying to change the background color of the cell of the table, not the background color of the dropdown.

Then this is a simple JavaScript question, not a PHP question. Create an change event handler to change the background color to the value selected by the user. Note that 'amber' is not recognized by Firefox as a valid color, so you'll probably want to do hex values, but this should get you started:

HTML:

<tr>
	<td id='capacity_cell' style='width:300px;height:250px;background-color:grey;'>
		<label for='capacity'>Capacity</label>
		<select id='capacity'>
			<option value=''>Select One</option>
			<option value='green'>Green</option>
			<option value='amber'>Amber</option>
			<option value='red'>Red</option>
			<option value='black'>Black</option>
		</select>
	</td>
</tr>

jQuery:

$(function(){
	$('#capacity').change(function(e){
		var bg = $(this).val();
		$('#capacity_cell').css({
			backgroundColor: bg
		});
	});
});

sarojthapa60 · September 13, 2016

Then this is a simple JavaScript question, not a PHP question. Create an change event handler to change the background color to the value selected by the user. Note that 'amber' is not recognized by Firefox as a valid color, so you'll probably want to do hex values, but this should get you started:

HTML:
<tr>
	<td id='capacity_cell' style='width:300px;height:250px;background-color:grey;'>
		<label for='capacity'>Capacity</label>
		<select id='capacity'>
			<option value=''>Select One</option>
			<option value='green'>Green</option>
			<option value='amber'>Amber</option>
			<option value='red'>Red</option>
			<option value='black'>Black</option>
		</select>
	</td>
</tr>
jQuery:
$(function(){
	$('#capacity').change(function(e){
		var bg = $(this).val();
		$('#capacity_cell').css({
			backgroundColor: bg
		});
	});
});

Thank you.

cyberRobot · September 13, 2016

To set the color based on the GET variable, you could run a switch statement after $capacity is set.

if (isset($_GET['id'])) {
    $id = $_GET['id'];
    $stmt = $conn->prepare("SELECT * FROM MATRIX WHERE OBJECTID=:id");
    $stmt->execute(array(':id' => $id));
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    $object_id = $row['OBJECTID'];
    $capacity = $row['Capacity'];
    $ownership = $row['Ownership'];
}
 
//DETERMINE DROPDOWN COLUMN COLOR
$bgColor = 'grey';  //whatever you want to use for the default color
switch($capacity) {
    case 'green': $bgColor='green'; break;
    case 'amber': $bgColor='yellow'; break;
    //etc...
}
?>

Then you can use the new $bgColor variable in your HTML code.

<td style="background-color:<?=$bgColor;?>"><label>
<select name="txt_capacity" class="textfields" id="capacity">
<option id="0">Select One</option>
<?php
require_once('include/database.php');
$stmt = $conn->prepare("SELECT * FROM MATRIX_DROPDOWNS");
//...

Sign In

Checking the value of a dropdowns in sql php

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information