How to build a SQL query where certain fields can be null or have value?

I need to do a SQL query to a custom WordPress database table where certain columns are nullable. I wrote this static method:

    public static function does_name_exist( $  name ) {      global $  wpdb;      $  table = $  wpdb->prefix . self::DB_TABLE;      if ( is_string( $  name ) ) {         $  query = $  wpdb->prepare( "SELECT `id` FROM `$  table` WHERE `given_name` = %s", $  name );     } elseif ( is_array( $  name ) ) {         $  query = $  wpdb->prepare(             "SELECT `id` FROM `$  table` WHERE `name_prefix` = %s AND `given_name` = %s AND `additional_name` = %s AND `family_name` = %s AND `name_suffix` = %s",             empty( $  name['name_prefix'] ) ? null : $  name['name_prefix'],             empty( $  name['given_name'] ) ? null : $  name['given_name'],             empty( $  name['additional_name'] ) ? null : $  name['additional_name'],             empty( $  name['family_name'] ) ? null : $  name['family_name'],             empty( $  name['name_suffix'] ) ? null : $  name['name_suffix']         );     } else {         return false;     }      $  id = $  wpdb->get_var( $  query );      if ( null !== $  id ) {         return (int) $  id;     } else {         return false;     }  } 

The problem is when a value in $ name array is empty the query compare NULL with equal instead of IS operator.

Example of wrong SQL query:

SELECT * FROM `wp_recipients` WHERE `name_prefix` = NULL AND `given_name` = 'John' AND `additional_name` = NULL AND `family_name` = 'Smith' AND `name_suffix` = NULL  

Example of correct SQL query:

SELECT * FROM `wp_recipients` WHERE `name_prefix` IS NULL AND `given_name` = 'John' AND `additional_name` IS NULL AND `family_name` = 'Smith' AND `name_suffix` IS NULL 

How can I solve the problem?

SE Yandex: NULL search results if Captcha show

Hello!

I’m new in GSA and try to use Yandex as search engine. If Yandex not use captcha in search results, no problem had happen. For example:
https://yandex.ru/yandsearch?text=KEYWORD&lr=213&redircnt=1627568884.1

But when Yandex show captcha before search – GSA show empty results http://prnt.sc/1ho12n0 .
https://yandex.ru/showcaptcha?cc=1&retpath=https://yandex.ru/yandsearch?text=KEYWORDlr=213&redircnt=1627568884.1_2a93890e6f558296776d36a1baa9b3f7&t=2/1627569772/4401ad78261350a3258422404aa8e715&u=6a577bb1-ba2f517f-83e021cb-f5d347c4&s=78ee8cdd38315fb016c7a14825d7b329

I think that GSA is not also trying to recognize the captcha, not send request to special Captcha’s software – GSA think that there are no results. Actually in many captchas Yandex not ask input any characters, it’s just need push mouse in the square – https://prnt.sc/1ho0pyv

Why GSA not try to recognize the Captcha in search on Yandex? How to resolve it?

Thank you for your help!

How can I get the Text component from TextMeshPro ? I’m getting null exception

At the top of the screen :

public GameObject uiSceneText; private TextMeshPro textMeshPro; 

Then in the script at some point :

uiSceneText.SetActive(true);  if (textMeshPro.text != "")     textMeshPro.text = ""; 

The exception error null is on the textMeshPro on the line :

if (textMeshPro.text != "") 

textMeshPro is null.

This screenshot shows the TextMeshPro in the Hierarchy :

TextMeshPro

and I see that the Scene Image object and its child Scene Text both are enabled true.

I want to get the text and add replace a new text with the text already in the Text but the textMeshPro is null.

The complete script :

