MySQL Server

47K

Solutions

23K

Contributors

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 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
) ENGINE=MyISAM AUTO_INCREMENT=7144 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE DEFINER=`sysop`@`%` TRIGGER `tgEndBreakAfterInsert` AFTER INSERT ON `agent_break_rec` 
FOR EACH ROW update agent_break_rec set 
     PauseEnd=NEW.PauseStart 
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

0
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.

Thoughts?
0
Hi,
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

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

Open in new window

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

Thanks
0
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.
0
Hi,
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 );

 

                    ?>

                                                               

                                                                                <?php

                                                                                if( mysqli_num_rows($result) > 0 ) {

 

                                                                                                // we have data!

                                                                                                // output the data

 

                                             

Open in new window

0

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;
CREATE TABLE 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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

Question
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

0
hi,

I found this page:
 https://www.convert-in.com/docs/ora2sql/types-mapping.htm

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 ?
0
Guide me how to create (deploy) exe file of  windows application in c#  with database of mysql ??
0
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
        query_cache_type=1
        query_cache_limit=1048576
        (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 …
0
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?
0
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?
0
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?
0
hi,

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 ?
0
Hi,
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
Ian
 
"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"
0
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?

Thanks!
0
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('http://www.mysite.com', '', $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']),
                                              'supplier'       

Open in new window

0
The following is coded in Laravel framework:

QUERY

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

Open in new window


RESULT

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.
0
Hi,

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

DELIMITER $$

CREATE PROCEDURE proc_text (
	IN var_plyr INT
)

BEGIN

	SET lmt = 0;
        SET cnt = 0;

       //original select statement
	SELECT
		p.first_name, p.email
	FROM players AS p 
       WHERE p.status = 1AND p.id = var_plyr
	GROUP BY p.id;
	
       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

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

END $$

DELIMITER ;

Open in new window


Thanks in advance!
0
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.

							<?php
							$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()){
								
										$dunit=$row5['unit_name'];
										$downer=$row5['owner'];
										$ddate=$row5['date'];
										$dinv_no=$row5['inv_no'];
										$dinvoice_sum=$row5['invoice_sum'];
										$dpayment=$row5['payment'];
										$dlatepayment=$row5['late_payment_charges'];
										$damt_overdue=$row5['amount_overdue'];
										$dlatecharge=$row5['late_charge'];
										$dtotal=$row5['total'];											
								?>	
    							<form method='POST' action='' enctype='multipart/form-data'>																																				

Open in new window

0
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";
            if(!empty($price)){
                  $price =implode("'or pro_price between'",$price);
                  $sql  .= " and pro_price between $price";
            }
0
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
newpackage.php

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

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

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

        <!-- 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

1
Is it possible at all to include multiple fields included in crosstab query? For example in below code I now have 2 fields I need to look at as below:

SELECT
  IfNull(c.Adviser, 'GRAND TOTAL') AS Adviser,
  Sum(CASE WHEN Month(i.CompDate) = 1 THEN i.comms + i.broker_fee + i.legal_fees ELSE 0 END) Jan,

Open in new window



What I now need if possible:
Sum(CASE WHEN Month(i.CompDate) = 1 THEN i.comms ELSE 0 END) OR Sum(CASE WHEN Month(i.signedupdate) = 1 THEN i.legal_fees ELSE 0 END)  Jan,

Open in new window



Does that make any sense?
0
Here is my configuration for protecting one of my directories using mod_authn_dbd...
<VirtualHost *:80>

DBDriver mysql

DBDParams host=127.0.0.1,port=3306,dbname=myDB,user=root,pass=*****

DBDMin  4
DBDKeep 8
DBDMax  20
DBDExptime 300

<Directory "/path/to/ProtectedDir">
AuthDBDUserPWQuery "SELECT encrypt(passwd) AS password FROM myTable WHERE user_name = %s AND DUE_DATE>=CURDATE()"
  AuthType Basic
 AuthName "CurrentIssue"
 Require valid-user
 AuthBasicProvider socache dbd
</Directory>


    ServerName www.mySite.com
    ServerAlias mySite.com
    DocumentRoot /Server/WebSites/mySite
    ErrorLog /Server/WebSites/mySite/error.log
    CustomLog /Server/WebSites/mySiterequests.log combined

<Directory "/Server/WebSites/mySite">
    AllowOverride None
    Require all granted
</Directory>

</VirtualHost>

Open in new window

My question is how do I protect another Directory using a different mysql DB?

I've tried adding
DBDParams host=127.0.0.1,port=3306,[u]dbname2[/u]=myDB,user=root,pass=*****

Open in new window

and
<Directory "/path/to/ProtectedDir2">
AuthDBDUserPWQuery "SELECT encrypt(passwd) AS password FROM myTable2 WHERE user_name = %s "
  AuthType Basic
 AuthName "Admin"
 Require valid-user
 AuthBasicProvider socache dbd
</Directory>

Open in new window

As expected this breaks both Directories.
Any ideas on how I would accomplish this?
Is there any way to use mod_authn_dbd with htaccess?
0
I have this query to check the last 3 months sales figures and another query to check the refund figures

       
           // sales
            SELECT DATE_FORMAT(order_date, '%b') as month, SUM(order_total) as totalSales FROM
            (
               SELECT order_date, order_total
               FROM order_summary
               WHERE order_date <= NOW()
               and order_date >= Date_add(Now(),interval - 3 month)
               AND `order_status` = 'Approved'
               
               UNION ALL
               SELECT  Date_add(Now(),interval - 2 month)  AS order_date, 0 as order_total
               UNION ALL
               SELECT  Date_add(Now(),interval - 1 month)   AS order_date, 0 as order_total
               UNION ALL
               SELECT  Now() AS order_date, 0 as order_total
            ) as test
            GROUP BY DATE_FORMAT(order_date, '%m-%Y')
            ORDER BY DATE_FORMAT(order_date, '%m-%Y') desc

Open in new window


           // refunds
        SELECT DATE_FORMAT(credit_date, '%b') as month, SUM(`credit_value`) as totalCredit FROM
            (
               SELECT credit_value, credit_date
               FROM credit
               WHERE credit_date <= NOW()
               and credit_date >= Date_add(Now(),interval - 3 month)
               AND `reason` = 'Refund'
               
               UNION ALL
               SELECT  Date_add(Now(),interval - 2 month)  AS credit_date, 0 as credit_value
               UNION ALL
               SELECT  Date_add(Now(),interval - 1 month)   AS credit_date, 0 as credit_value
               UNION ALL
               SELECT  Now() AS credit_date, 0 as credit_value
            ) as test
            GROUP BY DATE_FORMAT(credit_date, '%m-%Y')
            ORDER BY DATE_FORMAT(credit_date, '%m-%Y') desc
            ");

Open in new window


I want to show the data in a table like:

Month          Sales          Refunds

Sep                100                 0
Aug                100                20
Jul                   50                   5

I thought of trying to combine the queries into one but that seems very complex. And trying to nest the foreach loops creates 6 rows instead of 3. What would be the best way to achieve this?
0

MySQL Server

47K

Solutions

23K

Contributors

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.