Retrieve data from sql database and display in tables - Display certain data according to checkboxes checked
Try to create your checkbox like below:
Solar_Time_Decimal<checkbox name='columns[]' value='1'>
GHI<checkbox name='columns[]' value='2'>
DiffuseHI<checkbox name='columns[]' value='3'>
Zenith_Angle<checkbox name='columns[]' value='4'>
DNI<checkbox name='columns[]' value='5'>
And try to hange your PHP code to this:
<?php
//HTML forms -> variables
$fromdate = isset($_POST['fyear']) ? $_POST['fyear'] : data("d/m/Y");
$todate = isset($_POST['toyear']) ? $_POST['toyear'] : data("d/m/Y");
$all = false;
$column_names = array('1' => 'Solar_Time_Decimal', '2'=>'GHI', '3'=>'DiffuseHI', '4'=>'Zenith_Angle','5'=>'DNI');
$column_entries = isset($_POST['columns']) ? $_POST['columns'] : array();
$sql_columns = array();
foreach($column_entries as $i) {
if(array_key_exists($i, $column_names)) {
$sql_columns[] = $column_names[$i];
}
}
if (empty($sql_columns)) {
$all = true;
$sql_columns[] = "*";
} else {
$sql_columns[] = "DATE,Local_Time_Decimal";
}
//DNI CHECKBOX + ALL
$tmp ="SELECT ".implode(",", $sql_columns)." FROM $database_Database_Test.$table_name where DATE>=\"$fromdate\" AND DATE<=\"$todate\"";
$result = mysql_query($tmp);
echo "<table border='1' style='width:300px'>
<tr>
<th>DATE</th>
<th>Local_Time_Decimal</th>";
foreach($column_names as $k => $v) {
if($all || (is_array($column_entries) && in_array($k, $column_entries)))
echo "<th>$v</th>";
}
echo "</tr>";
while( $row = mysql_fetch_assoc($result))
{
echo "<tr>";
echo "<td>" . $row['DATE'] . "</td>";
echo "<td>" . $row['Local_Time_Decimal'] . "</td>";
foreach($column_names as $k => $v) {
if($all || (is_array($column_entries) && in_array($k, $column_entries))) {
echo "<th>".$row[$v]."</th>";
}
}
echo "</tr>";
}
echo '</table>';
if($result){
echo "Successful";
}
else{
echo "Enter correct dates";
}
?>
<?php
mysql_close();?>
This solution consider your particular table columns but if your wish a generic solution you can try to use this SQL too:
$sql_names = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '$database_Database_Test' AND TABLE_NAME = '$table_name'";
and use the result to construct the $column_names array.
How to use checkboxes to retrieve specific data in a database
I see at least 2 errors in your code.
the $column_names associative array values are supposed to be passed as field names, so I assume that they are not correct, as you have spaces in them (and as I know wordpress by default does not have such field names.
if some selection is provided by user you are adding some extra field names to the once which are passed by user and you have a colon after them so it will generate an error.
I would rewrite the code like this
<?php
$all = false;
$column_names = array('1' => '`field1`', '2' => '`field2`', '3' => '`field3`');
if(isset($_POST['columns'])){
$column_entries = $_POST['columns'];
$sql_columns = array();
foreach($column_entries as $i) {
if(array_key_exists($i, $column_names)) {
$sql_columns[] = $column_names[$i];
}
}
$sql_columns[] = "authorss";
$sql_columns[] = "research_source";
$sql_columns[] = "research_title";
} else {
$all = true;
$sql_columns[] = "*";
}
Also as you have said $wpdb->get_results returns already the results - array so that's why you get the errors. Plus before calling mysql_fetch_assoc it is better to check if the passed parameter is recource and if the number of rows is not 0.
if($result!==false && mysql_num_rows($result)>0){
while( $row = mysql_fetch_assoc($result)){
...
}
}
*********** UPDATE ***********
according to last changes try this code:
<?php
$all = false;
$column_names = array('1' => '`authorss`', '2' => '`research_source`', '3' => '`research_title`');
if(isset($_POST['columns'])){
$column_entries = $_POST['columns'];
$sql_columns = array();
foreach($column_entries as $i) {
if(array_key_exists($i, $column_names)) {
$sql_columns[] = $column_names[$i];
}
}
} else {
$all = true;
$sql_columns[] = "authorss";
$sql_columns[] = "research_source";
$sql_columns[] = "research_title";
}
global $wpdb;
//DNI CHECKBOX + ALL
$tmp = $wpdb->get_results( "SELECT ".implode(",", $sql_columns)." FROM wp_participants_database");
echo "<table border='1' style='width:450px'>
<tr>
<th>authorss</th>
<th>research_source</th>
<th>research_title</th>";
foreach($column_names as $k => $v) {
if($all || (is_array($column_entries) && in_array($k, $column_entries)))
echo "<th>$v</th>";
}
echo "</tr>";
if(count($tmp)>0){
for($i=0;$i<count($tmp);$i++){
echo "<tr>";
foreach($tmp[$i] as $key=>$value){
echo "<td>" . $value . "</td>";
}
foreach($column_names as $k => $v) {
if($all || (is_array($column_entries) && in_array($k, $column_entries))) {
echo "<th>".$row[$v]."</th>";
}
}
echo "</tr>";
}
}
echo '</table>';
?>
Fetch data from database into checkbox and get the selected values PHP
If you don't supply a value attribute to your checkbox in your HTML, it will default to on in most browsers to let you know it had been checked.
So if you're making checkboxes asking people to check their 3 favorite fruits.
<input type="checkbox" name="check_list[]"> Banana <br>
<input type="checkbox" name="check_list[]"> Apple <br>
<input type="checkbox" name="check_list[]"> Orange <br>
If all 3 are checked, you will have Array ( [0] => on [1] => on [2] => on )
Now if you add the value attribute
<input type="checkbox" name="check_list[]" value="banana"> Banana <br>
<input type="checkbox" name="check_list[]" value="apple"> Apple <br>
<input type="checkbox" name="check_list[]" value="orange"> Orange <br>
If all 3 are checked you'll have :Array ( [0] => banana [1] => apple [2] => orange )
You can read more about that here : https://developer.mozilla.org/en-US/docs/Web/HTML/Element/input/checkbox#Value
how to get checkbox checked with MySQL data?
What I could understand by your question is..
- You are requesting a php file to give the privileges details for 2 users {select}.
- And after requesting you should get the checkboxes to be checked according to the user.
What I couldn't understand
UPDATE roles SET privilege='$json' WHERE id=$role_idwhat is this query doing, are you trying to change the privileges..?
Anyways, I will do something like this...
Working Example here
HTML
<select onchange="showpriv(this)" id="role_selector" name="roles">
<option value='0' disabled selected >Please Choose</option><
<option value='1' disabled selected >Admin</option>
<option value='2' disabled selected >Editor</option>
</select>
<div style='display:none;' id="priv">
<li><input name="chk[]" id="settings" value="settings" type="checkbox">Can Access Settings</input></li>
<li><input name="chk[]" id="view_team" value="view_team" type="checkbox">Can View Team Data</input> </li>
<li><input name="chk[]" id="delete_team" value="delete_team" type="checkbox">Can Delete Team Profile</input></li>
<li><input name="chk[]" id="edit_team" value="edit_team" type="checkbox">Can Edit Team Profile</input> </li>
</div>
<!-- I am gonna load the scripts here to check and uncheck boxes accordingly -->
<div class="privscripts"></div>
Script/JQuery {for ajax request}
function showpriv(elem){
$.post("privileges.php",
{ role : elem.value, type : "privilege" },
function(data, status){
if(status == "success"){
console.log(data);
document.getElementById('priv').style.display='';
$('.privscripts').html(data);
}
else console.log("something went wrong");
}
);
}
PHP code
<?php
if(isset($_POST['type'])){
if($_POST['type']== "privilege"){//Just to ensure everything goes right...!
if($_POST['role'] == 1){//If admin
//Echo out script to check/uncheck boxes
echo "<script>
document.getElementById('settings').checked = true;
document.getElementById('view_team').checked = true;
document.getElementById('delete_team').checked = true;
document.getElementById('edit_team').checked = true;
</script>";
}
else if($_POST['role'] == 2){//If editor
//Echo out script to check/uncheck boxes
echo "<script>
document.getElementById('settings').checked = false;
document.getElementById('view_team').checked = true;
document.getElementById('delete_team').checked = false;
document.getElementById('edit_team').checked = true;
</script>";
}
}
}
?>
You can interact with DB and do your stuff as you wish, I have given an example.
You can ECHO out html/json/or some error/debug code, and then do your stuff in AJAX function accordingly, explore and learn.
For any queries feel free to comment down.
Select data from multiple tables in MySQL using check boxes and show the table field according to checkbox
If I undestand you, to do that you need add to ON d.uid=e.uid" something like this ON d.uid=e.uid" AND Uid=$id AND name=$name And m_atm=$atm, or to add this to where (to where I thinght is not good)
For example
HTML:
<form method="get" action="/a.php">
<input type="checkbox" name="check1" value="text1"/>
<input type="checkbox" name="check2" value="text2"/>
<input id="submit" onclick="f();return false;" type="button" value="ok"/>
</form>
PHP (test.php)
if(isset($_GET['check1'])) $id=" AND Uid='$_GET[check1]'"; //if is checked first
if(isset($_GET['check2'])) $name=" AND name='$_GET[check2]'"; //if is checked second
/* . . . */
$sql = "SELECT e.* ,d.* FROM emitra_basic As e INNER JOIN emitra_device as d ON (d.uid=e.uid $id $name )";
var_dump($sql);
JS:
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.0/jquery.min.js"></script>
<script>
function f() {
var url;
var xmlhttp,
url="/text.php?"+$('form').serialize(); //change text.php
if (window.XMLHttpRequest) {
xmlhttp = new XMLHttpRequest();
} else if (window.ActiveXObject) {
xmlhttp = new ActiveXObject('Microsoft.XMLHTTP');
}
xmlhttp.open('GET', url, true);
xmlhttp.onreadystatechange = function() {
if (xmlhttp.readyState == 4) {
myfunction(xmlhttp.responseText);
}
}
xmlhttp.send(null);
function myfunction(response) { alert(url+' '+response);
//do something
}
}
</script>
That Php code is simply, but you can use loop and key value to make it look more good
For example you can use <input name=text[]> for all ckeckboxes elements and do this
foreach ($_GET['text'] as $key => $value) {
if($key==0) $key='uid'; else
if($key==1) $key='name'; else
if($key==2) $key='m_atm';
$q.="$key='$value' AND ";
}
$q=substr($q,0,strlen($q)-5);
$sql2 = "SELECT e.* ,d.* FROM emitra_basic As e INNER JOIN emitra_device as d ON (d.uid=e.uid $q )";
var_dump($sql2);
How to display checked checkbox from database in php?
When you display your page (in your first section of code), at some point you do this:
echo "<td><input name='check[]' type=checkbox value=c".$course[$x]->courseId."-o".$subobj[$j]['SubObjId']." id=checked></td>";
The value is set to:
value=c"c.$course[$x]->courseId."-o".$subobj[$j]['SubObjId']";
This value is where you get the checked or not value you mentioned in the comments (like c1-o1.1).
Right. So before you do that echo, add a new if condition.
$value = "c$course[$x]->courseId" . "-o$subobj[$j]['SubObjId']";
if (verify_checked($value)) {
$checked_code = "checked=\"checked\"";
}
else {
$checked_code = "";
}
echo "<td><input name='check[]' type=checkbox value=$value id=checked $checked_code ></td>";
The verify_checked(value) function does (from what I understand of your database, you keep the "grid location" of checked elements):
function verify_checked($value)
{
// Connect to the database if needed
// Perform: SELECT count($value) FROM Report
// If the result is >0, return TRUE
// Else return FALSE
}
- The idea here is to query the database every time your are about to echo the
<input>element. - Note for concatenating text, I find it more legible to put spaces around the
.to clearly split what is part of the text and what is the concatenation dot. - As mentioned previously, indentation is critical for understanding of the different contexts. Until I indented your code, I had not realized how the different loops worked in relation to the others.
Related Topics
Generating Random Numbers from Skewed Normal Distribution
Post to a Facebook Page Without "Manage_Pages" Permission Using PHP
How to Set 777 Permission on a Particular Folder
Applied Coupons Disable Free Shipping Conditionally in Woocommerce
Dynamic Paypal Button Generation - Isn't It Very Insecure
Create New Xml File and Write Data to It
What Literal Characters Should Be Escaped in a Regex
Laravel 4 Removing Public from Url
Loadhtml Libxml_Html_Noimplied on an HTML Fragment Generates Incorrect Tags
Understanding Imagemagick's Convert and Translating to Ruby Rmagick
PHP Object Attribute with Dot in Name
Convert Number to Letter with PHP
Accessing Laravel .Env Variables in Blade
Design Patterns: How to Create Database Object/Connection Only When Needed