mysql search for needle

***metulburr*** · Apr-16-2017, 08:13 AM

Im playing around with the old old forum database and realize that i am pretty bad at mysql commands. In this last command i am trying to extract all needles in a haystack of the keyword metulburr.

        
          
          
              
              metulburr@ubuntu:~$ sudo mysql -p
[sudo] password for metulburr: 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.17-0ubuntu0.16.04.2 (Ubuntu)
 
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| py_forum           |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
 
mysql> use py_forum;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
mysql> show tables;
+----------------------------+
| Tables_in_py_forum         |
+----------------------------+
| phpbb_acl_groups           |
| phpbb_acl_options          |
| phpbb_acl_roles            |
| phpbb_acl_roles_data       |
| phpbb_acl_users            |
| phpbb_ad                   |
| phpbb_attachments          |
| phpbb_banlist              |
| phpbb_bbcodes              |
| phpbb_bookmarks            |
| phpbb_bots                 |
| phpbb_config               |
| phpbb_confirm              |
| phpbb_disallow             |
| phpbb_drafts               |
| phpbb_extension_groups     |
| phpbb_extensions           |
| phpbb_forums               |
| phpbb_forums_access        |
| phpbb_forums_track         |
| phpbb_forums_watch         |
| phpbb_groups               |
| phpbb_icons                |
| phpbb_lang                 |
| phpbb_log                  |
| phpbb_login_attempts       |
| phpbb_moderator_cache      |
| phpbb_modules              |
| phpbb_poll_options         |
| phpbb_poll_votes           |
| phpbb_posts                |
| phpbb_privmsgs             |
| phpbb_privmsgs_folder      |
| phpbb_privmsgs_rules       |
| phpbb_privmsgs_to          |
| phpbb_profile_fields       |
| phpbb_profile_fields_data  |
| phpbb_profile_fields_lang  |
| phpbb_profile_lang         |
| phpbb_ranks                |
| phpbb_reports              |
| phpbb_reports_reasons      |
| phpbb_search_results       |
| phpbb_search_wordlist      |
| phpbb_search_wordmatch     |
| phpbb_sessions             |
| phpbb_sessions_keys        |
| phpbb_sitelist             |
| phpbb_smilies              |
| phpbb_styles               |
| phpbb_styles_imageset      |
| phpbb_styles_imageset_data |
| phpbb_styles_template      |
| phpbb_styles_template_data |
| phpbb_styles_theme         |
| phpbb_topics               |
| phpbb_topics_posted        |
| phpbb_topics_track         |
| phpbb_topics_watch         |
| phpbb_user_group           |
| phpbb_users                |
| phpbb_warnings             |
| phpbb_words                |
| phpbb_zebra                |
+----------------------------+
64 rows in set (0.00 sec)
 
mysql> show columns from phpbb_posts;
+------------------+-----------------------+------+-----+---------+----------------+
| Field            | Type                  | Null | Key | Default | Extra          |
+------------------+-----------------------+------+-----+---------+----------------+
| post_id          | mediumint(8) unsigned | NO   | PRI | NULL    | auto_increment |
| topic_id         | mediumint(8) unsigned | NO   | MUL | 0       |                |
| forum_id         | mediumint(8) unsigned | NO   | MUL | 0       |                |
| poster_id        | mediumint(8) unsigned | NO   | MUL | 0       |                |
| icon_id          | mediumint(8) unsigned | NO   |     | 0       |                |
| poster_ip        | varchar(40)           | NO   | MUL |         |                |
| post_time        | int(11) unsigned      | NO   |     | 0       |                |
| post_approved    | tinyint(1) unsigned   | NO   | MUL | 1       |                |
| post_reported    | tinyint(1) unsigned   | NO   |     | 0       |                |
| enable_bbcode    | tinyint(1) unsigned   | NO   |     | 1       |                |
| enable_smilies   | tinyint(1) unsigned   | NO   |     | 1       |                |
| enable_magic_url | tinyint(1) unsigned   | NO   |     | 1       |                |
| enable_sig       | tinyint(1) unsigned   | NO   |     | 1       |                |
| post_username    | varchar(255)          | NO   | MUL |         |                |
| post_subject     | varchar(255)          | NO   | MUL |         |                |
| post_text        | mediumtext            | NO   | MUL | NULL    |                |
| post_checksum    | varchar(32)           | NO   |     |         |                |
| post_attachment  | tinyint(1) unsigned   | NO   |     | 0       |                |
| bbcode_bitfield  | varchar(255)          | NO   |     |         |                |
| bbcode_uid       | varchar(8)            | NO   |     |         |                |
| post_postcount   | tinyint(1) unsigned   | NO   |     | 1       |                |
| post_edit_time   | int(11) unsigned      | NO   |     | 0       |                |
| post_edit_reason | varchar(255)          | NO   |     |         |                |
| post_edit_user   | mediumint(8) unsigned | NO   |     | 0       |                |
| post_edit_count  | smallint(4) unsigned  | NO   |     | 0       |                |
| post_edit_locked | tinyint(1) unsigned   | NO   |     | 0       |                |
+------------------+-----------------------+------+-----+---------+----------------+
26 rows in set (0.00 sec)
 
