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

Hello there, wondering if anyone could point me in the right direction? I have the query below which outputs pivot style table in php. I now need to somehow break it down to show quarterly data too but have hit a wall! ie 2015_Q1, 2015_Q2 etc etc etc

If not possible within this query could it be done in a seperate query at all independent of this one?

SELECT
  Sum(CASE WHEN Year(i.signedupdate) = 2015 THEN i.comms + i.broker_fee + i.legal_fees ELSE 0 END) '2015',
  Sum(CASE WHEN Year(i.signedupdate) = 2016 THEN i.comms + i.broker_fee + i.legal_fees ELSE 0 END) '2016',
  (Sum(CASE WHEN Year(i.signedupdate) = 2016 THEN i.comms + i.broker_fee + i.legal_fees ELSE 0 END) - Sum(CASE
    WHEN Year(i.signedupdate) = 2015 THEN i.comms + i.broker_fee + i.legal_fees ELSE 0 END)) / Sum(CASE
    WHEN Year(i.signedupdate) = 2015 THEN i.comms + i.broker_fee + i.legal_fees ELSE 0 END) * 100 AS '15-16',
  Sum(CASE WHEN Year(i.signedupdate) = 2017 THEN i.comms + i.broker_fee + i.legal_fees ELSE 0 END) '2017',
  (Sum(CASE WHEN Year(i.signedupdate) = 2017 THEN i.comms + i.broker_fee + i.legal_fees ELSE 0 END) - Sum(CASE
    WHEN Year(i.signedupdate) = 2016 THEN i.comms + i.broker_fee + i.legal_fees ELSE 0 END)) / Sum(CASE
    WHEN Year(i.signedupdate) = 2016 THEN i.comms + i.broker_fee + i.legal_fees ELSE 0 END) * 100 AS '16-17',
  Sum(CASE WHEN Year(i.signedupdate) = 2018 THEN i.comms + i.broker_fee + i.legal_fees ELSE 0 END) '2018',
  (Sum(CASE WHEN Year(i.signedupdate) = 2018 THEN

Open in new window

0
Hi,

I've a table on AWS Redshift with couple of columns in JSON format.
So i'm using JSON_EXTRACT_PATH_TEXT Function to query the data in the JSON format cloumn.

For example ,

SELECT user,json_extract_path_text(payload,'code') as payload_code FROM activity_log.lx4_logs
where event_reference LIKE 'My Dashboard%'; .

So the output look like following with two coloumns

User       | Code
user1      | ["13877512003","13877642003","10078402003","13901222003","10118722003"]
user2     |["105700520150300750","106468920140600750","112279220150300750","104693120060101500","122805820150600750"]
user3    |NULL
user4    |["10112472015"]

Is there a possibility to get the output, into rows like below without any quotation, square brackets etc please ?

User       | Code
user1      |13877512003
user1      |13877642003
user1      |10078402003
user1      |13901222003
user1      |10118722003
user2     |105700520150300750
user2  |106468920140600750
user2   |112279220150300750
user2  |104693120060101500
user2  |122805820150600750
user3    |NULL
user4    |10112472015

Thanks
0
I have 2 MySQL databases who are identical in every aspect.

But DB1 changes daily with either some records changed or few new added.

I need to take the DB dump daily and then import to DB2 for them to stay identical.

My question is that, how can I dump the DB 1 so the dump should insert only new records to DB2 and for records that are changed, just update them.
0
I am trying to come up with a query that looks at table_1, and compares it to multiple rows in table_2 to see which row value that matches up inside table_1.

To provide further detail, I have a table of students that has a distance from school column.  The distance to school column is most likely a decimal value between 0 and 50 miles.

In Table 2, I have 5 potential rows that the distance of school would match up with. This table contains a minimum distance column and a maximum column value.

Row 1 has a minimum value of 0 and a maximum value of 10
Row 2 has a minimum value of 10 and a maximum value of 20
Row 3 has a minimum value of 20 and a maximum value of 30
Row 4 has a minimum value of 30 and a maximum value of 40
Row 5 has a minimum value of 40 and a maximum value of 50

The ultimate goal would be to get the price from Table 2 that correlates to the correct distance to school value from Table 1. Each range in Table 2 has a corresponding price.

I'm sure it's a matter of using a case statement, but I'm not sure how I'd go about writing it.

Any direction would be greatly appreciated.  Thank you!
0
Hello guys I know it is so simple with you guys.
How can I disable button if userID is not equal to "1"

<button id="edit" class="edit" value="Edit"/>

How can I disable that button if userID is not equal to 1


Thank You in advance
0
hi,

As MysQL community edition and also Maria DB  :

1) Do not have parallel query

and MySQL community edition:
1) Do not have Compression: columns.
2) Do not have Compression: logs.
3) Do not have Proxy: query cache.
4) Do not have Storage: write optimized (LSM tree).
5) Do not have Storage: write optimized (LSM tree)


