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

 <?php
            
            $query = $conn->prepare("SELECT sup_id, sup_ticketnumber, sup_company, sup_contact, sup_email, sup_phone, sup_rep, sup_comments, sup_addcom1, sup_addcom2, sup_addcom3, sup_editcom1, sup_editcom2, sup_editcom3, sup_todo, sup_timefrom,  sup_timeto,  sup_date,  radnew, radinprogress, radclosed, radnocategory, chkemail, chkphone, chksms, chkproject FROM support ORDER BY sup_date asc");
            
            $query->execute();
            $results = $query->get_result();
            ?>
I need to echo out the sum of the sup_id column
Help please

Open in new window

0
Microsoft Azure 2017
LVL 12
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

I'm trying to select the data from the most recent date/year in a table. For example, I have the following data:

countyID   | datadate   | dataPercentage
1             | 2015          | 54
2             | 2015          | 60
3             | 2015          | 51
1             | 2016          | 54
2             | 2016          | 59
3             | 2016          | 53
1             | 2017          | 56
2             | 2017          | 64
3             | 2017          | 59
1             | 2018          | 56
2             | 2018          | 72
3             | 2018          | 57

Open in new window


I want to get the result:
1             | 2018          | 56
2             | 2018          | 72
3             | 2018          | 57

Open in new window


From what I've read, I got the impression the following query would work:
SELECT countyID, dataPercentage, MAX(dataDate) 
FROM myTable
GROUP BY countyID

Open in new window


But that returns the first set of percentages (from 2015) with the most recent date.
1             | 2018          | 54
2             | 2018          | 60
3             | 2018          | 51

Open in new window


What am I doing wrong and what should I be doing instead? Thanks!
0
I have a MySQL table called 'projects'. I want to copy it to another brand new table called selections.

I exported the table 'projects' , edited it with Notepad to change 'projects' everywhere to 'selections'.

Tried to load it, didn't work.

What is the correct was to do this.

Thanks
0
How do I create a MySQL dump command line that ignores views, stored procs, triggers, everything except tables and table data? All I want to transfer from one db on a server to another db on a different server is table /drop /create and table data.
0
Dear Experts, I use PHP and mysql.

I'd like to select distinct voters from my table. Some of my users have voted more than once.

if user voted more than once, I'd like the get the latest answer.

how can I select distinct uid and answer column from my table with the latest vote?

how can I select the later vote? I want to select the last insert.

(I have an autoincrement id column on my table. )

I wrote it like this? is this correct?

SELECT DISTINCT uid,answer,city FROM mytable order by id DESC
0
Hello Experts!

the following Mysql command:

 UPDATE TBL_TREATMENTS SET PAT_COD = 1 , tre_des = "<old pat:2* Mrg:1>" + TRIM(tre_des) WHERE PAT_COD = 2

is giving me the error message:

Error Code: 1292. Truncated incorrect INTEGER value: '<old pat:2* Mrg:1>'

but when executing from workbench it works!
Why is this and how can I solve it?
Regards!
0
Dear Experts,
I use PHP and mysql,
I want to select the details and the total from my siparis list with the one sql sentence however, there is an error.
Although there is a coloumn named _key it gives the below error.
what do you suggest I should do?

#1054 - Unknown column 'g._key' in 'on clause'

SELECT
    t._key
  , t.uid
  , t.bayiadi
  , t.adet
  , t.toplamtutar
FROM siparis t
INNER JOIN (
        SELECT
            _key
          , uid
          , bayiadi
          , SUM( toplamtutar ) AS Total
        FROM siparis g
        GROUP BY
            _key
          , uid
          , bayiadi
    ) sq ON t._key = g._key
        AND t.uid = g.uid
        AND t.bayiadi = g.bayiadi
WHERE MONTH( t.tarih ) = 12
AND YEAR( t.tarih ) = 2018
AND t.uid = 'xxxxxxxx'
AND t.gecerli = '1'
ORDER BY
   t. _key DESC
0
Dear Experts,
I use PHP and mysql.

