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,
How do I zip Mysql database as I have to zip my project in visual studio along with database file.


Thanks in advance
0
Exploring SharePoint 2016
LVL 13
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

Create table from another with same structure

According to MySQL docs:

CREATE TABLE new_tbl [AS] SELECT * FROM orig_tbl;

I tried this in phpMyadmin, as in the attached.

How do I make this work
cannot_create_table.PNG
0
Hi Experts
I have a situation where i need to write a query to create multiple database in mysql.
For example i have database = (test,test1,test3)
How to write a mysql statement for create database
Create database test,test1,test3, How can i loop on my condition and create multiple databases.
Thanks in advance.
0
Assistance in writing a script that will:-

a) backup mySQL databases at 00:01, together a log to state that backups are completed successfully or failed and and e-mail notification is sent out
b) mySQL databases are kept for 7days before they are archived
0
In the code below I am using a CSV file and creating a temporary table, and then using an inner join to update a field where the SKU is matching.
DROP TABLE IF EXISTS oc_product_import; 
CREATE TABLE oc_product_import LIKE oc_product;
LOAD DATA INFILE '/var/lib/mysql-files/out.csv'
       INTO TABLE oc_product_import 
       FIELDS TERMINATED BY ','
       OPTIONALLY ENCLOSED BY ';'
       (sku, quantity);
UPDATE oc_product AS R 
INNER JOIN oc_product_import AS P 
      ON R.sku = P.sku 
SET R.quantity = P.quantity; 
DROP TABLE oc_product_import;"

Open in new window


This works fine for me my current purposes. But now I have two tables, TABLE1 has the QUANTITY, and an ID. TABLE2 has an ID, and a SKU. I want to update the quantity using the SKU’s from my CSV as i’ve done before but have to reference it by this ID in another table. That’s the part I don’t know how to do. If my question is confusing please ask a follow-up.
0
I am trying to pass a number with a decimal point as a part of a query string with this Javascript:

sname = document.st.sel_name.value;
			document.st.action = "sel_save.php?sname=" + sname + "&tptot=" + encodeURI(tptot);
			return true;

Open in new window


Here is what the url looks like:

https://www.lakoshvac.com/devdev/sel_save.php?sname=It%20better%20do%20it%20this%20time&tptot=12178.95

It seems obvious to me that the . in the number is screwing things up.

In save_sel.php, I do this: $tptot = urldecode($_GET['tptot']);

Later on, I am trying to insert a record (row) into a MySQL table, like this:

$qry = "INSERT into selections (ruid, sel_name, date, cltype, length, width, depth, flow, sspct, pgroup, access, flevel, model, purge_method, tpurge, voltage, pepump, pumprepair, valve_kit, dec_contact, multi, repl_bags,strainer, net_price, ssgroup) VALUES(" . $_SESSION['ruid'] . ", '" . $sel_name . "', '" . date('Y-m-d') . "', '" . $_SESSION['cltype'] . "', " . $length . ", " . $width . ", " . intval($depth) . ", " . intval($flow) . ", " . intval($sspct) . ", '" . strtolower(substr($_SESSION['model'],0,3)) . "', '" . $_SESSION['access'] . "', " . intval($_SESSION['flevel']) . ", '" . $_SESSION['model'] . "', '" . $_SESSION['purge_method'] . "', '" . $_SESSION['purge'] . "', '" . $_SESSION['voltage'] . "', '" . $_SESSION['pepump'] . "', '" . $_SESSION['pumprepair'] . "', '" . $_SESSION['valve_kit'] . "', '" . $_SESSION['DEC'] . "', '" . $_SESSION['multi'] . "', " . $replbags . ", '" . $_SESSION['strainer'] . "', " . $tptot . ", '" . $_SESSION['group'] . "')";

Open in new window


I echo the $qry, $tptot is null, so the insert fails.

How can I fix this?

Thank you.
0
I have 6 tables in a MySQL database. I want to copy the table structure of these 6 to another existing database.

Initially, there will be O data in any of these tables.

Can I do this using phpMyadmin? If not, how else?

Thanks
0
Hi Experts,

