What is a best way to maintain a table?

Yes indeed, the exhibitors table has one row per exhibitor and that is exactly what I was looking for. However, during the process of pulling the contact details from the orders table, only the first entry relative to each exhibitor has been selected. As you know, the orders table contains many Ids for one exhibitor.

Example: let's say exhibitors table has 4 different exhibitors (a, b, c, d) and orders table has 4 exhibitors visiting several shows (1, 2, 3). So a is visiting shows 1, 2, 3 and exhibitor b is visiting show 3 and etc. It occurs that exhibitor "a"has changed its contact details when visiting show 3.

My question is how should be the code to get it selects the last entry of exhibitor a?
 
You'd have to order the select which populates the table ...

INSERT INTO exhibitor (Exhibitor, Custname, CustPhone, CustEmail) SELECT Exhibitor, Custname, CustPhone, CustEmail FROM orders GROUP BY Exhibitor ORDER BY id DESC

Note that as you are having to rebuild that table, and now I know you have other fields to insert, I've modified this query to do all the fields in one go.

I haven't sanity checked the field names that hard, so you'll need to make sure it's correct. But that's the approach you need. Rather than doing a "DISTINCT Exhibitor", you are grouping by Exhibitor, ordering on the id in reverse (so last created should be the selected row).

-- hugh
 
I have no clue. I've really honestly gone as far as I can on this on a subscription basis. There simply is a limit on how much time I can put in for the cost of a sub, especially in this case where it's really not to do with Fabrik.

But if it's a join, then have you set that element up as a join to the exhibitors table?

I can help, but it'll have to be on an hourly rate basis.

-- hugh
 
Thank you for coming back to me. I understand your point of view. Time is money! how much is your hourly rate?
I'm sure it is something to do with the setup of the databasejoin element. I managed to set it up but now when I add a new entry and save, I can see the entry on the designated list. However, as soon as I edit this entry, the menu pre-filter does not apply to it. All the previous entries of this list don't have this issue.
 
Find me on Skype (cheesegrits, hugh.messenger@gmail.com) and walk me through the problem. Part of the issue is it's just difficult to follow this thread, what all we've done, and exactly what the issue is. It might be a 2 second fix once I actually understand the issue. Or it might be two hours. If it's the former, no problem. If it's the latter, I'll tell you how much. My rate varies from $50 to $100, depending on various factors - how many bills I have piled up, how technically challenging it is, if it's work I enjoy or not, what the deadline is (like, if it's a weekend and I have to drop everything and do it "now"), whether I like you (so you are good there!), etc.

-- hugh
 
Hi Hugh,
Thank you for that.
I sent you an invitation from Skype. I'm fine with your rate. Please let me know the best for you. My time zone is 00 GMT.
 
Hi Hugh,
I worked my way today and solved one issue. Deleting the menu and recreated it did the job. The pre-filters set in the menu link are now working fine for this list. I don't understand why it did not work before, maybe a cache issue:oops:. It was quite persistent as I put some hours on it.
However, in the database, only the id displays for the exhibitors field. Would it be possible to have a column displaying the text?
 
Have you set that element to be a join to the exhibitors table, with the value being the id (PK of the exhibitors table) and label as the name?

-- hugh
 
Yes indeed! The exhibitors field of the list Leads is a database join element to the list Exhibitors. Below is a screenshot of its settings. The element displays as it should be on the list. But in PhpMyadmin, only the id of the exhibitors displays

databasejoin exhibitors.png
 
Well, yes, only the id will show in phpMyAdmin, because that's what is stored in the orders table. Fabrik then joins the exhibitors table and gets the name to use as the label.

So if you did this in phpMyAdmin ...

select * from orders left join exhibitors as exhibitor.id = orders.exhibitor

... you will see all the fields from both tables.

That's how joins work in a relational database. You use a "foreign key" (FK) on one table, which points to the primary key (PK) of another table.

-- hugh
 
Thank you for coming back to me. I got a SQL syntax error message below when I tried the code:

select * from orders left join exhibitors as exhibitor.id = orders.exhibitor
or select * from orders left join exhibitors as exhibitors.id = orders.Exhibitors

dot.gif
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.id = orders.Exhibitors LIMIT 0, 30' at line 1
 
Well that's because that syntax is incorrect. You can't just make up query syntax out of thin air. :)

You only need one or the other selects. Either exhibitors starts with an upper case E or it doesn't. My example was just that, an example, I figured you'd know to replace the exact table / field names with their correct names.

-- hugh
 
You must log in or register to reply here.

Useful links

  • Create a Fabrik Account (Downloads)
  • Create a Forum Account
  • Fabrik Wiki
  • Fabrik 4 Changelog /Install /Upgrade
    • We are in need of some funding.
    More details.

    Thank you.

    Staff online

    Members online

    Total: 32 (members: 8, guests: 24)
    Back
    Top