MySQL Server





MySQL is an open source, relational database management system that runs as a server providing multi-user access to a number of databases. Acquired by Oracle in 2009, it is frequently used in combination with PHP installations, powering most of the WordPress installations.

Share tech news, updates, or what's on your mind.

Sign up to Post

I need to search for an order in my WooCommerce back office by email address.  I can see the email address in wp_users but don't know how to put a name to the email address.  Is there a plugin or mysql code that will accomplish that?

I'm working on a code that can record vocal messages and store them into a database to retrieve them later on, but my PHP script isnt working and I don't have enough expertise in web dev to correct it, can someone help me please ?
I am creating an after insert trigger that is supposed to update an existing row in the same table after a new insert.
Scenario:  This table records how long agents were on break.  They can go from break "Outbound" directly to break "Out to Lunch" without the "Outbound" break ever getting a `PauseEnd` timestamp.  This trigger is intended to put the NEW 'PauseStart'  into the existing 'PauseEnd' column of the agent's previous break.  I get no errors, but it doesn't do anything and I can't figure out why.

Here's the table and the trigger:

CREATE TABLE `agent_break_rec` (
  `id` int(10) NOT NULL,
  `AgentID` int(11) DEFAULT NULL,
  `AgentName` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `PauseCode` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `PauseStart` datetime DEFAULT NULL,
  `PauseEnd` datetime DEFAULT NULL,
  `PauseSec` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ixAgetCodeDT` (`AgentID`,`PauseCode`,`PauseStart`) USING BTREE

CREATE DEFINER=`sysop`@`%` TRIGGER `tgEndBreakAfterInsert` AFTER INSERT ON `agent_break_rec` 
FOR EACH ROW update agent_break_rec set 
where id=(select id from agent_break_rec where PauseEnd is null and AgentID=NEW.AgentID order by PauseStart desc limit 1);

Open in new window

Just got done creating a LAMP.  Then installed PHPMYADMIN.

Cannot log into PHPMYADMIN.  I have tried creating mySQL users, changing passwords, granting permissions, etc.

All attempts yield error 1045... access denied.

I have a Table called game with 4 columns:
clock_first_goal, clock_last_goal, clock_first_penalty, clock_last_penalty

and a second Table called tickets_one with 5 columns:
clock_one, clock_two, clock_three, clock_four, clock_five
where I've imported random clock times between 0 and 20:00 minutes that of a NHL period.
There are 1200 total times (60 seconds x 20 minutes) in 5 columns so there are 240 records in this Table.

In my application I have a page called enter_games.php. What I want is when the admin submits the times for First Goal, Last Goal, First Penalty and Last Penalty, I want to check if any of these values match the values in the tickets_one Table. If they do I just want to echo "we have a match!", if not I want to echo "no match".

I'm not sure the best way to do this if it should be a Join, if statement or loop etc... I think it can be done with an if statement.
I'm somewhat new to php and mysql. This is the section of the code I'm trying to get to work:

// find matching results
// query the database with client ID
$query = "SELECT * FROM tickets_one";
$result = mysqli_query( $conn, $query );

// if result is returned
if( mysqli_num_rows($result) > 0 ) {

    // we have data!
    // set some variables
    while( $row = mysqli_fetch_assoc($result) ) {
        $time1    = $row['time_one'];
        $time2    = $row['time_two'];
        $time3    = $row['time_three'];
        $time4    = $row['time_four'];
        $time5    = 

Open in new window

I have a column in MySql table which saves multiple values in one column seperated by pipe sign:
i.e: A|B| 

Open in new window

Is there any way to retrieve only distinct values from the above data in PHP?

I have a table like below

country       State.       county.        Address                                 zipcode.
USA              VA             C1               123 Main St                            00000
USA              VA             C1               124 Main St                            00001
USA              VA             C1               126 Main St                            00002
USA              VA             C2               128 Main St                            00011
USA              VA             C2               129 Main St                            00010

how can I write sql to have table as below
country       State.       county.        Address                                 zipcode.
USA              VA             C1               123 Main St                            00000
                                                           124 Main St                            00001
                                                           126 Main St                            00002
USA              VA             C2               128 Main St                            00011
                                                           129 Main St                            00010

I think distinct applies to one complete row, but how to achieve the above
I am using MySql DB.
I'd like to find matching results between 4 columns in one table and 5 columns in another table.
I have one table called game with 4 columns:
clock_first_goal, clock_last_goal, clock_first_penalty, clock_last_penalty

and a table called participants with 5 columns:
clock_one, clock_two, clock_three, clock_four, clock_five

Everything in these columns are numbers like 4:16, 8:47, 13:02 etc clock times of a period of an NHL hockey game. I think I should be using an Inner Join for this however, it's not working, it's just displaying all of the times in the database instead of just the matching results within those columns.

Please take a look at the screen shot attached to see what I mean.

Here is the code I have so far:

$query = "SELECT * FROM participants AS p INNER JOIN game AS a ON p.game_date = a.game_date WHERE a.user_ID='$userID'";


$result = mysqli_query( $conn, $query );





                                                                                if( mysqli_num_rows($result) > 0 ) {


                                                                                                // we have data!

                                                                                                // output the data



Open in new window

Hi there.

I have a query, that I may save as a view. That query/view contains a number of variables. One of them though can take either value A or B (arm A and B). So I want to evaluate all variables row by row distributed on the arms in columns like this:
              Arm A     Arm B
Var 1    eval 1a     eval 1b
Var 2    eval 2a     eval 2b
Var 3    eval 3a     eval 3b
Var 4    eval 4a     eval 4b
....         ....              ....
and so on and on...

How do I formulate the query?
Thank you in advance. :-)

Best regards


MySQL 5.7 and the JSON data type, Help with basic 3 basic functions / queries

I have a data structure I am trying to work with in experimenting and doing a proof of concept using the JSON data type in MySQL. I am using node.js for my back end.

Here is my table syntax
DROP TABLE if exists user;
  `id` varchar(128) NOT NULL,
	`device_id` varchar(128) NOT NULL,
  `email` varchar(100) DEFAULT NULL,
	`device_type` enum('','android','ios','web') DEFAULT NULL,
  `password` varchar(512) DEFAULT NULL,
  `login_by` enum('manual','facebook','anon') DEFAULT 'anon',
  `created_timestamp` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modified_timestamp` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `data` json NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `device_id` (`device_id`),
  UNIQUE KEY `email` (`email`)

Open in new window

This what I want my JSON data object to look like
  "name": "Allen",
  "albums": []

Open in new window

Issue 1.

When I create a user this is the query being executed. It is formatted as it is by node to actually run. This is based on the documentation I have seen

insert into user (id, device_id, device_type, data ) values ("39c48d40-de07-11e8-9d18-518c0c9d3ee2","this4","ios",'{"name": "allen", "albums": []}')

Open in new window

This works however creates a string in the JSON data type. I thought the data type was supposed to be binary
If I do a
select * from user 

Open in new window

then my results will return the JSON object as a string I need to parse.
        "id": "39c48d40-de07-11e8-9d18-518c0c9d3ee9",
        "device_id": "this3",
        "email": null,
        "device_type": "ios",
        "password": null,
        "login_by": "anon",
        "created_timestamp": "2018-11-01 14:52:05",
        "modified_timestamp": "2018-11-01 14:52:05",
        "data": "{\"name\": \"Allen\", \"albums\": []}"

Open in new window

If I try to put in straight JSON Object like this I get a SQL error.
insert into user (id, device_id, device_type, data ) values ("39c48d40-de07-11e8-9d18-518c0c9d3ee9","this4","ios",{"name": "allen", "likes": []})

Open in new window

How do I create the JSON data type that is binary and not a serialized string.

Issue 2

This is related to issue one.
I am hoping to add an object to the array for albums
This query worked but with issues
UPDATE user SET data = JSON_ARRAY_APPEND( `data` , '$.albums' , '{"id":"sd123asdwe","title":"OU812"}' ) WHERE id = "39c48d40-de07-11e8-9d18-518c0c9d3ee9"

Open in new window

It added an additional serialized string to the overall object, so now when I get my data I need to parse the json, then loop through the array and parse the individual items..

Open in new window


I found this page:

is about what data type should we use once we migrate from Oracle to MariaDB.

any other reference you can suggest ? I convert using that tools from the link above and it is not fair to get that information from that company again.

any type convert for Oracle user define type ?
Guide me how to create (deploy) exe file of  windows application in c#  with database of mysql ??
I have 2 queries

1> The current query takes approx 6-7 seconds. Data is been fetched from 7 tables. Tables are fully normalized, frequently updated but are mostly read approximately 100 times a day. The same query gets executed 7,00,000 times in a single day with updated data. A query is already optimized and indexed. Table approximately has 3,00,000 rows. Is there any way to reduce the query time? The table works on the InnoDB engine.

I was planning for few methods please correct me if I am wrong

    Memcache the rows using PHP
    Use replication Master/Slave architecture in slave have MyISAM (Doubt about the engine in this architecture)
    Use MySQL caching mechanism
        (a) query_cache_size = 268435456
        (b) buffer_pool
        Can both (a) and (b) perform together? Will it improve the performance for such huge data?

Is there some other technique for improving query execution time?

2> Table is completely normalized its in innodb. Size of data is more than 600 million records. Even after indexing and optimization queries are too slow. Size of records increases rapidly and it would be triple within a month due to a campaign.

Any idea how i can increase performance of the system.

    Increasing the innodb_buffer_pool_size (e.g., up to 80% of RAM)
    To use Memcache
    I was planning to do replication / Sharding.

But still, have doubt will it work to increase the …
Hello Experts!

The server I'm using is set to use UTC timezone. I have a table in MySQL with a timestamp column. The timestamp is always 1 hour less than client timezone (which is '+01:00').

I couldn't alter the timezone of my server because I'm on a shared hosting plan. Ok, how can I effectively either alter the timestamp stored in the database or my script to store and retrieve based on client timezone?
MYSQL Error:

Error 1136: at line 11: Column count doesn't match value count at row 1

Dear Experts:

I am having the issue with this error concerning code 1136. I have specified the correct number count in the Primary Key which is No.

This count is not being recognized in the corresponding VALUES line, which contains only 4 field values corresponding with the 4 in primary key No.  Please explain the reason for the occurrence.

Restriction: The current format must be followed.

Also, using -- or /* commentary in .sql files have also occurred with potential problem. Is there reason for this?
I am getting error like ..
error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)'

can anyone help me in this regards?

I heard that to use MySQL InnoDB cluster to implement a cross site cluster solution (primary and DR site), we need to setup 2 x InnodB cluster and setup replication between them, so MySQL InnoDB cluster DO NOT support cross site failover, can only setup replication between 2 x MySQL InnoDB cluster group, am I right?

but when failover, how can we do it ? using VIP, Virtual IP ?
If someone with experience using the MYSQL fo Excel version, can you please advise how to create a table from Excel.
My own effort failed. I had  selected 469 columns and 20 rows and it came up
on the screen, so far so good. I then clicked on Export data. Then the following message appeared.
What keys are they ??? There was a primary key which was automatically created.
Thanks for any help
"The MySQL Table `ukh_ireahcp` could not be created because of the following error(s):
MySQL Error 1069:
Too many keys specified; max 64 keys allowed"
I'm looking for a sql query to pull any woocommerce products or wordpress pages edited by a certain Wordpress user.  Ideally I'd get the post and the post revision along with the user that I'm targeting.

I did some Google'ing but couldn't find what I was looking for.  Any ideas?

I need the second to last item in the json response at the end of the code to include all the skus in each of the orders (so users can order all of that order's item's again all at once). What I have in there now only returns the sku in the last order. A preview of the current json response is embedded directly below. json
                // Organized Order Products into shipments //
                $shipments = array();
                foreach($order['order_details'] as $prod) {
            	   $grp = $dbAccessor->getShipmentGroup($prod['SKU']);
            	   if(!isset($shipments[$grp])) {
            	      $shipments[$grp] = array();  

            	   $shipments[$grp][] = array('sku'             => $prod['SKU'],
                                              'name'            => $prod['Product'],
                                              'qty'             => $prod['qty'],
                                              'url'             => str_replace('', '', $dbAccessor->getProductUrl($prod['SKU'])),
                                              'image_url'       => "/images/sku/small/".$dbAccessor->getSkuImage($prod['SKU']),
                                              'supplier_sku'    => $dbAccessor->getSupplierSku($prod['SKU']),
                                              'is_discontinued' => $dbAccessor->isDiscontinued($prod['SKU']),

Open in new window

The following is coded in Laravel framework:


$shipping = DB::table('shipping')->where('country',$delivery->country)->first();
$data= json_decode(json_encode($shipping),true);

Open in new window


array (
  'id' => 3,
  'carrier' => 'EN',
  'country' => 'AU',
  'rates_json' => '{"rates": [{"international": [{"zone4": [{"to_kg": "2", "total": "1", "from_kg": "1"}, {"to_kg": "4", "total": "2", "from_kg": "3"}]}]}]}',

Open in new window

In MySQL database i stored rates_json in a json datatype column. The attribute from_kg and to_kg is a range.  

I intend to retrieve the total if a value is between the range. For instance, if value 1.5 is between  1 and 2 then the total is 1.

Your help is appreciated.

Thank You.

Im curious if within a mysql stored procedure i can insert results from a query like so and then return the original select statement for further work in php


	IN var_plyr INT


	SET lmt = 0;
        SET cnt = 0;

       //original select statement
	FROM players AS p 
       WHERE p.status = 1AND = var_plyr
       SET cnt = @@ROWCOUNT;

	IF cnt > 0 THEN
                plyr_loop: WHILE lmt < cnt

		INSERT INTO messages
                 ( first_name, email ) VALUES //-> ????

                  SET lmt = lmt +1;
                END LOOP plyr_loop;

//-> return original select statement

		RETURN SELECT "There are no players with that id" AS error;

END $$


Open in new window

Thanks in advance!
Now i'm doing the monthly invoice system to calculate the late payment charges and the total amount , for the first month , the late payment charges is invoice sum * 10% then the total amount is invoice sum + late payment charges . For the second month , the late payment charges is total amount of first month * 10% then the total amount is total amount of first month + late payment charges .

This is what i do for the calculation but its manually
I have done the one like the picture above , but the amount overdue is manually input to get the late payment charges and total amount . Is another way to get the amount overdue from the total amount ? but the way i do to display the total amount is by loop method.

							$query5 = $DBcon->query("SELECT * FROM owner_monthlyfees WHERE owner = '$owner' AND unit_name = '$unit_name' AND date2 BETWEEN '$newDateString' AND '$today' order by inv_no");      
								while ($row5=$query5->fetch_array()){
    							<form method='POST' action='' enctype='multipart/form-data'>																																				

Open in new window

I want to run between query for value filter like this:
SELECT * FROM `wm_products` WHERE pro_price BETWEEN 2000 AND 3000 OR pro_price BETWEEN 3000 AND 4000

So, my query is

$sql = "SELECT * FROM wm_products WHERE";
                  $price =implode("'or pro_price between'",$price);
                  $sql  .= " and pro_price between $price";
i have page to add new package with inputs and i have multi row dynamic create row for more details to this package inside this page

and also i have multi image uploader the same idea for invoice but add to this multi images

i have this photos to help you think my case

<?php include "header.php"; ?>      

			/// validate vars
			$num=" SELECT MAX( package_id ) as package_id FROM packages ";
				$row = mysqli_fetch_array($query);
					$max= $row['package_id'];
					if($max > 500){
						$package_id= $max + 1 ;
						$first= 500 ;
						$package_id= $max + 501 ;

		<script src="../tinymce/tinymce.min.js"></script>
			selector: '.mytextarea'
      <!-- Right side column. Contains the navbar and content of the page -->
      <div class="content-wrapper">
        <!-- Content Header (Page header) -->
        <section class="content-header">
            <small>Control panel</small>

        <!-- Main content -->
        <section class="content">
          <!-- Main row -->
          <div class="row">
            <!-- Left col -->
            <section class="col-lg-12 connectedSortable">

              <!-- quick email widget -->

Open in new window


MySQL Server





MySQL is an open source, relational database management system that runs as a server providing multi-user access to a number of databases. Acquired by Oracle in 2009, it is frequently used in combination with PHP installations, powering most of the WordPress installations.