I want to select data from my table with one sql sentence.

how can I select the data and the sum of the data with the same sql sentence?

I want to select sum(number) as total also with the below sql sentence? How can I do that?
when I added to the sql sentence it gives me only one row, althoug the result must be more than one row..

SELECT _key,user,name,number,FROM mytable where month(tarih)='12' and year(tarih)=2018 and user='xxxx' and valid='1' order by _key DESC

thank you
0
We have been importing Epicor parts and purchase order data (Clients ERP system) for a few years now by using an MS Access queries via ODBC connections to isolate the needed data from Epicor and then populate a temporary MS Access table that is only used to support the import query process that runs on a two minute timer .  Then we are using MS Access queries and ODBC connections to move any new records needed into a MySQL database.  This has been working fine until recently the client consolidated two other locations into the Epicor system, so now USA, Canada, and Germany are all now working off of the same Epicor database.  Since that happened my 50 character text field in the MS Access table looks like it has a part number as:

 ‎P1.00KCCT-ND‎

When I run the append query to add it to MySQL, it ends up as:

?P1.00KCCT-ND?

The part description filed in MS Access contains some spaces and some additional lines :
‎ERJ-6ENF1001V‎              

RES SMD 1K OHM 1% 1/8W 0805


And ends up in MySQL as:
‎?ERJ-6ENF1001V?              

RES SMD 1K OHM 1% 1/8W 0805

I've been trying all manner of strategies to either prevent the question marks from being added, or running an update query to replace them but the following update query simply deletes the entire record instead of just getting rid of the question marks.

UPDATE Tbl_BOM
SET tbl_bom.PartOurNumber = REPLACE (PartOurNumber, "?", "")
WHERE tbl_bom.PartOurNumber LIKE "%?%"

Open in new window


I'm hoping …
0
 <?php while ($row = $results->fetch_assoc()):$style = "";
  //if ($row['frm_id'] < "5"){
    //$style2 = "style='background:#FF0000;'";
  //} 
                  
  //if ($row['frm_id'] > "5"){
    //$style2 = "style='background:#FFF000;'";
  //} 
         //if ($row['frm_id'] =="5"){
    //$style2 = "style='background:#ddd000;'";
  //} 
       $data_new = $row['radnew'];
       $data_progress = $row['radinprogress'];
        $data_closed = $row['radclosed'];
       
    
if (isset ($row['radnew'])) {
    $style2 = "style='background:#ddd000;'";
}
      else if (isset ($row['radinprogress'])){
    $style3 = "style='background:#FFa500;'";
}
         else if (isset ($row['radclosed'])){
    $style4 = "style='background:rgb(255,100,0);'";
}
       
        
?>

                <table id="customers">
                 <tr>
                  <th>ID:</th>
                  <th>Company:</th>
                  <th>Contact:</th>
                  <th>Ticket Status:</th>
                     <th>Ticket Status:</th>
                     <th>Ticket Status:</th>
                 </tr>
                
                    <tr>
                        <td><?= $row['sup_id'] ?>&nbsp;<?php echo "<a href=\"searchWorkformSitesDateStatus.php?id=" . $row['sup_id'] ."\" target=\"_blank\">RecordLink... </a>"; ?></td>
                        <td><?= $row['sup_company'] ?></td>
                        <td><?= $row['sup_contact'] ?></td>
                        <td <?php echo $style2 ?>><?= 

Open in new window

0
PMI ACP® Project Management
LVL 12
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

Hello,
If there is no permission to database how to get the event log.

Thanks
0
I have the following mssql code that I need converted into mysql code because I do not have mssql 2008 and above.  I have been reading about the insert function in mysql but I have no idea now to convert the stuff code to that.  Any help would be greatly appreciated.  Thank you in advance.
select lname
       , cert
       , STUFF((select ', ' + certExpDate 
                  from hubspot_list b 
                 where b.grad_id = a.grad_id
                 for xml path('')), 1, 2, '') as certExpDate
       , certname
    from hubspot_list a
group by lname)

Open in new window