I'm in need of a PHP solution that will emulate the import function of PHPMyAdmin.

The import should parse an Excel file, then use the first row within the Excel file as the column names of the DB table, then insert each row of the Excel document into the table.

I've seen many solutions out there which allow PHP to import an Excel file but, the catch with these solutions is that you must create the data table first, then use PHP to parse and insert the data. In my circumstance, I won't know how many columns exist within a spreadsheet that is being imported. I need PHP to figure how many columns should exist in the table, and what data types they are, then create the columns and insert each row of data into the table (this may not be correct programming logic).

Does anyone know of any snippets of code or, third party libraries that may accomplish this task?

Cheers
0
hi,

do you know if MariaDB has package ?

seems only when mariaDB in oracle mode can do start it CREATE it ?
0
hi,

we have a function like this:

create or replace FUNCTION        to_string (nt_in IN varchar2_ntt, delimiter_in IN VARCHAR2 DEFAULT ', ') RETURN VARCHAR2 IS
v_idx PLS_INTEGER;
v_str VARCHAR2(32767);
v_dlm VARCHAR2(10);
BEGIN
v_idx := nt_in.FIRST;
WHILE v_idx IS NOT NULL LOOP
v_str := v_str || v_dlm || nt_in(v_idx);
v_dlm := delimiter_in;
v_idx := nt_in.NEXT(v_idx);
END LOOP;
RETURN v_str;
END to_string;

Open in new window


what is this function suppose to do ?

and how to call this function to make some meaningful return ?
0
Active Protection takes the fight to cryptojacking
LVL 2
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

hi,

right now we try to migrate oracle view to MariaDB but most of the oracle view we have need to user this oracle system view:ALL_TAB_COLS , which MariaDB do not have and therefore gives error.

when we migrating VIEW containing ALL_TAB_COLS, what should we do in MariaDB?
0
I was recently asked to prepare a bunch of CSV files for import into ArcGIS.

I managed to do what was asked using VBA/ADO but it took 2 days to complete the task.

Wonder if anyone had any ideas on how I could have speeded up the process?

Can't post the code I used right now but I've attached a small example file.
0
I have a $paymentsTotal variable which is the sum of all payments. I then want to loop through all outstanding invoices and reduce the $paymentsTotal variable for each outstanding amount. If the payment amount is enough to cover invoice 1, it must be marked as paid, if the same for invoice2, mark as paid etc.

I have this code which works to an extent.

                foreach ($invoices as $invoice) {
                   $something = $paymentTotal - $paymentTotal -= $invoice->amount;
                   if($paymentTotal >= $something) {
                       echo $invoice->amount . ' - PAID <br>';
                   }
                }

Open in new window


The problem is that if for example I have:

invoice 1 = 550
Invoice 2 = 400

If I input 500 it should fail on invoice 1 but pass on invoice 2 and mark it is paid. But this doesn't happen. I have to input a minimum of 550 for it to pass and 950 for both to pass. Ideally if I enter 500 it should mark Invoice 2 as paid as 500 is more than 400.
0
What I am trying to do is when a payment is made, the amount is inputted into an input field and then a loop runs through all the customer invoices and as long as each invoice amount is less than the paid amount, less the deducted invoice amount, it inserts that invoice amount into another table. The problem though is if say half an invoice is paid and the user still has an outstanding balance. To cater for this I did another check which checks if the user after payment has an outstanding balance or not. If not or if in credit, it should then pay off all outstanding invoices as the user has a zero balance and therefore should not owe anything.

