Author Topic: SQL Query help  (Read 10876 times)

Offline JoeyShmoe

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Nov 2014
  • Posts: 1276
  • Total likes: 254
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
  • Location: Lakewood
Re: SQL Query help
« Reply #60 on: December 08, 2015, 11:04:02 AM »
My query is set up like this

update orders_products set products_name = replace(products_name, 'Product ABC', 'Supplies:Product ABC');

It seems like every time the cron runs it adds another "supplies:"

it ends up after being run a few times as:

Supplies:Supplies:Supplies:Supplies:Supplies:Supplies:Supplies:Supplies:Supplies:Supplies:Supplies:Product ABC

So i need to rewrite the statement to ignore if it already exists
Why not add
Code: [Select]
WHERE products_name = 'Product ABC'? Then it'll only update if it's 'Product ABC' and not 'Supplies:Product ABC'
DDF A-Z Link Extension
Chrome
Firefox
Info

Offline Alexsei

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Sep 2011
  • Posts: 5789
  • Total likes: 1421
  • DansDeals.com Hat Tips 5
    • View Profile
    • Travel & Kivrei Zadikim
  • Location: Truckistan
  • Programs: COVID-23
Re: SQL Query help
« Reply #61 on: December 08, 2015, 12:19:06 PM »
Why not add
Code: [Select]
WHERE products_name = 'Product ABC'? Then it'll only update if it's 'Product ABC' and not 'Supplies:Product ABC'

So the query would be:
Code: [Select]
update orders_products set products_name = replace(WHERE products_name = 'Product ABC', 'Supplies:Product ABC');
I guess i'm wrong somewhere
Quote
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE products_name = 'Product ABC', 'Supplies:Product ABC')' at line 1
« Last Edit: December 08, 2015, 12:26:22 PM by Alexsei »
Jews ≠ Zionists
Palestinians ≠ Hamas
Satmar ≠ SatmarHQ

Offline JoeyShmoe

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Nov 2014
  • Posts: 1276
  • Total likes: 254
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
  • Location: Lakewood
Re: SQL Query help
« Reply #62 on: December 08, 2015, 12:31:24 PM »
So the query would be:
Code: [Select]
update orders_products set products_name = replace(WHERE products_name = 'Product ABC', 'Supplies:Product ABC');
No, it would be
Code: [Select]
UPDATE orders_products SET products_name = REPLACE(products_name, 'Product ABC', 'Supplies:Product ABC') WHERE products_name  = 'Product ABC';
DDF A-Z Link Extension
Chrome
Firefox
Info

Offline Alexsei

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Sep 2011
  • Posts: 5789
  • Total likes: 1421
  • DansDeals.com Hat Tips 5
    • View Profile
    • Travel & Kivrei Zadikim
  • Location: Truckistan
  • Programs: COVID-23
Re: SQL Query help
« Reply #63 on: December 08, 2015, 01:57:22 PM »
No, it would be
Code: [Select]
UPDATE orders_products SET products_name = REPLACE(products_name, 'Product ABC', 'Supplies:Product ABC') WHERE products_name  = 'Product ABC';
Thanks
Jews ≠ Zionists
Palestinians ≠ Hamas
Satmar ≠ SatmarHQ

Offline yesitsme

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2014
  • Posts: 5020
  • Total likes: 2237
  • DansDeals.com Hat Tips 4
  • Gender: Male
    • View Profile
Re: SQL Query help
« Reply #64 on: December 08, 2015, 04:39:58 PM »
No, it would be
Code: [Select]
UPDATE orders_products SET products_name = REPLACE(products_name, 'Product ABC', 'Supplies:Product ABC') WHERE products_name  = 'Product ABC';
If that is Good Then This Is Better
Code: [Select]
UPDATE orders_products SET products_name = 'Supplies:Product ABC' WHERE products_name  = 'Product ABC';
"REPLACE" Replaces a part of the string
 Once you use "Where equals" Then You Don't Need A Replace,

If you Need To use Replace, You should Use "INSTR" or "LIKE" Instead of "Equals",
["-"]

Offline JoeyShmoe

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Nov 2014
  • Posts: 1276
  • Total likes: 254
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
  • Location: Lakewood
Re: SQL Query help
« Reply #65 on: December 08, 2015, 05:10:29 PM »
If that is Good Then This Is Better
Code: [Select]
UPDATE orders_products SET products_name = 'Supplies:Product ABC' WHERE products_name  = 'Product ABC';
"REPLACE" Replaces a part of the string
 Once you use "Where equals" Then You Don't Need A Replace,

If you Need To use Replace, You should Use "INSTR" or "LIKE" Instead of "Equals",
Agreed
DDF A-Z Link Extension
Chrome
Firefox
Info

Offline Alexsei

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Sep 2011
  • Posts: 5789
  • Total likes: 1421
  • DansDeals.com Hat Tips 5
    • View Profile
    • Travel & Kivrei Zadikim
  • Location: Truckistan
  • Programs: COVID-23
Re: SQL Query help
« Reply #66 on: December 09, 2015, 12:26:05 PM »
Is this the right way to create a php file for a cron ?

Code: [Select]
<?php
$servername 
"localhost";
$username "xxxxxxxxx";
$password "xxxxxxxxxx";
$dbname "xxxxxxxxxxxx";

// Create connection
$conn = new mysqli($servername$username$password$dbname);
// Check connection
if ($conn->connect_error) {
    die(
"Connection failed: " $conn->connect_error);
}


