Author Topic: SQL Query help  (Read 2605 times)

Offline JoeyShmoe

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Nov 2014
  • Posts: 797
  • Total likes: 6
  • 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'

Offline Alexsei

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Sep 2011
  • Posts: 1854
  • Total likes: 4
  • DansDeals.com Hat Tips 1
    • View Profile
    • Travel & Kivrei Zadikim
  • Location: Champlain NY, with 1' in canada
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 »
Слава Україні! Героям слава!

Offline JoeyShmoe

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Nov 2014
  • Posts: 797
  • Total likes: 6
  • 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';

Offline Alexsei

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Sep 2011
  • Posts: 1854
  • Total likes: 4
  • DansDeals.com Hat Tips 1
    • View Profile
    • Travel & Kivrei Zadikim
  • Location: Champlain NY, with 1' in canada
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
Слава Україні! Героям слава!

Offline yesitsme

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Dec 2014
  • Posts: 786
  • Total likes: 12
  • DansDeals.com Hat Tips 4
    • View Profile
    • perpetual talk
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",
be conservative in what you do, be liberal in what you accept from others.

Offline JoeyShmoe

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Nov 2014
  • Posts: 797
  • Total likes: 6
  • 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

Offline Alexsei

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Sep 2011
  • Posts: 1854
  • Total likes: 4
  • DansDeals.com Hat Tips 1
    • View Profile
    • Travel & Kivrei Zadikim
  • Location: Champlain NY, with 1' in canada
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();
?>
Слава Україні! Героям слава!

Offline JoeyShmoe

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Nov 2014
  • Posts: 797
  • Total likes: 6
  • 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

Offline birne

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Sep 2012
  • Posts: 386
  • 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?!?!

Offline etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 7823
  • Total likes: 46
  • 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 Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Nov 2014
  • Posts: 797
  • Total likes: 6
  • 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

Offline birne

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Sep 2012
  • Posts: 386
  • 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?!?!

Offline etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 7823
  • Total likes: 46
  • 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: 386
  • 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?!?!

Offline AsherO

  • Global Moderator
  • Dansdeals Lifetime 20K Presidential Platinum Elite
  • **********
  • Join Date: May 2008
  • Posts: 20100
  • Total likes: 33
  • DansDeals.com Hat Tips 73
    • View Profile
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.
HTs are the new post-count.