Jump to content


  • Posts

  • Joined

  • Last visited

Recent Profile Visitors

2,335 profile views

Adamhumbug's Achievements

Advanced Member

Advanced Member (4/5)



  1. Thanks so much and i appreciate you dealing with my rubbish post.
  2. prs_ptd_id is a typo - it should be pr_ptd_id pr_op_id is the id of the person that made the order stored on the order table (prs_pr) and links to op_id in the person table (prs_op) prs_ptd_id (is the order definition) pr_o_id is the organisation the order is stored against in the order table (prs_pr) this links to o_id stored in the organiastion table (prs_o) orders belong to a person and the person belongs to an organsation
  3. prs_o table: o_id, o_name prs_op table: op_id, op_o_id - op_o_id joins to o_id from above prs_pr table: pr_Id, pr_op_id, prs_ptd_id, pr_ip2ptd_id - pr_op_id joins to op_id from above
  4. Hi all, I have a query that shows me the number of bookings that an organisation has made which works perfectly select o_id as id, o_name as org, pass_641.media_attendee from prs_o inner join prs_op on o_id = op_o_id inner join (select pr_op_id, pr_ptd_id, count(*) as media_attendee from prs_pr where pr_ptd_id = 641 group by pr_o_id ) as pass_641 on op_id = pass_641.pr_op_id The data that i get out looks like the below (i have left out the name col) org count 1 2 2 3 3 9 I want to add another count of other types of order to the query and tried the following select o_id as id, o_name as org, pass_641.media_attendee, pass_1001.tv from prs_o inner join prs_op on o_id = op_o_id inner join (select pr_op_id, pr_ptd_id, count(*) as media_attendee from prs_pr where pr_ptd_id = 641 group by pr_o_id ) as pass_641 on op_id = pass_641.pr_op_id inner join (select pr_op_id, pr_ip2ptd_id, count(*) as tv from prs_pr where pr_ip2ptd_id = 1001 group by pr_o_id ) as pass_1001 on op_id = pass_1001.pr_op_id This only shows me organisations that have both of the order types rather than showing NULL if they have one and not the other - this is clearly to do with the inner joins. I tried changing to left joins but this shows a row per order like the below org count count 1 2 NULL 1 NULL NULL I would appreciate a point in the right direction with this. To confirm if i run this with only one order type the individual queries run correctly.
  5. I am in 100% agreement, i am afraid the stuff i am doing at the minute is not on my database and it is unlikely to get any better in the short term. So i have a person table with: id - fname - lname 1 - Adam - Smith 2 - John - Hughes I have a badge table with: badgeid - personid - zones 1 - 1 - 101,210,301,401,501,601 2 - 2 - 301,601,1101 and i need to get a list together of everyone that has more than one of the zones 301,401,501. Hopefully this does not require something as wonderful as the function in the last "mess tidying series of SQL statements"
  6. Hi, A person has a person id and they also have a list of zones (301,401,501,601,701 etc) I need a query that gives me the person id if their zone list contains more than one of (301,401,501) op_id - person id pr_azones - product ids I know this is going to be an IN, but not sure on the more than one bit.
  7. This unfortunately needs to to be entirely sql if that is even possible. If its not then we are where we are.
  8. I agree that this is less than ideal. As horrible as it sounds, would i be able to substring them and str_to_date them to get some of the way there?
  9. HI All, I have some dates stored as a comma sep string in a database (col = adates) The contents looks like the below 2021-04-04 07:00,2021-04-05 07:00,2021-04-06 07:00,2021-04-07 07:00,2021-04-08 07:00,2021-04-09 07:00,2021-04-10 07:00,2021-04-11 07:00,2021-04-12 07:00,2021-04-13 07:00 I am trying to select rows with 10 dates or less that are consecutive. I have done the number of dates with char_count but am struggling with the consecutive part. The table structure is id, azones Any help with this will be really appreciated as i will also need to find the people where dates are not consecutive.
  10. I dont have access to the ajax function, just a function that runs at the end. I know this is pretty ambiguous but the system i am using i dont have access to the source. I have created another button that just runs the function and i click that when i can see everything is in the DOM and it still wont log (each ran) so it still cannot find the elements. But again, when i paste the code into console, i get the desired effect.
  11. If i run this line manually in the console, i get the correct number of rows showing $("[id^='OPRow']").length and if i run my function (without the function part) in console, i get the correct outcome. $("[id^='OPRow']").each(function(index, value){ console.log('each ran') var fn = $("[id^='op_firstname']", this) var ln = $("[id^='op_lastname']", this) var nation = $("[id^='op_nationality']", this) var mobile = $("input[id^='op_t1']", this) var cc = $("select[id^='op_t1d']", this) var role = $("[id^='op_role']", this) var e1 = $("[id^='op_e1']", this) var fnlabel = $("[id$='op_firstname']").text() var lnlabel = $("[id$='op_lastname']").text() var nationlabel = $("[id$='op_nationality']").text() var mobilelabel = $("[id$='op_t1']").text() var cclabel = $("[id$='op_t1d']").text() var rolelabel = $("[id$='op_role']").text() var e1label = $("[id$='op_e1']").text() var row = $("<div class='row'><div class='col-6'><label class='fnlab'></label><div class='input-group mb-3 fnholder'></div></div><div class='col-6'><label class='lnlab'></label><div class='input-group mb-3 lnholder'></div></div></div><div class='row'><div class='col-6'><label class='nationlab'></label><div class='input-group mb-3 ccholder'></div></div><div class='col-6'><label class='mobilelab'></label><div class='input-group mb-3 mobileholder'></div></div></div><div class='row'><div class='col-4'><label class='cclab'></label><div class='input-group mb-3 nationholder'></div></div><div class='col-4'><label class='rolelab'></label><div class='input-group mb-3 roleholder'></div></div><div class='col-4'><label class='e1lab'></label><div class='input-group mb-3 e1holder'></div></div></div>") row.find('.fnholder').append(fn) row.find('.lnholder').append(ln) row.find('.nationholder').append(nation) row.find('.mobileholder').append(mobile) row.find('.ccholder').append(cc) row.find('.roleholder').append(role) row.find('.e1holder').append(e1) row.find('.fnlab').append(fnlabel) row.find('.lnlab').append(lnlabel) row.find('.nationlab').append(nationlabel) row.find('.mobilelab').append(mobilelabel) row.find('.cclab').append(cclabel) row.find('.rolelab').append(rolelabel) row.find('.e1lab').append(e1label) $('.content').append(row) $('.batchrequest input').addClass('form-control') $('.batchrequest select').addClass('custom-select') })
  12. Just to add some more clarification here. The new line is created with AJAX and i have called my function bootRow() at the end of the AJAX which i can see running in the console logs. What is not happening is the second log inside the each function suggesting that the each function is not finding the id when i can see it several times when inspecting the code and can see several of these lines on the screen. The $("[id^='OPRow']").length that i have always returns 0
  13. Perfect, thanks so much, that really helped. Has led me into another issue however (another thread). Unrelated to this answer which is perfect.
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.