SMF 2.1.4 Search

[Anmer]

"If I do the same search on a Category, I don't get any results where the input criteria exists in the description. "
Do you have the search description box checked?

No.  Unchecked in both searches.

If you have phpmyadmin I would the SQL query in there and see what data is displayed.

What query?

[SMFHacks]

The first query output of your post

Code: [Select]

SELECT
p.id_picture, p.id_cat, p.commenttotal, p.keywords, p.filesize, p.thumbfilename, p.approved, p.views,
p.id_member, m.real_name, p.date, p.mature, v.id_picture as unread, mg.online_color,
p.title, p.rating, p.totalratings, p.totallikes, (p.rating / p.totalratings ) AS ratingaverage
FROM {db_prefix}gallery_pic as p
LEFT JOIN {db_prefix}members AS m ON (m.id_member = p.id_member)
LEFT JOIN {db_prefix}gallery_usersettings AS s ON (s.id_member = m.id_member)
LEFT JOIN {db_prefix}membergroups AS mg ON (mg.ID_GROUP = IF(m.ID_GROUP = 0, m.ID_POST_GROUP, m.ID_GROUP))
LEFT JOIN {db_prefix}gallery_log_mark_view AS v ON (p.id_picture = v.id_picture AND v.id_member = 1 AND v.user_id_cat = p.USER_ID_CAT)
LEFT JOIN {db_prefix}gallery_exif_data AS e ON (e.id_picture = p.id_picture)
LEFT JOIN {db_prefix}gallery_catperm AS c ON (c.id_group IN (1,8,9) AND c.id_cat = p.id_cat)

WHERE (((s.private =0 OR s.private IS NULL ) AND (s.password = '' OR s.password IS NULL ) AND p.USER_ID_CAT !=0 AND p.approved =1) OR (p.approved =1 AND p.USER_ID_CAT =0 AND (c.view IS NULL OR c.view =1))) AND p.approved = 1 AND (p.title LIKE '%ANA%' )
GROUP BY p.id_picture ORDER BY p.id_picture DESC
LIMIT 0,126


[Anmer]

Maybe I did something wrong but running the SQL against the forum db returns an error:

Code: [Select]

Error
Static analysis:

32 errors were found during analysis.

Unexpected character. (near "{" at position 298)
Unexpected character. (near "}" at position 308)
Unexpected character. (near "{" at position 337)
Unexpected character. (near "}" at position 347)
Unexpected character. (near "{" at position 403)
Unexpected character. (near "}" at position 413)
Unexpected character. (near "{" at position 482)
Unexpected character. (near "}" at position 492)
Unexpected character. (near "{" at position 590)
Unexpected character. (near "}" at position 600)
Unexpected character. (near "{" at position 726)
Unexpected character. (near "}" at position 736)
Unexpected character. (near "{" at position 804)
Unexpected character. (near "}" at position 814)
An alias was previously found. (near "}" at position 308)
Unexpected token. (near "}" at position 308)
Unexpected token. (near "gallery_pic" at position 309)
Unrecognised keyword. (near "as" at position 321)
Unexpected token. (near "p" at position 324)
An alias was previously found. (near "}" at position 347)
Unrecognised keyword. (near "AS" at position 356)
Unexpected token. (near "m" at position 359)
Unexpected token. (near "m" at position 365)
Unexpected token. (near "." at position 366)
Unexpected token. (near "id_member" at position 367)
Unexpected token. (near "=" at position 377)
Unexpected token. (near "p" at position 379)
Unexpected token. (near "." at position 380)
Unexpected token. (near "id_member" at position 381)
Unexpected token. (near ")" at position 390)
This type of clause was previously parsed. (near "LEFT JOIN" at position 393)
Unrecognised statement type. (near "LEFT JOIN" at position 393)
SQL query: Copy Documentation

SELECT p.id_picture, p.id_cat, p.commenttotal, p.keywords, p.filesize, p.thumbfilename, p.approved, p.views, p.id_member, m.real_name, p.date, p.mature, v.id_picture as unread, mg.online_color, p.title, p.rating, p.totalratings, p.totallikes, (p.rating / p.totalratings ) AS ratingaverage FROM {db_prefix}gallery_pic as p LEFT JOIN {db_prefix}members AS m ON (m.id_member = p.id_member) LEFT JOIN {db_prefix}gallery_usersettings AS s ON (s.id_member = m.id_member) LEFT JOIN {db_prefix}membergroups AS mg ON (mg.ID_GROUP = IF(m.ID_GROUP = 0, m.ID_POST_GROUP, m.ID_GROUP)) LEFT JOIN {db_prefix}gallery_log_mark_view AS v ON (p.id_picture = v.id_picture AND v.id_member = 1 AND v.user_id_cat = p.USER_ID_CAT) LEFT JOIN {db_prefix}gallery_exif_data AS e ON (e.id_picture = p.id_picture) LEFT JOIN {db_prefix}gallery_catperm AS c ON (c.id_group IN (1,8,9) AND c.id_cat = p.id_cat) WHERE (((s.private =0 OR s.private IS NULL ) AND (s.password = '' OR s.password IS NULL ) AND p.USER_ID_CAT !=0 AND p.approved =1) OR (p.approved =1 AND p.USER_ID_CAT =0 AND (c.view IS NULL OR c.view =1))) AND p.approved = 1 AND (p.title LIKE '%ANA%' ) GROUP BY p.id_picture ORDER BY p.id_picture DESC LIMIT 0,126;

MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '}gallery_pic as p
LEFT JOIN {db_prefix}members AS m ON (m.id_member = p.id_m...' at line 5

[SMFHacks]

Ah forgot about db_prefix replacements try

Code: [Select]

SELECT
p.id_picture, p.id_cat, p.commenttotal, p.keywords, p.filesize, p.thumbfilename, p.approved, p.views,
p.id_member, m.real_name, p.date, p.mature, v.id_picture as unread, mg.online_color,
p.title, p.rating, p.totalratings, p.totallikes, (p.rating / p.totalratings ) AS ratingaverage
FROM smf_gallery_pic as p
LEFT JOIN smf_members AS m ON (m.id_member = p.id_member)
LEFT JOIN smf_gallery_usersettings AS s ON (s.id_member = m.id_member)
LEFT JOIN smf_membergroups AS mg ON (mg.ID_GROUP = IF(m.ID_GROUP = 0, m.ID_POST_GROUP, m.ID_GROUP))
LEFT JOIN smf_gallery_log_mark_view AS v ON (p.id_picture = v.id_picture AND v.id_member = 1 AND v.user_id_cat = p.USER_ID_CAT)
LEFT JOIN smf_gallery_exif_data AS e ON (e.id_picture = p.id_picture)
LEFT JOIN smf_gallery_catperm AS c ON (c.id_group IN (1,8,9) AND c.id_cat = p.id_cat)

WHERE (((s.private =0 OR s.private IS NULL ) AND (s.password = '' OR s.password IS NULL ) AND p.USER_ID_CAT !=0 AND p.approved =1) OR (p.approved =1 AND p.USER_ID_CAT =0 AND (c.view IS NULL OR c.view =1))) AND p.approved = 1 AND (p.title LIKE '%ANA%' )
GROUP BY p.id_picture ORDER BY p.id_picture DESC
LIMIT 0,126


[Anmer]

Different error.

Is it a db path issue?

Code: [Select]

Error
SQL query: Copy Documentation


SELECT
p.id_picture, p.id_cat, p.commenttotal, p.keywords, p.filesize, p.thumbfilename, p.approved, p.views,
p.id_member, m.real_name, p.date, p.mature, v.id_picture as unread, mg.online_color,
p.title, p.rating, p.totalratings, p.totallikes, (p.rating / p.totalratings ) AS ratingaverage
FROM smf_gallery_pic as p
LEFT JOIN smf_members AS m ON (m.id_member = p.id_member)
LEFT JOIN smf_gallery_usersettings AS s ON (s.id_member = m.id_member)
LEFT JOIN smf_membergroups AS mg ON (mg.ID_GROUP = IF(m.ID_GROUP = 0, m.ID_POST_GROUP, m.ID_GROUP))
LEFT JOIN smf_gallery_log_mark_view AS v ON (p.id_picture = v.id_picture AND v.id_member = 1 AND v.user_id_cat = p.USER_ID_CAT)
LEFT JOIN smf_gallery_exif_data AS e ON (e.id_picture = p.id_picture)
LEFT JOIN smf_gallery_catperm AS c ON (c.id_group IN (1,8,9) AND c.id_cat = p.id_cat)

WHERE (((s.private =0 OR s.private IS NULL ) AND (s.password = '' OR s.password IS NULL ) AND p.USER_ID_CAT !=0 AND p.approved =1) OR (p.approved =1 AND p.USER[...]
MySQL said: Documentation

#1146 - Table 'ccfcbasi_KrystalOne.smf_gallery_pic' doesn't exist


[SMFHacks]

Could be if your database tables have a different prefix under than smf_
You would need to replace smf_ with your forums database prefix

[Anmer]

Thanks, that worked.  Prefix was "SM" not "SMF".

The SQL returned 126 rows compared to over 800 using GalleryPro search with Search Picture Description unchecked.  Sample results attached.

Not sure where we go next?

[Anmer]

Hmm, not sure about the results?  Just noticed the list displayed is incomplete.  The SQL shows:

LIMIT 0,126

If I change this to a higher number I still only get to see 126 rows but there are more available?

Edit

Resolved the limit issue and can confirm the rogue files are not included in the SQL results.

The GalleryPro search with Search Picture Description unchecked includes results that aren't in the SQL search.

[SMFHacks]

In the phpadmin screenshot the results look correct they all have "ana" inside the title column

[Anmer]

Yes I can confirm they're correct.  How do we fix the GalleryPro search?

[SMFHacks]

Hmm not sure why it would be broke then... Since that is the same query...the gallery used for your logs to pull the data.

[Anmer]

I just did a GalleryPro search for "ANA" and left all options unchecked.  It retuuned over 800 results!

[SMFHacks]

I would need a copy of your sm_gallery tables if you can export for phpmyadmin for me to further look into this.

[Anmer]

Do you need all the sm_gallery tables?

I can count 26.

[SMFHacks]

yes