any work around for it?
0
i would like to help me to make search button in this code to filter data by first_name or last_name or birth of date or any thing  .
0
I need one support regarding monitoring MYSQL database in this below mentioned scenario.

We have  seperate server for java application and have galera cluster for MYSQL and we are looking for monitoring number of hits between our java application & Mysql database.

Please let me know if there is any tool to find out the number of hits on application with db connectivity.
0
Hi,

I am trying to connect MySQL workbench to my database in plesk. I have tried entering my user credentials and domain ip/port without any luck

Any suggestions would be greatly appreciated
0
Hi there, hoping this is possible but unsure so thought Id ask. The below code sends an email to a hardcoded email address but I wondered if its possible to have it send instead to the field in this query 'u.emailaddress', I am using sendmail..

            $sql = "SELECT l.id, c.Adviser, c.ClientName, l.LeadStatus, l.CompDate, u.emailaddress
            FROM  tbl_lead l
            INNER JOIN tbl_clients c ON c.client_id = l.client_id
            INNER JOIN tbl_users u ON c.Adviser = u.username
            WHERE id = {$rowData['id']}";
            
            $customerFullName = $this->GetConnection()->ExecScalarSQL($sql);            
            $messageSubject = 'Case Completed';                           
            $messageBody = "<p>Adviser case has completed:</p>";           
              
              //compose email body
              $messageBody = '<html><body>';
              $messageBody .= '<table rules="all" style="border-color: #666;" cellpadding="10">';
              $messageBody .= "<tr style='background: #eee;'><td><strong>Adviser:</strong> </td><td>{$rowData['Adviser']}</td></tr>";
