I'm trying to create what I would call an "inclusive" search and by that, I'm not referring to "between". I have a snippet of code and it does what I need it to do but it's hacky and inefficient so I'm wanting to know if I can possibly do it with just SQL. I'm using a third party library called MysqliDb but if you want to forego phrasing your answer in that format, it's fine. if ($this->data != '') { $searchParams = explode(' ', trim($this->data)); foreach ($searchParams as $s) { $this->db->orWhere('customer_name', '%'.$s.'%', 'like'); $this->db->orWhere('customer_address', '%'.$s.'%', 'like'); $this->db->orWhere('customer_city', '%'.$s.'%', 'like'); $this->db->orWhere('customer_zip', '%'.$s.'%', 'like'); $this->db->orWhere('customer_email1', '%'.$s.'%', 'like'); } $this->db->having('customer_status', 'Active'); $this->db->having('owner_id', $this->owner_id); $binaryArray = array_fill(0, sizeof($searchParams), 0); $results = $this->db->get('tblcustomers'); $filtered = []; foreach ($results as $r) { $binaryArray = array_fill(0, sizeof($searchParams), 0); foreach ($binaryArray as $key=>$b) { if (strpos(strtolower($r['customer_name']), strtolower($searchParams[$key])) !== false || strpos(strtolower($r['customer_address']), strtolower($searchParams[$key])) !== false || strpos($r['customer_city'], strtolower($searchParams[$key])) !== false || strpos($r['customer_zip'], strtolower($searchParams[$key])) !== false || strpos($r['customer_email1'], strtolower($searchParams[$key])) !== false ) { $binaryArray[$key] = 1; } } if (!in_array(0, $binaryArray)) { $filtered[] = $r; } } } else { $this->db->where('owner_id', $this->owner_id); $this->db->having('customer_status', 'Active'); $filtered = $this->db->get('tblcustomers'); } $this->data is an input box with possible search words separated by spaces so my intent is to split the value of the input box up based on that and perform a query that **has** to include all of the parameters. By that I mean, I can easily do a query where it retrieves every row with $this->data or $this->data[1] but I want to exclude rows that don't actually have have all of the words in $this->data in one column or another. What I have does that but it uses a lot of PHP and I'd rather keep the db querying to SQL exclusively.

I've got a table similar to this data: | id | username | value | |-----------|----------|-------| | 1 | bob | 46 | | 483 | alice | 90 | | 176 | sue | 3001 | | 82 | harry | 0 | | 10493 | bill | 327 | I have this query that returns me the ranking of a user based on their id SELECT username, value, rank from ( SELECT tp.username, tp.value, tp.id, @curRank := @curRank + 1 AS rank FROM table tp, (SELECT @curRank := 0) r ORDER BY tp.value DESC ) as temp WHERE id = 483; So for the above query, I would get a ranking returned of **4** for the id 483. Let's say I want to insert the following row into the table: | id | username | value | |---------------------------| | 2 | sally | 2000 | Is there any way to know what rank this row _will_ have after it is inserted, **without** actually inserting it? I.e. sally would have a rank of 2 from the above query if inserted. The reason I am curious if this is possible is that I'm attempting to insert the row into the database and only have this one transaction, rather than having to insert the row, then re-run the rank query. Thanks!