mysql> SELECT phpbb_posts WHERE post_text LIKE '%$metulburr%';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE post_text LIKE '%$metulburr%'' at line 1
mysql> 

            

        
      

but i am getting a syntax error

Error:mysql> SELECT phpbb_posts WHERE post_text LIKE '%$metulburr%';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE post_text LIKE '%$metulburr%'' at line 1

***zivoni*** · Apr-16-2017, 09:28 AM

Shouldnt there be FROM when table is referenced?

Output:
mysql> SELECT * FROM phpbb_posts WHERE post_text LIKE '%$metulburr%';

***ichabod801*** · Apr-16-2017, 10:15 AM

Select is for variables, from is for tables, where is for rows.

***metulburr*** · (This post was last modified: Apr-16-2017, 11:56 AM by metulburr.)

Quote:Shouldnt there be FROM when table is referenced?

I thought i had it at one point. Must of twisted the commands

So here is one post given from the command
SELECT * FROM phpbb_posts;

Quote:having trouble reading a binary file | Hello there!
Brand new to the forum, and to Python. As background... I have not coded in *years*, since my amiga days! Wanted to get back into coding, especially games, and after some research decided that Python and Pygame are the way to go.

Anyway, this has me stumped...

For a simple game (just to get me back in the habit), I have created a 'map' using 'tile studio', which it saves maps as raw binary files; i.e. lists of numbers corresponding to a tile. Unfortunately, as I have slightly over 256 different tiles, they are stored as words (doubles).

The best code I could come up with to read this file is this...

[code:2ig6rwl4]metromap=[]
metrofile=open('metromap.bin','r')
for f in range(0,2239):
code=0
s=metrofile.read(1)
if len(s)>0:
code=ord(s)
s=metrofile.read(1)
if len(s)>0:
code+=ord(s)*256
metromap.append(code)
metrofile.close()
print metromap # just for debug purposes [/code:2ig6rwl4]

Sorry for 'f' but I am a BASIC programmer at heart!

The file is 4480 bytes long. The problem occurs at the 2040th byte (i.e. f=1020)

Suddenly it doesn't want to read the rest of the file. As the forum does not allow .bin files to be attached (I tried renaming it to .jpg, but it saw right through that ruse!), I have *attempted* to include the 'bin' code below. I can supply the file to anyone curious. The next four words after the problem are '1', '2' and '3', but surely that is coincidence!

Everything up until that point is fine. I have tried various permutations of the above code. The most obvious, using the 'rb' flag on the 'open' statement and ignoring strings (I like strings!), causes python to lock up at that point. Removing the 'if len(s)' condition, causes an exception:

[code:2ig6rwl4]ord() expected a character, but string of length 0 found[/code:2ig6rwl4]

Something is happening here that my stupid brain doesn't understand... any help would be much appreciated! S

and if I search for BASIC it comes up dry

        
              mysql> SELECT * FROM phpbb_posts WHERE post_text LIKE '%$BASIC%';
Empty set (0.82 sec)
mysql> SELECT * FROM phpbb_posts WHERE post_text LIKE '%$\bBASIC\b%';
Empty set (0.82 sec)
mysql>

***zivoni*** · Apr-16-2017, 12:01 PM

Why '$'?

Output:
SELECT * FROM phpbb_posts WHERE post_text LIKE '%BASIC%';

***metulburr*** · Apr-16-2017, 12:06 PM

(Apr-16-2017, 12:01 PM)zivoni Wrote: Why '$'?

son of a ***. I thought it was a mysql thing. Every time i researched it, it had that before the keyword....i guess they were looking for that too then. lol.

mysql search for needle

User Panel Messages

Announcements