$sql "UPDATE orders_products SET products_name = REPLACE(products_name, 'Product ABC', 'Supplies:Product ABC') WHERE products_name  = 'Product ABC'";


if (
$conn->query($sql) === TRUE) {
    echo 
"New record created successfully";
} else {
    echo 
"Error: " $sql "<br>" $conn->error;
}

$conn->close();
?>
Jews ≠ Zionists
Palestinians ≠ Hamas
Satmar ≠ SatmarHQ

Offline JoeyShmoe

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Nov 2014
  • Posts: 1276
  • Total likes: 254
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
  • Location: Lakewood
Re: SQL Query help
« Reply #67 on: December 09, 2015, 01:29:04 PM »
Is this the right way to create a php file for a cron ?
Seems about right. Although I think you should update the query to the one @yesitsme posted above
DDF A-Z Link Extension
Chrome
Firefox
Info

Offline birne

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Sep 2012
  • Posts: 403
  • Total likes: 0
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: SQL Query help
« Reply #68 on: March 15, 2017, 05:46:52 PM »
any SQL Queries pros here can help me out ??

i have a query that puts the address , city , state , and zip in different columns , and im trying to merge them

any help would be much appreciated!

TIA

examples    

WHY NOT?!?!

Online etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12862
  • Total likes: 3317
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: SQL Query help
« Reply #69 on: March 15, 2017, 05:54:40 PM »
any SQL Queries pros here can help me out ??

i have a query that puts the address , city , state , and zip in different columns , and im trying to merge them

any help would be much appreciated!

TIA

examples    
Do you want them to end up in one column?
You can do something like this:

SELECT ST2.Address + ' ' + ST2.Room + ' ' + ST.city + ', ' + ST2.State + ' ' + ST.zip As Address FROM .....
Workflowy. You won't know what you're missing until you try it.

Offline JoeyShmoe

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Nov 2014
  • Posts: 1276
  • Total likes: 254
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
  • Location: Lakewood
Re: SQL Query help
« Reply #70 on: March 15, 2017, 05:58:25 PM »
any SQL Queries pros here can help me out ??

i have a query that puts the address , city , state , and zip in different columns , and im trying to merge them

any help would be much appreciated!

TIA
I believe you're looking for the CONCAT function
DDF A-Z Link Extension
Chrome
Firefox
Info

Offline birne

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Sep 2012
  • Posts: 403
  • Total likes: 0
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: SQL Query help
« Reply #71 on: March 15, 2017, 06:07:07 PM »
Do you want them to end up in one column?
You can do something like this:

SELECT ST2.Address + ' ' + ST2.Room + ' ' + ST.city + ', ' + ST2.State + ' ' + ST.zip As Address FROM .....

do i have to add the FROM there ?

its already at the end of the SELECT..
WHY NOT?!?!

Online etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12862
  • Total likes: 3317
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: SQL Query help
« Reply #72 on: March 15, 2017, 06:54:02 PM »
do i have to add the FROM there ?

its already at the end of the SELECT..
No need to add an extra FROM
Workflowy. You won't know what you're missing until you try it.

Offline birne

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Sep 2012
  • Posts: 403
  • Total likes: 0
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: SQL Query help
« Reply #73 on: March 16, 2017, 11:07:43 AM »
another Q :P

im trying to pull the full LAT/LON from the system , but its only coming up as XX.XX , not the exact location...  i n the xml file it has the full location...   any ideas?

 

TIA
WHY NOT?!?!

Online AsherO

  • Global Moderator
  • Dansdeals Lifetime 30K Presidential Platinum Elite
  • **********
  • Join Date: May 2008
  • Posts: 30534
  • Total likes: 7831
  • DansDeals.com Hat Tips 79
    • View Profile
  • Location: NYC
Re: SQL Query help
« Reply #74 on: March 16, 2017, 11:42:56 AM »
another Q :P

im trying to pull the full LAT/LON from the system , but its only coming up as XX.XX , not the exact location...  i n the xml file it has the full location...   any ideas?

 

TIA

If you're importing the XML into the database, store the long/lat as strings, not numbers.
100% of Likes will be donated to support our brothers and sisters in Eretz Yisrael

Offline birne

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Sep 2012
  • Posts: 403
  • Total likes: 0
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: SQL Query help
« Reply #75 on: April 04, 2017, 11:24:25 AM »
instead of having to change the date each time , how can i get the system to use todays date each time?

what do i have to put instead?


TIA
WHY NOT?!?!

Offline ponash123

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Jun 2015
  • Posts: 473
  • Total likes: 264
  • DansDeals.com Hat Tips 0
    • View Profile
Re: SQL Query help
« Reply #76 on: April 04, 2017, 11:52:37 AM »
GETDATE()

Offline birne

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Sep 2012
  • Posts: 403
  • Total likes: 0
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: SQL Query help
« Reply #77 on: April 04, 2017, 11:54:37 AM »
where do i put that? replace what?

thanks
WHY NOT?!?!

Offline lubaby

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jun 2010
  • Posts: 5335
  • Total likes: 736
  • DansDeals.com Hat Tips 12
    • View Profile
Re: SQL Query help
« Reply #78 on: April 04, 2017, 12:06:34 PM »
where do i put that? replace what?

thanks
Code: [Select]
set @FromDate = GETDATE()

Offline Ergel

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Jun 2010
  • Posts: 12818
  • Total likes: 905
  • DansDeals.com Hat Tips 3
    • View Profile
Re: SQL Query help
« Reply #79 on: April 05, 2017, 12:30:34 AM »
Out just use sysdate
Life isn't about checking the boxes. Nobody cares.