[FIXED] How to insert a value that contains an apostrophe (single quote)?

Issue

What is the correct SQL syntax to insert a value with an apostrophe in it?

Insert into Person
  (First, Last)
Values
  'Joe',
  'O'Brien'

I keep getting an error as I think the apostrophe after the O is the ending tag for the value.

Solution

Escape the apostrophe (i.e. double-up the single quote character) in your SQL:

INSERT INTO Person
    (First, Last)
VALUES
    ('Joe', 'O''Brien')
              /\
          right here  

The same applies to SELECT queries:

SELECT First, Last FROM Person WHERE Last = 'O''Brien'

---

The apostrophe, or single quote, is a special character in SQL that specifies the beginning and end of string data. This means that to use it as part of your literal string data you need to escape the special character. With a single quote this is typically accomplished by doubling your quote. (Two single quote characters, not double-quote instead of a single quote.)

Note: You should only ever worry about this issue when you manually edit data via a raw SQL interface since writing queries outside of development and testing should be a rare occurrence. In code there are techniques and frameworks (depending on your stack) that take care of escaping special characters, SQL injection, etc.

Answered By - Paul Sasik

Answer Checked By - Timothy Miller (PHPFixing Admin)

Read More

[FIXED] How can i save a list into a mysql database?

Issue

I want to save a list with discord invites into my database. But everytime I ran this code, get's only 1 item saved in the database. I tried to print the inviter ids to check if he just runs one item, but he runs the code for every item. So what is wrong? Why get's only 1 item saved to the database?

            for invite in invites:

                if invite.inviter is None:
                    continue

                if not ctx.author.guild.get_member(int(invite.inviter.id)):
                    continue

                if int(invite.uses) == 0:
                    continue

                await cursor.execute("SELECT * FROM guild_invite_count WHERE guild_id = %s AND user_id = %s IS NOT NULL", (ctx.author.guild.id, invite.inviter.id))
                find_user = await cursor.fetchone()
                if find_user:
                    await cursor.execute("UPDATE guild_invite_count SET real_count = real_count + %s, total_count = total_count + %s WHERE guild_id = %s AND user_id = %s", (int(invite.uses), int(invite.uses), ctx.author.guild.id, invite.inviter.id))
                else:
                    await cursor.execute("INSERT INTO guild_invite_count (guild_id, user_id, real_count, total_count) VALUES (%s, %s, %s, %s)", (ctx.author.guild.id, invite.inviter.id, int(invite.uses), int(invite.uses)))
                print(invite.inviter.id)
                await mydb.commit()

Solution

This SQL

SELECT * FROM guild_invite_count WHERE guild_id = %s AND user_id = %s IS NOT NULL

is logically incorrect. AND user_id = %s IS NOT NULL reduces to AND user_id = true (unless the parameter is null). Just do

SELECT * FROM guild_invite_count WHERE guild_id = %s AND user_id = %s

Answered By - snakecharmerb

Answer Checked By - Clifford M. (PHPFixing Volunteer)

Read More

[FIXED] How to Update and Insert Array of Objects in table with OPENJSON and where conditions

Issue

I want to update and insert the stock, InvM, and Invoice table with OPENJSON(). I am new at OPENJSON() in SQL Server. I have an array of objects and I want to insert each object to new row of the tables. I want to iterate through every object and insert or update it using Where clause and OPENJSON():

Array of Objects:

DECLARE @f NVARCHAR(MAX) = N'[{
"Batch": "CP008",
"Bonus": -26,
"Code": 002,
"Cost": 50,
"Disc1": 0,
"Name": "Calpax_D Syp 120Ml",
"Price": undefined,
"Quantity": "1",
"SNO": 9,
"STP": 153,
"Stax": 0,
"TP": 50,
"Total": 50,
"invoiceno": 71,
"profit": 156,
"randomnumber": "3MO0FMDLUX0D9P1N7HGV",
"selected": false,
},
{
"Batch": "P009",
"Bonus": 0,
"Code": 823,
"Cost": 237.14999389648438,
"Disc1": 0,
"Name": "PENZOL TAB 40 MG",
"Price": undefined,
"Quantity": "2",
"SNO": 94,
"STP": 263.5,
"Stax": 0,
"TP": 263.5,
"Total": 527,
"invoiceno": 71,
"profit": 156,
"randomnumber": "3MO0FMDLUX0D9P1N7HGV",
"selected": false,
}
]'

How to update the Stock table and reduce the quantity with where condition if the Name of the medicine in the object array matches with the medicine in the Stock table (I came up with this but it is not working correctly):

