根据数据库记录动态生成复选框表单 - Dynamically generate checkbox form based on database records

嗨，我的数据库中有两个表，它们的结构如下:

表:

service_id, service_name

2) business_services_offered表:

record_id, business_id, service_id_fk

当企业主注册我的网站时，他们使用一个简单的表格选择他们的业务提供的服务，像这样的复选框(我只包括两个服务，使事情简单):

 <form action ="insert_services.php">
 <input type="checkbox" name="lang[ ]" >Behavior supports<br><br />
 <input type="checkbox" name="lang[ ]" >Case Management<br><br />
 </form>

这个系统非常直接，工作得很好，但我想使用一个类似的形式，允许企业编辑他们提供的服务，如果他们需要。

我不知道如何做的是如何根据数据库中包含的信息动态生成"编辑表单"。为了更清楚，我们假设一个企业主在最初注册网站时只勾选了行为支持。

因此，business_services_offered表中相应的记录看起来像这样:

record_id | business_id | service_id_fk

1 0000023 1

哦，服务表是这样的:

service_id | service_name

1行为支持

2案例管理

现在同一个所有者决定要编辑他们提供的服务…我如何动态地向他们显示一个复选框表单，其中他们的服务(在本例中是Behavior支持)已经被选中。

显然，我将使用服务继承数据库并连接两个表。Service_id和business_services_offered。service_id_fk但是在生成表单的while循环期间，我如何导致行为支持已经被选中?我猜是某种if语句，但我不确定。

任何帮助都将非常感激!

这是我猜会工作的查询

 $query = "SELECT service_name, service_id, business_name" .
          "FROM services, business_services_offered " .
          "WHERE services.service_id = business_services_offered.service_id_fk";
 $result = mysql_query($query) 
      or die(mysql_error());

while循环应该是这样的

 while($row = mysql_fetch_array($result)){
 $service_name = $row['service_name'];
 echo "<form action ='edit_services.php'>" .
      "<input type='checkbox' name='lang[ ]' >$service_name<br><br />" .
       "<input type='checkbox' name='lang[ ]' >$service_name<br><br />" .
      "</form>";
 }

那么，我如何确保行为支持的复选框被选中呢?

谢谢!

下面是表单代码和jQuery我将在一分钟内用单独的PHP文件编辑这个答案，以处理DB查询

<!-- Must include jQuery Library -->
<script src="//ajax.googleapis.com/ajax/libs/jquery/1.11.0/jquery.min.js"></script>
// Lets build the Services form
${'The Form'} = '<form name="editServicesForm" id="editServicesForm" action="edit_services.php" method="POST">
<h2>Services</h2>';
// query the service table
$query = mysql_query('SELECT * FROM `services`');
while($row = mysql_fetch_assoc($query)){
    ${'The Form'} .= '<label><input type="checkbox" name="CheckboxGroup[]" value="'.$row['service_id'].'" id="CheckboxGroup_0" />'.$row['service_name'].'</label><br />';
}
${'The Form'} = '</form>';
// add the form on the page where you need with this
?>
<?= ${'The Form'}; ?>
<!-- The jQuery to do the magic -->
<script type="text/javascript">
// waits for the document to finish loading so all the elements are there to manipulate
$(document).ready(function() {
    // your users id to reference the services in the database query
    var businessID = "1" // replace this with the users id
    // Do a basic post to and external php file
    $.post('post.api.php', {'api': 'getServices', 'business_id': businessID}, function(resp){
        // parse the response and check the boxes
        var obj = $.parseJSON(resp);
        // loop through the services returned as active (checked)
        $.each(obj, function(i, value){
            // Check the checkbox with the corresponding value
            $('input[type="checkbox"][value="'+value.service+'"]').attr('checked', true);
        });
    });
});
</script>

目录op post.api.php

<?php
// only access this if there is value for api being posted to it
if(isset($_POST['api'])){

    // only access this if $_POST['api'] = getServices
    if($_POST['api'] == 'getServices'){
        // Create and array to store the data
        ${'Response'} = array();        
            // Get the users id
            ${'Business ID'} = $_POST['business_id']; // you should clean this to avoid sql injection
        // Iterator
        ${'Iterator'} = 0;
        // Query your database and return the values of the services that you stored during registration.   
        $sql = "SELECT `service_id_fk` FROM `business_services_offered` WHERE `business_id` = '".${'Business ID'}."'"; // your WHERE statement should include the user id sent here in ${'User ID'}
        $query = mysql_query($sql);
        // Do your while loop with your query results
        while($row = mysql_fetch_assoc($query)){
            // store our service value
            ${'Response'}[${'Iterator'}]['service'] = $row['service_id_fk'];
            // increment our iterator
            ${'Iterator'}++;
        }
        // return the json to the posting file
        echo json_encode(${'Response'});
    }
    exit;
}
?>

或者你可以这样做:

${'Business ID'} = "1";
// query the service table
$query = mysql_query('SELECT `a`.`service_id`,`a`.`service_name`, `b`.`record_id` FROM `services` `a` LEFT JOIN `business_services_offered` `b` ON `b`.`service_id_fk` = `a`.`service_id` WHERE `b`.`business_id` = "'.${'Business ID'}.'"');
while($row = mysql_fetch_assoc($query)){
    if($row['record_id'] != NULL){
        $checked = ' checked="checked"';
    } else {
        $checked = '';
    }
    ${'The Form'} .= '<label>
                      <input type="checkbox" name="CheckboxGroup[]" value="'.$row['service_id'].'" id="CheckboxGroup_0"'.$checked.' />'.$row['service_name'].'</label>
                      <br />';
}
${'The Form'} = '</form>';
echo ${'The Form'};