CODEWITHSUNDEEP
php
user3388726
user3388726

Reputation: 63

Searching for an entry in a MYSQL table

I have a MYSQL table called members with 2 columns as follows:

username
john
sam

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

I would like to create a PHP query that allows me to select users who like a certain college ($college).

I wrote this code but it is not working (I am new to PHP, this is the first line of code I am writing). Can you please help me correct it?

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

Upvotes: 0

Views: 45

Answers (3)

Mostafa Talebi
Mostafa Talebi

Reputation: 9183

First, make sure your use at least PHP MySQLi and not deprecated MySQL extension.

Second, you should use WHERE statement if you'd like to match something to something else. Below is my code, written for MySQLi.

Your connection when using MySQLi:

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

You have to write something like this:

// 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 />";
}

Now if you have several colleges in your field, you query should be like this:

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

And then wrap your variable (which replaces ? in the bind_param()) with this:

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

I like to add that, if your PHP version is below 5.4, for fetching the results, you should follow this:

$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 />";
}

Upvotes: 1

Orion_Blackstock
Orion_Blackstock

Reputation: 179

If the two columns are in the same table and the data in "college_i_like" is in separate rows you could do this:

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

Upvotes: 1

SpencerX
SpencerX

Reputation: 5723

Try someting like :

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

Upvotes: 1

Related Questions