bronyaur bronyaur - 8 months ago 15
SQL Question

php script returns every row containing a word

This php script returns every row in the mySQL database:


$host = "host_name";
$db = "database_name";
$user = "user_name";
$pass = "password";

$connection = mysql_connect($host, $user, $pass);

//Check to see if we can connect to the server
die("Database server connection failed.");
//Attempt to select the database
$dbconnect = mysql_select_db($db, $connection);

//Check to see if we could select the database
die("Unable to connect to the specified database!");
$query = "SELECT * FROM farmer";
$resultset = mysql_query($query, $connection);

$records = array();

//Loop through all our records and add them to our array
while($r = mysql_fetch_assoc($resultset))
$records[] = $r;

//Output the data as JSON
echo json_encode($records);



This script works well but returns every row.

Let's say my database contains the names of farmers and their types of produce. The first column is "id, the second is "farmer_name", and the third is "produce". The produce is entered like this: beans, corn, lettuce, strawberries, tomatoes.

What I want to do is change the above script so that it returns only the rows containing the names of farmers who grow corn, and all the types of produce they grow. So, I don't just want the name, but also the list of all products, so long as the farmer grows corn.

Is this possible based on the way the database is structured? I made several attempts using needle/haystack but came up empty handed. I know very little about php.

SELECT `farmer_name`, `produce` FROM `farmer` WHERE `produce` LIKE '%corn%'

This will return the rows where corn is found anywhere in the produce field.