[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

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 need to update all the rows in a MySql table like this:

START TRANSACTION;
update product set  t1="t1" where p_id=40717;
update product set  t1="3" where p_id=40713;
.....
commit;

Open in new window


The problem is that there can be thousands of rows and it is very slow.
Is there a better way to do it?

Thank you!
0
I'm trying to write to two mySQL tables at the same time.  I have been able to write a test field, but I am trying to write a var to the table.  I want to write the record number ($theRN)  of the first table into the second table.  This is my code:

===================================================
if ((isset($_POST["InsertProducts"])) && ($_POST["InsertProducts"] == "form1")) {
  $insertSQL2 = sprintf("INSERT INTO Products (Name, Display_Name, Brief_Description, Part_no, Short_name, Change_Date, Base_Price, Bullet1, Bullet2, Bullet3, Bullet4, Model_No, Large_Image, Tax_Item, Handling_Fee, Weight, Ship_Using_Old, Postage_Is) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s,   %s, %s, %s, %s, %s, %s, %s, %s )",
                                      GetSQLValueString($_POST['name'], "text"),
                                 GetSQLValueString($_POST['title'], "text"),
                                 GetSQLValueString($_POST['description'], "text"),                                 
                                 GetSQLValueString($_POST['Product_no'], "text"),
                                 GetSQLValueString($_POST['Product_no'], "text"),                                 
                                          GetSQLValueString($_POST['today'], "date"),
                                 GetSQLValueString($_POST['SubTotal_pri'], "double"),         
                                  GetSQLValueString($_POST['title'], "text"),
                                 GetSQLValueString($_POST['project'], "text"),
                                          GetSQLValueString($_POST['HUsername'], "text"),                                                
                                          GetSQLValueString($_POST['quote_no'], …
0
Hi Experts!

Could you point a good (didactic) material preferencialy that you know about AWS web service creation by using PHP?

Thanks in advance
0
My title is that it's complicated. Complicated for me that is :-P

When a product is purchased it goes into a product summary and product detail table. The product detail table holds info like the order id and then each item that was ordered as well as the qty.

Here is the basic structure

order_detail_id (primary key)
order_id
product_id
product_qty
product_price (this is the price of the item at the time of purchase)

Then at the same time, data is inserted into a coupon/voucher table but that table holds no item price.

I am trying to join the 2 tables but having difficulty in getting the product price.

The voucher/coupon table:

voucher_id
product_id
serial (serial number of the voucher/coupon)
rep (sales rep)
purchased (date of purchase)
order_id
customer_id

I want to show each voucher serial number with the price it was sold for. But I can't seem to get it right. The prices don't match up with the serial or it shows duplicate serials.

        $this->db->query("SELECT `serial`, `purchased`, `vcs_code`, `prod_name` FROM `vouchers` AS `v`
        INNER JOIN `order_summary` AS `os` ON os.`order_id` = v.`order_id`
        INNER JOIN `bb_products` AS `bbp` ON bbp.`prod_id` = v.`product_id`
        INNER JOIN `order_detail` AS `od` ON od.`order_id` = v.`order_id`
        WHERE `rep` = :rep
        ORDER BY `purchased` DESC
        LIMIT 10
        ");
        $this->db->bind("rep", $_SESSION['rep_id']);
        $results = 

Open in new window

0
I have a php while loop that I need to increment through a MySQL multi query. The first time around, it writes to the database as intended. But as soon as it loops around once, I get the following Query Error:

Commands out of sync; you can't run this command now

Here's the loop containing the query:

$i = 285;
while ( $i <= 286 ) {
	$sql = "
		CREATE TEMPORARY TABLE links_temp SELECT * FROM links WHERE 
			schoolid = 0 
			AND planid >= 8000
			AND planid <= 8005;	
		UPDATE links_temp SET linkid = NULL, schoolid = {$i} WHERE 
			schoolid = 0;
		INSERT INTO links SELECT * FROM links_temp;
		DROP TEMPORARY TABLE IF EXISTS links_temp;
	";
	$result = mysqli_multi_query( $conn, $sql ) or die( "Query Error: " . mysqli_error( $conn ) );
	$i++;
}

Open in new window


What do I need to do to enable this query to loop successfully?
0
I would like to connect my MYSQL to access form.
Is there any article to connect access form to MYSQL.
I tried to add datasource but I cant see MYSQL listed
test.JPG
0
I'm trying to duplicate a row in a MySQL table while updating 2 fields.  Here's the MySQL:

$sql = "
	CREATE TEMPORARY TABLE links_temp SELECT * FROM links WHERE linkid=0 AND planid=8000;
	UPDATE links_temp SET linkid=NULL, schoolid=285;
	INSERT INTO links SELECT * FROM links_temp;
	DROP TEMPORARY TABLE IF EXISTS links_temp;
";

Open in new window


And here's the error message generated:

Query Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE links_temp SET linkid=NULL, schoolid=285; INSERT INTO links SELECT * FRO' at line 2

Honestly, I'm going by what I've found on the web for doing this without fully understanding the syntax. That said, how can I correct my code and achieve my goal?

Thanks!
0
I am having a problem with a trigger in mysql.
This is the first time that I have tried it, but it should be simple. I'm just having trouble with the syntax.
I know that this is a good example of non-normalized tables. I have a good reason for the duplicate columns. Please don't waste time lecturing.

I have a database called test. it has two tables customers and cpes.

      
CREATE TABLE `customers` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `custname` varchar(25) NOT NULL,
 `clientMAC` varchar(18) NOT NULL DEFAULT '00:00:00:00:00:00',
 `CustAccountNumber` varchar(8) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1


CREATE TABLE `cpes` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `CustAccountNumber` varchar(8) NOT NULL,
 `WANMAC` varchar(18) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1

mysql> select * from customers;
+----+----------+-------------------+-------------------+
| id + custname | clientMAC         | CustAccountNumber |
+----+----------+-------------------+-------------------+
|  1 | andy     | 00:00:00:00:00:00 | NUM0001           |
|  2 | billy    | 00:00:00:00:00:00 | NUM0002           |
+----+----------+-------------------+-------------------+
2 rows in set (0.00 sec)

mysql> select * from cpes;
+-----+-------------------+-----------+
| id  | CustAccountNumber | WANMAC    |
+-----+-------------------+-----------+
|  1  | NUM0001           | unknown   |…
0
How do I format this output please:
printf('<td><a href="?page=%1$s">Page %1$s</a></td>&nbsp;&nbsp;', $i);

ie colour the fonts or add images in place of text
0
Hello to all,

I'm having trouble connecting a Google Spreadsheet to a local MySql database in one of our offices.
Error displayed by Google is: A connection could not be established with the database, check user password and connection string.

This is the code i'm trying:

   var connectionstring = "jdbc:mysql://public_ip:9002/db_name";
   var conn = Jdbc.getConnection(connectionstring, "user", "password");
   var stmt = conn.createStatement();

Other scenario considerations:
1. The Firewalls incoming 9002 port is being forwarded correctly into the 3306 on the local db server.
2. user and password work fine since Remote Workbench connections are possible.
3. I've even tied up a remote MS Excel sheet to the db and it works with the same url and login information
4. No IP incomming connections are being blocked explicitly, you can call the port from whatever ip you want and you will be redirected to the internal server

Does somebody have any guidance on what to try?
i've been fighting for over a week!

Thanks to all
Eric.
0
My site went down due to Maria DB increasing in memory usage.  I am running atop and went through the logs and saw it increase in size enough to take the server down.

I ran mysqltuner on the DB and got these results

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Control warning line(s) into /var/log/mariadb/mariadb.log file
    Control error line(s) into /var/log/mariadb/mariadb.log file
    Run OPTIMIZE TABLE to defragment tables for better performance
      OPTIMIZE TABLE `thefruga_smf_test`.`smf_log_errors`; -- can free 47.8366432189941 MB
    Total freed space after theses OPTIMIZE TABLE : 47.8366432189941 Mb
    Restrict Host for user@% to user@SpecificDNSorIp
    MySQL was started within the last 24 hours - recommendations may be inaccurate
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
    Performance schema should be activated for better diagnostics
    Consider installing Sys schema from https://github.com/mysql/mysql-sys
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: http://bit.ly/2wgkDvS
Variables to adjust:
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_limit

Open in new window

0
<html>
<head>
<title>Inventas Sites Visits</title>
<link rel="stylesheet" type="text/css" href="css/style.css">
     <link href="css/layout.css" rel="stylesheet" type="text/css" />
        <link href="css/menu.css" rel="stylesheet" type="text/css" />

    <?php 
    //Connect to our MySQL database using the PDO extension.
    require_once('incSearchDatabaseConnectionBySelection.php'); ?>
        
    <?php
    
$space = "&nbsp;";
$break= "</br>";
 
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} ?>
</head>
    <body align=center>
        <?php require_once('incMenu.php'); ?> 
        <br />
        <h1>Inventas Database Search Results - All Records</h1>
        <div style= "text-align: left; margin-left:300px; width:600px;">
    <?php
$sql = "SELECT sites_id, sites_name, sites_email, sites_company, sites_representative, sites_comments, sites_todo, sites_datevisited, sites_return FROM inventassites";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo '<font color="blue">' . "id:" . '<font color="purple">' .$space.$row["sites_id"]. " - Name: " . $row["sites_name"]. " email address: " .  $row["sites_email"]. " Company: " . $row["sites_company"]. '<font color="red">'.$break." Representative: " . $row["sites_representative"]. '<font 

Open in new window

0
Hi expert , any solution for me to generate auto increment number starting from 735/2018 in column  registration number of  textbox html.. then 736/2018 when new entry  is insert.
Capture.PNG
i wish registration number in my textbox can auto increment starting 735 then 736 ... so on
0
I have a table named entries. I have another table named entries2. In each table there is an id column and a content column. I need to replace all the content in table entries with all the content from table entries2. They tie together by column id.
0
I have spent hours trying to figure this out but am seriously stuck. I installed the latest version of mysql today and some things in my website broke. I believe it has to do with strict settings, IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

I am trying to disable strict mode just to get my site working but it seems impossible. I have tried this in terminal but no luck.

sudo vim /etc/mysql/conf.d/disable_mysql_strict_mode.cnf

[mysqld]
sql_mode="IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Please help!
0
Hi Experts

Could you point how to create a custom plugin to prevent that our customizations are lost during updates?

Directly customized theme functions.php

img004
I followed these steps, but something else must to be done to get it working.

1. Create a folder and a file under plugins' folder to customize to avoid directly customize functions.php

img001
2. The plugin is now avaliable
img002
3. But if it's selected, it doesn't exists.
img003
Thanks in advance!
0
I have this MySQL Query:

SELECT * from products where type = 'Industrial' and Appl = 'Sepa' and Access = 'Acc' and micron = 74 and 7000 >= lowflow and 7000<= hiflow order by hiflow desc

It produces the result as attached.Query_wrong.JPG Why is it NOT ordering by hiflow desc? Do there need to be paren's around the where conditions? Or?

Thanks
0
Hey,

I am building an application that will run on multiple instances of the same Mysql schema.  Same database schema, different data in each.  The goal is to keep each clients data in separate instances of the same database.  I have designed my model in MySQL Workbench and have been using the "Synchronize Model" function and that works OK, but i have to specifically push the changes to each instance of the DB.   As this grows that will become unmanageable.  

Is anyone aware of any database management tools that will allow me to push schema changes to multiple instances simultaneously?   Again, this is not replication as the data in each DB is different, i am looking for a way to push Schema changes only.

Thanks in advance.
0
hi,

this is the first time I install MySQL  enterprise 8 and it is very different from what I install MySQL 5.7.19 before,both using generic binary before by extracting Tar.

hi,

Right now trying initialize the mysql enterprise 8 with a my.cnf of this content:

[client]
socket = /POC/mysqlserver8/data/mysql5719.sock

[mysqld]
server-id = 3

auto-increment-offset= 3    

auto-increment-increment=10  

log-slave-updates = ON
socket = /POC/mysqlserver8/data/mysql5719.sock
port = 3306
basedir = /POC/mysqlserver8
datadir = /POC/mysqlserver8/data
log_bin = mysql-bin
innodb_flush_log_at_trx_commit=1
sync_binlog=1
binlog_do_db = sample
bind-address    = 0.0.0.0       #connection from all IPs allowed

general_log = 1
general_log_file = /POC/mysqlserver8/mysql_general.log



slow_query_log = 1
slow_query_log_file  =  /POC/mysqlserver8/slow_query.log

log_error = /POC/mysqlserver8/MySQL_error.log

log_queries_not_using_indexes = 'ON'
log_syslog = 1


1) Once  initialize it by using bin/mysqld --defaults-file=my.cnf --user=mysql  --initialize, I  don't see this file exist: /POC/mysqlserver8/data/mysql5719.sock, any problem of it ?
2) once initialize, it return no message to me but the data folder has a lot of file generated which means the process worked, but when compare to the MySQL setup I did before, there should be a temporary password generated by this process, any problem with it?
3)  when login using root account to try the …
0
Dear Experts,
I use php and mysql.
I create an announcements page, I list here the announcements.
First I insert the announcements into my database as BLOB. Everything is fine until this part.
I check the database and BLOB ( the text ) is the same as I entered it. ( the text has the break lines )

However when I select it from my database with below code, it is comes without break lines.
What do you suggest I should do? I need the show the text on my page properly, with break lines.
Thank you

$sql = "SELECT * FROM announcements";

$result = $mysqli->query($sql);

$num = $result->num_rows;
   
if ($num > 0) {

    while($row = $result->fetch_assoc()) {
        	
	$announcements= $row["announcements"];	

Open in new window

0
The only message returned is, "Connected successfully"
No errors. No data.
I am doing something wrong here. Likely many
Can you adjust my code to properly function?

<?php
/*
 Template Name: MySQL SP
 */
$servername = "localhost";
$username = "resourt2_remote";
$password = "mypassword";

// Create connection
$conn = mysqli_connect($servername, $username, $password);

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";

$storedProc = "call resourt2_ss_dbname21c.recordingfees('Ohio','Butler',5,@recordingfee)";
echo "<br>";
// echo $storedProc;

// Call sproc
// IsSupervisor(IN username CHAR(20), OUT success BOOLEAN)
if(!$mysqli->query("CALL resourt2_ss_dbname21c.recordingfees('Ohio','Butler',5,@recordingfee)"))
{die("CALL failed: (" . $mysqli->errno . ") " . $mysqli->error);}

// Fetch OUT parameters
if (! $res = $mysqli->query("SELECT @recordingfee AS result;"))
    die("Fetch failed: (" . $mysqli->errno . ") " . $mysqli->error);
$row = $res->fetch_assoc();

// Return result
if($row['result'] == TRUE) {
    return true;
}
else {
    return false;
}
echo "DONE";
?>

Open in new window



Also the Stored Proc is working well

CREATE DEFINER = 'resourt2_remote'@'108.999.999.999'
PROCEDURE resourt2_ss_dbname21c.recordingfees(IN state VARCHAR(255), IN county VARCHAR(255), IN pages SMALLINT, OUT recordingfee FLOAT)
BEGIN
SELECT 
  CASE state 
  WHEN 'Ohio' THEN /* OHIO */ 
  IF(pages <= wuct.MinNumberOfPages, wuct.satisfactionMinPages, ((pages - wuct.MinNumberOfPages) * SatisfactionAdditionalPage + wuct.satisfactionMinPages)   )
  WHEN 'Florida' then 35.50
  ELSE 0
  END

  INTO recordingfee  
  FROM wp_udip_CountyTax wuct
  WHERE wuct.State = state AND wuct.County = county;
END

Open in new window


This returns 52:
CALL resourt2_ss_dbname21c.recordingfees('Ohio','Butler',5,@recordingfee);
SELECT @recordingfee as RecordingFee;

Open in new window

0
Hi:
I am getting the error "Unknown column 'cr_tags.tag' in 'field list'" when executing the code.
I first built the code for two tables and I got the results that I expected; however, when I added the third table I got this result.
Any ideas why this is, and how to resolve 'cr_tags.tag' in the code?

MySQL version: 5.7.19
PHP version: 7.0.27
APACHE: 2.4.25

Table: cr_name
Columns: id (int 10), name (varchar 70)
Values:
id      name
---------------------
424     name 1
425     name 2
426     name 3
427     name 4
428     name 5
429     name 6

Table: cr_notes
Columns: id (int 10), notes (varchar 500)
Values:
id      notes
---------------------
424     notes 1
425     notes 2
426     notes 3
427     notes 4

Table: cr_tags
Columns: id (int 10), tag (varchar 30)
Values:
id      tag
------------------
424     tag 1
425     tag 2
426     tag 3
428     tag 5

Desired output:
id        name     notes        tag
--------------------------------------------
424     name 1
425     name 2  notes 2
426     name 3  notes 3     tag 3
427     name 4  notes 4
428     name 5                    tag 5
429     name 6

Actual output:
"Unknown column 'cr_tags.tag' in 'field list'"

$sel = "(SELECT cr_name.id AS rank, cr_name.name, cr_notes.notes, cr_tags.tag
FROM cr_name
LEFT JOIN cr_notes
ON cr_name.id = cr_notes.id
WHERE cr_name.id > 423)

UNION

(SELECT cr_name.id AS rank, cr_name.name, cr_notes.notes, cr_tags.tag
FROM cr_notes
RIGHT 

Open in new window

0
<?php


if(!empty($_FILES)){
    
    //database configuration
    $company = $_GET['company'];
    $dbHost = 'localhost';
    $dbUsername = 'root';
    $dbPassword = 'billadmin2006';
    $dbName = 'inventas';
    //connect with the database
    $conn = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName);
    if($mysqli->connect_errno){
        echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
    }
    
    $targetDir = "uploads/";
    $fileName = $_FILES['file']['name'];
    $fileSize = $_FILES['file']['size'];
    $targetFile = $targetDir.$fileName;
    
    if(move_uploaded_file($_FILES['file']['tmp_name'],$targetFile)){
        //insert file information into db table
        $conn->query("INSERT INTO documents (companyName, f_name, d_date, f_size) VALUES('".$company."','".$fileName."','".date("Y-m-d H:i:s")."','".$fileSize."')");
    }
    
}
?>

Open in new window


The $company = $_GET['company']; is just not getting added to my database. The url is fine - the GET value is correct. If I set the $company = 'test'; it works. I am only using GET to test - I would usually use POST
Could I use a session variable - if so how do I set a session variable that can be used in multiple pages as a variable for database input
0
Hi,

I'm using TCPDF/PHP/MySQL to produce picking slips for my orders. I want to highlight orders where the quantity is greater than 1 so that the background is black and the text is white to make it stand out from a standard white background with black text for single unit orders.

I presume that this must be possible - does anyone have any pointers?

J
0
hi all,

Any known problem on  MariaDB to work with MySQL Enterprise and MySQL community?

what need to setup so that they all work together well ?

we are considering to save cost on, e.g. only some of them are MySQL Enterprise and the rest are MySQL community/MariaDB.

any idea?
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.