Resolved: Help with custom wordpress query


#1

I have a custom table in my Wordpress site that is created by a form plugin. I need to do a custom query to get the data from this custom table. There are several entries in the table with the same entry_id field as per the attached screenshot, one entry for every field completed on the form. What I need to do is to grab all the entries for a particular month and display the first and last name and telephone number on a table on a page. I am having problems creating the query that will pull this data from the table. I can easily get the entry_id where the value is the month I am looking for.

I can’t get my head around getting the other data I require without performing other multiple queries. I am sure that there must be a way to do this easily but I can’t work it out. Anyone help?

This is the code I have to pull the entry_id by month
$query ="
SELECT *
FROM wp_cf_form_entry_values
WHERE field_id = ‘fld_6840543’
AND VALUE = ‘October’
ORDER BY entry_id ASC";
$results = $wpdb->get_results($query,OBJECT);

This is the table structure screenshot with real identifiable data changed 26%20copy


#2

I’ve solved this problem several times in the last 2 years. It’s easy. Don’t use a damn plugin.

You might be able to use some kind of date casting to turn your month strings into real dates (https://stackoverflow.com/questions/26165882/mysql-cast-as-date) … but that isn’t really your problem.

I would say your data table is oriented in the wrong direction. This query would be easy if your custom table fields were: entry_id, first_name, last_name, email, mobile, mem_no, group, comments, thedate
where thedate is a DATETIME type of field.
(I called it “thedate” because you’re not allowed to call it date or time or datetime or timestamp because those are reserved words)

Then you would only need to query SELECT first_name, last_name, mobile FROM mytable WHERE thedate >= '2018-10-01' AND thedate < '2018-11-01'

Now if you don’t actually care about which YEAR the data was entered you can search on specifically the month part of the date field to get everything ever entered in October of every year.

SELECT first_name, last_name, mobile FROM mytable WHERE MONTH(thedate) = 10


#3

Thanks I continued development of this and have managed to do it with two queries, so not too database intensive.