检索并更改MySQL视图中用于重新执行的SQL [英] Retrieve and alter the SQL used in a MySQL View for re-execution

问题描述

如何获取当前的视图条件使用php和mysql语句显示创建视图viewname

How can i get the current view criteria using php and the mysql statement "Show create view viewname"

我想将当前的标准放入一个变量，修改该变量，然后重新运行查询。

I want to put the current criteria into a variable, amend that variable and then re-run the query. I just don't get how to use the statement from PHP.

感谢任何帮助

EDIT -

可能的代码：

if($_POST['flag']=='flag'){
    $sqlShow = 'show create view `filter_tbl`';
    $result = mysql_query($sqlShow);
    $resultQuery = $result;
    $resultQuery .= ' AND `lead_id`!="'.$idval.'"';
    $result2 = mysql_query($resultQuery);
    echo $result;
    if($result){
        echo true;
    }
    else{
        echo mysql_error();
    }
}

推荐答案

这是为了获取View使用的SQL语句：

Use this to get the SQL statement that the View uses:

$viewSQL = "SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS 
WHERE TABLE_SCHEMA = 'myDatabase' AND TABLE_NAME = 'filter_tbl'";

$result = mysql_query($viewSQL);
$resultQuery = mysql_fetch_assoc($result);
$SqlUsedByView = $resultQuery['VIEW_DEFINITION'];
// Now alter the WHERE clause to your needs:
$MyNewSQL = str_ireplace( "WHERE ", "WHERE `lead_id`!=".$idval." AND " , $SqlUsedByView);

注意我不知道View的实际WHERE子句是，所以上面的str_ireplace（）只是一个例子，你如何可以进行你的变更。

Note I don't know what the actual WHERE clause of your View is, so the str_ireplace() above is just an example of how you might make your alteration.

这篇关于检索并更改MySQL视图中用于重新执行的SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！