MySQL Server

46K

Solutions

61

Articles & Videos

22K

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 have a table that has a longtext column. Here is an example of the type of data that is stored in it:

a:2:{s:4:"name";s:9:"Anonymous";s:13:"email address";s:26:"xxxxx@networkxxxxxxxxx.net";}

If I break it down we have 4 pieces of information. It appears the s:?? format is the number of characters for that piece of information. I would like to strip out the forth piece of info that is the email address. I know it looks confusing because the 3 piece also says email address but that is not where the data is going. In the end I want the following assigned to a variable:

xxxxx@networkxxxxxxxxx.net

Thanks in advance for your help.
0
NEW Veeam Agent for Microsoft Windows
LVL 1
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Using PHP/Mysqli (InnoDB):
How can one user who legitimately deletes a MySqli record from a table not create an issue with the other users who are also using the same record from the same table and who may try to update that record?
0
Hi,

I'm using an update query via a form to adjust stock due to discrepancies & breakages etc. I've just noticed that the update query fails when a single 0 is used in any part of the update, but not when this is done directly using terminal and mysql.

So for example if I use the following mysql in terminal to revised a dummy codes stock level from -7,684 back to 0 it works as anticipated.

INSERT INTO manualAdj (partID, partDescription, adjustmentCode, revisedQty, qtyPreRevision) VALUES (2710, 'DUMMYCODE_BM_FIRELIGHTERS','DUMMY CODE TIDY',0,-7684);

Open in new window


However, when I process the same data via the following:
if ((isset($_POST['submit']))
			&& (!empty($_POST['partID']))
            && (!empty($_POST['catID']))
            && (!empty($_POST['reason']))
			&& (!empty($_POST['revisedQty']))
			&& (!empty($_POST['originalQty']))
				

            ) 

            {
        
            // YES, THE DATA HAS BEEN POSTED, NOW ESCAPE IT FOR USE IN A QUERY
			$partID 			= $mysqli->real_escape_string($_POST['partID']);
            $catID 				= $mysqli->real_escape_string($_POST['catID']);
            $reason 			= $mysqli->real_escape_string($_POST['reason']);
			$revisedQty 		= $mysqli->real_escape_string($_POST['revisedQty']);
			$originalQty 		= $mysqli->real_escape_string($_POST['originalQty']);
				
        
            $sql = "INSERT INTO manualAdj (partID, partDescription, adjustmentCode, revisedQty, qtyPreRevision) VALUES ('$partID', '$catID', '$reason', '$revisedQty', '$originalQty')";
            if (!$res = $mysqli->query($sql)) trigger_error( $mysqli->error, E_USER_ERROR );

            }

Open in new window


The query fails to update anything at all but only fails when a single zero(0) is included, otherwise it works as anticipated - any idea why this would happen?

J
0
Hello
I am using a php script that handles the backup & restore of my databases. There are several problems with it but I shall post 1 error at a time. When I click restore backup button I am getting the error in the title. I am not experienced enough to troubleshoot this error and would appreciate some help from the experts. I have posted my code where the error occurs, but if you need to see anything else please let know. The error occurs at line 109.

I will add that this is not my script but it does all all I need it to do including showing th progress of the backup, which visually is better than just a white page.

I would if possible like to sort these errors out and have a working script. Many thanks.

PHP V5.3.13
MYSQL V5.5.24
APACHE V2.2.22

<?php

