No announcement yet.

Assist on making query as efficient as possible

  • Filter
  • Time
  • Show
Clear All
new posts

  • Assist on making query as efficient as possible

    EDIT: dang it, i can't edit it and save the attachments right, i'll have to reply again
    Attached Files
    Last edited by story97; 07-23-2017, 04:00 AM.

  • #2
    EDIT: ok let me try this again. Have a feeling this forum is sitting on Cloudflare or something similar.

    I have a rather odd request.

    I have a client that I do manual work for. I have to pick a winner from a list of around 2500 winners. But I have to do this 4 times per week. Every week.

    To assist, i've written some pretty newbie generic queries and I'm wondering about maybe automating this:
    Click image for larger version

Name:	image_379.jpg
Views:	2
Size:	17.2 KB
ID:	4757

    I am using a WordPress website that puts form entries into tables in a db (obviously). To pick my winner I login to the DB, i look at the queries, pick the appropriate and run it to generate a list of entries for that week on that giveaway. Those queries look like this:
    Click image for larger version

Name:	image_381.jpg
Views:	2
Size:	181.7 KB
ID:	4759

    I export the results to a folder that is created for every draw. The structure looks like this:

    Click image for larger version

Name:	image_380.jpg
Views:	3
Size:	206.5 KB
ID:	4761

    The export is done in sql format and saved. Then i open that file manually, just scroll down and take out the results from one line item.

    Click image for larger version

Name:	image_383.jpg
Views:	1
Size:	477.2 KB
ID:	4760

    IN that line item I note the IP address, username and email. I then search the resulting dump for duplicates. if there are none I use a website to check and make sure of the location of the user. This makes shipping costs very high in places like Bolivia so we need to know in advance.

    I then take the items I need, save that file as an archive so i can go back and research old work, as well as search all the folders for duplicate emails (you can only win once a year)

    after all this is done I create an email using a template i made in Outlook. I created an icon for each giveaway that i post to a quick access ribbon for one click access.

    (each lock icon is name the day of the week of that template)
    Click image for larger version

Name:	image_378.jpg
Views:	5
Size:	11.6 KB
ID:	4758

    As you can imagine this is a very tedious process that i do 4 times per week. it only takes about 4 minutes but i sure am tired of it after doing this for 1.5 years and no end in sight.

    I would like to automate as much of this as possible and i'm assuming i'll be very limited in what that is. I know this is a Navicat forum but was thinking maybe this will hit home with someone that's automated some of these items as well.

    does anyone have a suggestion or some direction on how I can make this less of a burden? i realize the answers will be limited to Navicat but if anyone has ideas about the rest of it that would be welcomed too obviously.

    Last edited by story97; 07-23-2017, 11:58 PM.


    • #3
      OK the caching stuff finally settled. so i updated the post above.

      Sorry for the confusion