0
This is not really a problem - yet. It is merely me trying to understand how to secure my code from sql injection. I'm using classic asp as it's all I know.

As far as I understand using stored procedures within my mysql database and then  calling these procedures would be the way forward. I'd like to get this working.
I was directed towards this web-page in an earlier question: https://www.mikesdotnetting.com/article/5/saved-parameter-queries-with-ms-access-and-asp

However this concerns an MS access database, and I can't get it to work in my mysql database (for obvious reasons I guess).

Let's assume that I have table MyTable with the followong fields: Id (mediumint), GUID (varchar), Name (Text), DateInserted (Datetime), Comments (Text) (DateTime), Status (mediumint)

I need to read from, update, insert and delete in this table.
I would need to create stored procedures for this, right? I can't seem to find a way to do that using phpMydAdmin (which is what I got and will use, regardless). How would I create these procedures?

Remember that this has to work with classic asp.
Best regards
Michael
0
Dear Experts,

with below code I select the data from my table and show it on the google maps.

my link is like this: xml.php?birinci=2018-12-12&ikinci=2018-12-29&satisci=me
this link works perfectly fine

however this link is not bringing any data althoug there is data.
xml.php?birinci=2018-12-11&ikinci=2018-12-29&satisci=me

I don't understand why it is not working with diffrent date? what do you think I should do?

my code is below


$birinci= $_GET["birinci"];
$ikinci= $_GET["ikinci"];
$satisci= $_GET["satisci"];

$birinci = date("Y-m-d", strtotime($birinci));
$ikinci = date("Y-m-d", strtotime($ikinci));


// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE


// OPEN A CONNECTION TO THE DATA BASE SERVER


// DID THE CONNECT/SELECT WORK OR FAIL?

$mysqli->query("set names utf8mb4");

$sql = "SELECT * FROM mytable WHERE tarih between '$birinci' AND '$ikinci' AND uid='$satisci' and lat is not Null order by id asc ";

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

$xml = new XMLWriter();

$xml->openURI("php://output");
$xml->startDocument();
$xml->setIndent(true);

$xml->startElement('markers');