UPDATE Stock 
SET Qty = Qty - qty  
from OPENJSON(@files) 
with(qty INT '$.Quantity', Name12 VARCHAR(55) '$.Name') 
where Stock.Name = Name12  

Same goes for the InvM and Invoice table I want to insert new row with where condition

insert into InvM (RNDT, Dat, SMID, CID, Total, USR, RefNo, SRID, Txt,InvTime) 
 select RNDT, getdate(), Salesman.SMID, Customer.CID,@total, USR.Name, 0, 
 0,Salesman.Name,CURRENT_TIMESTAMP
 from Salesman, USR,Customer, OPENJSON(@files)  
 with(
    RNDT NVARCHAR(max) '$.randomnumber'
    )
 where USR.Name = 'moiz'



insert into Invoice (SNO, RNDT, Invno, Code, Name, Batch, STP, Qty, Bon, Disc, Stax, NET, 
TP, Cost, Profit)
select SNO, RNDT, InvNo, Code, Name, Batch, STP, Qty, Bon, Disc, Stax, NET, TP, 
Cost,profit  
from OPENJSON(@files) 
with (  
Batch INT '$.Batch',
Bon INT '$.Bouns',
Code INT '$.Code',
Cost INT '$.Cost',
Disc INT '$.Disc1',
Name NVARCHAR(Max) '$.Name',
STP INT '$.STP',
Qty INT '$.Quantity',
SNO INT '$.SNO',
Stax INT '$.Stax',
RNDT NVARCHAR(max) '$.randomnumber',
InvNo INT '$.invoiceno',
TP INT '$.TP',
NET INT '$.Total',
profit INT '$.profit'
)

Solution

You need to parse the input JSON with OPENJSON() and update the table using an appropriate JOIN. The following example is a posiible solution to your problem:

Sample data:

SELECT *
INTO Stock
FROM (VALUES
   ('PENZOL TAB 40 MG', 100),
   ('Calpax_D Syp 120Ml', 100)
) v (Name, Quantity)

JSON:

DECLARE @files NVARCHAR(MAX) = N'[
   {
      "Batch":"CP008",
      "Bonus":-26,
      "Code":2,
      "Cost":50,
      "Disc1":0,
      "Name":"Calpax_D Syp 120Ml",
      "Price":"undefined",
      "Quantity":"1",
      "SNO":9,
      "STP":153,
      "Stax":0,
      "TP":50,
      "Total":50,
      "invoiceno":71,
      "profit":156,
      "randomnumber":"3MO0FMDLUX0D9P1N7HGV",
      "selected":false
   },
   {
      "Batch":"P009",
      "Bonus":0,
      "Code":823,
      "Cost":237.14999389648438,
      "Disc1":0,
      "Name":"PENZOL TAB 40 MG",
      "Price":"undefined",
      "Quantity":"2",
      "SNO":94,
      "STP":263.5,
      "Stax":0,
      "TP":263.5,
      "Total":527,
      "invoiceno":71,
      "profit":156,
      "randomnumber":"3MO0FMDLUX0D9P1N7HGV",
      "selected":false
   }
]';

UPDATE statement:

UPDATE s
SET s.Quantity = s.Quantity - j.Quantity
FROM Stock s
JOIN OPENJSON(@files) WITH (
   Name varchar(100) '$.Name', 
   Quantity int '$.Quantity' 
) j ON s.Name = j.Name

Result:

Name	Quantity
PENZOL TAB 40 MG	98
Calpax_D Syp 120Ml	99

Answered By - Zhorov

Answer Checked By - Mary Flores (PHPFixing Volunteer)

Read More

[FIXED] How do I update every row of a particular column with values from another column?

Issue

I am trying to update a column called software_id with a possible three values from a lookup table. So update user softwares software id column with the values from the softwares table of the id column. But there are only three rows in the softwares table. In the user softwares table there are over 1000. But when I run the query below only three rows get updated and the rest are left as null.

So the softwares id column is number and goes 1,2,3 and the user_sofwares software_id column is all null.

When I run the following query.

UPDATE user_software c 
SET sotware_id = (
                  SELECT DISTINCT id
                  FROM softwares
                  WHERE id = c.id
                  );

Only the first three rows of the destination table get updated. I want every row on the user_softwares column to be updated with either 1,2 or 3. So it should be 1,2,3,1,2,3 for example.

I keep getting this error whenever i tried to update all rows

Error report - ORA-01427: single-row subquery returns more than one row

Solution

You can do this with a MERGE statement:

MERGE INTO (SELECT software_id, 1, ora_hash(ROWID, 2) + 1 AS fake_id
              FROM user_software) u_soft
