如何在MySQL中将数组作为参数传递给存储过程? [英] How to pass array as parameter to stored procedure in mysql?

问题描述

我正在尝试将数组作为参数传递给我的存储过程.但是我没有得到预期的结果.

I'm trying to pass array as parameter to my stored procedure. But I'm not getting expected result.

我试图将按钮单击时查看的电子邮件和ID的列表发送到控制器，然后尝试通过将该列表作为参数传递给我的存储过程从数据库中获取数据.

I'm trying to send list of emails and ID's from view on button click to controller, and then I'm trying to fetch data from database by passing that list as parameter to my stored procedure.

这是我的代码:

查看:

<form action="<?= base_url('certificate/sendMail') ?>" method="post">
    <table id="example1" class="table table-striped table-bordered" cellspacing="0" width="100%">
        <thead>
            <tr>
                <th>Mail</th>
            </tr>
        </thead>
        <tfoot>
            <tr>
                <th style="text-align: center;">
                    <input type="submit" name="submit" value="Send" class="send btn btn-primary btn-xs" disabled="" />
                </th>
            </tr>
        </tfoot>
        <tbody>
            <tr>
                <td style="text-align: center;">
                    <input type="checkbox" class="checkbox" name="sendMail[]" value="<?= $certify->landlord_main_email; ?>">
                </td>   
            </tr>
        </tbody>
    </table>
</form>

在这里，我通过选中复选框来获取电子邮件和ID的列表.

Here I'm getting list of emails and ID's by checking checkbox.

控制器:

public function sendMail($certID)
    {
        # code...

        if(isset($_POST['submit'])){//to run PHP script on submit
            if(!empty($_POST['sendMail'])){
            // Loop to store and display values of individual checked checkbox.
                foreach($_POST['sendMail'] as $selected){
                    //echo $selected."</br>";
                }
            }
        }
        $result['certMails'] = $this->c->getCertificateByEmail($certID);
        $email_to = $this->input->post('sendMail[]');
        $body = $this->load->view('Emails/emailAll', '', true);
    }

在这里，我试图获取数据并将其发送到电子邮件模板视图.

Here I'm trying to get data and send it to email template view.

型号:

public function getCertificateByEmail($certID)
    {
        # code...
        $query = $this->db->query("call getCertificateByEmail($certID)");

        if ($query) {
            $data = $query->result();
            $query->next_result(); 
            $query->free_result();
            return $data;
        } else {
            return false;
        }
    }

存储过程:

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `Certificate_By_Email`(IN `certID` INT)
BEGIN
SELECT `cert_id`, `cert_user_id`, `cert_landlord_id`,
        tbl_landlord.landlord_id, tbl_landlord.landlord_main_email, 
        `cert_property_id`, tbl_property.property_code, 
        tbl_property.property_city,  tbl_property.property_cluster, 
        tbl_cluster.cluster_name, `cert_status`, `cert_number`, 
        tbl_certificates.certificate_name, `cert_issue_date`, 
        `cert_expiry_date`, `cert_duration`, `cert_updated_date`, 
        `cert_remarks`, `cert_notes` 
FROM `tbl_certificate_details` 
    LEFT OUTER JOIN tbl_landlord ON  tbl_certificate_details.cert_landlord_id = tbl_landlord.landlord_id 
    LEFT OUTER JOIN tbl_property ON tbl_certificate_details.cert_property_id = tbl_property.property_id 
    LEFT OUTER JOIN tbl_certificates ON tbl_certificate_details.certificate_id = tbl_certificates.certificate_id 
    LEFT OUTER JOIN tbl_cluster ON tbl_property.property_cluster = tbl_cluster.cluster_id 
    LEFT OUTER JOIN tbl_area ON tbl_property.property_area = tbl_area.area_name 
WHERE FIND_IN_SET(cert_id, certID);
END$$
DELIMITER ;

欢迎您提供任何帮助，

推荐答案

1. 我看不到sendMail()中任何地方定义的$certID.如果将其打印出来，您将获得什么价值?
2. 我很确定您只是使用$this->input->post('sendMail');而不是$this->input->post('sendMail[]');来获取数组
3. 您是否已确认您的(不安全的)SQL调用适用于自定义值?如果是这样，请确保您传递正确的值

1. I can't see $certID defined anywhere in sendMail(). What value do you get if you print that out?
2. I'm fairly sure you just use $this->input->post('sendMail'); instead of $this->input->post('sendMail[]'); to get the array
3. Have you confirmed that your (unsafe) sql call works with custom values? If it does, make sure you're passing the correct values

尝试调试和疑难解答，您可能会找到原因.

Try debugging and troubleshooting and you'll probably find what's the cause.

这篇关于如何在MySQL中将数组作为参数传递给存储过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！