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 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
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
hi,

I am studying MariaDB trigger:

this is  a trigger:

CREATE DEFINER=`root`@`%` TRIGGER `HR`.`UPDATE_JOB_HISTORY` AFTER UPDATE ON HR.EMPLOYEES FOR EACH ROW 
Begin 
   IF (NEW.job_id <> OLD.job_id) AND(NEW.department_id <> OLD.department_id) then 
      CALL HR.ADD_JOB_HISTORY(OLD.EMPLOYEE_ID,OLD.HIRE_DATE,CURRENT_TIMESTAMP,OLD.JOB_ID,OLD.DEPARTMENT_ID); 
   end if; 
end; 

Open in new window


it will call a SP called ADD_JOB_HISTORY and what this SP does is:

DROP PROCEDURE IF EXISTS HR.ADD_JOB_HISTORY; 
CREATE PROCEDURE HR.`ADD_JOB_HISTORY`(P_EMP_ID          MEDIUMINT 
   , P_START_DATE      DATETIME 
   , P_END_DATE        DATETIME 
   , P_JOB_ID          VARCHAR(10) 
   , P_DEPARTMENT_ID   SMALLINT) 
BEGIN 


   INSERT INTO JOB_HISTORY(EMPLOYEE_ID, START_DATE, END_DATE, 
                           JOB_ID, DEPARTMENT_ID) 
    VALUES(P_EMP_ID, P_START_DATE, P_END_DATE, P_JOB_ID, P_DEPARTMENT_ID); 
END; 

Open in new window


when when I insert a record in EMPLOYEES  tables nothing created in JOB_HISTORY table, what is going on ?
0
hi,

as I am tried to migrate from Oracle to MariaDB/MySQL and we are try to tools Ispirer, once the tools convert the following object for us how can we verify the converted objects can works without any problem:

1) sequence,

by running Select NEXTVAL(<sequence name>); ?

what should be the output?

2) Function.

3) trigger

4) package,

5) synonyms.

any idea?
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
Hi I need to use phpmyadmin to replace one doman name with another

for example   replace google.com with yahoo.com

There are no http;// involved -- just the doman name as in this format  google.com

I want to replace it in the entire database.  All tables.

How can I do that within phpmyadmin

Thanks!

Rowby
0
Hello!

I have loaded up a mysql database and the workbench app to create a local project, that I will eventually publish to a server.

However, I have a laptop and a pc, that I want the project to be on both, so I have mysql and workbench installed on both machines.

I use dropbox to put my web project on both machines, but I'm curious how to find the pathing for the database I created so that I can put it in dropbox and have the database load it from that location so both machines have access to the contents of the same file.

For instance, if I change it on one machine, it sends out to the dropbox and updates the copy on the other machine etc. Which should be fine while I'm just working locally.

Then when I'm ready to publish it to the web, I need to know where those files are so I can copy it out there.

Any suggestions how to proceed?

and thanks in advance for being nice about it! when I researched similar topics people treated the asker like an idiot, but you don't know until you know.

Thanks!
0
Hiya hope you guys can help me once again


yes i know php has changed ...   but how to i write a php page to accept details from this form in a mysql database -


thanks in advance

<?php 
	include('profile/validate.php');
?>
<!DOCTYPE html>
<html lang="en">
<head>
<title>cs97jjm3 Fantasy F1</title>

<!--[if (gt IE 9)|!(IE)]><!-->
<script src="js/jquery.mobile.customized.min.js"></script>
<!--<![endif]-->
<script>
 $(window).load(function(){
  $().UItoTop({ easingType: 'easeOutQuart' });
  $('.gallery .gall_item').touchTouch();
 });
 $(document).ready(function(){
     jQuery('#camera_wrap').camera({
      loader: false,
      pagination: true ,
      minHeight: '500',
      thumbnails: false,
      height: '22.42708333333333%',
      caption: true,
      navigation: false,
      fx: 'mosaic'
    });
    $('.gallery .gall-item').touchTouch();
  });
</script>
<!--[if lt IE 9]>
<script src="js/html5shiv.js"></script>
<link href="css/ie.css" media="screen" rel="stylesheet" />
<![endif]-->
<style>
 margin-top: 100px;
 margin-bottom: 100px;
 margin-right: 150px;
  margin-left: 80px;
  </style>

</head>
<body style="background-color: #FFFFFF; background-image: url('../images/main_texture.png'); margin-top: 80px;
 margin-bottom: 100px;
 margin-right: 150px;
  margin-left: 180px;">
<div class="texture">
<!--==============================
              header
=================================-->
<h2 class="ta__left">
<div class="ta__left">
	Register Have you read the 
	

Open in new window

0
Hello

Probably a typèical newbie question but an admittedly short google search did not yield an obvious answer.

I just installed mySQL 8.0.15 server on my Mac (from 8.0.13) and clearly did not do it correctly as I have just the basic schema available but not the one I am working with...

In usr/local I see two folders:

mysql-8.0.13-macos10.14-x86_64 <- with a subfolder containing my schema
mysql-8.0.15-macos10.14-x86_64 <- only with basic internal schema

What's my best best to "reattach" the schema to my new 8.0.15 instance ?
0
I have a mysql table that I want to copy all the rows over to another table (same database). The new table (empty) has all the same columns as the old, except for one mare added, which is a unique key. Can I do this is MySQL? I know how to do it in php, but to my knowledge I have to use mysqli functions & code an INSERT, naming every column in the table, there are about 30ish.

Just looking for an easier way.

Thanks
0
Windows 2012 r2 server, mysql 5.1

Using LOAD DATA INFILE  I get this error:

Incorrect integer value: '' for column 'BelowGradeFinishedSqFt' at row 1;

Basically its trying to load empty string into an integer field and it's throwing an error.  I figured it will skip this check if I turn off strict mode, and it does, but each time I try and load the data, I have to restart Mysql , else it will throw this same error. If I restart mysql prior to running, then it does not throw the error.  

To turn off strict mode I went to my.ini and changed this line to:
# Set the SQL mode to strict
sql-mode=""

What am I missing here??

Thanks!
0
hi,

as you all may know that we are trying migrate Oracle to MariaDB on few schema only, and we need to verify number of record of each tables of a particular schema on both Oracle and MariaDB in order to verify that migration complete.

Might I know any script to help finding out the totally number of records of each table of a particular schema on Oracle and of a user database, e.g. once schema  A of oracle migrated to MariaDB, all table of schema A will belongs to a user database called A in MariaDB.

please share script for Oracle and MariaDB. tks.

I believe that the output has to be in the same order.
0
Dear Experts,
I use Mysql and PHP

I select the first enterance of the data of the users at that date with this sql.

SELECT uid,tarih FROM takip WHERE tarih >= '2019-02-14' group by uid ORDER BY `takip`.`id` DESC

Open in new window


however I want to select the last enterance of the users at that day,
I have id column which is autoincremenent. I have date (datetime) column.

how can I do that? thank you
0
HI All,

Hope somebody can help. I need to grant remote root access to our MySQL database to a developer. I've done this in the past with out issue using the command line inputs below. However, this time it throws an error Error When Trying to Grant An IP Access to ROOT and I cannot figure out why or how to make this work. Any idea?

mysql> use mysql
mysql> GRANT ALL ON *.* to root@'IP Address' IDENTIFIED BY password';
mysql> FLUSH PRIVILEGES;
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.