This topic is locked

Where clause sytax

3/25/2017 6:36:10 PM
PHPRunner General questions
H
Hertz2P author

Can someone please enlighten me on the correct way to write this WHERE clause?
"currentLocation = '" . $Session ['Onsite Equipment_project']."'"
'project' is a Lookup wizard text field from the table 'Onsite Equipment'. Code is being executed from the Onsite Equipment Add page. I'm not sure if I have a syntax issue or if I need to write event code to populate the field after a lookup wizard selection.. It works fine if I use the dependent dropdown box, but I can't use that because I need to involve a couple of OR clauses to this statement.
Any help appreciated.
Thanks!

admin 3/25/2017

$_Session is incorrect, should be $_SESSION. Also you need to make sure that this session variable in fact exists.

H
Hertz2P author 3/26/2017



$_Session is incorrect, should be $_SESSION. Also you need to make sure that this session variable in fact exists.


Thanks Sergey, I'm actually trying to go a different direction with this one now. I'm trying to follow this tutorial, which is based on one of yours: Dependent dropdown lookup in child table based on master record
I'm not sure if I've changed all the necessary variables, or if I've put all the code into the correct event locations, or if code in the output directory is correct and in the correct location on the server. Here's what I've done:
I have a Master table 'Foreman's Daily Report' with a field 'Project'. I have a Details Table called 'Onsite Equipment' with a field 'Model' and I have another table 'Equipment' with fields 'Model' and 'CurrentLocation'
I want the Lookup Wizard on 'Onsite Equipment' to only show 'Model' WHERE 'Project' (Master Table) = 'CurrentLocation'.

So I put this in Master Table Add page, JavaScript OnLoad event:



//Lets make the LinkProductID field in master record trigger a dependent lookup in the child based on the selected product

var LinkProductID = Runner.getControl(pageid, 'Project');
//The window var is used by the details table to adjust the dependent dropdowns accordingly in JS onload events

LinkProductID.on('change',function(){

window.productID = this.getValue();

//console.log(window.productID); //debug

});


I put this in 'Onsite Equipment' Add page, JavaScript OnLoad event:



var ctrlModel = Runner.getControl(pageid, 'Model');

var func = function(e){

$.post("mylookup.php", {

'category' : window.productID },

function(data) {

setTimeout(function(){

var sel = $("#value_Model_1");

sel.empty();

for (var i=0; i<data.length; i++) {

sel.append('<option value="' + data[i].id + '">' + data[i].desc + '</option>');

}

},1000);//end setTimeout (set for 1 sec)

}, "json");

};
ctrlModel.on('keyup', func);

ctrlModel.on('change', func);


And I put this in mylookup.php file in output directory:



<?php
include("include/dbcommon.php");
$result = array();

$make = db_addslashes(trim(postvalue('category')));

$sql = "SELECT * FROM Equipment WHERE currentLocation = '$make'";

$rs = CustomQuery($sql);
while ($data = db_fetch_array($rs)) {

$result[] = array(

'id' => $data['Model'],

'desc' => $data['Model'],

);

}

echo json_encode($result);

?>


Incidentally, I couldn't find mylookup.php on the server, even after a full build/upload all files, so I manually uploaded it to the root directory on the server.
Any help appreciated,
Thanks!

H
Hertz2P author 3/26/2017

Here is the console error that I'm getting: SCRIPT438: Object doesn't support property or method 'on'
Here is the Debugger code:



Runner.pages.PageSettings.addPageEvent('Onsite Equipment',Runner.pages.constants.PAGE_ADD,"afterPageReady",function(pageObj,proxy,pageid,inlineRow,inlineObject){var ctrlModel=Runner.getControl(pageid,'model');var func=function(e){$.post("mylookup.php",{'category':window.productID},function(data){var sel=$("#value_Model_1");sel.empty();for(var i=0;i<data.length;i++){sel.append('<option value="'+data[i].id+'">'+data[i].desc+'</option>');}},"json");};ctrlModel.on('keyup',func);ctrlModel.on('change',func);});
Y
YCH 3/27/2017



