在MYSQL表中搜索条目

Question

我有一个名为members的MYSQL表，其中有2列，如下所示：

username
john
sam

colleges_i_like
"yale ", "stanford", "columbia"
"princeton", "cornell"

我想要创建一个PHP查询，它允许我选择喜欢某所大学($college)的用户。

我编写了这段代码，但它不起作用(我是PHP新手，这是我编写的第一行代码)。你能帮我改正一下吗？

$college='yale';
$result = "SELECT * FROM members where $college IN 'colleges_i_like'";

Answer 1

试着做些像：

$college='yale';
$result = "SELECT * FROM members where 'colleges_i_like' LIKE '%".$college."%'";

Answer 2

首先，确保至少使用PHP MySQLi，而不是反对使用MySQL扩展。

第二，如果您想要将某些内容与其他内容匹配，则应该使用WHERE语句。下面是我为MySQLi编写的代码。

使用MySQLi时的连接：

$myDB = new mysqli("localhost", "username", "password", "dbname");

你必须写这样的东西：

// Prepare you QUERY to be exeuted, note the questuion mark at the end of
//  of the query, this should be replaced with a real value 
$stmt = $myDB->prepare("SELECT * FROM myTable WHERE colleges_i_like = ?");

// In this statement we replace the ? with real variable. "s" means string and
// I assume your colleges_i_like is string.
$stmt->bind_params("s", $myCollege);

// execute your query
$stmt->execute();

// get the result (PHP 5.4 > )
$result = $stmt->get_result();

// iterating over the results
while($row = $result->fetch_array(MYSQL_ASSOC))
{
   echo "Username is: " . $row["username"] . "<br />";
}

现在，如果您在您的领域中有几所学院，您的查询应该如下所示：

$stmt = $myDB->prepare("SELECT id, username FROM myTable WHERE colleges_i_like LIKE ?");

然后包装你的变量(哪一个代替？)在bind_param()中，如下所示：

$colleges = "%"."CollegeName"."%";

我想补充的是，如果PHP版本低于5.4，为了获取结果，您应该遵循以下步骤：

$results = array();

// Instead of star, we use certain fields so that our job in fetching them gets easier
$stmt = $myDB->prepare("SELECT id, username FROM myTable WHERE colleges_i_like = ?");

// bind params (no change)
$stmt->bind_params("s", $myCollege);

// execute your query (no change)
$stmt->execute();

// we binds the results to certain variables. Define the variable first, so to avoid
// undefined warning/notice
$stmt->bind_result($results['id'], $results['username']);

// fetching the results
while($stmt->fetch())
{
   echo "Id is: " . $row["id"] . "<br />";
   echo "Username is: " . $row["username"] . "<br />";
}

Answer 3

如果这两列位于同一个表中，并且"college_i_like“中的数据位于单独的行中，则可以这样做：

$query = "SELECT * FROM members WHERE college_i_like LIKE '%yale%'";
$result = mysql_query($query);
if(!$result){
    die(mysql_error());
}