|
Joined: Aug 2004
Posts: 460
Addict
|
Addict
Joined: Aug 2004
Posts: 460 |
Need to pull up a list of all different/unique post IPs by a certain user. Could someone please let me know what the correct MySQL command for this is?
|
|
|
|
Joined: Dec 2003
Posts: 6,562 Likes: 78
|
Joined: Dec 2003
Posts: 6,562 Likes: 78 |
Should be something like: SELECT DISTINCT `POST_POSTER_IP`, `USER_ID` FROM `ubbt_POSTS` WHERE `USER_ID` = 2 change the user id for the member in question Also the ubbt prefix is the default so if you used something different you will need to change that too.
Blue Man Group There is no such thing as stupid questions. Just stupid answers
|
|
|
|
Joined: Apr 2007
Posts: 3,940 Likes: 1
Former Developer
|
Former Developer
Joined: Apr 2007
Posts: 3,940 Likes: 1 |
little addition ( optional ) SELECT
COUNT(POST_POSTER_IP),POST_POSTER_IP, USER_ID
FROM ubbt_POSTS
WHERE USER_ID = 2
GROUP BY POST_POSTER_IP gives you a count of how many times each IP was used
|
|
|
|
Joined: Dec 2003
Posts: 6,562 Likes: 78
|
Joined: Dec 2003
Posts: 6,562 Likes: 78 |
I like it when we get enhancements. More better!
Blue Man Group There is no such thing as stupid questions. Just stupid answers
|
|
|
|
Joined: Aug 2004
Posts: 460
Addict
|
Addict
Joined: Aug 2004
Posts: 460 |
Both excellent, many thanks guys! Just thinking how I can factor in the date here as well (since IP lookup in the CP focuses on registration or last post)... Maybe one more query that would list all post IPs (this time not just unique ones obviously) in chronological order with the time&date next to each one?
|
|
|
|
Joined: Dec 2003
Posts: 6,562 Likes: 78
|
Joined: Dec 2003
Posts: 6,562 Likes: 78 |
Well the timestamp is in binary format not sure how to convert it in a query but SELECT `POST_POSTED_TIME`, `POST_POSTER_IP`, `USER_ID` FROM `ubbt_POSTS` WHERE `USER_ID` = 2 ORDER BY `POST_POSTED_TIME` ASC
Blue Man Group There is no such thing as stupid questions. Just stupid answers
|
|
|
|
Joined: Dec 2003
Posts: 6,562 Likes: 78
|
Joined: Dec 2003
Posts: 6,562 Likes: 78 |
Just a fyi, As a cheat tool I usually will use phpmyadmin to create a query I am not sure of what the result would be. It has a search tool that is kinda a gui interface for most all query functions.
Blue Man Group There is no such thing as stupid questions. Just stupid answers
|
|
|
|
Joined: Aug 2004
Posts: 460
Addict
|
Addict
Joined: Aug 2004
Posts: 460 |
Yes, in the "POST_POSTED_TIME" column I get figures like 1359508389 and 1359889641. Is there a way to pull the time and date using a regular query that I could run from the board's admin CP?
|
|
|
|
Joined: Apr 2007
Posts: 3,940 Likes: 1
Former Developer
|
Former Developer
Joined: Apr 2007
Posts: 3,940 Likes: 1 |
select FROM_UNIXTIME(POST_POSTED_TIME) returns a perty date/time
|
|
|
|
Joined: Aug 2004
Posts: 460
Addict
|
Addict
Joined: Aug 2004
Posts: 460 |
Many thanks, works like a charm. Just one more query that I need comes to mind, something that would allow me to input a particular IP address and get all posts with that IP. So I would input the IP and the system would pull out the following data: 1. data/time of post 2. user ID 3. user's display name
|
|
|
|
Joined: Aug 2004
Posts: 460
Addict
|
Addict
Joined: Aug 2004
Posts: 460 |
One if I may one more short one, where I could input just the post # and get the exact time (including seconds) of the post.
|
|
|
|
Joined: Dec 2003
Posts: 6,562 Likes: 78
|
Joined: Dec 2003
Posts: 6,562 Likes: 78 |
Like I said before if you don't know queries that well and you have Myphpadmin in your host control panel there is a gui interface to run queries all day long.. Might be time to explore that option The current samples here have most all you need you just need to move them around a bit.
Blue Man Group There is no such thing as stupid questions. Just stupid answers
|
|
|
|
Joined: Jun 2006
Posts: 16,299 Likes: 116
|
Joined: Jun 2006
Posts: 16,299 Likes: 116 |
SELECT FROM_UNIXTIME(`POST_POSTED_TIME`)
FROM `ubbt_POSTS`
WHERE `POST_ID` = '1'
|
|
|
|
Joined: Aug 2004
Posts: 460
Addict
|
Addict
Joined: Aug 2004
Posts: 460 |
Thanks Gizmo, works perfectly! Ruben, I would prefer to run things via the Threads CP than have to log on to PHPMyAdmin. Especially as all these things CAN be done via the CP alone. What db command would I need to use to input just the IP address and receive the following: 1. data/time of post 2. user ID 3. user's display name I could try experimenting with the commands I've seen thus far, but none of these brought up the display name...
|
|
|
|
Joined: Jun 2008
Posts: 48 Likes: 1
Newbie
|
Newbie
Joined: Jun 2008
Posts: 48 Likes: 1 |
Look for various MySQL tutorials available on the web.
There is a lot of information there on MySQL functions like the one to convert Unix time stamp. In addition they cover the basic structure to format the query.
Lastly you should spend some time with PHP Admin to browse the tables and the fields in the tables. That will give you an idea of the fields you can use in your queries to get the results you are looking for.
Without this bit of research you are going to end up asking "how do I" at virtually every step. Just trying to help you be self sufficient.
The reason people are suggesting PHP Admin is because the software has a query-by-example form where you enter your desired selection criteria and output fields and IT writes the SQL for you. If you like the results then copy the SQL and save it for future use.
Abbott
|
|
|
|
Joined: Aug 2004
Posts: 460
Addict
|
Addict
Joined: Aug 2004
Posts: 460 |
Thanks, will give it a look and see if I can "compose" some queries this way.
On a side note, it would be a useful feature in Threads to see some of these queries made available in the CP. Stuff like looking up posts with a certain IP should be standard.
|
|
|
|
Joined: Dec 2003
Posts: 6,562 Likes: 78
|
Joined: Dec 2003
Posts: 6,562 Likes: 78 |
Thanks, will give it a look and see if I can "compose" some queries this way.
On a side note, it would be a useful feature in Threads to see some of these queries made available in the CP. Stuff like looking up posts with a certain IP should be standard. Something I requested some years ago to include some useful sql commands in the default zip file but it never happened. Or at the very least a new forum with useful MYSQL queries.
Blue Man Group There is no such thing as stupid questions. Just stupid answers
|
|
|
|
Joined: Jun 2013
Posts: 3
stranger
|
stranger
Joined: Jun 2013
Posts: 3 |
|
|
|
Bots
by Outdoorking - 04/13/2024 5:08 PM
|
|
|
|
|
|
|
|