MYSQL: Retrieve all rows from one column containing multiple data

Question

I've looked online for about two days and I'm not sure how to ask Google this scenario. I'm trying to pull all rows from a single column that contain the word 'Helmet'. What I'm getting is only 2 rows. There's actually about 10 rows but it contains words like 'Legs, Helmet' The rows that only have the word 'Helmet' are populating correctly. Code I'm using.

  id | rune_slot
  ---------------------------------
  0  | Shoulders
  ---------------------------------
  1  | Chest
  ---------------------------------
  2  | Main Hand, Off Hand, Ranged
  ---------------------------------
  3  | Helmet
  ---------------------------------
  4  | Legs, Helmet
  ---------------------------------
  5  | Helmet
  ---------------------------------
  6  | Legs, Helmet
  ---------------------------------

As you can see, this is my table. Like I said, I'm getting rows 3 and 4, but I can't seem to get rows 4 and 6 also.

$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
      die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT * FROM runes WHERE rune_slot LIKE "Helmet"' ;

mysql_select_db('runelist');
$retval = mysql_query( $sql, $conn );
if(!$retval )
{
      die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_ASSOC))
{
      echo "Rune Name : {$row['rune_name']} "." Craft Level :  {$row['craft_level']} "."   Rune Slot : {$row['rune_slot']} ".
      "<br />";
} 
echo "Fetched data successfully\n";
mysql_close($conn);

This is the code I got from http://www.tutorialspoint.com/php/mysql_select_php.htm .

I've tried as many different select statements that I know of. I've tried changing mysql_fetch_array to _assoc and I've browsed the http://www.php.net/manual/en/function.mysql-fetch-assoc.php . I'm guessing it's bad practice to put multiple data in a single column? Any help would be appreciated, I come here often to learn about PHP and MYSQL, now I'm just stuck. Thanks!!

[you should probably stop using mysql_*](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). Look at the red warning in your documentation link. — Amelia, Feb 25 '13 at 22:56
I did notice the warning, but when I tried to implement the new api type, I received an error message, at that point, I went back to the old style thinking it has not yet been implemented. I'll rework the code to get it updated. Thanks for the head up!! :) — magezero, Feb 26 '13 at 17:34

score 0 · Accepted Answer · answered Feb 25 '13 at 20:41

0

You need to use % in your query:

  $sql = 'SELECT * FROM runes WHERE rune_slot LIKE "%Helmet%"' ;

which means any combination of Helmet

answered Feb 25 '13 at 20:41

Boynux

5,958
2
21
29

Wow, very easy. I thought I already tried this, but apparently I didn't code it correctly. Thanks for the help! Another question I have after reading through some MYSQL documentation and websites, is it okay to fill in one column with CSV's? I'm reading that it doesn't conform to 1NF and I could have issues down the road. – magezero Feb 25 '13 at 22:58
You can, but searching like this in large tables could be slow in relational databases. if you have just a few list then there should be no problem but I suggest you to review your schema and use more relational approach. – Boynux Feb 26 '13 at 07:43

score 0 · Answer 2 · answered Feb 25 '13 at 20:44

0

You are missing % in your like query. Change query to like "%helmet%"

answered Feb 25 '13 at 20:44

DevelopmentIsMyPassion

3,541
4
34
60

MYSQL: Retrieve all rows from one column containing multiple data

2 Answers2