基于用户选项的PHP动态SQL SELECT语句 [英] Dynamic SQL SELECT Statement with PHP based on user options

问题描述

首先，我想提一提的是，我一直在疯狂地进行搜索，以找到解决问题的办法，但到目前为止还没有运气.我的问题如下:

First of all I want to mention that I've been trying and searching like crazy to find a solution to this but no luck so far. My issue is the following:

我有一个包含数十行的MySQL数据库，并且创建了一个jQuery网格来显示数据.此页面已经可以使用了. 根据要求，我整理了一个页面，人们可以在其中从复选框中选择几个选项并过滤结果.它是这样工作的:

I have a MySQL database with dozens of rows and I created a jQuery grid to display the data; this page is working already. As per requirements I'm putting together a page where people can select a couple options from checkboxes and filter the results. This is how it will work:

第1页将包含3组复选框(名称，位置，语言)，每组都有不同的选项([名称:David，Nick等]，[位置:纽约，华盛顿，孟买，伦敦，迈阿密]， [语言:英语，西班牙语].一旦用户选择了自己的选项，它将提交带有这样的内容的表格

Page 1 will have 3 groups of checkboxes (Name, Location, Language) and each set has different options ([Name: David, Nick, etc], [Location: New York, Washington, Mumbai, London, Miami], [Language: English, Spanish]. Once the user selects his options it will submit a form with something like this

grid.php?location =华盛顿& location =孟买& language =英语& name =大卫

grid.php?location=Washington&location=Mumbai&language=English&name=David

创建字符串很容易，但是接收器将使用此参数，然后将其放置在数组中然后创建SQL语句，这是我所努力的地方.

Creating the string is easy but the receiver that will take this parameters and place then in an array to then create the SQL statement is where I'm struggling.

我已经尝试过这样的事情:

I've tried with something like this:

$location= $_GET['location'];
$language= $_GET['language'];
$name= $_GET['name'];

if($location!=""){
    $where[] = 'location=';
    $where_args[] = $location;
}
//same code for the other 2 options

$where_clause = implode(' OR ', $where_args);

$sql_json = "SELECT * FROM myTable WHERE $where_clause ORDER BY id DESC";

所有选项都不是强制性的，因此查询需要接受是否可以设置或不设置该值的可能性，因此最终可能是grid.php?language = English之类的东西.

None of the options are mandatory so the query needs to accept the possibility that the value may or may not be set so it may end up being something like grid.php?language=English.

我知道上面的代码并不能正常工作，但是如果有人可以向我指出正确的方向，我将不胜感激.

I know that the code above doesn't quite work but I will appreciate if someone can please point me in the right direction.

推荐答案

尝试一下:

<?php
$location= $_GET['location'];
$language= $_GET['language'];
$name= $_GET['name'];

if($location!=""){
    $where[] = " `location` = '".mysql_real_escape_string($location)."'";
}
if($language!=""){
    $where[] = " `language` = '".mysql_real_escape_string($language)."'";
}
if($name!=""){
    $where[] = " `name` = '".mysql_real_escape_string($name)."'";
}
$where_clause = implode(' OR ', $where);
//same code for the other 2 options

$sql_json = "SELECT * FROM myTable WHERE $where_clause ORDER BY id DESC";
?>

这篇关于基于用户选项的PHP动态SQL SELECT语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！