LIKE operator and correct usage with NOT IN

Question

Possible Duplicate:
mysql_fetch_array() expects parameter 1 to be resource, boolean given in select

This currently works...

$resultInput = mysql_query("SHOW COLUMNS FROM " . $table. " WHERE Field NOT IN ('id', 'created', 'date_modified', 'content', 'type', 'bodytext', 'project_content', 'content_short') AND Field NOT LIKE '%_image%'");

However I would like to remove all the fields with the name content and add it to the LIKE function with something like %content%

$resultInput = mysql_query("SHOW COLUMNS FROM " . $table. " WHERE Field NOT IN ('id', 'created', 'date_modified', 'type', 'bodytext') AND Field NOT LIKE ('%_image%', %content%)");

But this doesn`t seem to work? and returns a"

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in
C:\xampp\htdocs\framework.php on line 33

missing single quotes around %content% ?? and you have two items in the not like I think you need to put and not like twice, once for each — sdjuan, Jun 30 '12 at 00:42

Michael Berkowski · Accepted Answer · 2012-06-30T00:51:00.447

3

What you are attempting is actually a syntax error. LIKE conditions must be individually listed and separated by AND or OR. In your case however, you can do it with REGEXP by separating the conditions with |:

SHOW COLUMNS 
FROM " . $table. " 
WHERE Field NOT IN ('id', 'created', 'date_modified', 'type', 'bodytext') 
AND Field NOT REGEXP '_image|content'

Because the regular expression will match the patterns content or _image anywhere inside Field, there is no need for any additional equivalent characters to the % wildcards used in LIKE.

We assume that $table has been compared against a list of valid table names to use in this query for security purposes.

Finally, you received the fatal error mysql_num_rows() expects parameter 1 to be resource because you performed no error checking on the query result.

$result_input = mysql_query("SHOW COLUMNS.....");
if (!$result_input) {
  // Something's wrong
  echo mysql_error();
}
else {
  // ok, everything is working, proceed.
}

edited Jun 30 '12 at 00:51

answered Jun 30 '12 at 00:44

Michael Berkowski

267,341
46
444
390

very good solution! thanks man! what would one do when they want to include say content? AND Field REGEXP 'content'? – Alex Jun 30 '12 at 00:50
For example this query doesnt seem to be working.... $resultTextarea = mysql_query("SHOW COLUMNS FROM " . $table. " WHERE Field IN ('bodytext') AND Field REGEXP 'content'"); – Alex Jun 30 '12 at 01:17
@AlexFagard If you want positive results rather than negative, you would need an `OR` as in `Field IN ('bodytext','somethingelse') OR Field REGEXP '_image|content'`. – Michael Berkowski Jun 30 '12 at 01:31
@AlexFagard Don't use `REGEXP` for only one simple condition like `content` alone, as it is probably more efficient to use `LIKE`. – Michael Berkowski Jun 30 '12 at 01:32
Thanks. Yea In my research I saw info on the greater efficiency of the like statement! – Alex Jun 30 '12 at 01:37

LIKE operator and correct usage with NOT IN

1 Answers1