Delete from DB if not in array

PHOTO

Tue Mar 02 2021 10:31:38 GMT+0000 (Coordinated Universal Time)

Saved by @vskand #prestashop #delete #mysql #db #database #ids

Db::getInstance()->executeS("DELETE FROM product_images WHERE id_product NOT IN (" . join($allProductIds,',') . ")")

COPY

Save snippets that work from anywhere online with our extensions

Comments

More like this

#pyproj #python #database #path

Fix pyproj database path

 """ 
'UserWarning: pyproj unable to set database path.' <- This happens with multiple pyproj installations on the same machine, which is quite common, as almost every geo software depends on it.

How to fix:
First, find all copies of 'proj.db' on the machine. Get the path for the correct one, which is probably something like 'C:/Users/Clemens Berteld/.conda/pkgs/proj-8.2.0-h1cfcee9_0/Library/share/proj'. Definitively not a QGIS or PostGIS path.

Then, use it as a parameter for this function and run it at the top of your code:
"""

def set_pyproj_path(proj_path):
    from pyproj import datadir
    datadir.set_data_dir(proj_path)
    # print(datadir.get_data_dir.__doc__)

#sql #database #querying-data

SQL Select all columns with GROUP BY one column

https://stackoverflow.com/a/39816161/6942743

#prestashop #delete #mysql #db #database #ids

Delete from DB if not in array

Db::getInstance()->executeS("DELETE FROM product_images WHERE id_product NOT IN (" . join($allProductIds,',') . ")")

#wordpress #database #credentials #automate

Store database credentials from wp-config in shell variable

WPDBNAME=`cat wp-config.php | grep DB_NAME | cut -d \' -f 4`
WPDBUSER=`cat wp-config.php | grep DB_USER | cut -d \' -f 4`
WPDBPASS=`cat wp-config.php | grep DB_PASSWORD | cut -d \' -f 4`

#database #mysql

Create user and database and grant access

# If your logged in to mysql (mysql -u root -p) you can create the database
#CREATE DATABASE database_name;
CREATE DATABASE <database> CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';

# The user can be created with
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'user_password';

# This user also needs access to the database
GRANT ALL PRIVILEGES ON database_name.* TO 'database_user'@'localhost';

# To make the new access rights available you have to reload them
FLUSH PRIVILEGES;

#php #codeigniter #database #model

Query Builder for database CI

https://codeigniter.com/userguide3/database/query_builder.html

#php #codeigniter #database #model

Shortcuts in Model CI

$sql = "SELECT * FROM some_table WHERE id = ? AND status = ? AND author = ?";
$this->db->query($sql, array(3, 'live', 'Rick')); // insert in ci

#php #codeigniter #database #model

Validate in Codeigniter

$this->load->library("form_validation");
$this->form_validation->set_rules("username", "User name", "trim|required");
if($this->form_validation->run() === FALSE)
{
     //$this->view_data["errors"] = validation_errors();
}
else
{
     //codes to run on success validation here
}

#database

Count group by date

SELECT count(*) as application, YEARWEEK(created_date) as weekNum
FROM nepaldevjobsapplication
GROUP BY YEARWEEK(created_date)

#database

Chat DB Design

Inbox
id, userId(holder of this account), senderId, lastMessage

Chat
id, senderId, recieverId, message, seen, deleted

//
DB query when inbox is opened
findall where userId = senderId or userid = receiverId

#python #mysql #database #thirdparty

My SQL Connection

#!/usr/bin/python



-- coding: UTF-8 --


pip install MySQL-python



import MySQLdb, os




try:
    conn = MySQLdb.connect(host='172.17.42.1', user='my_email', passwd='normal', db='database', port=3306)




cur = conn.cursor()
cur.execute('SELECT `id`, `name`, `path`, FROM `doc_file`')

# results
resulsts=cur.fetchall()    id, name, path = r[0], r[1], r[2]

    if path and not os.path.exists(path):
        print 'file not exist: ', id, name, path, flashpath

cur.close()
conn.close()

 



except MySQLdb.Error,e:
     print "Mysql Error %d: %s" % (e.args[0], e.args[1])




</pre>

#database #postgresql #nodejs #indiafirst #javascript #pgadmin4

PostgreSql-> Table exist check query

SELECT EXISTS (
   SELECT FROM information_schema.tables 
   WHERE  table_schema = 'public'
   AND    table_name   = 'abcd'
   );

#database #postgresql #nodejs #indiafirst #javascript #pgadmin4

html to pdf download working

import { jsPDF } from "jspdf";

export default async function lead({
}) {

  const doc = new jsPDF('p', 'pt', 'a4');
  doc.setFontSize(12)
  doc.html(document.getElementById("dream-house-full"),  {
    useCORS: true,
    autoPaging:'text',
    // html2canvas:
    callback: function (pdf) {
      pdf.save("12.pdf");
    },
    html2canvas: {
      scale: 0.5,
      // ignoreElements: element => element.id === autoTableConfig?.elementId,
    },
    x:10,
    y:10,
    width:1400,
  });
  // doc.save("a4.pdf");

  // htmlPdf.create(html, options).then((pdf) => pdf.toFile('test.png'));
}

#laravel #database #eloquent #model

db-models-and-eloquent

https://github.com/LaravelDaily/laravel-tips/blob/master/db-models-and-eloquent.md

#tdd #test #in-memory #db

In memory db setup

using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Mvc.Testing;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.AspNetCore;


namespace TDD.Tests
{
    public class PatientTestsDbWAF&lt;TStartup&gt; : WebApplicationFactory&lt;TStartup&gt; where TStartup : class
    {

        protected override IWebHostBuilder CreateWebHostBuilder()
        {
            return WebHost.CreateDefaultBuilder()
                .UseStartup&lt;TStartup&gt;();
        }
        protected override void ConfigureWebHost(IWebHostBuilder builder)
        {
            builder.ConfigureServices(async services =&gt;
           {
               // Remove the app's DbContext registration.
               var descriptor = services.SingleOrDefault(
                      d =&gt; d.ServiceType ==
                          typeof(DbContextOptions&lt;DataContext&gt;));

               if (descriptor != null)
               {
                   services.Remove(descriptor);
               }

               // Add DbContext using an in-memory database for testing.
               services.AddDbContext&lt;DataContext&gt;(options =&gt;
                  {
                      // Use in memory db to not interfere with the original db.
                      options.UseInMemoryDatabase("PatientTestsTDD.db");
                  });
           });
        }
    }
}

Delete from DB if not in array

Save snippets that work from anywhere online with our extensions

Comments

More like this

Browse more snippets >>

Embed code snippet