Here is the console error that I'm getting: SCRIPT438: Object doesn't support property or method 'on'
Here is the Debugger code:



Runner.pages.PageSettings.addPageEvent('Onsite Equipment',Runner.pages.constants.PAGE_ADD,"afterPageReady",function(pageObj,proxy,pageid,inlineRow,inlineObject){var ctrlModel=Runner.getControl(pageid,'model');var func=function(e){$.post("mylookup.php",{'category':window.productID},function(data){var sel=$("#value_Model_1");sel.empty();for(var i=0;i<data.length;i++){sel.append('<option value="'+data[i].id+'">'+data[i].desc+'</option>');}},"json");};ctrlModel.on('keyup',func);ctrlModel.on('change',func);});



Suppose your syntax here might be wrong (i have no javascript tuition)

ctrlModel.on('keyup', func);

ctrlModel.on('change', func);

Object doesn't support property or method 'on'[b]
[/b]

admin 3/27/2017

Most likely you do not have a field named 'Model' in this table and this is why it breaks.

H
Hertz2P author 3/27/2017

Thanks Sergey
I have a field called 'model' in both my 'Onsite Equipment' and 'Equipment' tables which is my linked field in the Lookup Wizard. I fixed the case, and it doesn't break anymore, but the code still doesn't work. Can you see anything else that doesn't look right? Where should I be able to find the file "mylookup.php" on my server?

admin 3/27/2017

I don't know what might be wrong because I cannot see your database and application, but 'Model' and 'model' are two different things.

H
Hertz2P author 3/27/2017

I went ahead and posted it to the demo account.. http://demo.asprunner.net/ed_guinasso_com/TE_Briggsv97/menu.php
timothy@tebriggs.com / 123456 if you (or anyone else) has time to look at it..
My issue is in "New Daily Report" the field in the Master table "Project" is supposed to be limiting the choices of the Details table "Onsite Equipment" in the 'model' field to WHERE 'currentLocation' (Equipment table) = 'Project'.. However, all choices are available no matter the selection of "Project".
Thanks!

Y
YCH 3/27/2017



I went ahead and posted it to the demo account.. http://demo.asprunne...ggsv97/menu.php
timothy@tebriggs.com / 123456 if you (or anyone else) has time to look at it..
My issue is in "New Daily Report" the field in the Master table "Project" is supposed to be limiting the choices of the Details table "Onsite Equipment" in the 'model' field to WHERE 'currentLocation' (Equipment table) = 'Project'.. However, all choices are available no matter the selection of "Project".
Thanks!


What is the sense of $make in your SQL ? Is it the right field?
<?php
include("include/dbcommon.php");
$result = array();

$make = db_addslashes(trim(postvalue('category')));

$sql = "SELECT * FROM Equipment WHERE currentLocation = '$make'";

$rs = CustomQuery($sql);
while ($data = db_fetch_array($rs)) {

$result[] = array(

'id' => $data['Model'],

'desc' => $data['Model'],

);

}

echo json_encode($result);

?>

H
Hertz2P author 3/27/2017



What is the sense of $make in your SQL ? Is it the right field?
<?php
include("include/dbcommon.php");
$result = array();

$make = db_addslashes(trim(postvalue('category')));

$sql = "SELECT * FROM Equipment WHERE currentLocation = '$make'";

$rs = CustomQuery($sql);
while ($data = db_fetch_array($rs)) {

$result[] = array(

'id' => $data['Model'],

'desc' => $data['Model'],

);

}

echo json_encode($result);

?>


Hi YCH,
If I'm following the code correctly, $make is determined by 'catagory' value, which is determined by this code in the php file: 'category' : window.productID }, which is determined by by the code from the master table:



//Lets make the LinkProductID field in master record trigger a dependent lookup in the child based on the selected product

var LinkProductID = Runner.getControl(pageid, 'Project');
//The window var is used by the details table to adjust the dependent dropdowns accordingly in JS onload events

LinkProductID.on('change',function(){

    window.productID = this.getValue();

    //console.log(window.productID); //debug

});


