SQL Query help

[Mike Gillespie]

Hey y'all,

Sure hope you can help with this one.

Is there a way to write a sql query for MySQL such that it returns recordset of say 100 rows with my target recordset in the center from a table of 1000 rows?

I need it to load a page of items on an ecomm site, but want to provide not only the target item, but 100 other choices as well.

The number 100 and 100 are arbitrary.

Basically, pull all records with a price range between .99 and 9.99 with the 4.99 target row in the center of the resultset. ( and yeah, the example is horrible, but it gets the point across)

TIA,

M

[james....@bakerbotts.com]

This might help: https://stackoverflow.com/questions/30068252/mysql-query-to-return-the-range-of-coordinates

 

James E. Thomas
Enterprise Practice Support Systems Manager

Baker Botts L.L.P.
james....@bakerbotts.com
T +1.713.229.2196
F +1.713.229.8130
M +1.832.373.8117

910 Louisiana Street
Houston, Texas 77002
USA

   

--
--
You received this message because you are subscribed to the "Houston ColdFusion Users' Group" discussion list.
To unsubscribe, send email to houcfug-u...@googlegroups.com
For more options, visit http://groups.google.com/group/houcfug?hl=en

---
You received this message because you are subscribed to the Google Groups "Houston ColdFusion Users' Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to houcfug+u...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Confidentiality Notice:

The information contained in this email and any attachments is intended only for the recipient[s] listed above and may be privileged and confidential. Any dissemination, copying, or use of or reliance upon such information by or to anyone other than the recipient[s] listed above is prohibited. If you have received this message in error, please notify the sender immediately at the email address above and destroy any and all copies of this message.

[james....@bakerbotts.com]

This might as well: https://dev.mysql.com/doc/refman/5.7/en/partitioning-range.html

 

James E. Thomas
Enterprise Practice Support Systems Manager

Baker Botts L.L.P.
james....@bakerbotts.com
T +1.713.229.2196
F +1.713.229.8130
M +1.832.373.8117

910 Louisiana Street
Houston, Texas 77002
USA

   

 

From: hou...@googlegroups.com [mailto:hou...@googlegroups.com] On Behalf Of Mike Gillespie
Sent: 22 March, 2018 7:05 PM
To: Houston ColdFusion Users' Group <hou...@googlegroups.com>
Subject: [houcfug] SQL Query help

 

Hey y'all,

--

--
You received this message because you are subscribed to the "Houston ColdFusion Users' Group" discussion list.
To unsubscribe, send email to houcfug-u...@googlegroups.com
For more options, visit http://groups.google.com/group/houcfug?hl=en

---
You received this message because you are subscribed to the Google Groups "Houston ColdFusion Users' Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to houcfug+u...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[james....@bakerbotts.com]

Mike,

 

See my comments in red below – hopefully this gets you thinking in a different direction…

 

James E. Thomas
Enterprise Practice Support Systems Manager

Baker Botts L.L.P.
james....@bakerbotts.com
T +1.713.229.2196
F +1.713.229.8130
M +1.832.373.8117

910 Louisiana Street
Houston, Texas 77002
USA

   

 

From: hou...@googlegroups.com [mailto:hou...@googlegroups.com] On Behalf Of Mike Gillespie
Sent: 22 March, 2018 8:35 PM
To: Houston ColdFusion Users' Group <hou...@googlegroups.com>

Subject: Re: [houcfug] SQL Query help

 

I already use something similar to this, but since I cannot be sure of the range I am looking for, this solution won't work for me.  This is actually pretty much what I am trying to replace with something better.

 

Lets see if I can explain better what I am trying to solve.

 

I have a page that lists items for sale  it.  Is is paginated, 25 per page.  The page also has filters on it so that you can reduce the page count.

 

Are you using DataTables for your data grid output? We use it extensively in projects where a data grid with pagination, filtering, etc. is required. Very easy to use and very powerful (and extensible). Also very CFML-friendly.

 

I want to link to a specific product and highlight it -which is super simple, until you need to figure out all the filters and the specific page that product shows up on.

 

I was with you until you said “until you need to figure out all the filters and the specific page the product shows up on” – I’m going to travel into the future and use the next question to ask if you are showing the product in a modal, isn’t the modal page being called doing its own queries to pull up the product based on a passed product ID and/or additional info? You’re not creating a modal for every product on that page currently at run time and then displaying them, are you?

 

Each row in your tabular data output (e.g., each row in your table displaying the product info, and presumably a hyperlink that invokes the modal) should be able to have the correct params for each item at the time the table row is generated by a CFLOOP.

 

I think a bit more detail is needed to understand the issue here.

 

I currently do not have a separate "detail" page for the products, instead, click the product and you get a modal window with the detail (and additional add to cart button).

 

So this modal shows the detail of the product, based on the passed params – is the script that does the query to lookup the product info and populate the modal being called inside a modal DIV or similar structure?

 

Using javascript and a hash and URL params isn't working well in all browsers.

 

page.cfm/?f1=A&f2=B&f3=C#hashforsku or even page.cfm#hashforsku/?f1=A&f2=B&f3=C

 

Why do you have a “/” after page.cfm? why not page.cfm?f1-A&… ?

 

Params load page correctly, capture the hash to open the modal with javascript

 

I could just add another param

 

page.cfm/?f1=A&f2=B&f3=C&product=sku

 

But that opens a whole other can of worms with my security module (I only use integers in url params and a module to deal with non-integers)

 

If you have a module to deal with non-int, then what’s the issue with passing the SKU as a param? Can you look up a product only by say ProductID or similar, and then call the SKU inside the modal (if needed)? Or is the SKU the unique identifier?

 

