Custom Queries with parameters

[Marco van Herwaarden]

Custom Queries - version 1.02
================================================== ========================

Hack: Custom Queries
Version: 1.02
vB Version: 3.5.0 RC3
Author: MarcoH64
Description:
This hack will let you store SQL-statements that you use more then once in the database for later use. You can see it as an
extension the the standard predefined queries, but now you can define your own, use parameters and parse TABLE_PREFIX.

Features:
- Store SQL-statements for later use
- Use any number of parameters in your SQL-statements
- Query execution is done by standard vB scripts, so normal vB security is active on them.
- Full Admin Help (needs to be installed seperate)
- Can handle TABLE_PREFIX
- Quick Query for executing 1 time queries (including TABLE_PREFIX parsing)
- Import/Export groups of safed Queries

Donations are always welcome to Paypal: marcoh64 @ gmail.com

Change log:
v1.02 (18-9-2005)
- Added 'groupname' for queries. (used when exporting queries)
- Export Queries to an XML-File
- Import Queries from XML-File
- Direct safe Quick Query

v1.01 (16-9-2005)
- Parse TABLE_PREFIX
- Added Quick Query
- Added TABLE_PREFIX to install code

v1.00 (13-9-2005)
- Update: Admin help was not in product xml-file, see readme.txt

================================================== =========================

Installation:
- Upload all files from the upload directory to your forumhome directory.
- Install the product XML-File
- If Admin Help is wanted, also upload the file mh_cq-adminhelp.xml in your Admin Help Manager (only accessible in debug mode, or
by going to www.myboard.com/admincp/help.php?do=files (http://www.myboard.com/admincp/help.php?do=files))

================================================== =========================

When using parameters in a query, you can use the parametername surrounded by brackets (ie. {parametername})
in the SQL-statement.

Example:
Parametername: username

Query:
SELECT * FROM user WHERE username LIKE '%{username}%'

Attached is also an example export file with a demo query.

[Oblivion Knight]

Very nice, although I don't think I have a use for this (yet).

Thanks for sharing Marco! :)

[Boofo]

booboo? LOL

Just what I was looking for. ;)

[Marco van Herwaarden]

Update: Admin Help was not in productfile, see readme.txt on how to fix (just upload the adminhelp.xml file into the Admin Help Manager)

[nexialys]

my dream would be to be able to just have the " . TABLE_PREFIX . " element parsed by this SQL manager... would be simplier for all copy/pastes!

[Marco van Herwaarden]

my dream would be to be able to just have the " . TABLE_PREFIX . " element parsed by this SQL manager... would be simplier for all copy/pastes!S$!t, that reminds me that i added the install code for the product last minute, and i didn't add a table prefix there.

* Marco van Herwaarden makes a note to fix that tomorrow.

Hmm parsing table prefix could be done easily, will have a look. Although my hack is more for queries that are run more then once, not for 1 time queries. You can see it as extending the buildin predefined queries with your own, plus that you can use parameters on them.

[Boofo]

S$!t, that reminds me that i added the install code for the product last minute, and i didn't add a table prefix there.

* MarcoH64 makes a note to fix that tomorrow.

Hmm parsing table prefix could be done easily, will have a look. Although my hack is more for queries that are run more then once, not for 1 time queries. You can see it as extending the buildin predefined queries with your own, plus that you can use parameters on them.

But having table prefix in the query can test it make sure it will work on the forum before using it anywhere. ;)

[Marco van Herwaarden]

Lol, here i go again, what started as an idea for a small simple plugin that would let you add your own queries to the predefined list (which wasn't possible since there are no hooks in that file) seems to be growing into something bigger then i ever intended. And i already have so many ideas for my existing hacks that i still need to finish.

But with only 1 install, i don't think i need to put too much work in this. ;)

[Boofo]

Thanks alot, buddy! ;)

[nexialys]

But with only 1 install, i don't think i need to put too much work in this. ;)
* nexialys did not click install because he never do it...
but you can count me in...

i can even add some more requests on ALL your hacks MarcoH64! (that 64 remembers me of a certain Commodore!)

[Marco van Herwaarden]

New version released
Change log:
v1.01 (16-9-2005)
- Parse TABLE_PREFIX
- Added Quick Query

[Marco van Herwaarden]

my dream would be to be able to just have the " . TABLE_PREFIX . " element parsed by this SQL manager... would be simplier for all copy/pastes!Your dream has come true, so what are you waiting for.

[Boofo]

How about this?

Use descriptions to describe what the query is for and list it on the main page next to the query title. And then have a Notes variable (and link) that will allow you to add any notes on parameters used or anything like that. ;)

Can I get someone esle to agree to this as I was told it might be done with another person wanting the same thing? ;)

[Marco van Herwaarden]

Version 1.02 released:
Change log:
v1.02 (18-9-2005)
- Added 'groupname' for queries. (used when exporting queries)
- Export Queries to an XML-File
- Import Queries from XML-File
- Direct safe Quick Query

I will try to update the screenshots tomorrow.

PS If someone create some awesome queries, feel free to export and share them in here. If you want i will link them in the first posts.

[Marco van Herwaarden]

Screenshots updated

(Sigh they don't stay in original order)

[Floris]

Nice job Marco :)

And I am curious at the queries people store. Perhaps the generic ones could be posted here :)

[Marco van Herwaarden]

Thanks Floris,

Have even ideas for expanding this one, but since i am a bit short on time lately, id din't have the chance yet (also because it didn't get as much installs as i expected).

Yes posting some queries here has also crossed my mind, and i still plan to create a first set myself of common queries.

[Zia]

hello will it works for vb3.5.2
Obiviously it should reduce page loading time...i think..
Am i right ??

Thanks

[Marco van Herwaarden]

hello will it works for vb3.5.2 Yes it should work for 3.5.2
Obiviously it should reduce page loading time...i think..I am sorry, but i don't understand what this hack has to do with pageloads?

[Zia]

Yes it should work for 3.5.2
I am sorry, but i don't understand what this hack has to do with pageloads?

truly....im thinking...if sql query qty reduced....say:
an forum home page need 24 sql qery to open..
now using this hack if sql query reduced to 16 ..
should the page will load bit faster than before?

Should it??

Pls let me know..
Hope u wont mind to describe some more about this one..controle/rreduce sql query..what good result it will bring...
Thanks

[Marco van Herwaarden]

This hack has nothing to do with queries executed on pages, not does it in any way influence pageloads.

In vBulletin you have the option to run some predefined queries from the AdminCP. This hack let you define your own queries to be run manually.

I don't understand what in the hacks description make you think this got anything to do with queries run when a page load.

[Uno]

works it for vb3.6? (3.6.3)
Thank

[Uno]

works it for vb3.6? (3.6.3)
Thank

Yes i have try now on local

[Boofo]

Will this work on 3.7.1?

[EvilHawk]

Yes, it works fine!

[Boofo]

Thank you, sir. ;)

[Uno]

Work on 3.8.*?

[Marco van Herwaarden]

I see no reason why it should not work on any 3.5 or higher version.

[Uno]

I see no reason why it should not work on any 3.5 or higher version.

Oh yes i have try now on local (vb 3.8.1) and it go
Thank you Marco, this mod is simple, i use not much, but for query fast, without open phpMyAdmin or shell is very useful and allow of to remember easily the query more used :up:

[Uno]

No there is a little problem, export not work but i found solution

edit file mh_cq.php

$xml = new XMLexporter();

and replace with

$xml = new vB_XML_Builder($vbulletin);

and mod is good also for 3.6.0 and up