query by date with other entries help

[samoht]

Hello all,

 

I have a database table that stores form info from my website. Unfortunately the form plugin uses two tables (contact_form_submit and contact form_submit_data) to store the filled out forms. I want to query the contact_form_submit_data table and look for any values with a date = the current month and then grab all the other values with the same foreign key value.

 

Here are the two table structures:

CREATE TABLE IF NOT EXISTS `wp_contactform_submit_data` (
  `id` int(11) NOT NULL auto_increment,
  `fk_form_joiner_id` int(11) NOT NULL,
  `form_key` varchar(100) NOT NULL,
  `value` text NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `fk_form_joiner_id` (`fk_form_joiner_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1139 ;

 

CREATE TABLE IF NOT EXISTS `wp_contactform_submit` (
  `id` int(11) NOT NULL auto_increment,
  `fk_form_id` varchar(50) NOT NULL,
  `session_id` varchar(50) NOT NULL,
  `submit_date` datetime NOT NULL,
  `client_ip` varchar(20) NOT NULL,
  `client_browser` varchar(100) NOT NULL,
  `request_url` varchar(100) NOT NULL,
  `read_flag` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `fk_form_id` (`fk_form_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=91 ;

 

Now the date info I want to query is stored in the "value" field as text not as a date! Today's date would look like this = 2011 02 04

So how to I query a text field just for the center two characters?

 

Then I do I include all the other values from the table with the same fk_form_joiner_id ?

 

Thanks,

[DarkKnight2011]

hi,

 

im not really sure why you are using 2 tables for this but anyway you can select what you need by doing

 

SELECT * FROM `wp_contactform_submit_data`sd
    INNER JOIN `wp_contactform_submit` s
    ON sd.`fk_form_joiner_id` = s.`fk_form_id`
    WHERE s.`submit_date` = DATE_FORMAT(now(), '%Y %m %d')

 

hope this helps

[samoht]

Actually the submit_date is not what I need. That is just when a user filled out the form. The form has a javascript datepicker that populates a text field and is saved as a text value in the wp_contactform_submit_data table.

 

I am using two tables because that is how the form plugin was designed. I suppose I could code the form from scratch, but I was hoping I wouldn't have to do that.

 

I don't think I need an INNER JOIN because the wp_contactform_submit doesn't have any of the values I need?

[samoht]

@darkknight2011

 

does your query match only the exact day?

DATE_FORMAT(now(), '%Y %m %d')

 

or would this pull all entries with the similar month?

[DarkKnight2011]

that particular query just matches today, i was just giving you an example of what you could do.

 

so the query will depend on exactly where the date is saved (which field is it?) and what you want to receive from your query, if its for all submissions this month then you could do something like this...

 

SELECT * FROM `wp_contactform_submit_data`sd
    INNER JOIN `wp_contactform_submit` s
    ON sd.`fk_form_joiner_id` = s.`fk_form_id`
    WHERE SUBSTRING(sd.`value`, 6, 2) = DATE_FORMAT(now(),'%d')

 

The above code may not just work, you will need to change parts, im doing this from my phone so i cant confirm it works as expected but it should at least give you a good starting point, i have also just presumed that the date is stored in value, you will need to correct that

 

Hope it helps, let me know if you need any further help, someone may even show you a more efficient way of doing it

[samoht]

@darkknight2011,

 

Thanks for the help I slightly changed the query to this:

SELECT * FROM `wp_contactform_submit_data`sd
    INNER JOIN `wp_contactform_submit` s
    ON sd.`fk_form_joiner_id` = s.`id`
    WHERE SUBSTRING( sd.`value` , -5, 2 ) = DATE_FORMAT( now( ) , "%m" )

 

Which does pull records from both tables but the problem is that it only pulls the 1 value and 1 form_key value from the contactform_submit_data table because that is "WHERE" clause. I think the WHERE clause should be on the fk_form_joiner_id and then some kind of IF statement for the fk_form_joiner_id