while ($row = mysqli_fetch_assoc($res)) {
  $xml->startElement("marker");

 $xml->writeAttribute('id', $row['id']);
  $xml->writeAttribute('name', $row['adi']);
 $xml->writeAttribute('address', $row['tarih']);
  $xml->writeAttribute('lat', $row['lat']);
  $xml->writeAttribute('lng', $row['lng']);
  $xml->writeAttribute('type', $row['bayikodu']);
  

Open in new window

0
I am bound in this project to using mysql.
My problem is I've never had to use "count" in a join, and there must be a better way to do this because it is taking an incredibly long time.
The query is
   $Q = "FROM AccountsMain INNER JOIN PricingMain2 ON PricingMain2.AppID = AccountsMain.AppID
            WHERE PricingMain2.Status != 'Limbo' AND PricingMain2.Status != 'Deleted' AND PricingMain2.ModifyDate > '$RecordDate'";

Open in new window

I then do this to get the total count for pagination
   $Result = mysql_query("SELECT count(*) ".$Q;
   if(!$Result){$Error[] = "Database Failure [1]";}
   else
   {
      $query_data = mysql_fetch_row($Result);
      $TotalCount = $query_data[0];
   }

Open in new window


Then this
   $GSReq = "AccountsMain.AcctName"; 
   $Limit = 'LIMIT '.($PageNo - 1) * $PageSize.','.$PageSize;
   $TotalPages = ceil($TotalCount/$PageSize);
   $Query = "SELECT * ".$Q." ORDER BY ".$GSReq." ".$Limit;

Open in new window


This process takes almost 60 seconds on a mere 10,000 records.
0
Does the MySQL/MariaDB RAND() function take an argument, and if it does, what does it do?

I've tried entering numbers and it still just gives me a number between 0 and 1.
0
Hi EE,

I have just installed xampp with the Apache and MySQL services installed.

When I am to reset the Mysql Server username/password but I go to phpmyadmin I am getting the following error message (see attached).

Attached are the config files for Apache and MySQL as well.

Any assistance is welcome.

Thank you.
Access-Denied.PNG
my.ini
httpd.conf.txt
config.inc.php.txt
0
I have the following query that is not working the way I would like.  I would like each provider printed out with 0 in the columns if they do not have a associated value.

Here is the query I have thus far:
SELECT IFNULL(SUM(a.trans_ttl),0) as `ttl`, service_category, provider 
FROM code_map b LEFT JOIN ( SELECT COUNT(*) AS `trans_ttl`, service_code, provider 
FROM transaction_data 
WHERE site_id = 6 
AND tran_date >= '2018-01-01 00:00:00'
AND tran_date <= '2018-12-31 23:59:59'
AND impacts = 'P' and type = 'S' and status = 'A' 
GROUP BY service_code, provider ) a ON b.service_code=a.service_code 
WHERE b.site_id = 6
GROUP BY b.service_category, provider
ORDER BY b.service_category, provider
LIMIT 0,100

Open in new window


Which returns:
+-----+--------------------+------------+
| ttl | service_category   | provider   |
+-----+--------------------+------------+
|   0 | AdProphy           | NULL       |
| 328 | AdProphy           | Provider 1 |
| 352 | AdProphy           | Provider 3 |
|  57 | AdSeal             | Provider 1 |
| 106 | AdSeal             | Provider 3 |
|   0 | AllExams           | NULL       |
| 465 | AllExams           | Provider 1 |
|  15 | AllExams           | Provider 2 |
| 496 | AllExams           | Provider 3 |
|   0 | Amalgam            | NULL       |
|   0 | Arestin            | NULL       |
|   0 | Bridge             | NULL       |
|  16 | Bridge             | Provider 2 |
|   0 | CHProphy           | NULL       |
|  63 | CHProphy           | 

Open in new window

0
Hello,

I am trying to import a SQL file dumped using MySQL administrator from v5.5.12, first using MysQL8 on Windows 10 then using 5.6.41 on AWS/RDS, the error using MySQL Workbench 8 is:

15:25:12 Restoring C:\path\sql\sqlfile.sql
Running: mysql.exe --defaults-file="c:\users\users\appdata\local\temp\tmpyzknop.cnf"  --protocol=tcp --host=awsendpoint.rds.amazonaws.com --user=myuser --port=3306 --default-character-set=utf8 --comments --database=mysql  < "C:\\Users\\users\\OneDrive\\Documents\\sqlfile.sql"
ERROR 1044 (42000) at line 21: Access denied for user 'myuser'@'%' to database 'mysql'

Operation failed with exitcode 1
15:25:17 Restoring C:\Users\abell\OneDrive\Documents\\sqlfile.sql
Running: mysql.exe --defaults-file="c:\users\user\appdata\local\temp\tmpvhirjz.cnf"  --protocol=tcp --host=awsendpoint.rds.amazonaws.com --user=myuser --port=3306 --default-character-set=utf8 --comments --database=mysql  < "C:\\Users\\user\\OneDrive\\Documents\\sqlfile.sql"
ERROR 1045 (28000): Access denied for user 'myuser'@'123.456.78.9' (using password: NO)

Operation failed with exitcode 1

I've established that the username and password with which I logged on to AWS.RDS via Workbench is the master user for this instance, BUT I also notice it doesn't have DBS and maintenance perms and I am not sure if these are required or how to set them from AWS dashboard ? or how to create a new user for the existing RDS instance MYSQL as permission is denied using the …
0
IT Pros Agree: AI and Machine Learning Key
LVL 1
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Hi,

We will undergo a migration from Oracle to MariaDB and as now I found out that there will be column type different in both DB, so comparing column type is not making sense at all, usually AFTER migration from Oracle 10gR2/12c to MariaDB 10.3.x, what is the best way to compare and make sure that the migration is ok?
0
Hi EE,

What's the reason behind the following error (see attached) I am getting mixed results when I google it, I am current the local admin on my PC and when I change port numbers in the .ini file (attached) xampp just tries to pick random ones.

Attached is the log file as well no clues I can see here.

Any assistance is welcome.


Thank you.
error.PNG
my.ini
mysql_error.log
0
Hello Experts,

I have a sample Oracle table where I want to migrate the table to MariaDB .  Any suggested way so that while moving the date I should not get any issues.

SQL_MODE is not the option that I am looking for.

CREATE TABLE my_oracle_tab
(
   var_col           VARCHAR2 (255 CHAR) DEFAULT '0',
   dt_col            DATE DEFAULT TO_DATE ('01-JAN-1900', 'DD-MON-YYYY'),
   clob_col          CLOB DEFAULT '0',
   var_max_col       VARCHAR2 (4000 BYTE),
   num_col           NUMBER,
   num_default_col   NUMBER (20) DEFAULT '0'
)
LOB (clob_col) STORE AS
   (TABLESPACE ora_data
    STORAGE (PCTINCREASE 0
             MINEXTENTS 1
             MAXEXTENTS UNLIMITED
             FREELISTS 1
             BUFFER_POOL DEFAULT)
    CHUNK 8192
    RETENTION
    ENABLE STORAGE IN ROW
    NOCACHE LOGGING);

Open in new window

0
ok this seems pretty basic...

Why does

SELECT COUNT (tblClients.cli_Name) FROM tblClients

Open in new window


returns error

* SQL Error (1630): FUNCTION myDB.COUNT does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual */
/* Affected rows: 0  Found rows: 0  Warnings: 0  Duration for 0 of 1 query: 0.000 sec. */

Open in new window


in mySQL 8.0

seems like a correct syntax to me ?
0
Hello

I have setup MySQL 4.6.x on AWS, and created a new DB schema to receive data from a SQL file created using MySQL DUMP.  Using MySQL Workbench 8.0 CE I can establish a connection, but when I import the data (it seems to start with password-yes then switch to password-no after first failure exit code 1) I get the following error:

ERROR 1044 (42000) at line 21: Access denied for user 'admin'@'%' to database 'mysql'

If it is relevant the SQL file was dumped with a different admin account/password on a different SQL box.

I've looked at a lot of articles and believe this is related to logins/perms but I am mostly good with data rather than sysadmin so any help getting started greatly appreciated...

The full error text is..

box1.ap-southeast-1.rds.amazonaws.com
14:26:34 Restoring C:\Program Files\MySQL\MySQL Server 8.0\bin\yfsql.sql
Running: mysql.exe --defaults-file="c:\users\user\appdata\local\temp\tmpgq7exl.cnf"  --protocol=tcp --host=box1.ap-southeast-1.rds.amazonaws.com --user=admin --port=3306 --default-character-set=utf8 --comments  < "C:\\Program Files\\MySQL\\MySQL Server 8.0\\bin\\sql.sql"
ERROR 1044 (42000) at line 21: Access denied for user 'admin'@'%' to database 'mysql'

Operation failed with exitcode 1
14:26:55 Restoring C:\Program Files\MySQL\MySQL Server 8.0\bin\yfsql.sql
Running: mysql.exe --defaults-file="c:\users\user\appdata\local\temp\tmpjzz_nr.cnf"  --protocol=tcp --host=box1.ap-southeast-1.rds.amazonaws.com --user=admin
0
<?php
    include('SupportTicketsConnect.php');
	$id=$_GET['id'];
	$result = $db->prepare("SELECT * FROM support WHERE sup_id= :userid");
	$result->bindParam(':userid', $id);
	$result->execute();
	for($i=0; $row = $result->fetch(); $i++){
    ?>

<?php  if (isset($row['sup_addcom1'])) {
<label>Additional Comments  :</label>
<textarea name="p_new_3" type="text" class="commenttext" ><?php echo $row['sup_addcom1']; ?></textarea><br/><br />
} 
    ?>

<?php
	}
?>

Open in new window


The above code gives a page error
Can someone please explain why
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.