USING (SELECT DISTINCT id    
         FROM softwares) sftw
   ON (sftw.id = u_soft.fake_id)
 WHEN MATCHED THEN UPDATE SET u_soft.software_id = sftw.id;

(considering your matches are unique)

Answered By - Gnqz

Answer Checked By - Katrina (PHPFixing Volunteer)

Read More

[FIXED] How to change array header and insert the array into MySQL database in PHPExcel?

Issue

I have a piece of code for making an Excel Import (.xlsx). I have a question about inserting iterated array and changing the array header.

controller:

$this->load->library('Excel');
$file = 'upload/keuangan.xlsx';

//read file from path
$objPHPExcel = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel->setReadDataOnly(true);

$objPHPExcel = $objPHPExcel->load($file);
$objWorksheet = $objPHPExcel->getActiveSheet();

$highestRow = $objWorksheet->getHighestRow();
$highestColumn = $objWorksheet->getHighestColumn();
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); //dari 0

$data['nomor'] = $this->participant_model->get_dummy(false)->result_array();
$num = $this->participant_model->get_dummy(false)->num_rows();

for($row=2; $row <= $highestRow; ++$row){
    for($col=0; $col < $highestColumnIndex; ++$col){
        $ExcelData[$col] = $objWorksheet->getCellByColumnAndRow($col,$row)->getValue();
    }
    echo "<pre>";print_r($ExcelData);echo "</pre>";
    $this->session->set_flashdata('message_alert','<div class="alert alert-success">Data berhasil dimasukkan</div>');
    $this->participant_model->save($ExcelData);
}

model :

public function save($data_participant){
    $this->db->insert('t_keuangan',$data_participant);
}

This is keuangan.xlsx, it consists a string header. I select from second row. The column may be expanded.

No Ijazah   Jumlah      Keluar 1    Keluar 2    Keluar 3
1234/SH     100000000   21000000    19000000    18000000
2345/SK     120000000   16000000    19000000    13000000
1245/SA     140000000   20000000    15000000    25000000

The result of $ExcelData is :

Array
(
    [0] => 1234/SH
    [1] => 100000000
    [2] => 21000000
    [3] => 19000000
    [4] => 18000000
)
Array
(
    [0] => 2345/SK
    [1] => 120000000
    [2] => 16000000
    [3] => 19000000
    [4] => 13000000
)
Array
(
    [0] => 1245/SA
    [1] => 140000000
    [2] => 20000000
    [3] => 15000000
    [4] => 25000000
)

I'm trying to put this $ExcelData into my database MySQL. I can insert $ExcelData into my database but the header is still in numerical ([0],[1],[2],[3]), because $row and $col that used by $getCellByColumnAndRow are numeric.

How to insert these arrays into database after the header are changed into

Array
(
    [no_ijazah] => 1245/SA
    [jumlah] => 140000000
    [keluar_1] => 20000000
    [keluar_3] => 15000000
    [keluar_4] => 25000000
)

?

Solution

Hope this will help you:

You can change your $ExcelData data like this to insert into the database;

See working example : https://eval.in/1026123

Use array_combine to add key value pair like this:

$value1 = Array
(
    '0' => '1234/SH',
    '1' => 100000000,
    '2' => 21000000,
    '3' => 19000000,
    '4' => 18000000
);
$value2 = Array
(
    '0' => '2345/SK',
    '1' => 120000000,
    '2' => 16000000,
    '3' => 19000000,
    '4' => 13000000
);



$key = array('no_ijazah','jumlah','keluar_1','keluar_3','keluar_4');

$combined[] = array_combine($key, $value1);
$combined[] = array_combine($key, $value2);.

print_r($combined);
$this->participant_model->save($combined);

Output

Array
(
    [0] => Array
        (
            [no_ijazah] => 1234/SH
            [jumlah] => 100000000
            [keluar_1] => 21000000
            [keluar_3] => 19000000
            [keluar_4] => 18000000
        )

    [1] => Array
        (
            [no_ijazah] => 2345/SK
            [jumlah] => 120000000
            [keluar_1] => 16000000
            [keluar_3] => 19000000
            [keluar_4] => 13000000
        )
)

Use insert_batch to insert the data into table

$this->db->insert_batch('t_keuangan',$combined);

Whole code should be like this :

$key = array('no_ijazah','jumlah','keluar_1','keluar_3','keluar_4');
for($row=2; $row <= $highestRow; ++$row)
{
    for($col=0; $col < $highestColumnIndex; ++$col)
    {
        $ExcelData[$col] = $objWorksheet->getCellByColumnAndRow($col,$row)->getValue();
    }

    $combined[] = array_combine($key, $ExcelData);
}
$this->session->set_flashdata('message_alert','<div class="alert alert-success">Data berhasil dimasukkan</div>');
$this->participant_model->save($combined);