I am trying to submit data to a database and then have it return the value of the user_id (primary key) created by the insertion, but I can't get it to return the value. I use the same query in phpAdmin and it works just fine. I know this should be simple, but I just can't get it. Here is my code: (I do have values to connect to the database, just didn't include them for privacy.) $dbc = @mysqli_connect (DB_HOST, DB_USER, DB_PASSWORD, DB_SITE) OR die ('Could not connect to MYSQL: ' . mysqli_connect_error() ); $nameQuery="INSERT into nameReunion (username, password,email,firstName,middleName,lastName, maidenName, lastUpdate) VALUES ('$username', '$password', '$email', '$fname', '$miname', '$lname', '$maname', CURDATE());"; $r = @mysqli_query ($dbc, $nameQuery); if(!$r) { echo "The database was not updated."; } else { echo 'The connection was successful
'; }; $getUserID="SELECT user_id FROM nameReunion WHERE username='$username';"; $r2=@mysqli_query ($dbc, $getUserID); echo $r2; echo $getUserID; Please help. I have spent wayyy too much time on something I fear should be very simple.

I had a local table with arabic values.when exported i got the following query. DROP TABLE IF EXISTS type; CREATE TABLE IF NOT EXISTS type ( bs_id int(11) NOT NULL AUTO_INCREMENT, types varchar(250) NOT NULL, PRIMARY KEY (bs_id) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8; -- -- Data for table business_type -- INSERT INTO type (bs_id, types) VALUES ('1', 'صناعة'), ('2', 'معارض'), ('3', 'أسواق'), ('4', 'إستيراد وتوزيع'), ('5', 'خدمات'), ('6', 'إستشارات'), ('7', 'أخرى'); I want to create this table on server so i just copied the above query and run it on server terminal.The query works fine but the values in table is showing like ".....". tried this SET NAMES utf8; before inserting the values,but failed.I don't have a cpanel installed on server.Command prompt is the only option. Can anyone help!! Thanks

I am trying to do mysql replication. But I want to while the backup is being uploaded to the slave server, the changes in the master are not received. For example: I have a user table on my X db. And 1000 user records. When I run:

mysqldump -u root -p --all-databases --master-data=2 --single-transaction --flush-logs --delete-master-logs --triggers --routines --events > master_data.sql

master_data.sql is have 1000 user records. Then I added 200 user record my master db. Now my Master DB has 1200 user records, but my backup has 1000 records. When I run:

mysql -u root -p < /path/to/master_data.sql

Result: MY slave DB has 1000 record. MY master DB has 1200 record. What can I do. my.cnf of the Slave DB my.cnf of the Master DB

I am attempting to lock some tables while running some code and unlocking the tables when I am done. Process: 1. Run Lock Tables MySQL Query. 2. Run some PHP Code. 3. Run Unlock Tables MySQL Query. While running this process, 9 times out of 10 everything runs perfectly. Sometimes, when I run my query no response comes back from MySQL and PHP just waits for a response. Since no response happens, I never get to steps 2 or 3 and the table stays locked indefinitely. I am running the exact same Lock Tables Query in every single attempt. After some research, I found that the problem happens when a Second Server writes to the database and then the First Server tries to do a "LOCK TABLES" query. It appears that the Second Server's write does a "metadata lock". I believe this is due to MySQL having the "autocommit" feature enabled and the transaction is not yet completed. So when I attempt to lock the tables, MySQL never responds and the "show processlist;" has "Waiting for table metadata lock | LOCK TABLES ..." listed in it until I manually kill the process. I am locking Numerous tables. Below is a similar example to my actual table query. I am using the same table multiple times with different aliases based on the queries I am attempting to prevent from accessing the tables. $sql = "LOCK TABLES table1 as t1 WRITE , table2 as t2 WRITE , table3 WRITE , table2 WRITE , table4 WRITE , table1 WRITE , table5 WRITE , table5 as t5 WRITE , table6 as t6 WRITE , table7 as t7 WRITE , table7 WRITE , table8 as t8 WRITE , table9 t9 WRITE , table10 t10 WRITE , table11 t11 WRITE , table12 WRITE;"; $this->mysqli = new mysqli( $this->credentials->server , $this->credentials->user , $this->credentials->password , $this->credentials->database ); try{ error_log('before first attempt lock tables '.$sql); $this->mysqli->query($sql); error_log('after first attempt lock tables'); } catch (Exception $e){ error_log('Error locking tables: '.$e->getMessage()); } error_log('After try catch.'); if($this->mysqli->error){ error_log('lock table error: '.$this->mysqli->error); } When the process fails, I see "before first attempt lock tables" in my PHP Error log. I do not see any of the other error_log() calls. After some checking, I determined it is because PHP has not received a response from MySQL. I never get into the Catch Exception, since MySQL is not returning an error. MySQL is not returning anything unless I manually kill the MySQL Lock Tables Process. If I don't kill the process, the PHP code never stops waiting for a response from mysql. I am confused why the "LOCK TABLES" query is not gracefully waiting for the "metadata lock" to complete and then running. It was my understanding that more than one server was allowed to write to a database and the lock table features would work together instead of one hanging indefinitely.

In what format do I need Insert 3 table sql? animals(AnimalsID, Animals, IUCN_Red_List, TypesID, BloodedID) types(TypesID,Types) blooded(BloodedID,Blooded)

From the command line, one can get **mysql** and **mysqldump** to read a config file using --defaults-extra-file. This can contain username and password for connection. Is there a way to get **mysqli** from **php** to read a similar file? I know I could just read it and parse it, but that destroys its protection.

Not sure to post on the right StackExchange forum. If not, let me know! Working environment: - OpenSUSE Leap 15.4 - MariaDB : mariadb Ver 15.1 Distrib 10.7.7-MariaDB - PHP 8.0.25 (cli) (built: Oct 31 2022 12:00:00) ( NTS ) Based on phpinfo(), PHP ini file is: /etc/php8/cli/php.ini. **phpMyAdmin 5.1 is working well**. Installation directory is: /usr/share/phpMyAdmin (default directory created when installing *via* zypper install phpMyAdmin command). The "famous" option $cfg['Servers'][$i]['host'] from /etc/phpMyAdmin/config.inc.php file is set to localhost (and it does work!). I have then upgraded phpMyAdmin version this way:

srv-bla:~ # mv /usr/share/phpMyAdmin /usr/share/phpMyAdmin.old
srv-bla:~ # mkdir /usr/share/phpMyAdmin
srv-bla:~ # wget https://files.phpmyadmin.net/phpMyAdmin/5.2.0/phpMyAdmin-5.2.0-all-languages.tar.gz 
srv-bla:~ # tar -xzf phpMyAdmin-5.2.0-all-languages.tar.gz
srv-bla:~ # mv phpMyAdmin-5.2.0-all-languages/* /usr/share/phpMyAdmin/

I then restart daemons and test the connection:

-bla:~ # systemctl restart mysqld mariadb apache2

The connection page is showing right. I then enter my credentials and I get stuck with this bloody message: >Impossible to connect to server. >mysqli::real_connect(): (HY000/2002): No such file or directory I have wandered for a while on numerous webpages. Many of them suggest to modify the config.inc.php file and to set $cfg['Servers'][$i]['host'] option to 127.0.0.1 instead of localhost. Unfortunately, **this does not fix the problem for me...** Am I editing the right config.inc.php file? Actually, I can find only one on the server:

srv-bla:~ # updatedb
srv-bla:~ # locate config.inc.php
/etc/phpMyAdmin/config.inc.php
/etc/phpMyAdmin/config.inc.php.rpmnew
/etc/phpMyAdmin/config.inc.php.rpmsave

Apache logs are not friendly either. Access logs returns 200 codes only, which seems normal to me (the phpMyAdmin webpage is served properly). Error logs are empty... Mysql logs are empty also (/var/log/mysql/mysqld.log). Or course, I have check that mysqld service (same as mariadb service) is running. Any help or ideas would be much appreciated! ---------- **EDIT** The socket file from MariaDB point of view is:

srv-bla:~ # mariadb -u root -p
Enter password:
MariaDB [(none)]> \s
[...]
UNIX socket: /var/lib/mysql/mysql.sock
[...]

The php.ini file is configured the same way:

srv-bla:~ # cat /etc/php8/cli/php.ini | grep mysqli.default_socket
mysqli.default_socket = /var/lib/mysql/mysql.sock

To me, there is no socket issue...

I am using a PHP framework called CodeIgniter for my application. This is using the MySQLI driver for its database work. This framework has built in support for things like a query builder where I could just code the statement into the model, but I am trying to use a stored procedure setup. Anyway, I posted some questions with their support and they suggested asking here would be more appropriate. In my model, I am calling a stored procedure like so: $data = array("customerID" => 123, "productID" => 456); ... public function addCustomerToProduct($data){ $procedure = "CALL addCustomerProduct(?,?)"; $result = $this->db->query($procedure, $data); } As it stands, this works perfectly fine. I send an array of data over, I tell my stored procedure to expect two variables, and I pass them along. **Stored Procedure:** CREATE DEFINER=root@localhost PROCEDURE addCustomerProduct(IN in_customerID INT, in_productID INT) BEGIN INSERT INTO order_customer_product (customerID, productID, retailAmountAtPurchase, faceValue) SELECT in_customerID, in_productID, p.retail, p.faceValue FROM products as p WHERE p.productID = in_productID; END While this works fine, I am not able to get the insert_id from the PK/AI. The only way this data is passed back (it seems) is when you use : $this->db->insert('customers', $data); return $this->db->insert_id(); I am trying to stick with stored procedures as it just makes things easier to debug when its all separate. How can I return the insert_id to my application when using a stored procedure setup like this?

I a running a relatively simple query:

SELECT gb2_designs.indx,
              gb2_designs.response_required,
                            gb2_designs.user_design_name,
                            gb2_designs.submitted,
                            DATE_FORMAT(gb2_designs.submitted_timestamp,'%c/%d/%y') AS stime,
                            DATE_FORMAT(gb2_designs.promised_ship,'%c/%d/%y') AS ps,
                            DATE_FORMAT(gb2_designs.updated,'%c/%d/%y') AS upd,
                            DATE_FORMAT(gb2_designs.promised_ship,'%c/%d/%y') AS dp,
                            DATE_FORMAT(gb2_designs.shipped,'%c/%d/%y') AS ds,
                            order_accepted, order_completed,
                            gb2_designs.status,
                            gb2_users.first_name,
                            gb2_users.last_name,
                            gb2_users.email
                     FROM gb2_designs, gb2_users
                     WHERE  1 && gb2_designs.user_indx=gb2_users.indx  && 
                            response_required=1
                     ORDER BY gb2_designs.updated DESC";

And then iterate over the rows with:

while($row=mysqli_fetch_array($query,MYSQLI_ASSOC))
    {
      $indx              = $row["indx"];
      $user_design_name  = $row["user_design_name"];
      ...more stuff''
    }

There are 104,422 rows in gb2_users and 70,385 in gb2_designs. When run manually, or in a .php file on apache that does only this, 5 elements are returned which appears to be correct. I am running Mysql version 5.7.40 When I am running it in a larger script, it returns the 5 rows matching the criteria successfully, then when it does the 6th (which should return null for $row), the code properly returns numm for $row, but issues the following to stdout: Fatal error: Allowed memory size of 33554432 bytes exhausted (tried to allocate 4096 bytes) in /home/omittedforprivacy/public_html/svadmin/lookup.php on line 1376. Line 1376 is the mysqli_fetch_arra($query,MYSQLI_ASSOC). It appears to be getting everything but issuing that pesky memory error. Apache is running without a memory limit set. Any ideas?

Can you confirm if this is a bug... I cannot work out if it's MariaDB (10.3.34 or 10.7.3), PHP (mysqlnd), or a mistake I've made:

query('SET in_predicate_conversion_threshold=2000;');

  $count = 1000;

  $params = implode(',', array_fill(0, $count, '?'));
  $types = str_repeat('i', $count);
  $values = array_fill(0, $count, 3);

  $statement = $db->prepare('EXPLAIN SELECT id FROM my_table WHERE id IN (' . $params . ')');
  $statement->bind_param($types, ...$values);
  $statement->execute();

  $result = $statement->get_result();

  var_dump($db->error);
  var_dump($statement->error);
  var_dump($statement->errno);
  var_dump($result);

?>

This hangs when it gets to $statement->execute(), and does not show any error message. If you remove EXPLAIN from the SQL (to make a normal SELECT query), the $statement->get_result() fails (returns false or exception, depending on mysqli_report), and you get the error message "Commands out of sync; you can't run this command now". --- The 1,000 parameter limit within IN() seems to be related to in_predicate_conversion_threshold; and you can have multiple IN() operators with 999 parameters each.

I've got a problem like this , I cannot get the id of the parent table . table_name : transaction_tbl - transaction_id -file_name -file_path -description table_name : transaction_details -details_id -transaction_id - details here is my code to insert : $sql = "INSERT INTO transaction_tbl (file_name,file_path,description) VALUE('$file_name','$file_path','$description') " ; $query = $conn->query($sql); $transaction_id = mysqli_insert_id($conn); if ($query === True ){ $sql = "INSERT INTO transaction_details (transaction_id,details) VALUES ($transaction_id,$details) "; }else { trigger_error('Wrong SQL: ' . $sql . 'Error: ' . $conn->error, E_USER_ERROR); } Now I can insert in my transaction_tbl but not in transaction_details. what should I do? Can somebody help me?

On my local WAMP stack things are working perfectly. When I upload the database on GOdaddy.com, this error shows up. > Warning: mysqli_connect(): (HY000/1045): Access denied for user 'root'@'localhost' (using password: NO) What does it mean?

I'm trying to determine if I need to use mysqli_ssl_set to encrypt my db layer. I have a standard Amazon RDS instance that I'm connecting to with MySQL user and pass, but I'm unsure if that connection is automatically encrypted. My server is SSL-ready, but does that mean mysqli_connect is encrypted by default? This is my code right now: $connection = mysqli_connect(self::$host, self::$user, self::$password); self::$lastConnection = $connection; $db = "db_prod"; mysqli_select_db($connection, $db); mysqli_set_charset($connection, "utf8mb4"); Do I need to add in mysqli_ssl_set too?

Question: How would you iterate over the previous result (referenced below) over for each of the values in column b3? MariaDB Fiddle (this includes prior fiddle information to solve the previous question): https://dbfiddle.uk/?rdbms=mariadb_10.5&fiddle=222f694a36cc41131fe558438e1d6ccd NOTE: this is a continuation from a problem referenced here: Update SET N + 1 equal to a row
Requesting a solution when adding 1 more dimension of complexity. Input Table: 3 columns (iteration for each b3) b1 b2 b3 1 X P1 2 Z P1 3 X P1 4 Y P1 5 Z P1 6 X P1 7 Y P1 8 X P2 9 Z P3 10 X P3 11 Z P3 12 X P3 13 Z P2 14 Z P3 Desired Result: 3 columns (iteration for each b3) b1 b2 b3 1 X P1 2 Y P1 3 X P1 4 Y P1 5 Z P1 6 X P1 7 Y P1 8 X P2 9 Z P3 10 X P3 11 Y P3 12 X P3 13 Y P2 14 Y P3 Simple iteration for reference on this data:
-- This code increments count and then iterates over each value of b3
-- Then sets the resulting values to b2. UPDATE b JOIN( SELECT b1, row_number() over (partition by b3 order by b1) rn FROM b) n on n.b1 = b.b1 SET b2 = rn;

SELECT u.*,h.*,d.* FROM User u LEFT JOIN HealthDec h ON u.ID = h.EmpId LEFT JOIN Document d On u.ID = d.UserID; and: SELECT count(id) FROM OHS WHERE Category = 'POLICIES' AND ID NOT IN ( SELECT OHSID FROM User_OHS WHERE UserID='1' AND Type='POLICIES' ); Both queries are working fine separately as required. But I want to combine both queries to get result for each user.

I have a query which was working fine a few days back: SELECT id, title, details, filetype, filepath, size, details, location, datetime, IF(hid=1, 'Anonymous', ( SELECT name FROM users WHERE did = userid )) AS username FROM infotable WHERE ext2!='0' ORDER BY id DESC LIMIT 50 but a couple of days back, it started giving an error: > Commands out of sync; you can't run this command now I am calling it from a PHP page but I have also tried it from MySQL's GUI to run it as SQL query. It gave the the same error: When I googled, I found that it may be due to MySQL to MySQLi update. I tried a lot to write an alternate query using MySQLi, but every time I got an error (Some times the same error and some times other errors). My questions are: **1. What this can be called as: *Sub Query* or *Multi Query*** **2. What is the exact error and what is its solution?**

I have a question about this piece of code I just wrote. I have a problem with the syntax, the first query executes its purpose, the problem is in the second query which does not.

if(isset($_POST['deletecontr'])){
  $dcquery = $_POST['dni']; // This get the ID of the person who I want to delete.
  $catchempr = ("SELECT empresa FROM contratistas WHERE dni='$dcquery'"); //This catch the name of the company of that person
  $query = ("DELETE FROM contratistas WHERE dni='$dcquery'"); // This works fine
  $query2 = ("DELETE FROM empleados WHERE empresa='$catchempr'"); This does not.
  mysqli_query($link,$query);
  mysqli_query($link,$query2); 
  echo "La empresa a sido eliminada de la base de datos";

I hope you guys can see something that I'm not seeing.

I am using this script to import a mysql file but I want to set foreign key checks to 0 when he will do the import. https://github.com/davcs86/php-mysqlimporter/blob/master/php-mysqlimporter.php Calling the file below with this function include("Mysqlimporter.php"); $mysqlImport = new MySQLImporter($host, $username, $password); $mysqlImport->doImport($source . "/var/dump.sql", $dbname, true, true); }