Plugin Author
Eli
(@scheeeli)
Must be some kind of syntax error or something in the query first query that doesn work right in the getvar function of the wpdb object. Try taking out any line break because WordPress will insert
or <p> tags there and that will break the SQL. You could also look in your server’s error_log file to see if there are any other clues there.
Let me know what you find.
Aloha, Eli
Aloha,
Thank you so much for your quick response. I removed the line break in the simple sql and it worked.
I tested with a more complex sql that does not have any line breaks and it does not work in the [sqlgetvar] but does work in the phpMyAdmin sql. I wonder if you have any can see any issues with this sql:
SELECT sum(members) members FROM (SELECT count( last_name_1 ) members FROM wp_participants_database WHERE UPPER(active)=’YES’ UNION SELECT count( last_name_2 ) members FROM wp_participants_database WHERE last_name_2 != “” AND last_name_2 IS NOT NULL AND upper(active) = ‘YES’) m
Mahalo, Jeri
Plugin Author
Eli
(@scheeeli)
Aliases should be announced with the keyword “AS”, and I don’t know why you would have an “m” at the end of the query. Try this:
SELECT sum(members) AS members FROM (SELECT count( last_name_1 ) AS members FROM wp_participants_database WHERE UPPER(active)=’YES’ UNION SELECT count( last_name_2 ) AS members FROM wp_participants_database WHERE last_name_2 != “” AND last_name_2 IS NOT NULL AND upper(active) = ‘YES’)
Actually I’m not even sure that UNION will work but try it, if not try startign with a smaller version of a single part of that same query that work, and then build it up one piece at a time.
Sorry I am new to mysql, but believe the issue is in the where clause, not quite sure I understand why but here is what I have found:
members = [sqlgetvar]SELECT count(*) FROM wp_participants_database [/sqlgetvar]
members1 = [sqlgetvar]SELECT count(*) FROM wp_participants_database WHERE UPPER(active)=’YES’ [/sqlgetvar]
The page displays the following:
members = 449
members1 =
in the phpadmin mysql I get the following:
SQL query
SQL query:
SELECT count( * )
FROM wp_participants_database
WHERE UPPER( active ) = ‘YES’
Profiling [ Edit ] [ Explain SQL ] [ Create PHP Code ] [ Refresh ]
count(*)
398
Thanks for everything
Jeri
I forgot to mention I have tried both single and double quotes around the YES
Plugin Author
Eli
(@scheeeli)
single quotes should be fine around strings. try using back-ticks ` around your column names, like active.
I tested using a different field just to make sure active was not the issue. The count should be 1 and is 1 in the phpadmin sql screen for all the selects with first_name_2.
members = [sqlgetvar]SELECT count(*) FROM wp_participants_database [/sqlgetvar]
members1 = [sqlgetvar]SELECT count(*) FROM wp_participants_database WHERE account_number = 1877 [/sqlgetvar]
members2 = [sqlgetvar]SELECT count(*) FROM wp_participants_database WHERE UPPER(first_name_2) = ‘JERI'[/sqlgetvar]
members3 = [sqlgetvar]SELECT count(*) FROM wp_participants_database WHERE UPPER(first_name_2) LIKE ‘JERI%'[/sqlgetvar]
members4 = [sqlgetvar]SELECT count(*) FROM wp_participants_database WHERE UPPER(first_name_2) LIKE “JERI%”[/sqlgetvar]
members5 = [sqlgetvar]SELECT count(*) FROM wp_participants_database WHERE UPPER(first_name_2) LIKE ‘JERI%'[/sqlgetvar]
members6 = [sqlgetvar]SELECT count(*) FROM wp_participants_database WHERE UPPER(first_name_2) LIKE “JERI%”[/sqlgetvar]
RESULTS:
members = 449
members1 = 1
members2 =
members3 =
members4 =
members5 =
members6 =
Plugin Author
Eli
(@scheeeli)
Sorry I missed this before but UPPER is not a function in MySQL, I think you want to use UCASE 😉
I changed them to UCASE and still no result for the queries.
I had found on the internet references to UPPER and LOWER for mySQL:
FROM: http://www.sqlinfo.net/mysql/mysql_function_upper_lower.php
You can use either the MySQL UPPER() or LOWER() functions to format columns in your SQL SELECT. An example they give on the page is:
Return rows for a column case insensitive
It is common practice to make a column case insensitive to ensure that you return all of the desired rows.
SELECT *
FROM Courses
WHERE LOWER(education_delivery_method) = ‘classroom’
I guess I have a lot to learn about mySql.
Thanks for all your ideas.
Plugin Author
Eli
(@scheeeli)
I think I found out why you are having this problem. WordPress automatically changes your content and replaces your single quotes with something they call smart quotes 🙁
You will need to remove the filter for the wptexturize function before this shortcode will work with quotes in it. You’ll need to add this line of code to a PHP file where it can run in the init action hook:
remove_filter (‘the_content’, ‘wptexturize’);
If you can’t find a better place to put this code you can add it to my plugin inside the ELISQLREPORTS_init function on line 899 of elisqlreports/trunk/index.php
Aloha,
Thank you so much for your response. I added your code above into elisqlreports/trunk/index.php and it worked!
What a great plugin and support, I will try to get some funds to donate to your efforts. (By the way is there a way to export a csv from your reports?)
Thanks again for everything.
Jeri
Plugin Author
Eli
(@scheeeli)
There is not an option to export directly to a CSV, however I have found that most browsers will allow you to select the entire contents of an HTML Table and copy (CTRL + C) those content, which can then be pasted into a spreadsheet and saved as a CSV file.
Aloha, Eli