/* 
* function - restoredata
* This function restore a database from the backup.
* It does'nt write all data at once. Writing has a defined limit. 
* Writing step by step controlled by/from Javascript
* This prevents PHP from going beyond Maximum Execution Time.
* uploadedfile - backup file
* ustartindex - offset, keeps row index of a table 
* uti - table index of databse
* ulen - number of tables in database	 
* ucount - number of rows in table
* name - table name	
* limit -self explanatory
* restoremethod -if '0' restore only data , if '1' recreate databse,drop and recreate tables and restore data
* */
function restoredata($uploadedfile, &$ustartindex, &$uti, &$ulen, &$ucount, &$name, &$limit, 

Open in new window

0
See my below code.

$query_rs_media_new_group = "SELECT supplier_code, title, url, description, description_item, brand, type, weight, category, section, width, label_artwork, min(id) as id 
							 FROM media_new 
							 WHERE media_binder = '1'
							 GROUP BY title 
							 ORDER BY brand";
$rs_media_new_group = mysql_query($query_rs_media_new_group, $conn_cmyuk) or die(mysql_error());
$row_rs_media_new_group = mysql_fetch_assoc($rs_media_new_group);
$totalRows_rs_media_new_group = mysql_num_rows($rs_media_new_group);

Open in new window


The order by brand is not coming out in the correct order. Could this be something to do with the alias or group by?
0
The user provides my vb.net code with a SQL query that would look like this  for example:

select DISTINCT *from employees a INNER JOIN orders b on b.[Employee ID] = a.ID

I want to find a way to populate the datatable I create in code with the query columns and information but not actually get data. Is there a way to do this? The code also accepts queries from MySQL, MS Access, CSV as well as SQL.
0
Hi,

I need little help with the query.
i.e I've following  table with products data:
id, store_id, title, price, is_store_own_product

Followng is the scenerio:

Stores can have their own products as well as other products. With each product we have boolean field "is_store_own_product" which shows its store own product also there can be 1 store selected all the time:

Now, I need to show all products for selected store i.e where (store_id = current_store_id AND is_store_own_product=0 or is_store_own_product=1) but also want to show other store products that are not their own i.e where (store_id <> current_store_id AND is_store_own_product=0).

I tried with "NOT IN" but its taking lot of time.

I hope this make sense.

Thanks a lot.
0
Hi experts.

I am coding a login system, However when I submit a registration form it just won't save the data into the database. im sure all my code is valid and can't seem to find the reason why??

no error messages are displayed and I have tested connection to the database and it connects fine.

I have set some code to echo "USER REGISTERED" if the data was submitted and stored in the database successfully but I can't get that message to show so there must be something im missing.

Please see code below, If you have any suggestions or spot any errors please let me know. Thanks.

login.php

<?php

// includes original html for login page
include('includes/header.php');
include('includes/login_form.php');
include('includes/footer.php');

/*
$sql = "select * from users"; // Pull user info from Database.
$result = query($sql);
confirm($result);
$row = fetch_array($result);

echo $row['username']; // Get username from database.
*/

?>

Open in new window


register.php

<?php

// includes original html for Create Account page
include('includes/header.php');
include('includes/create_account_form.php');
include('includes/footer.php');

?>

Open in new window


header.php

<?php include("functions/init.php"); // includes all code for login  
?>

<!DOCTYPE html>
<!------------------------------------------------------------------------------------------
WARNING!
Copyright (C) Mike Z Moore - All Rights Reserved
Unauthorized copying of this file, via any medium is 

Open in new window

0
DELIMITER $
CREATE FUNCTION MAXSCOREID(START_STUDENT_ID INT, END_STUDENT_ID INT)
RETURNS INT
BEGIN
DECLARE STUDENT_ID INT;
BEGIN
      SELECT MAX(SCORE) INTO STUDENT_ID
      FROM STUDENT ST, SCORE S
      WHERE ST.STUDENT_ID = S.STUDENT_ID
      AND ST.STUDENT_ID BETWEEN 1 AND 10;
END;
RETURN student_id;
END$
DELIMITER ;

Hi. With the function above, I get the higher score but I want to get the student`s id who has the max score.  Any help, please?
0
Can you please tell me where the error is?
screenshot.PNG
INSERT INTO `coffee` (`id`, `name`, `type`, `price`, `roast`,

`country`, `image`, `review`) VALUES
(1, 'Cafe au Lait', 'Classic', 2.25, 'Medium', 'France',

'Images/Coffee/Cafe-Au-Lait.jpg', 'A coffee beverage consisting strong

or bold coffee (sometimes espresso) mixed with scalded milk in

approximately a 1:1 ratio.'')'
0
Webinar: Choosing a MySQL HA Solution
LVL 2
Webinar: Choosing a MySQL HA Solution

Join Percona’s Principal Technical Services Engineer, Marcos Albe as he presents Choosing a MySQL High Availability Solution on Thursday, June 29, 2017 at 10:00 am PDT / 2:00 pm EDT (UTC-7).

I have mysql table named "sheet" and column name is "cep" which is varchar (50)

I want to add "0" as a prefix to the "cep" which reserves mobile numbers for me,

what is the correct mysql statement for that?

what I want to do is to concatenate "0" + "cep column" with a mysql

how can I do that?
0
Hi -
I have a table with time card type data -
I need help creating a trigger for two specific fields in the table (start_time, end_time) that will not allow the fields to be updated once they have values in them -
The process is that a 'record' is inserted into the table for a specific task with empty start and stop datetime fields -
The start and stop fields are updated using a form when the task is started and finished -
I do not want the user to be able to use the form again to 'change' the start and finished times -

This is my first attempt at creating a trigger and I have spent way too many hours trying to figure out how to make this work :-)

Any and all help gratefully appreciated -
Richard
0
Hi Experts

Could you point the correct way to format a MySQL query to obtain data between 02 dates accordingly to:
img002
What I tryed with no success:

SELECT DATE_FORMAT(_updated,'%Y-%m-%d %h-%p') as _datetime, _number, _callback, _causeid FROM meeta_callback.incomingcall_history where _updated between '2017/06/01 12:00 00:00:00' and '2017/06/14 12:00 23:59:59' order by DATE_FORMAT(_updated,'%Y-%m-%d %h-%p'),_callback

Open in new window


Thanks in advance!
0
Hi Experts

Could you point a way to generate an Excel chart and PDF File by using  Fusioncharts ?

After the grid is formed, accordingly to:
img001
I need to generate an Excel chart with its content.

Thanks in advance.
0
Hi,

I'm trying to use the following php to post multiple rows from a form to a table via an insert query ...

if ((isset($_POST['submit1']))
			&& (!empty($_POST['supplier']))
            && (!empty($_POST['sku']))
            && (!empty($_POST['unitsRequired']))
			&& (!empty($_POST['unitsReceived']))
			&& (!empty($_POST['receipt']))
				

            ) 

            {
        
            // YES, THE DATA HAS BEEN POSTED, NOW ESCAPE IT FOR USE IN A QUERY
	    $supplier 			= $mysqli->real_escape_string($_POST['supplier']);
            $SKU 				= $mysqli->real_escape_string($_POST['sku']);
            $unitsRequired 	= $mysqli->real_escape_string($_POST['unitsRequired']);
	   $unitsReceived 		= $mysqli->real_escape_string($_POST['unitsReceived']);
	   $receiptedBy 		= $mysqli->real_escape_string($_POST['receipt']);
				
        
            $sql = "INSERT INTO goodsIn (supplier, SKU, unitsRequired, qtyReceived, receiptedBy) VALUES ('$supplier', '$SKU', '$unitsRequired', '$unitsReceived', '$receiptedBy')";
            if (!$res = $mysqli->query($sql)) trigger_error( $mysqli->error, E_USER_ERROR );

            }

Open in new window


The php only runs when the results in the form (this varies depending on the search criteria which produces the form) contain 1 row of data when 2 or more are present the process fails.

Where do I introduce a while loop in the code to accomodate this?

Hope this makes sense?

J
0
Follow on question to  recent support from LajuanTaylor.

I am using the "mysql community installer" to install mysql and mysql workbench on a windows 7 pc. All components appear to install correctly. The workbench launches but does not succeed in connecting with the mysql server.   See embedded file below

status of connection between workbench and mysqlserver
I have tried to start the server from the workbench, but this has failed. See two images below

atttempt to connect part 1attempt to connect part 2
The following is a status from the community installer showing what has been installed. Seems to be ok ?

community installer status - looks ok ?
Appreciate someone taking a look and hopefully identifying the stupid mistake I am making

PS - I have several other installations on windows 10 pcs which work perfectly. Hopefully its not a windows 7 problem !!
0
I want a tool that will run through our website once daily and send a report of any broken links it finds. I can do it either in VB and use Access 2003 here at the office or I can install it as a php app on our  web server.

Any ideas?
0
Hi Experts;
I am having a problem using a Case Statement for my Group by in the following MySQL query.
Please see the CASE line below. I'm getting
Error Code: 1064. 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 ' spt.pt, bs.bscount  ELSE 0 END

If I group by only one column the case statement works as expected.
Adding additional columns gives the error. Is it because of the comma?
I've tried enclosing in ticks and quotes - When I do so, there is no error & the query runs, but the group by doesn't work.

SET @BySide = 1, @ByPT = 1, @ByCt = 1;

       SELECT  
            bs.BSCt,
        spt.PT,
        pae.side,
       
            sum(e.AB) as AB,
            sum(e.H) as H

      FROM    
            GDR.header gh
            INNER JOIN PE pae ON pae.Gid = gh.Gid
            INNER JOIN GDR.bscount bs ON bs.bscount = pae.bscount
            LEFT JOIN GDR.EVT e ON pae.PE = e.iCode
            LEFT JOIN GDR.subt spt ON spt.PT = pae.pt
            
      WHERE
            gh.gdt BETWEEN '2017-04-01' AND '2017-06-03'

        Group BY
        CASE WHEN @BySide = 1 AND @ByPT = 1 AND @ByCt = 1 THEN pae.side, spt.pt, bs.BSCt  ELSE 0 END
        #group by pae.pitcherside, spt.pt, bs.bscount <= this works
;
0
Hi,

I have a mysql select query that returns a table in html showing stock due in.

Fields are as follows: -

PONumber
DateofPONumber
Supplier
SKU
CartonsSize
CartonsRequired
UnitsRequired

I've also added into the code two text input fields to enter manually the actual units rec'd and actual units outstanding (if any) for each row.

UnitsRecieved
UnitsOutstanding

How do I post the data into another table capturing the fields from the select query and the new data from the 2 input text fields?

J
0
[Webinar] Learn How Hackers Steal Your Credentials
LVL 9
[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

I have a table with the following fields: Username | Occupation | Skills

There are multiple rows from the same user

SELECT * FROM TABLE ORDER BY username gives the following results

User1 | Teacher | Photoshop, Wordpress
User1 | Cashier | CMS
User1 | Painter | Carpentry
User2 | Cashier | Drupal, Window Washing
User2 | Captain | Flying
User2 | Driver | Loading
User3 | Technician | Software, Windows
User3 | Technician II | Hardware

I don't know if it's possible but I would like to find a query that can merge/combine rows with the same column name so that the result would be like below.

WANTED RESULT

User1 | Teacher, Cashier, Painter | Photoshop, Wordpress, CMS, Carpentry
User2 | Cashier, Captain, Driver | Drupal, Window Washing, Flying, Loading
User3 | Technician, Technician II | Software, Windows, Hardware
0
My iMac hosts two Virtual Machines. One runs Windows10 (my development machine) and the other runs WindowsServer2016 (my database test-server).  My question is how do I connect to the MySQL database residing on my WinServer2016 VM, from HeidiSQL (or SQLyor) on my Win10 VM?

When I try to connect to the WinServer2016 VM from my Win10 VM I get a “Cannot connect to MySQL Server 10060” error.

On my Win10 VM I have tried to simply use the IP address of my WinServer2016 VM. I specify the IP address (192.168.2.151) as the Hostname/IP, and port 3306 when I try to connect. However, the connection fails.

I know that MySQL is running on the WinServer2016 VM because I can connect to it using Heidi locally (on the WinServer2016 VM) using the IP address 127.0.0.1. Also, the VM's recognize each other as I can freely share files between them.

I read that MySQL User privileges are a factor. Accordingly, I gave myself universal login privileges as follows:

GRANT ALL ON *.* to nelson@localhost IDENTIFIED BY 'abc123';
GRANT ALL ON *.* to nelson@'%' IDENTIFIED BY 'abc123';
flush privileges;

Part of the problem might be that this is a database conversion project where the MySQL version I am upgrading from is very old. I am upgrading from version 3.26. That is what is installed on my WinServer2016. I must maintain the environment until the conversion is completed.  As previously mentioned, my host is an iMac (OSX 10.11) running Parallels Desktop 12 Business Edition.  The reason…
0
Hi,

Just looking for a sample PHP export script for exporting data from a mysql database into a txt file.

Thanks J
0
Hi,

I got an url type connect string for MySQL database, what tool to use to access the MySQL database on a Windows machine?

Thanks,

Luchuan
0
Can somebody help me figure out why my query is not showing any matches?  the only thing I can see different is the CASE of the data.  

SELECT
`group`.`Group ID`,
`group`.`Additional Address Line 1` AS ga,
smart.subs_acct_num,
smart.Address AS sa,
smart.mac_address
FROM
`group` ,
smart
WHERE
`group`.`Additional Address Line 1` = smart.Address
0
Hello,

I have MySql 5.7.
I need to rewrite some SQL code to improve performance at the user side.

I thought of using SQL_CACHE or  SQL_NO_CACHE in each query depending on the user need.

There are many queries which select only one particular product e.g.:
"Select * from products where product_id=12".

Do I think it correctly that it should use SQL_NO_CACHE?
There is a small chance that the user will use that product again.

On the contrary when the user selects from a list:
"Select  product_name from products order by product_name ". I should use SQL_CACHE , right?

Thank you very much.
0

MySQL Server

46K

Solutions

61

Articles & Videos

22K

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.