But then again, there's a good chance that I'm not following it correctly ;|

HJB 3/27/2017




I think, that's what ADMIN had been looking at here ...
P.S. Naming in lower case avoids case-sensitive issues while correctly grammared "field caption" runs for the public ...

H
Hertz2P author 3/27/2017






I think, that's what ADMIN had been looking at here ...
P.S. Naming in lower case avoids case-sensitive issues while correctly grammared "field caption" runs for the public ...


Yes, I figured that one out when he made the previous post. I had changed it before making my next one, where I said that I had "fixed the case". I no longer get the break error, but also the code isn't working.
and YES I have since learned to avoid using capital letters and spaces whenever possible <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=81799&image=3&table=forumreplies' class='bbc_emoticon' alt=';)' />

HJB 3/27/2017

https://jsonformatter.curiousconcept.com/
While PHP is one thing, JSON is another, say, I'm using

online validators when it comes to e.g. cut and paste

your ONSITE EQUIPMENT Java script onload event for

trouble-shooting purposes and it seems that definitely

something needs to be done here, a.k.a. SYNTAX.

H
Hertz2P author 3/27/2017



https://jsonformatter.curiousconcept.com/
While PHP is one thing, JSON is another, say, I'm using

online validators when it comes to e.g. cut and paste

your ONSITE EQUIPMENT Java script onload event for

trouble-shooting purposes and it seems that definitely

something needs to be done here, a.k.a. SYNTAX.


I'm guessing that it does, but the problem is that when I post Sergey's original code in there, it also lights up like a Christmas tree. I don't know enough to know which are real problems and which are 'false positives'.

HJB 3/27/2017



I'm guessing that it does, but the problem is that when I post Sergey's original code in there, it also lights up like a Christmas tree. I don't know enough to know which are real problems and which are 'false positives'.


Source: http://www.freeformatter.com/json-validator.html



Yes, this one is talking about line #1 and #12 to look at on a deeper basis.

H
Hertz2P author 3/27/2017



I actually removed that code as it was only necessary for use with an additional dependent dropdown.. Here is my current 'Onsite Equipment' code:



var ctrlModel = Runner.getControl(pageid, 'model');

var func = function(e){

$.post("mylookup.php", {

'category' : window.productID },

function(data) {

setTimeout(function(){

var sel = $("#value_Model_1");

sel.empty();

for (var i=0; i<data.length; i++) {

sel.append('<option value="' + data[i].id + '">' + data[i].desc + '</option>');

}

},1000);//end setTimeout (set for 1 sec)

}, "json");

};
ctrlModel.on('keyup', func);

ctrlModel.on('change', func);






Yes, this one is talking about line #1 and #12 to look at on a deeper basis.


However, that code was posted by the original writer of the article: Dependent dropdown lookup in child table based on master record
Cheers,
Ed

HJB 3/27/2017



However, that code was posted by the original writer of the article: Dependent dropdown lookup in child table based on master record
Cheers,
Ed





Okay, FunkDaddy in 2015, meanwhile a lot of water flew down the Hudson river or say, various RFC codes of JSON are existing by today and as well some three different JSON classes to go too. Seems to find the needle in a hay heap here ..., and certainly requires some very cool minutes to get things straight <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=81804&image=3&table=forumreplies' class='bbc_emoticon' alt=':unsure:' />

H
Hertz2P author 3/27/2017






Okay, FunkDaddy in 2015, meanwhile a lot of water flew down the Hudson river or say, various RFC codes of JSON are existing by today and as well some three different JSON classes to go too. Seems to find the needle in a hay heap here ..., and certainly requires some very cool minutes to get things straight <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=81805&image=3&table=forumreplies' class='bbc_emoticon' alt=':unsure:' />


I get all that, but the code was based on stuff Sergey posted in 2015 also, and he didn't say anything about possible syntax issues when commenting in this thread.. I feel like it's something simple that I'm missing..

HJB 3/27/2017



I get all that, but the code was based on stuff Sergey posted in 2015 also, and he didn't say anything about possible syntax issues when commenting in this thread.. I feel like it's something simple that I'm missing..


