Adamhumbug

Perfect answer as always. That did of course work, as for getting the data out of the other end, could you lend a hand with that also please?

Hi All, I have been scouring the internet for a good source that shows how to create an array and loop through it to output it to the page. I have done this several times but everytime it comes to doing it i have to scratch my head and google to get anywhere near the answer. I have created a 2 dimentional array of data using the following: function getExistingAwards(){ include 'includes/dbconn.php'; $stmt = $conn -> prepare("SELECT id, award_name, award_year, award_winner from award"); $stmt -> execute(); $stmt -> bind_result($id, $an, $ay, $aw); $awards = array(); $out = ''; while($stmt -> fetch()){ $awards[$ay] = [$id, $an, $aw]; } } This gives me a data structure that looks like this. Array ( [2021] => Array ( [0] => 1 [1] => Avenue Park Award [2] => 1 ) [2019] => Array ( [0] => 2 [1] => Avenue Park Award [2] => 1 ) ) Issue 1 - This looks correct but if there are more than one entries that has the year 2021 for example, only the last one will be added to the array. Issue 2 - After i have the correct data in the array how do i go about looping through each level of the array. I really struggle with for each loops. As always, your help is appreciated.

Thanks so much and i appreciate you dealing with my rubbish post.

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

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

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.

As always - thank you so much!

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"

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.

WOW!!! - Thank you VERY much for this!!

This unfortunately needs to to be entirely sql if that is even possible. If its not then we are where we are.

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?

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.

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.

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') })

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

Perfect, thanks so much, that really helped. Has led me into another issue however (another thread). Unrelated to this answer which is perfect.

Hi, I have a form to collect personal data, fn, ln, email etc. There is a button that creates a new blank line for you to fill out with another persons data. I am wanting to turn this form into a nice bootstrap layout (i have that bit sorted). This worked in my testing environment when i copied some html over (not created from the button). When you click the button you get a line like this. <tr id="OPRow605b06bd13161" class=""> <td class="BRFOPRowPhotoPreviewCell"> <input type="hidden" name="OPID605b06bd13161" id="OPID605b06bd13161" value="New"> </td> <td> <input type="text" size="20" name="op_firstname605b06bd13161" id="op_firstname605b06bd13161" style="" value=""> </td> <td> <input type="text" size="20" name="op_lastname605b06bd13161" id="op_lastname605b06bd13161" style="" value=""> </td> <td> <select name="op_nationality605b06bd13161" id="op_nationality605b06bd13161" style=""> <option value="" selected="selected">Please Select</option> </select> </td> <td> <input type="text" size="30" name="op_t1605b06bd13161" id="op_t1605b06bd13161" style="" value=""> </td> <td> <select name="op_t1d605b06bd13161" id="op_t1d605b06bd13161" style=""> <option value="" selected="selected">Please Select</option> </select> </td> <td> <input type="text" size="30" name="op_role605b06bd13161" id="op_role605b06bd13161" style="" value=""> </td> <td><input type="text" size="20" name="op_e1605b06bd13161" id="op_e1605b06bd13161" style="" value=""> </td> <td class="BRFOPRowPhotoCell"> <input type="hidden" name="op_photo605b06bd13161" id="op_photo605b06bd13161" value=""> <span onclick="doBRForm('editPhotoFromBatchRequest','605b06bd13161','')" class="IPListMenuItem BRFOPRowPhoto noPhoto"> <span class="BRFOPRowPhotoSpan">Add</span> </span> </td> <td class="BRFRemoveRowCell"> <span class="IPListMenuItem BRFRemoveRow" onclick="BRFRemoveRow('OPRow605b06bd13161')"> <span class="BRFRemoveRowSpan">X</span> </span> </td> </tr> The code that i am running is looking for each iteration of the opening ID and then moves stuff around. This works great in the testing environment but does not find that id when it is run inside the system. function bootRow(){ console.log('boot ran') $("[id^='OPRow']").length $("[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') }) } The button uses jquery to call add the new line so i have also included a call to this function at the end of the Ajax and can confirm that it runs every time the button is pressed. I have console logged the length and it always says 0. I appreciate any pointers you may be able to offer here and i will provide any further information that is required. Thanks in advance.

Something like this might be useful as a point to start with - this is jquery by the way $('#element').on('change', function(){ //do stuff })

I think i have fixed this. Added "as newtime" after the ",time" in your example and that seems to be working when i use "newtime" in the where clause. Thanks so much as every for your continued support!!

This is really useful, thanks so much. As always, one more issue. I am getting an error in the where clause. My where clause is WHERE concat(newdate, ' ', extractvalue(pr_customfields,'/CUSTOMFIELDS/CF[@CFN="TestedTime"]/@CFV')) < NOW() - INTERVAL 36 HOUR; I am getting the error - Unknown column 'pr_customfields' in 'where clause' I think the issue may have something to do with the coma in the concat but i have replaces all of your istances of "time" with the above and "date" with something very similar. The data is stored in xml in the database in the following format hence the method for extraction (maybe there is another way) <?xml version="1.0"?> <CUSTOMFIELDS> <CF CFN="TestedTime" CFV="" CFUID="CF5fb7e3b8ca7d8"/> </CUSTOMFIELDS>

I have one varchar column. Unfortunately i am unable to change the table structure and have to deal with one varchar for date and one varchar for time. An example of the data is below

I have a table that has dates stored in various formats (2021-03-12, 12 Mar 2021). I also have a time stored as a string (07:00) I am wanting to convert the dates in their various formats into one that can be concatonated with the time so that i can find any date/times that are 36 hours old or more. I am pretty stuck with this after trying many different ways and would really appreciate a helping hand on this. Thanks in advance.

Hi, I am trying to move an element with JQ. I am wanting to add the input box to some appended content to a page. My code is below: $("[id^='OPRow']").each(function(index, value){ var fn = $("[id^='op_firstname']", this) console.log(fn) 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 = $("select [id^='op_e1']", this) var cc = $("select [id^='op_t1d']", this) $('.content').append("<div class='row'><div class='col-6'><label for='basic-url'>Your vanity URL</label><div class='input-group mb-3'>"+fn+"</div></div><div class='col-6'><label for='basic-url'>Your vanity URL</label><div class='input-group mb-3'><input type='text' class='form-control' id='basic-url'></div></div></div><div class='row'><div class='col-4'><label for='basic-url'>Your vanity URL</label><div class='input-group mb-3'><input type='text' class='form-control' id='basic-url'></div></div><div class='col-4'><label for='basic-url'>Your vanity URL</label><div class='input-group mb-3'><input type='text' class='form-control' id='basic-url'></div></div><div class='col-4'><label for='basic-url'>Your vanity URL</label><div class='input-group mb-3'><input type='text' class='form-control' id='basic-url'></div></div></div><div class='row'><div class='col-4'><label for='basic-url'>Your vanity URL</label><div class='input-group mb-3'><input type='text' class='form-control' id='basic-url'></div></div><div class='col-4'><label for='basic-url'>Your vanity URL</label><div class='input-group mb-3'><input type='text' class='form-control' id='basic-url'></div></div><div class='col-4'><label for='basic-url'>Your vanity URL</label><div class='input-group mb-3'><input type='text' class='form-control' id='basic-url'></div></div></div>") }) The output i am getting is [object Object] where the element should be placed. I would appreciate any help with this trying to put the input into the content that gets append to $('.content')

Thanks so much for your reply as always! Will the IFNULL be used in the maths at the bottom of this query? (ptsl_limit - IFNULL(used, 0)