working alone sometimes sucks - once in a while I need a sounding board..

 

Thanks again,

 

M

 

M

[Mike G]

comments below your comments

On Mon, Mar 26, 2018 at 8:39 AM, <james....@bakerbotts.com> wrote:

Mike,

 

See my comments in red below – hopefully this gets you thinking in a different direction…

 

James E. Thomas
Enterprise Practice Support Systems Manager

Baker Botts L.L.P.
james....@bakerbotts.com
T +1.713.229.2196
F +1.713.229.8130
M +1.832.373.8117

910 Louisiana Street
Houston, Texas 77002
USA

   

 

From: hou...@googlegroups.com [mailto:houcfug@googlegroups.com] On Behalf Of Mike Gillespie
Sent: 22 March, 2018 8:35 PM
To: Houston ColdFusion Users' Group <hou...@googlegroups.com>
Subject: Re: [houcfug] SQL Query help

 

I already use something similar to this, but since I cannot be sure of the range I am looking for, this solution won't work for me.  This is actually pretty much what I am trying to replace with something better.

 

Lets see if I can explain better what I am trying to solve.

 

I have a page that lists items for sale  it.  Is is paginated, 25 per page.  The page also has filters on it so that you can reduce the page count.

 

Are you using DataTables for your data grid output? We use it extensively in projects where a data grid with pagination, filtering, etc. is required. Very easy to use and very powerful (and extensible). Also very CFML-friendly.

This won't apply here, I use Bootstrap and layout the page either in box view or row view aka pods and grid - like Home Depot does

 

I want to link to a specific product and highlight it -which is super simple, until you need to figure out all the filters and the specific page that product shows up on.

 

I was with you until you said “until you need to figure out all the filters and the specific page the product shows up on” – I’m going to travel into the future and use the next question to ask if you are showing the product in a modal, isn’t the modal page being called doing its own queries to pull up the product based on a passed product ID and/or additional info? You’re not creating a modal for every product on that page currently at run time and then displaying them, are you?

No, and this is why it is so difficult.  Page is laid out with boxes, one for each item. Has very limited info.  Click the pic and a modal pops up with details about the item. To make the interaction instant, as well as for SEO, all of the modal content is on the page, just hidden with CSS (in row view the data is not hidden, you click to expand the row).  Also why I use pagination and page filters to find the data. the F1, F2, etc are page filters and the S in the query string is for the page. 

here is a page - https://www.sawblade.com/order-circular-saw-blades.cfm - Desktop or table, am in the middle of a mobile redesign so it ain't too pretty on a phone.

 

Each row in your tabular data output (e.g., each row in your table displaying the product info, and presumably a hyperlink that invokes the modal) should be able to have the correct params for each item at the time the table row is generated by a CFLOOP.

 

I think a bit more detail is needed to understand the issue here.

 

I currently do not have a separate "detail" page for the products, instead, click the product and you get a modal window with the detail (and additional add to cart button).

 

So this modal shows the detail of the product, based on the passed params – is the script that does the query to lookup the product info and populate the modal being called inside a modal DIV or similar structure?

No params - jquery climbs the tree to find the "param" data and populates the modal by unhiding the fragment off the page

 

Using javascript and a hash and URL params isn't working well in all browsers.

 

page.cfm/?f1=A&f2=B&f3=C#hashforsku or even page.cfm#hashforsku/?f1=A&f2=B&f3=C

 

Why do you have a “/” after page.cfm? why not page.cfm?f1-A&… ?

Typo 

 

Params load page correctly, capture the hash to open the modal with javascript

 

I could just add another param

 

page.cfm/?f1=A&f2=B&f3=C&product=sku

 

But that opens a whole other can of worms with my security module (I only use integers in url params and a module to deal with non-integers)

 

If you have a module to deal with non-int, then what’s the issue with passing the SKU as a param? Can you look up a product only by say ProductID or similar, and then call the SKU inside the modal (if needed)? Or is the SKU the unique identifier?

Because the security module is a bit more than that. Is was specifically designed to thwart sql injection, thus it uses a wide set of sql key words.  Since I do not control the naming of the skus, I don't pass them.  The only params I ever pass in the URL are system ids, nothing else. The security module, when it detects an "attack" passes back a cfheader status and aborts the page processing.  I did this because soon after our pre-game superbowl ads ran, we were hammered mercilessly by sql injection attacks from bot farms that brought the server down in a DDOS, twice. Since implementation, though it has been tried, sever has not been stopped by a DDOS. 

https://www.sawblade.com?select will show it in action and also shows that DotDefender was bypassed until the error was thrown... lol

And, as an aside, if you are looking for a tool that can emulate sql injection, send me an email and I will send you a link.  It is a pretty hazardous tool, so I won't post the link here - but it can perform 1200 "tests" in about 20 seconds

To unsubscribe, send email to houcfug-unsubscribe@googlegroups.com

For more options, visit http://groups.google.com/group/houcfug?hl=en

---
You received this message because you are subscribed to the Google Groups "Houston ColdFusion Users' Group" group.

To unsubscribe from this group and stop receiving emails from it, send an email to houcfug+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

--
You received this message because you are subscribed to the "Houston ColdFusion Users' Group" discussion list.

To unsubscribe, send email to houcfug-unsubscribe@googlegroups.com

For more options, visit http://groups.google.com/group/houcfug?hl=en

---
You received this message because you are subscribed to the Google Groups "Houston ColdFusion Users' Group" group.

To unsubscribe from this group and stop receiving emails from it, send an email to houcfug+unsubscribe@googlegroups.com.