JSON is outside of PHP and ASP code generation and not the daily bread and butter biz of Xlinesoft as they are just providing the OPTION to use JSON in the event programming section at own will and expertise while new versions with some syntax changes are adding on top of more complex issues once a mix between PHP and JSON is meant to work on a server under third party control. So..., we have to wait whether your demo account upload might be able to shed more light on the problem.

H
Hertz2P author 3/27/2017



JSON is outside of PHP and ASP code generation and not the daily bread and butter biz of Xlinesoft as they are just providing the OPTION to use JSON in the event programming section at own will and expertise while new versions with some syntax changes are adding on top of more complex issues once a mix between PHP and JSON is meant to work on a server under third party control. So..., we have to wait whether your demo account upload might be able to shed more light on the problem.


Do you know of another way to take the value from the master table and use it as a dependency from the Details table dropdown? That's my #1 goal.. If I can't do that, I'd like to make them select the jobsite again within the Details table, and have the 'make' field dependent on that.. My problem is that I can't just use the Lookup Wizard's dependent table feature, because it doesn't allow for an OR statement. In the end, I need to list all 'model's that are either at the jobsite, in the "Yard" or don't have any jobsite selected as 'currentLocation'.
I figured out how to write the WHERE...OR...OR...OR statement, but the variable from the other dropdown isn't recognized by the second table when you don't use the dependency checkbox feature. I'm not sure how else to capture that data to make my Lookup Wizard field filter the results.
What I want is pretty much identical to FUNKDADDY'S description, I just wish he posted his code instead of using the original.. I think I would have been able to figure it out from that.

HJB 3/28/2017



Do you know of another way to take the value from the master table and use it as a dependency from the Details table dropdown? That's my #1 goal.. If I can't do that, I'd like to make them select the jobsite again within the Details table, and have the 'make' field dependent on that.. My problem is that I can't just use the Lookup Wizard's dependent table feature, because it doesn't allow for an OR statement. In the end, I need to list all 'model's that are either at the jobsite, in the "Yard" or don't have any jobsite selected as 'currentLocation'.
I figured out how to write the WHERE...OR...OR...OR statement, but the variable from the other dropdown isn't recognized by the second table when you don't use the dependency checkbox feature. I'm not sure how else to capture that data to make my Lookup Wizard field filter the results.
What I want is pretty much identical to FUNKDADDY'S description, I just wish he posted his code instead of using the original.. I think I would have been able to figure it out from that.


http://stackoverflow.com/questions/31919501/php-query-search-in-table-for-json-value
.. in other way.

Y
YCH 3/28/2017

e>
Do you know of another way to take the value from the master table and use it as a dependency from the Details table dropdown? That's my #1 goal.. If I can't do that, I'd like to make them select the jobsite again within the Details table, and have the 'make' field dependent on that.. My problem is that I can't just use the Lookup Wizard's dependent table feature, because it doesn't allow for an OR statement. In the end, I need to list all 'model's that are either at the jobsite, in the "Yard" or don't have any jobsite selected as 'currentLocation'.
I figured out how to write the WHERE...OR...OR...OR statement, but the variable from the other dropdown isn't recognized by the second table when you don't use the dependency checkbox feature. I'm not sure how else to capture that data to make my Lookup Wizard field filter the results.
What I want is pretty much identical to FUNKDADDY'S description, I just wish he posted his code instead of using the original.. I think I would have been able to figure it out from that.


--> On the Visual Editor screen did you try creating a new query (create new query button) joining the right tables and using this query as the 'lookup table' and further using dependent dropdown boxes?

H
Hertz2P author 3/28/2017



--> On the Visual Editor screen did you try creating a new query (create new query button) joining the right tables and using this query as the 'lookup table' and further using dependent dropdown boxes?


