Welcome Guest, Not a member yet? Register   Sign In
Do I need to bind tables? Please help
#1

(This post was last modified: 11-21-2014, 09:00 AM by alexandervj.)

I have a database app which gets info from a mySQL table that is generated by WordPress. It has a slightly strange structure, but I dont have control over the table structure (second image). My database app gets data from that table and displays it in rows (see other attached image). All the data in those rows in the image are from that table except for the "Sent" column. The sent column is a bool value from another table which has a foreign key (submit_time) that can refer to each of the rows in the first table.

I'm trying to make it so that in the image attached (first image), the sent column respectfully shows whether the submit time for each row has been marked "Sent" or not, if it has been marked sent then it shows a check marked image, if not it shows a non check marked image.

Im not sure how to go about this since these are two different tables. I wish I had more control over the structure of the first table and could modify it to contain a "Sent" column, it would make things much easier but I'm pretty sure I cant do that.

Any help you can give I appreciate it
Reply
#2

(This post was last modified: 11-21-2014, 10:12 AM by bclinton.)

It sounds like you are describing a simple SQL join.

I don't like the sounds of submit time as your foreign key though.  How can you be sure to avoid collisions?  Does the first table have an ID field you can join on? Or some other primary or unique key?
Reply
#3

I agree, :/ thats the problem with the way the WordPress plugin builds the table that holds the data. Theres no id or unique key other than the submit time, which is calculated down to a fraction of a second. We dont have that many submissions (maybe up to about 5 or 6 per day) so they SHOULD be unique, but there is the possibility of them being not unique. I might just want to build my own WordPress plugin in the near future for this
Reply
#4

(This post was last modified: 11-21-2014, 11:27 AM by bclinton.)

Also it looks like there is a record for every form element, so not only is the time not unique, but by design there are multiple records with the same time.

It looks like you want a "select distinct submit_time" here, and join on that.  Check out the plug-in code - that's probably how they are doing it. Take their query and just join your table on the submit time.  see below

It's not ideal, but since you get very few submissions there's probably almost no danger of corrupt data or results.
Reply
#5

(This post was last modified: 11-21-2014, 07:36 PM by bclinton.)

Actually, I'm probably wrong in my previous post.  I think you probably want a "group by" clause rather than selecting distinct.   Group by submit_time.

something like

select table_a.submit time, table_a.other_fields etc... table_b.sent
from table_a
join table_b on table_b.submit_time = table_a.submit_time
group by table_a.submit_time

One suggestion: when you are not quite yet sure what you are doing with a query, sometimes it's best to just work in MySql or PhpMyAdmin until you get it right, then put it into Active Record syntax in your application once you have it working.
Reply
#6

Thanks very much! that helps a lot. I will try that tomorrow and let you know how it goes
Reply




Theme © iAndrew 2016 - Forum software by © MyBB