sql - How can I use a php array in a mysql search query?

sql - How can I use a php array in a mysql search query?

I was going to use the scuttle solution on: http://www.pui.ch/phred/archives/2005/04/tags-database-schemas.html for handling searches on my website. I was wondering how I could take the search input from a user and turn it into a single query.

For instance, let's say a user inputted 'blue dogs' in their search query... How could I dynamically update the query to include ('blue', 'dogs') in union and intersection queries?

Destinee Nedyalko 2022-09-29
sql server - My SQL Query take too long when using a non-clustered index

sql server - My SQL Query take too long when using a non-clustered index

I have a database table that hold customer details and when they call in we use their telephone to lookup their details, but this usually takes about 2-3 seconds but recently it has been taking 5 seconds with no extra data. If I query the table using the home_phone_no = '441903354676' this returns in sub second. But if it is queried using home_phone_no = '441903354676' or business_phone_no = '441903354676' then this takes 5 seconds.

Now there are some 1.4m customer records. But if anyone can see anything obvious or provide some helpful suggestions this be most welcome.

This is the structure of the table

CREATE TABLE [dbo].[CCDB_ICR]
(
                [bill_account_no] [varchar](10) NOT NULL,
                [reference_id] [varchar](11) NULL,
                [bill_account_status] [varchar](2) NULL,
                [customer_type] [varchar](1) NULL,
                [customer_name] [varchar](56) NULL,
                [property_address] [varchar](69) NULL,
                [outer_post_code] [varchar](4) NULL,
                [inner_post_code] [varchar](3) NULL,
                [customer_move_in_date] [datetime2](7) NULL,
                [customer_move_out_date] [datetime2](7) NULL,
                [debt_flag] [varchar](1) NULL,
                [payment_category_flag] [varchar](2) NULL,
                [payment_plan_flag] [varchar](1) NULL,
                [key_customer_flag] [varchar](1) NULL,
                [home_phone_no] [varchar](12) NULL,
                [business_phone_no] [varchar](12) NULL,
                [contact_type] [varchar](4) NULL,
                [contact_code] [varchar](1) NULL,
                [contact_method] [varchar](1) NULL,
                [contact_date] [date] NULL,
                [contact_time] [varchar](5) NULL,
                [contact_status] [varchar](1) NULL,
                [contact_unit_resp] [varchar](4) NULL,
                [outstanding_balance] [decimal](9, 2) NULL,
                [date_time_inserted] [datetime] NOT NULL,
                [date_time_updated] [datetime] NULL,

    CONSTRAINT [PK_CCDB_ICR] 
        PRIMARY KEY CLUSTERED([bill_account_no] ASC)
                    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
                          IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, 
                          ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

This is the structure of the index:

CREATE NONCLUSTERED INDEX [NC_Business&Home_Phone$CCDB_ICR] 
    ON [dbo].[CCDB_ICR] ([home_phone_no] ASC, [business_phone_no] ASC)
    INCLUDE ([bill_account_no]) 

Here is the stored procedure

ALTER procedure [dbo].[GET_ACCOUNT_BY_PHONE_NUMBER]
(
@CLI varchar(12),
@RETURN_VALUE int out,
@NUMBER_OF_ACCOUNTS int out,
@ACCOUNT_NUMBER varchar(10) out
)
as
Begin Try
      declare @ret int
      Select @ret=COUNT(*) from CCDB_ICR where home_phone_no=@CLI or business_phone_no=@CLI
      if @ret=0
      select @RETURN_VALUE=0,
      @NUMBER_OF_ACCOUNTS=0,
      @ACCOUNT_NUMBER=null
      else if @ret=1
      select @RETURN_VALUE=0,
      @NUMBER_OF_ACCOUNTS=1,
      @ACCOUNT_NUMBER=(Select  bill_account_no from CCDB_ICR where home_phone_no=@CLI or business_phone_no=@CLI)
      else if @ret>1
      select @RETURN_VALUE=0,
      @NUMBER_OF_ACCOUNTS=@ret ,
      @ACCOUNT_NUMBER=null
end Try

Begin Catch
      select @RETURN_VALUE=-1,
      @NUMBER_OF_ACCOUNTS=null ,
      @ACCOUNT_NUMBER=null
End Catch
Mallt Agapi 2022-08-26
sql - Getting Values From Json Data Inside Array in Mysql

sql - Getting Values From Json Data Inside Array in Mysql

We are saving information in a json Column which contain json data in an array.

Data structure:

[
    {
        "type":"automated_backfill",
        "title":"Walgreens Sales Ad",
        "keyword":"Walgreens Sales Ad",
        "score":4
    },
    {
        "type":"automated_backfill",
        "title":"Nicoderm Coupons",
        "keyword":"Nicoderm Coupons",
        "score":4
    },
    {
        "type":"automated_backfill",
        "title":"Iphone Sales",
        "keyword":"Iphone Sales",
        "score":3
    },
    {
        "type":"automated_backfill",
        "title":"Best Top Load Washers",
        "keyword":"Best Top Load Washers",
        "score":1
    },
    {
        "type":"automated_backfill",
        "title":"Top 10 Best Cell Phones",
        "keyword":"Top 10 Best Cell Phones",
        "score":1
    },
    {
        "type":"automated_backfill",
        "title":"Tv Deals",
        "keyword":"Tv Deals",
        "score":0
    }
]

What we are trying:

SELECT id, ad_meta->'$**.type' FROM window_requests

that returns:

enter image description here

We are looking to get each type as row, which i think only possible with stored procedure, return whole column and then run loop on each row and return data...

Or can you think of a better solution?

Either Update Architecture:

or should we change our database and save information in separate table instead to json column ?

And then we can get easily join to get data with adding a foreign key.

Thanks you.

Sveinn Erland 2022-08-19
asp.net - Why would I get this error intermittently? "The server was not found or was not accessible"

asp.net - Why would I get this error intermittently? "The server was not found or was not accessible"

We keep getting this error randomly in our web application.

System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

It would make sense if we got the error ever time but to get it intermittently suggest something else is going on.

Has anyone experienced this? Any suggestions or theories?

Thanks!

Zaxaria Geirr 2022-08-17
sql - PostgreSQL - how do I create a window frame with a condition?

sql - PostgreSQL - how do I create a window frame with a condition?

Let's say I have the following table:

CREATE TABLE stock_prices (
  stock TEXT NOT NULL,
  date  DATE NOT NULL,
  price REAL NOT NULL,
  UNIQUE (stock, date)
);

I want to calculate for each day, the highest price for each stock in the preceding 3-month window.

I can't do a simple self join with date - INTERVAL(3 'MONTH') since my stock_price table has some "holes" for holidays and weekends. Similarly a naive window also does not work:

SELECT
  stock,
  date,  
  LAST_VALUE(price) OVER (PARTITION BY stock ORDER BY date ROWS 90 PRECEDING)
FROM stock_prices

I almost want a window frame here with a condition based on the current row. Is that possible in PostgreSQL?

Edna Morgen 2022-08-23
mysql - SQL SELECT WHERE NOT IN from same table query

mysql - SQL SELECT WHERE NOT IN from same table query

I'm having problem with the following SQL query and MySQL

SELECT
  id, cpid, label, cpdatetime
FROM
  mytable AS a
WHERE
  id NOT IN
  (
    SELECT
      id
    FROM
      mytable AS b
    WHERE
      a.label = b.label
    AND
      a.cpdatetime > b.cpdatetime
  )
AND
  label LIKE 'CB%'
AND
  cpid LIKE :cpid
GROUP BY label
ORDER BY cpdatetime ASC

the table looks like this

1 | 170.1 | CB55 | 2013-01-01 00:00:01
2 | 135.5 | CB55 | 2013-01-01 00:00:02
3 | 135.6 | CB59 | 2013-01-01 00:00:03
4 | 135.5 | CM43 | 2013-01-01 00:00:04
5 | 135.5 | CB46 | 2013-01-01 00:00:05
6 | 135.7 | CB46 | 2013-01-01 00:00:06
7 | 170.2 | CB46 | 2013-01-01 00:00:07

I would like my query to return

3 | 135.6 | CB59
5 | 135.5 | CB46

Edit

labels are dogs/cats and cpids are temporary family keeping the dogs/cats.

Dogs/cats move from family to family.

I need to find dogs/cats who were in :userinput family but only if they were not in another family previously

I can't alter the database and just have to work with the data as they are and I'm not the one who wrote the application/database schema.

Filibert Nosizwe 2022-08-18
sql server - SQL Query aggregate may not appear in WHERE clause

sql server - SQL Query aggregate may not appear in WHERE clause

I have this SQL statement and SQL Server is giving me the following error:

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list.

SELECT 
  SUM(M1.InvoiceTotal)-SUM(M1.AmountApplied) as PastDueAmount
  , M1.BillingID
  , M2.Name
  , M2.DelinquentDaysThreshold
  , M2.DelinquentAmountThreshold
  , DATEDIFF(d, MIN(BillingDate),GETDATE()) as DaysLate
FROM Invoices M1 
LEFT JOIN ClientAccounts M2 ON M1.BillingID = M2.ID
WHERE 
  InvoiceTotal <> AmountApplied
  AND M2.DelinquentDaysThreshold > DATEDIFF(d, MIN(BillingDate),GETDATE())
  OR (SUM(M1.InvoiceTotal)-SUM(M1.AmountApplied)) > M2.DelinquentAmountThreshold
GROUP BY 
  M1.BillingID
  , M2.Name
  , M2.DelinquentDaysThreshold
  , M2.DelinquentAmountThreshold

In the where clause, I only want to pull records where the oldest unpaid Billing Invoice Date is greater than the DelinquentDaysThreshhold (in days), OR the PastDueAmount (a calculated value) is greater than the DelinquentAmountThreshold.
For some reason SQL Server does not like aggregated amounts.

Vlastimila Zhanna 2022-08-13
sql - Complete list of special characters for hive LIKE operator

sql - Complete list of special characters for hive LIKE operator

select * from table where column like '%a|b%'

The above query matches all rows with the column having either 'a' OR 'b' as a substring. What if I want to match the substring "a|b"? Using the query,

select * from table where column like '%a\|b%'

yields the same result.

Can I get the complete reference for the LIKE operator in hive? The UDF manual seems insufficient.

Naliaka Dipika 2022-09-26
sql - MYSQL - Combine rows with multiple duplicate values and delete duplicates afterwards

sql - MYSQL - Combine rows with multiple duplicate values and delete duplicates afterwards

So I have my database set up as a single table. In that table I have collected source URL and description (I am scraping the product description from a number of pages). Unfortunately I have ended up with multiple rows in the database for a URL/source page if there is more than one paragraph.

What I would like to do is, if there are multiple rows with the same URL, combine the description from each row and then delete the duplicate rows for that URL.

My table is literally structured like so:

table             
+----+----------------------------+-------------+
| id | url                        | description |
+----+----------------------------+-------------+
|  1 | http://example.com/page-a  | paragraph 1 |
|  2 | http://example.com/page-a  | paragraph 2 |
|  3 | http://example.com/page-a  | paragraph 3 |
|  4 | http://example.com/page-b  | paragraph 1 |
|  5 | http://example.com/page-b  | paragraph 2 |
+----+----------------------------+-------------+

How I want it is like:

table             
+----+----------------------------+-------------------------------------+
| id | url                        | description                         |
+----+----------------------------+-------------------------------------+
|  1 | http://example.com/page-a  | paragraph 1 paragraph 2 paragraph 3 |
|  2 | http://example.com/page-b  | paragraph 1 paragraph 2             |
+----+----------------------------+-------------------------------------+

I'm not so bothered about the IDs being updated to be correct, I just want to be able to combine the rows where the paragraphs should be in the same field as they are the same URLs, and then delete the duplicates.

Any help would be greatly appreciated!

Urbain Vidar 2022-09-23
sql - How to get a count even if there are no results corresponding mysql?

sql - How to get a count even if there are no results corresponding mysql?

I am formalating a query to give the number of reports submitted over the last year ordered by date. I get the current year and month with php:

$year = date('Y') - 1;
$month = date('m');

and execute the following query: SQL:

SELECT month(date_lm) AS `month` ,
count(*) AS `count`
FROM `reports` 
WHERE (status = 'submitted') 
AND (date_lm > 2012-08) 
GROUP BY month(date_lm) 
ORDER BY month(date_lm) ASC

And because there has only been 1 submitted in the last year it gives me only 1 result...

| month  |  count  |
|   7    |    1    |

But I would like the result set to show:

| month  |  count  |
|   9    |    0    |
|   10   |    0    |
|   11   |    0    |
|   12   |    0    |
|   1    |    0    |
|   2    |    0    |
|   3    |    0    |
|   4    |    0    |
|   5    |    0    |
|   6    |    0    |
|   7    |    1    |
|   8    |    0    |

Is that possible?

Helle Sigrid 2022-08-18