using System.Collections; using System.Collections.Generic; using TMPro; using UnityEngine;  public class PlayerSpaceshipAreaColliding : MonoBehaviour {     public float rotationSpeed;     public float movingSpeed;     public float secondsToRotate;     public GameObject uiSceneText;      private float timeElapsed = 0;     private float lerpDuration = 3;     private float startValue = 1;     private float endValue = 0;     private float valueToLerp = 0;     private Animator playerAnimator;     private bool exitSpaceShipSurroundingArea = false;     private bool slowd = true;     private TextMeshPro textMeshPro;      // Start is called before the first frame update     void Start()     {         textMeshPro = uiSceneText.GetComponent<TextMeshPro>();          playerAnimator = GetComponent<Animator>();     }      // Update is called once per frame     void Update()     {         if (exitSpaceShipSurroundingArea)         {             if (slowd)                 SlowDown();              if (playerAnimator.GetFloat("Forward") == 0)             {                 slowd = false;                  LockController.PlayerLockState(false);                  uiSceneText.SetActive(true);                  if (textMeshPro.text != "")                     textMeshPro.text = "";                  textMeshPro.text = "Here I will add the new text.";                              }              if (slowd == false)             {                              }         }     }      private void OnTriggerEnter(Collider other)     {         if (other.name == "CrashLandedShipUpDown")         {             exitSpaceShipSurroundingArea = false;             Debug.Log("Entered Spaceship Area !");         }     }      private void OnTriggerExit(Collider other)     {         if (other.name == "CrashLandedShipUpDown")         {             exitSpaceShipSurroundingArea = true;             Debug.Log("Exited Spaceship Area !");         }     }      private void SlowDown()     {         if (timeElapsed < lerpDuration)         {             valueToLerp = Mathf.Lerp(startValue, endValue, timeElapsed / lerpDuration);             playerAnimator.SetFloat("Forward", valueToLerp);             timeElapsed += Time.deltaTime;         }          playerAnimator.SetFloat("Forward", valueToLerp);         valueToLerp = 0;     } } 

How to allow NULL in foreign keys of a compound primary key

I want to have NULLs in foreign keys of a compound primary key. This is an example of what I would expect to be valid data.

product_id variant_id
123-123 ABC
123-123 NULL
456-456 ABC

I cannot figure out why the following SQL in postgres gives NOT NULL violation constraint me when inserting NULL as variant_id.

CREATE TABLE IF NOT EXISTS inventory.price (   product_id             UUID NOT NULL, -- this has to be always to a valid product   variant_id             UUID,          -- this could be NULL   amount                 MONEY NOT NULL,   created_at             TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),   -- Constraints   CONSTRAINT inventory_price_pkey PRIMARY KEY (product_id, variant_id),   CONSTRAINT inventory_price_inventory_product_fkey FOREIGN KEY (product_id)     REFERENCES inventory.product (id) MATCH FULL,   CONSTRAINT inventory_price_inventory_variant_fkey FOREIGN KEY (variant_id)     REFERENCES inventory.variant (id) MATCH SIMPLE,   CONSTRAINT inventory_price_amount_gt_0 CHECK (amount > '0'::money) ); 

And the inspection to information_schema confirms the non-nullable constraint.

column_name column_default is_nullable data_type
product_id NULL NO uuid
variant_id NULL NO uuid
amount NULL NO money
created_at now() NO timestamp with time zone

How to check if a column in a query can produce NULL?

I am scheming how to implement a type generator for arbitrary SQL queries.

I found that for any query, the simplest way to get the types of every column is to create a temporary table and then inspect that table. However, this tells nothing about which columns can be NULL.

I am interested to find a solution to identifying which columns can be NULL for any arbitrary query with the goal of generating types for that query.

What’s the way to go about it?

I am trying to convert my SQLite database into Postgres database, but I don’t know how to convert name TEXT (32) NOT NULL

CREATE TABLE integrals (    id SERIAL PRIMARY KEY NOT NULL UNIQUE,    name TEXT (32) NOT NULL,    ip TEXT (32),    posx REAL NOT NULL,    posy REAL NOT NULL,    port INTEGER,    image INT,    zones TEXT,    pass TEXT ); 

This is my SQLite generated database, and I am trying to convert it, but I am very new to Postgres.

get_comments with post_status ‘public’ retrieves NULL result

This is my loop:

<?php $  comments = get_comments(array(     'status' => 'approve',     'type' => 'comment',     'number' => 10,     'post_status' => 'public' )); ?>      <ul class="sidebar-comments">         <?php foreach ($  comments as $  comment) { ?>             <li>                 <div><?php echo get_avatar($  comment, $  size = '35'); ?></div>                 <em style="font-size:12px"><?php echo strip_tags($  comment->comment_author); ?></em> (<a href="<?php echo get_option('home'); ?>/?p=<?php echo ($  comment->comment_post_ID); ?>/#comment-<?php echo ($  comment->comment_ID); ?>">link</a>)<br>                 <?php echo wp_html_excerpt($  comment->comment_content, 35); ?>...             </li>         <?php } ?>     </ul> 

This always gives an empty result (no errors). If I remove 'post_status' => 'public' from the get_comments arguments, the function works, comments load but also comments from private posts (which I don’t want).

Any ideas on why 'post_status' => 'public' is not working?

meta_query order by date present -> future then show null

Thanks for taking the time to read this. I’ve been struggling with a meta_query for an events site I’m working on.

I have used ACF to create a field for date start and date end, but not all events will have a date.

What I’m trying to achieve, is when you go to the archive or tax view, the first thing you see are the posts that have a date assigned, in order from today’s date into the future. Then after those dated events have been output, to cycle through all empty date posts.

So far I have the below in my functions.php file. This kind of works, but in the wrong order. So the correct events that are dated are output and in the right order. But only after the null valued items have output. I thought that may be because of the ordering in the arrays themselves, so moved the date ordered array to the end. That had no effect.

$  query->set( 'post_type', 'courses' ); $  query->set( 'meta_query', array(     'relation' => 'OR',     array(         'key'        => '_course_date_from',         'compare'    => '=',         'value'      => '',     ),     array(         'key'     => '_course_date_from',         'compare' => '>=',         'value'   => date('Ymd'),     ) ) ); $  query->set( 'orderby', 'meta_value title' ); $  query->set( 'order', 'ASC' ); 

Thanks for your time. Ben.