</td><td></td></tr>";
              $messageBody .= "</table>";
              $messageBody .= "</body></html>";
              
              //end email body
              
            $address = 'u.emailaddress';
            $cc = array('useremail2', 'myemail@:domain.co.uk'); 
              
            try {
                sendMailMessage($address, 

Open in new window

0
hi,

any ASM like service MySQL can offer?
0
I want to include a ranking into following query by the same as it is ordered:


SELECT BANname as name, BBWbezeichnung as class, BAsumme as points, BANgast as guest

FROM BW_BEW_WGR INNER JOIN (BWAUSWERTUNG INNER JOIN BWANMELDUNG ON BWAUSWERTUNG.BWANMELDUNG_BANid = BWANMELDUNG.BANid) ON BW_BEW_WGR.BBWid = BWANMELDUNG.BBW_BBWid

ORDER BY class, guest, points desc

Open in new window



the result should looks like this:

rank;name;class;points;guest
1;vienna;A;200;y
2;munich;A;100;y
1;zurich;A;300;n
2;moscow;A;200;n
1;lisbon;B;300;n
2;madrid;B;200;n
3;london;B;100;n
0
I'm creating a program to take student attendance data and store it in the database.I have created student_table which stores student details after registration, such as Names, Passport no, Class. I have created another table "attendance_table". I have fetched the student details on a php file and displayed with additional column "attendance" which takes radio button values to mark either present or absent.

My problem is after marking the attendance I want it get stored in attendance_table. Since the details are fetched from student_table, how can I able to store it in another table. I'm new to php. Thanks.


// FETCHING DATA FROM STUDENT_TABLE//

  <tr>
<td><?php echo $row['student_name']; ?></td>
<td><?php echo $row['Passport_no'] ?></td>
<td><?php echo $row['Teacher'] ?></td>

<td><?php echo $row['Class'] ?></td>
<td><?php echo $row['Period'] ?></td>

<td>
Present<input required type="radio" name="attendance[<?php echo $row['stud_id'] ?>]" value="Present" checked>
Absent<input required type="radio" name="attendance[<?php echo $row['stud_id']; ?>]" value="Absent" >
</td>

</tr>
  <?php } ?> 
//ATTENDANCE TABLE//

Table structure for table attendance

DROP TABLE IF EXISTS `attendance`;
CREATE TABLE IF NOT EXISTS `attendance` (
  `at_id` int(11) NOT NULL AUTO_INCREMENT,
  `stud_id` int(11) NOT NULL,
`student_name` varchar(100) NOT NULL,
  `Passport_no` varchar(100) NOT NULL,
  `Class` varchar(100) NOT NULL,
  `Period` varchar(100) NOT NULL,
  `Status` varchar(100)

Open in new window

0
Hi,

I have a procedure in mysql 5.7.20 which does some computation and finally inserts 10k+ records in a table which has about 20 million records. When I call the procedure from workbench, it takes around 6 seconds for the insert part. But when I call it from within the application (java) it takes forever and results in one of the queries being ending up as a deadlock. I have compared with identical parameters. Can you provide any possible reasons and solution for this issue? Also, can you please explain why I am getting these deadlocks for two inserts.

Some of the params -

ignore_builtin_innodb      OFF
innodb_adaptive_flushing      ON
innodb_adaptive_flushing_lwm      10
innodb_adaptive_hash_index      ON
innodb_adaptive_hash_index_parts      8
innodb_adaptive_max_sleep_delay      150000
innodb_api_bk_commit_interval      5
innodb_api_disable_rowlock      OFF
innodb_api_enable_binlog      OFF
innodb_api_enable_mdl      OFF
innodb_api_trx_level      0
innodb_autoextend_increment      64
innodb_autoinc_lock_mode      2
innodb_buffer_pool_chunk_size      134217728
innodb_buffer_pool_dump_at_shutdown      ON
innodb_buffer_pool_dump_now      OFF
innodb_buffer_pool_dump_pct      25
innodb_buffer_pool_filename      ib_buffer_pool
innodb_buffer_pool_instances      8
innodb_buffer_pool_load_abort      OFF
innodb_buffer_pool_load_at_startup      ON
innodb_buffer_pool_load_now      OFF
innodb_buffer_pool_size      24696061952
innodb_change_buffer_max_size      25
innodb_change_buffering      all
innodb_checksum_algorithm      crc32
innodb_checksums      ON
0
Hi,

I need to create a trigger / stored procedure in MySQL that fires off each time a table is modified, does a select that potentially returns multiple results, then matches those results with a human readable name in another table, concatenates all the results into plain text, and then updates a field in yet another table. I am basically looking for an "UPDATE" version of "INSERT INTO".

Background to this is SugarCRM. We have a custom field in the main contact field for Outlook Categories, and that needs to be filled by the names of the target lists that contact belongs to.

So the basic update query would quite simple:
UPDATE contacts_cstm SET cat_type_c = 'XXXXX' WHERE id_c = 'YYYYY'. XXXXX is the concatenated value from the select query and YYYYY is a unique id that should be able to be extract the data from a field value in the altered/new record.

So the SELECT query looks something like this:
SELECT GROUP_CONCAT( DISTINCT prospect_lists.name ) FROM prospect_lists LEFT JOIN prospect_lists_prospects ON prospect_lists.id = prospect_lists_prospects.prospect_list_id WHERE prospect_lists_prospects.deleted = 0 AND prospect_lists_prospects.related_id = 'YYYYY'

Where YYYYY is the id number of the person who's record has changed, and the DISTINCT is just in case there are duplicate rows.

So I figure I need two triggers, one AFTER INSERT, and one AFTER UPDATE, on prospect_lists_prospects but after that I am starting to get lost. First of all, I think I can use …
0
<?php
include("db.php");

if(isset($_POST['submit'])){
 
 $name=$_POST['name'];
$code=$_POST['code'];
$price=$_POST['price'];
 $name1 = $_FILES['image']['name'];
 $target_dir = "product-images/";
 $target_file = $target_dir . basename($_FILES["image"]["name"]);
echo $target_file;
 
  // Insert record
  $query = "insert into tblproduct(`name`,`code`,`image`,`price`) values('$name','$code','".$target_file."','$price')";
 $q1= mysqli_query($db,$query) or die(mysqli_error($db));
  if($q1)
{
	echo "Product inserted";
	//header("Location:sell_add_products.php");
}
	else
	{
		echo"data not inseted";
	}

  // Upload file
  //move_uploaded_file($_FILES['image']['tmp_name'],$target_dir.$name1);
 //}
 
}
?>

Open in new window


This is my docs.php for inserting data into table

<HTML>
<HEAD>
<TITLE>Simple PHP Shopping Cart</TITLE>
<link href="css/style_products.css" type="text/css" rel="stylesheet" />
 
</HEAD>
<BODY>

<div id="product-grid">
	<div class="txt-heading">Products <a href="sell_add_products.php" id="btnEmpty">Back</a></div>
	<?php
	include("db.php");
$product_array=array();
	$product_array="SELECT * FROM tblproduct";
	$result = mysqli_query($db,$product_array);
		while($row = mysqli_fetch_assoc($result))  
      {
	if (!empty($product_array)) { 
		foreach($product_array as $key=>$value){
	?>
		<div class="product-item">
			<form method="post" action="">
			<div class="product-image"><img src="<?php echo $product_array[$key]["image"]; ?>"></div>
			<div><strong><?php echo $product_array[$key]["name"]; ?></strong></div>
			<div class="product-price"><?php echo "$".$product_array[$key]["price"]; ?></div>
			<div><input type="text" name="quantity" value="1" size="2" /><input type="submit" value="Add to cart" class="btnAddAction" /></div>
			</form>
		</div>
	<?php
			}
		}
	}
	?>
</div>
</BODY>
</HTML>

Open in new window


here I'm rretrieving data from table but I'm facing problem

Warning: Invalid argument supplied for foreach() in C:\wamp\www\Ministore\view_products.php on line 19

please let me know the solution  for this problem
0
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Check out</title>
<link href="style.css" rel="stylesheet" type="text/css" />
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<style type="text/css">
<!--
.style9 {font-size: 95%; font-weight: bold; color: #003300; font-family: Verdana, Arial, Helvetica, sans-serif; }
.style10 {color: #FFFFFF}
-->
</style>
</head>
<body>


<form action="" method="POST">
<?php

 include("includes/db.php");
$total=0;
if(isset( $_POST['checkout'] ) )
{
 
$price = "select cart.p_id,cart.ip_add,cart.qty,products.prd_title,products.prd_price from cart inner join products on cart.p_id =products.prd_id";

   $run_price = mysqli_query($con,$price) ;

  
      while($ppprice = mysqli_fetch_array($run_price)){

         $product_id = $ppprice['p_id'];
         $ip_add = $ppprice['ip_add'];
         $qty = $ppprice['qty'];
         $product_title = $ppprice['prd_title'];
         $product_price = array($ppprice['prd_price']);
        

         $price_sum = array_sum($product_price);

         $total +=$price_sum;
        
        echo '<label>Product Id</label><input type="text" value='.$ppprice['p_id'].'>';
         echo ' <label>Order Id</label><input type="text" value='.$ppprice['ip_add'].'>';

 echo '<label>Quantity</label><input type="text" value='.$ppprice['qty'].'>';

 echo 

Open in new window

0
import mysql.connector
from mysql.connector import Error
import os


db = mysql.connector.connect(host='localhost',database='mysql', user='root',password='admin@123')
cursor = db.cursor()

for root, dirs, files in os.walk('music'):
    # pref = ''
    head, tail = os.path.split(root)
    while head:
        # pref +='  '
        head, _tail = os.path.split(head)
      fname = os.path.join(tail)
      cursor.execute("INSERT INTO category(name,parent_id) VALUES('" + fname + "',1)")
      db.commit()
      print(head)

for f in dirs:
      print (f)



output:-
+-------------+----------+-----------+
| category_id | name     | parent_id |
+-------------+----------+-----------+
|           1 | rxl      |         1 |
|           2 | jazz     |         1 |
|           3 | del      |         1 |
|           4 | del      |         1 |
|           5 | mum      |         1 |
|           6 | mum      |         1 |
|           7 | jmv      |         1 |
|           8 | jmv      |         1 |
|           9 | jmv      |         1 |
|          10 | hip hop  |         1 |
|          11 | kol      |         1 |
|          12 | kol      |         1 |
|          13 | bang     |         1 |
|          14 | bang     |         1 |
|          15 | hyd      |         1 |
|          16 | hyd      |         1 |
|          17 | pop      |         1 |
+-------------+----------+-----------+
inserting dirs/subdir/files to mysql from python with parent id.here parent =music …
0
Anyone knows a good GUI tool for MySQL to be the alternative to Sequel Pro? Since I used it to connect to MySQL 8. It keeps crashing.
0
Java Code :
package me.DevGamer08.nexus.handler;

import me.DevGamer08.nexus.Start;
import org.bukkit.Bukkit;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Timer;
import java.util.TimerTask;

public class DatabaseHandler {
    private String username;
    private String passwort;
    private String adress;
    private String database;
    private String logTag = "[Server: %name% / %date% at %time%] -> %adress% -> %port% | %log%";
    Timer timer = new Timer();
    private int port;

    private Connection con;

    public DatabaseHandler(String name, String passwort, String ipadress, int port, String database) {
        this.adress = ipadress;
        this.passwort = passwort;
        this.username = name;
        this.port = port;
        this.database = database;
    }

    public void addServer(String name, String motd, int rconport, String rconpw, String serverip, String status, int playeron, int playermax, String serverid, int serverport) throws SQLException {
        try {
            loggData(name, serverip, serverport, "Seccefully added to Chache!");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        openConnection();
        PreparedStatement ps = queryUpdate("INSERT INTO server (Name, Motd, Rconport, Rconpw, Serverip, Status, Playeron, Playermax, Serverid, Serverport) VALUES (?,?,?,?,?,?,?,?,?,?)");
        ps.setString(1, name);
        ps.setString(2, motd);
        

Open in new window

0
I have an sql function nightmare. My predecessor has created a 16000+ line function which consists of update table functions. Is there a way I can take the values and put them into a table so that I can create one function to do the same thing.

Here is a sample

 UPDATE
     COMPANYTABLE
SET
     COMPANYNAME =  N'NEXUS URBAN LIVING APTS',
     INCAREOFNAME =N'APARTMENT MANAGER',
     COMPANYADDRESS =N'6810 GLENDORA AVE OFC',
     COMPANYCITY =N'SAN ANTONIO',
     COMPANYSTATE =N'TX',
     COMPANYZIP =N'78218',
     COMPANYPHONE =N'2104659680',
     PLTFCOUNTY =N'DONE'
WHERE
     OWNERADDRESS LIKE N'%Glendora%' AND
     OWNERZIP =N'78218' AND
     COMPANYNAME LIKE N'%NEXUS URBAN LIVING%'
0
I have a wordpress site.

Plugins

sucuri
wordfence

I run the scan with wordfence and no problems but google adwords

Says these dangerous links have been found

         deloton[.]com

·         mobisla[.]com

·         mobpushup[.]com

·         pub2srv[.]com

any idea of how to find them?

I have run a string search in the site but nothing s found. I guess the problem is in the mssql tables.

Or are they in the hosting space? Can't find them either

Any  ideas?
0
I need help with how to do this project using the database with php and mysql.

DBproject--1-.docx
0
Hello,

I am creating a data model in power designer. Could you please help me on auto increment for PK column. I am trying to enable auto increment for PK column by checking "Identity" but I am getting error while executing the script in MySQL.

Error Code: 1075
Incorrect table definition; there can be only one auto column and it must be defined as a key
0
hii all
i have two tables table1 and table2 there are two common fields in both tables test_id (not a primary key or unique key).
i fetched all data from table1 and get test_id (can be more than 1) using while loop. now from table2 i want fetch description of that test_id( description rows more than 1 according to test_id).
how to design my nested loop. i am sharing my both tables here. please help me. thank you


Table1

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