To do this, I used INSERT IGNORE. It seems to work but I was concerned that it might be inefficient over time as it has to check more and more records?

           $stmt = $db->prepare("INSERT IGNORE INTO `payment_link` (`invoice_id`, `customer_id`, `amount`) VALUES (:invoice_id, :customer_id, :amount)");
            $stmt->bindParam(":invoice_id", $invoice_id, PDO::PARAM_INT);
            $stmt->bindParam(":customer_id", $this->customer_id);
            $stmt->bindParam(":amount", $amount, PDO::PARAM_STR);
            
            foreach ($invoices as $invoice) {
                if ($invoice->amount <= $this->amount) { 
                    $amount = $invoice->amount;
                    $invoice_id = $invoice->id;
                    $this->amount = $this->amount - $invoice->amount;
                    $stmt->execute();
 

Open in new window

0
Hello Experts,
Please your advice on LOAD DATA INFILE

Trying to upload a text file into a mysql table.

here is the table structure.
CREATE TABLE `tbl_patients` (
  `reg_cod` int(6) NOT NULL AUTO_INCREMENT,
  `pat_cod` int(6) NOT NULL DEFAULT '0',
  `tmp_mr` varchar(10) DEFAULT '',
  `azv_cod` bigint(10) NOT NULL DEFAULT '0',
  `pat_tit` varchar(5) DEFAULT '',
  `pat_ini` varchar(10) DEFAULT '',
  `pat_na1` varchar(30) DEFAULT '',
  `pat_prf` varchar(10) DEFAULT '',
  `pat_na2` varchar(40) DEFAULT '',
  `int_nam` varchar(70) DEFAULT '',
  `acc_nam` varchar(60) DEFAULT '',
  `mar_sta` char(1) DEFAULT '',
  `pat_sex` char(1) DEFAULT '',
  `pos_cod` int(10) NOT NULL DEFAULT '0',
  `pos_des` varchar(70) DEFAULT '',
  `add_num` int(5) NOT NULL DEFAULT '0',
  `add_dat` varchar(5) DEFAULT '',
  `int_add` varchar(70) DEFAULT '',
  `ema_add` varchar(30) DEFAULT '',
  `sms_cel` varchar(10) DEFAULT '',
  `pat_tel` varchar(70) DEFAULT '',
  `pat_dob` date DEFAULT NULL,
  `wor_pla` varchar(30) DEFAULT '',
  `pat_rem` text,
  `photo` varchar(150) DEFAULT '',
  `pol_num` varchar(10) DEFAULT '',
  `cer_num` varchar(10) DEFAULT '',
  `gua_nam` varchar(70) DEFAULT '',
  `gua_id` varchar(70) DEFAULT '',
  `pro_cod` int(5) NOT NULL DEFAULT '0',
  `pro_des` varchar(30) DEFAULT '',
  `ref_cod` int(5) NOT NULL DEFAULT '0',
  `ref_des` varchar(30) DEFAULT '',
  `sta_cod` int(5) NOT NULL DEFAULT '0',
  `sta_des` varchar(50) DEFAULT '',
  `ins_day` date …
0
Hello Experts,
 We are working with mysql 8.0.13 in windows 10

I am trying to upload to a table a relatively large text file
(columns separated by TAB CHR(9), or should it be COMMA? )

this is my command:

mysql> LOAD DATA INFILE 'C:/tmp/sql/999-YY-MM___HH-MM-SS/tbl_data.txt' INTO TABLE db1.tbl_data;

message:
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

how can I solve  this issue?
Regards,
0
I have an invoice for 300 and an invoice for 500 therefore my balance is 800. I make a payment of 500 and my balance is therefore 300 and I only pay off 1 invoice out of 2. I want that one invoice payment to be inserted into a table.

I was looping through to see where the invoice amount was less than or equal to the payment amount but the problem is both are true because 300 is less than or equal to 500 and 500 is equal than or less than 500. So, both records are moving to the table instead of one.

I need to check against one, reduce the 500 by that amount and then check again to see if 500 - 300 is enough to pay 500 which it isn't so only one record should be inserted.

            $stmt = $db->prepare("INSERT INTO `payment_link` (`amount`) VALUES (:amount)");
            $stmt->bindParam("amount", $amount, PDO::PARAM_STR);

            foreach ($invoices as $invoice) {
                if ($invoice->amount <= $this->amount) {
                    $amount = $invoice->amount;
                    $stmt->execute();  
                }
            }

Open in new window

0
I am returning an array of results which I put into a foreach loop and each result checks against a single amount. As long as the condition is met it should insert rows. However, no data is ever inserted into the database but the error logs are also empty ie: I get no errors.

      foreach ($invoices as $invoice) {
           if ($invoice->amount <= $this->amount) {
               $stmt = $db->prepare("INSERT INTO `payment_link` (`amount`) VALUES (:amount)");
               $stmt->bindValue(":amount", $invoice->amount, PDO::PARAM_STR);
               $stmt->execute();
           }
        }

Open in new window

0
Dear All,

I use MS SQL and usually create User Defined Data Types and UID.
Could I do it using MySQL ?

Thank you.
0
Acronis Global Cyber Summit 2019 in Miami
 Acronis Global Cyber Summit 2019 in Miami

The Acronis Global Cyber Summit 2019 will be held at the Fontainebleau Miami Beach Resort on October 13–16, 2019, and it promises to be the must-attend event for IT infrastructure managers, CIOs, service providers, value-added resellers, ISVs, and developers.

Hello Experts,
I have several sql files 30 MB size average,
and I am using MySQL command line: Source c:/sql/filename.sql
to upload it to the database.
Works just fine but for this size too slow.
is there any way to upload a MySQL database faster ?
Regards,
0
Take backup of MySQL and My Apps from Azure to the local PC.
I have only 2 more days to take backup after that it will get deleted.

Appreciate your help to download/backup the database and apps.

Thanks
MAS
0
Is there are way to send update tasks to the MySQL server that releases the user interface to move onto other things?

I'm trying to improve the performance of my application when a user makes a change that effects data in other tables, as it triggers a handful of various update and delete queries.

I thought I could use the trigger capability in the MySQL table, and simply append a temporary  table with needed parameters, and then have the trigger  run though the list of queries that use those parameters and all of this would run on the server and the UI would be released so the user could navigate to their next task instead of waiting for all this to finish processing.

I know the Events I set up in MySQL can run in the background and run a list of queries at a set interval that don't effect the UI, so I'm thinking there must be a  way to trigger a similar capability.

So far I'm still getting the spinning cursor wheel while the UI waits for the trigger queries to run.  I need more speed Scotty!!!  What am I missing?

The UI is MS Access using VBA and passthrough queries with MySQL ODBC connections to the MySQL server.

And I'm using Navicat for MySQL for all my MySQL development.

Thanks!
0
Hi Experts

Could you pont a way to obtain the volume of accesses to a customer portals (Apache/ MSSQLServer) ?

Accordingly to:
 img002
The customer enter on some site's pages with it's credentials, choosing it's name on a combo, etc...

Is it possible to obtain the access volume by using the Apache default features, f.e. ?

Thanks in advance!
0
Dear Experts,

I use PHP 7.2 and mySQL version 5.6

with below SQL I got the first entry of my users which is entered yesterday, however I also need to get the last entry of the same users which is entered yesterday,
how can I do that?
I want to show the first and the last entry of my users and their entrance time on the same table.

this sql works perfectly fine to bring the first enterance,

$sql = "SELECT uid,tarih FROM takip WHERE tarih >= '$raportarihi' group by uid ORDER BY `takip`.`tarih` DESC";

Open in new window


this sql works perfectly fine to bring the last enterance,

$sql = "SELECT uid,max(tarih) as tarih FROM takip WHERE tarih >= '$raportarihi' group by uid ORDER BY max(tarih) asc";

Open in new window


how can merge these two sql statements to get the data inside only one table?

thank you
0
I have this line of code in a php program where I am trying to insert a row into a MySQL table:
$qryipsp = "INSERT into proj_sel_pricing (pid, selno, adj_price, ruid) values(" . $pid . ", " . $selno . ", " . $adj_price . ", " . $_SESSION['ruid']; . ")";

Open in new window


When I run it through https://phpcodechecker.com/, it says this:

PHP Syntax Check: Parse error: syntax error, unexpected '.' in your code on line 332

    $qryipsp = "INSERT into proj_sel_pricing (pid, selno, adj_price, ruid) values(" . $pid . ", " . $selno . ", " . $adj_price . ", " . $_SESSION['ruid']; . ")";

PHP Syntax Check: Errors parsing your code

I cannot see any inconsistency with number of '.' in this line.

I suspect, as usual, I am overlooking the obvious.

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