model function should be like this :

public function save($data_participant)
{
    $this->db->insert_batch('t_keuangan',$data_participant);
}

For more : http://php.net/manual/en/function.array-combine.php

Answered By - Pradeep

Answer Checked By - Mildred Charles (PHPFixing Admin)

Read More

[FIXED] Postgres error: null value in column "id" - during insert operation

Issue

I use postgresql and yii2 framework. Well I got a very interesting error message:

SQLSTATE[23502]: Not null violation: 7 ERROR: null value in column "id" violates not-null constraint
DETAIL: Failing row contains (null, 1, null, null, null, null, 1, Demo, , , , 1998-01-01, , , , 345345435453453, , , , , 1, , , f, f, f, f, 10, f, 1, f, f, f, null, null, null, 1470477479, 1470477479, null).

But I checked my Insert command, and there is not "id" column there!

INSERT INTO "advertiser" ("languages", "type", "name", "display_name", "title", "about", "birthday", "gender", "country_id", "county_id", "city_id", "city_part", "street", "house_number", "phone", "public_email", "public_url", "motto", "message", "im_facebook", "im_skype", "has_viber", "has_whatsapp", "has_sms_response", "visible_birthday", "is_checked", "status", "version", "user_id", "created_at", "updated_at") VALUES (NULL, 1, 'Demo', '', '', '', '1998-01-01', 1, NULL, NULL, NULL, '', '', '', '345345435453453', '', '', '', '', '', '', FALSE, FALSE, FALSE, FALSE, FALSE, 10, NULL, 1, 1470477479, 1470477479) RETURNING "id"

So I really cannot understand this error message. I do not find that the Postgres or Yii try to insert a null ID value or what.

By the way here you can find the structure

                                                    Table "public.advertiser"
        Column         |          Type          |            Modifiers            | Storage  | Stats target | Description 
-----------------------+------------------------+---------------------------------+----------+--------------+-------------
 id                    | integer                | not null                        | plain    |              | 
 user_id               | integer                |                                 | plain    |              | 
 country_id            | integer                |                                 | plain    |              | 
 county_id             | integer                |                                 | plain    |              | 
 city_id               | integer                |                                 | plain    |              | 
 district_id           | integer                |                                 | plain    |              | 
 type                  | smallint               |                                 | plain    |              | 
 name                  | character varying(255) | not null                        | extended |              | 
 display_name          | character varying(255) | default NULL::character varying | extended |              | 
 title                 | character varying(255) | default NULL::character varying | extended |              | 
 about                 | text                   |                                 | extended |              | 
 birthday              | date                   | not null                        | plain    |              | 
 city_part             | character varying(255) | default NULL::character varying | extended |              | 
 street                | character varying(255) | default NULL::character varying | extended |              | 
 house_number          | character varying(20)  | default NULL::character varying | extended |              | 
 phone                 | character varying(15)  | not null                        | extended |              | 
 public_email          | character varying(255) | default NULL::character varying | extended |              | 
 public_url            | character varying(255) | default NULL::character varying | extended |              | 
 motto                 | character varying(255) | default NULL::character varying | extended |              | 
 message               | text                   |                                 | extended |              | 
 gender                | smallint               | not null default 1              | plain    |              | 
 im_facebook           | character varying(255) | default NULL::character varying | extended |              | 
 im_skype              | character varying(255) | default NULL::character varying | extended |              | 
 has_viber             | boolean                | not null default false          | plain    |              | 
 has_whatsapp          | boolean                | not null default false          | plain    |              | 
 has_sms_response      | boolean                | not null default false          | plain    |              | 
 visible_birthday      | boolean                | not null default false          | plain    |              | 
 status                | smallint               | not null default 10             | plain    |              | 
 is_checked            | boolean                | not null default false          | plain    |              | 
 geo_latitude          | double precision       |                                 | plain    |              | 
 geo_longitude         | double precision       |                                 | plain    |              | 
 languages             | integer[]              |                                 | extended |              | 
 created_at            | integer                |                                 | plain    |              | 
 updated_at            | integer                |                                 | plain    |              | 
 version               | bigint                 | default 0                       | plain    |              | 
Indexes:
    "advertiser_pkey" PRIMARY KEY, btree (id)

What is your advice? Where should I looking for the problem?

Solution

You aren't inserting a value for id. Since you don't explicitly set it, it's implicitly given a null value, which is, of course, not a valid value for a primary key column. You can avoid this entire situation by defining this column as serial instead of a plain old integer, and leave all the heavy lifting to the database.

Answered By - Mureinik

Read More