Jump to content

Saved Queries


DawPi

Recommended Posts

thumb-5d25fa52ed56c1ea8d59290d50b8c48b-s



File Name: Saved Queries

File Submitter: DawPi

File Submitted: 26 Mar 2009

File Category: Maintenance

Supported Versions: IP.Board 3.1.x, IP.Board 3.2.x, IP.Board 3.3.x, IP.Board 3.4.x



Gives you a place to store commonly used SQL queries so that they can be re-run later just by clicking a Run Query button as opposed to saving the full SQL code and running it manually in your SQL Toolbox. The same security checks that IPB uses in it's SQL Toolbox (making sure you can't DROP or FLUSH tables, or delete data out of admin_login_logs) is also enforced when using this application.

In addition, this application supports the ability to add 'parameters' to your queries. When you set up or edit a query, you can define these parameters, which are pieces of data you provide when you go to actually run the query. For example, you can set up a query that finds the 5 latest posts of a member, and the parameter would be the ID# of the member to find the posts for. When you run a query that contains parameters, it prompts you for this required information, which it then uses in the actual execution of the query.



Click here to download this file

Link to comment
Share on other sites

  • 1 year later...
  • 6 months later...
  • 1 year later...

Michael - this was working really well for me until recently (maybe w/ the recent IP Board update a couple of weeks back?). I have two saved queries. Running them works fine, but when I try and edit either of them, I receive a "You did not enter a Title for the Query" error, and the change doesn't take. Likewise, I receive the same error when I try and create a NEW query.

Any idea what's up?

Dave

post-375389-0-19417300-1362603086_thumb.

Link to comment
Share on other sites

Which version of IP.Board are you on? My main and test boards are still 3.4.2 and I can't reproduce any problem like this on either one.

Something funky with my work laptop was causing this. It's right as rain now on my home desktop. Ugh!

Link to comment
Share on other sites

  • 4 months later...
  • 6 months later...

I've tried this for a bit now and can I see a lot of potential and easier statistics in using it :smile:

But I have two things I wish to take up:

Bug: Clicking the title doesn't parse the query params, it runs the query without the params. I have to click the green "Run query"-icon on the right side in order to be prompted with the value of the query params.

Feature idea: Query execution time could be useful to see for each query (I have set up three queries separated by ; in the same method now)

Edit: additional feature idea: default values for the params.

Link to comment
Share on other sites

Bug: Clicking the title doesn't parse the query params, it runs the query without the params. I have to click the green "Run query"-icon on the right side in order to be prompted with the value of the query params.

Feature idea: Query execution time could be useful to see for each query (I have set up three queries separated by ; in the same method now)

Done.

Link to comment
Share on other sites

Great!

Just that you misunderstood my first feature idea :P But this is good too.

What I meant was to display the time it took for the database to respond to each individual query on the results screen, the query execution time. You could include it after the part where it displays the query it ran.

Link to comment
Share on other sites

  • 1 month later...

Not sure what I'm doing wrong but when I try to use this i get an error that the table doesn't exist.

I even tried the example you had in the screenshots and get this error:

Table 'mydb_ipb.topics' doesn't exist

edit. seems like I wasn't using the full table name as I should have been, I knew it had to be simple.

Link to comment
Share on other sites

  • 3 weeks later...
suggestion: add subquery while fetch main query...
main query is for example

SELECT t.tid, t.starter_id, pf.field_18
FROM ibf_topics t
LEFT JOIN ibf_pfields_content pf ON ( pf.member_id = t.starter_id )
WHERE t.forum_id =<#FORUM_ID#>
LIMIT <#LIMIT#>
Subquery run while fetching main query
UPDATE ibf_pfields_content
set field_18 =<#MAIN_QUERY#>.tid
where member_id=<#MAIN_QUERY#>.starter_id and (field_18 is null or field_18='')
Now i have solved with this query
UPDATE ibf_pfields_content pf JOIN
(
     SELECT t.tid, t.starter_id, pf.field_18
     FROM ibf_topics t
     JOIN ibf_pfields_content pf ON pf.member_id = t.starter_id
     AND (
     pf.field_18 IS NULL
     OR pf.field_18 = ''
     )
     WHERE t.forum_id =4
     LIMIT 0 , 1000
) m
 ON pf.member_id = m.starter_id
SET pf.field_18 = m.tid
WHERE pf.member_id=m.starter_id
Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...