I'm not sure I follow what you're asking. On the visual editor Lookup Wizard dialog, it's possible to add a WHERE clause to the initial dropdown, but I don't see a way to modify the dropdown that it is dependent upon.
In other words, if I put a second 'project' field in the details table, I can easily filter 'model' to WHERE 'currentLocation' = 'project', but I can't add anything to it.. I also need 'currentLocation' = "Yard" and 'currentLocation' = ''.
I don't see a "Create new query" button.. Are you talking about the "Edit query" button?

Y
YCH 3/28/2017



I'm not sure I follow what you're asking. On the visual editor Lookup Wizard dialog, it's possible to add a WHERE clause to the initial dropdown, but I don't see a way to modify the dropdown that it is dependent upon.
In other words, if I put a second 'project' field in the details table, I can easily filter 'model' to WHERE 'currentLocation' = 'project', but I can't add anything to it.. I also need 'currentLocation' = "Yard" and 'currentLocation' = ''.
I don't see a "Create new query" button.. Are you talking about the "Edit query" button?


If your field has yet to be configured as dropdown the button name is "Create new query", if your field is already defined as a dropdown then the button name is "Edit query";

So we are definitely speaking about the same button.
Could you elaborate on your tables and their relationship and show some lines (records)?

a) Master table : keyfield, other fields ... (some records with only the essential fields)

bb) Detail table : keyfield, other fields ... (some records with only the essential fields)

c) your Lookup table ( or lookup query or Lookup custom view table): how would it look like (some records with only the essential fields) ? Which field(s) should be filtered by which other field(s) from which table(s) ?

admin 3/28/2017

You can achieve this either using custom WHERE clause or by creating a totally custom dropdown boxes. In both cases you need to understand what you doing, get your syntax right and use proper troubleshooting steps if something doesn't work as expected.
Here are some troubleshooting tips that can help you with custom dependent dropdown boxes approach:

https://xlinesoft.com/phprunner/docs/troubleshooting_javascript_errors.htm

https://xlinesoft.com/phprunner/docs/troubleshooting_custom_buttons.htm

https://xlinesoft.com/phprunner/docs/debugging_tips.htm
Also using JSON formatter to validate your Javascript code is just a plain wrong idea.

H
Hertz2P author 3/28/2017



If your field has yet to be configured as dropdown the button name is "Create new query", if your field is already defined as a dropdown then the button name is "Edit query";

So we are definitely speaking about the same button.
Could you elaborate on your tables and their relationship and show some lines (records)?

a) Master table : keyfield, other fields ... (some records with only the essential fields)

bb) Detail table : keyfield, other fields ... (some records with only the essential fields)

c) your Lookup table ( or lookup query or Lookup custom view table): how would it look like (some records with only the essential fields) ? Which field(s) should be filtered by which other field(s) from which table(s) ?


I posted it onto the Demo site and put an admin username/password in a previous post..
My Master table is 'Foreman's Daily Report' keyfield = ID, other fields 'Project', 'Weather', datetime, userid, images, notes.
There are several Details tables in the Master, but the one I'm concerned with now is:

'Onsite Equipment' keyfield = ID, report_id, 'project'(was trying to get this field from master table), 'model', starttime, endtime, totaltime, notes
Link table is 'Equipment' keyfield = ID, other fields type, model, currentLocation
Feel free to log in and check it out.. should be much easier to follow what I'm looking for..

H
Hertz2P author 3/28/2017



You can achieve this either using custom WHERE clause or by creating a totally custom dropdown boxes. In both cases you need to understand what you doing, get your syntax right and use proper troubleshooting steps if something doesn't work as expected.
Here are some troubleshooting tips that can help you with custom dependent dropdown boxes approach:

https://xlinesoft.com/phprunner/docs/troubleshooting_javascript_errors.htm

https://xlinesoft.com/phprunner/docs/troubleshooting_custom_buttons.htm

https://xlinesoft.com/phprunner/docs/debugging_tips.htm
Also using JSON formatter to validate your Javascript code is just a plain wrong idea.


Sergey, is there a way to add an OR to the dependent dropdown? I see that you can add additional fields which are essentially AND clauses. I haven't seen any information on creating custom dropdown boxes, but that sounds like the approach I need to take. Thanks for the above links.