can't search _ or _ 2 field in database, only search the last field

Question

can't search _ or _ 2 field in database,
below is my code and I used before in other project, but now if I set like this for search keyword in two field, it only can search the 2nd field, I can't figure why? How to fix this make it work?

Thanks.

php
$keyword = mysql_real_escape_string($_POST['keyword']);
$sql = "select * from $table where subject or content like '%$keyword%';";
$query = mysql_query($sql);
while($list = mysql_fetch_array($query)){
    $id = $list[id];
    $subject = $list[subject];
    $content = $list[content];
}

mysql
id    mediumint(6)    AUTO_INCREMENT  INDEX
subject    text    utf8_unicode_ci
content    longtext    utf8_unicode_ci    PRIMARY UNIQUE INDEX

Oh my good god, why do you have *any* indexes defined on your LONGTEXT `content` field at all, let alone *three* of them? — Sammitch, Jan 18 '13 at 17:51
but if I click content INDEX in phpMyAdmin to cancel the setting, it will show #1170 - BLOB/TEXT column 'content' used in key specification without a key length — user1775888, Jan 18 '13 at 17:56
Yeah, you really shouldn't have *any* indexing set on a BLOB/TEXT type field. Maybe FULLTEXT, but that's a whole other bag of cats. — Sammitch, Jan 18 '13 at 18:01
Thanks for reply. I just creat a new table in phpmyadmin and it auto set the PRIMARY UNIQUE INDEX. and is this table structure http://imgur.com/veHG2 something wrong?? any advice will be apperciated — user1775888, Jan 18 '13 at 18:10
Those indexes generally rank somewhere between useless and counter-productive on a TEXT field and, given what you've posted, they will not have any benefit to your program. Remove any indexes from the `content` field, remove the INDEX from the `id` field, then set the `id` field as your primary key. — Sammitch, Jan 18 '13 at 18:17
but those icon after I click, not change anything, not remove INDEX or can't change PRIMARY. or did I miss something? I already set Privileges Database-specific privileges (Check All) — user1775888, Jan 18 '13 at 20:13
and this not work too.. ALTER TABLE `table` DROP PRIMARY KEY , ADD PRIMARY KEY ( `id` ) ; — user1775888, Jan 18 '13 at 20:23

score 6 · Accepted Answer · edited May 23 '17 at 11:44

it should be

select * 
from   $table
where  subject like '%$keyword%' or 
       content like '%$keyword%'

As a sidenote, the query is vulnerable with SQL Injection if the value(s) came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements you can get rid of using single quotes around values.

How to prevent SQL injection in PHP?

score 2 · Answer 2 · answered Jan 18 '13 at 17:50

2

u can use it like that

where  subject like '%$keyword1%' or 

        content like '%$keyword2%'

answered Jan 18 '13 at 17:50

echo_Me

37,078
5
58
78

can't search ___ or ___ 2 field in database, only search the last field

2 Answers2

can't search _ or _ 2 field in database, only search the last field