MySQL Server

47K

Solutions

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 prepare statement (php 7) I need to understand how to make.

Using this example:
            if($getData = $mysqli->prepare("SELECT val1, val2, val3, val4 from db.table where field1 like ? and field2 like ? and field3 like ?"))
            {
                  $getData->bind_param('sss', $item1, $item2, $item3);                  
                  $getData->execute();                        
                  $getData->bind_result($val1, $val2, $val3, $val4);      
                  $getData->store_result();
                  $getData->fetch();      
               }

Except I won't know field1, field2, or field3....or if there just field1 and field3.  And I need to deal with the "like" scenario...just as I would this query...(this one works with one variable and one field)...

$param = "%{$_REQUEST['ans']}%";
if($getShipToInfo = $mysqli->prepare("select stid, vendor_id, name_of, address_1_of, address_2_of, city_of, state_of, zip_of, country_of from db_table where owner_of=? and " . $_REQUEST['fld'] . " like ? order by ?"))
{
      $getShipToInfo->bind_param('sss', $_SESSION['s_owner'],  $param, $_REQUEST['fld']);                  
      $getShipToInfo->execute();
      $getShipToInfo->bind_result($stid, $vendor_id, $name_of, $address_1_of, $address_2_of, $city_of, $state_of, $zip_of, $country_of);      
      $getShipToInfo->execute();
      $getShipToInfo->store_result();
}

So, to recap...I need to be able to pass my fields AND my values to build a query.  Whether I'm passing address_2_of and city_of or any number of items.  I started by looking into call_user_func_array...but I'm still not getting it.  

Can anyone …
0
Learn to develop an Android App
LVL 12
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

I need to have an automated routine which looks in a MySQL table for any missing records by date field. The table should have multiple records from every single day, but something is causing some days to not get any records inserted at all. I need to be notified of these missing dates so I can investigate. Id rather do this in VB so I could set up a Windows scheduled task to run it once every 24 hours, but if need be I could do it in php and put it on our web server under a cron job. I'd highly prefer the VB route though.
0
Hi Experts,

I am not able to view databases using MySQL command line shell. When I enter the command SHOW DATBASES I get an error NOT CONNECTED.
Is there some config change or another way to view the databases?
0
I am look at an old PHP project that uses mysql_connect, and I am trying to update it to PHP 7, by using mysqli_connect.

In the original project, I had a file - connect.php which had the database info, I would then include that file where needed.  But I cant seem to get this to work in PHP7

Original file looked like this.

 <?php
// Connects to my Database
mysql_connect("your.hostaddress.com", "username", "password") or die(mysql_error());
mysql_select_db("Database_Name") or die(mysql_error());
?>

I have changed to this

<?php

$con=mysqli_connect("your.hostaddress.com", "username", "password","Database_Name");

?>

now on  a page where I had

return mysql_real_escape_string($data);

and am now trying to use

return mysqli_real_escape_string($con, $data);

and I get the error con is not defined.

How can i keep the original structure but update to PHP7
0
Hi,

We are using WAMP to bulk send emails through our Exchange 2010 server using MySQL/PHP script (and mail.php) but every time I do a send, Exchange sends to some users then just stops without any errors.

I've tried adding a usleep(100000); to throttle it but I still have the issue and there's nothing in any logs on either Exchange or PHP to explain why.

It doesn't stop after a certain user/number of emails. Exchange does not have any send limits and I'm baffled. It is also very hard to test as I don't want to annoy our customers (at least the ones who receive the emails) with repeated testing.

The code I'm using is:

If anyone can suggest some extra error trapping I could add it would be most appreciated.


###############################################
// Function: Email Send
function email_alerter_send($SQLservername, $SQLusername, $SQLpassword, $SQLdatabase, $email_body) {
#################################################	
//echo "send email function" . $email_body;
require_once "Mail.php";
require_once "Mail/mime.php";
$host = "EXCHANGE IP ADDRESS";
$port = "25";
$email_username = "user@ourdomain.com";
$email_password = "THEPASSWORD";
$from = "COMPANYNAME WebServices <administrator@ourdomain.com>";
$subject = "COMPANYNAME WebServices Notice";
$conn = new mysqli($SQLservername, $SQLusername, $SQLpassword, $SQLdatabase);
// Check connection
if ($conn->connect_error) {die("Connection failed: " . $conn->connect_error);} 	
$sql = "SELECT * FROM user

Open in new window

0
Hi everyone,
I need to create an INVOICE out of a booking form already existing.
When the submit button is pressed, I need to send an email to the customer in a template that I'm calling 'INVOICE'.
Actually the email is already set but the data is sent just via plain text and not html and I think as a string.
Unfortunately the booking form has been 'custom' created and the fields for collecting data are just in a form of raw data. (and I cannot get single field with $_POST...)
If someone can take a look at the code I attached and give some hints/solutions it will be great!
Also, if you need more data I'll post more files.
Thanks!
pre_booking-test.php
expertTestcallback.php
0
Hi

I have setup a MariaDB server with a database called test.

I have setup the users on the system to connect over odbc.

Each user has been setup as follows:

CREATE USER 'username'@'%' IDENTIFIED BY 'password';

They have then had grants applied to the relevant tables they need access to:

grant select,insert,update,delete on databasename.tablename to 'username'@'%';

The problem i am having though is when trying to connect i am getting the error

Access denied for user 'username'@'pcname.domain.local'

Shouldnt the fact that i have used the % sign when creating the user cover the different PC names etc due to it being a wildcard?

thanks
0
Cannot make joomla tags work again for my custom joomla component.
I did all steps regarding conent type adding from here https://docs.joomla.org/J3.x:Using_Tags_in_an_Extension
My table tableMy content type
INSERT INTO `dbasenew`.`iis_content_types` (`type_id`, `type_title`, `type_alias`, `table`, `rules`, `field_mappings`, `router`, `content_history_options`) VALUES (NULL, 'Sermon', 'com_sermonials.sermon', '{"special":{"dbtable":"#__sermonials_sermons","key":"id","type":"Sermon","prefix":"SermonsTable","config":"array()"},"common":{"dbtable":"#__ucm_content","key":"ucm_id","type":"Corecontent","prefix":"JTable","config":"array()"}} ', '', '{"common":{"core_content_item_id":"id","core_title":"name","core_state":"state","core_alias":"null","core_created_time":"date","core_modified_time":"null","core_body":"about", "core_hits":"null","core_publish_up":"null","core_publish_down":"null","core_access":"null", "core_params":"null", "core_featured":"null", "core_metadata":"null", "core_language":"null", "core_images":"null", "core_urls":"null", "core_version":"null", "core_ordering":"ordering", "core_metakey":"null", "core_metadesc":"null", "core_catid":"null", "core_xreference":"null", "asset_id":"asset_id"}, "special": {"author":"author", "scripture":"scripture", "chapter":"chapter", "verse":"verse", "when":"when", "scripture2":"scripture2"}} ', '', '{"formFile":"administrator\\/components\\/com_sermonials\\/models\\/forms\\/field.xml", 

Open in new window

0
Hi All

I built the below script in MySQL Version 5.5

use airrider_tunn3l;
select distinct
	b.customer_name as 'Flyer Name'
    ,FROM_UNIXTIME(b.timeslot) as 'Flight Date and Time'
	,i.num as 'Invoice Number'
    ,b.voucher_title as 'Product'
    ,b.minutes as 'Flight Minutes'
    ,b.origin_nb_flights as 'Flight Quantity'
    ,b.customer_num as 'Guest Number'
    ,i.paid_ttc as 'Gross'
    ,i.total_ht as 'Net'
    ,i.total_tva as 'Tax'
    ,i.payment_method as 'Pay Method'
    ,FROM_UNIXTIME(b.checkin) as 'Check In'
FROM booking b
join invoices i on i.id = b.invoice_id
order by b.customer_name, b.timeslot, i.num, b.voucher_title, b.minutes, bb.origin_nb_flights, 
b.customer_num, i.paid_ttc, i.total_ht, i.total_tva, i.payment_method, b.checkin asc

Open in new window


The output is great, however despite using the distinct function I still end up with duplicate entries, for example (duplicate in bold)

Customer Name	Flight Date and Time	Invoice Number	Product	Flight Minutes	Flight Quantity	Gueest Number	Gross	Net	Tax	Pay Method	Check In
Test Test Test	1/21/18 01:30 PM	00000228	Kids Party Extra Flyer	1.9	2	18000182	1498	1413.21	84.79	 paymentexpress	                         1/21/18 01:44 PM
[b]Test Test Test	1/21/18 01:30 PM	00000228	Kids Party Extra Flyer	1.9	2	18000182	1498	1413.21	84.79	paymentexpress	                          1/21/18 01:44 PM[/b]

Open in new window


How do I tackle the duplicates? I know this can be done in Excel but since I'm saving the script in MySQL as a view. Any help is greatly appreciated!
0
Hello, i want to do timer on my localhost/phpmyadmin. I mean if somebody come to my page on his computer, this page will read value of this timer and show it and this timer will work always on server.
0
Free Tool: Path Explorer
LVL 12
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

I've been racking my brain for hours on creating a query to drill down into categories of a blog based on the topics they're assigned to in a blog post. For example, let's say I have the categories of Continents, North America, South America, Africa, Antarctica, Countries, United States, China, Canada, Mexico, States, New York,  California, Florida, Texas, Cities, New York City, Long Beach, Houston, Miami, Tampa, Orlando. Assuming every topic has the category "Continents", the page would then display all categories. If I clicked on Florida, I would only see the categories "Orlando, Tampa, Miami". I've written my query as such:

SELECT DISTINCT fcat.id, fcat.name FROM topic_category AS cat 
JOIN topic_category_bridge AS bridge ON cat.id = bridge.category_id 
JOIN topic AS topic ON bridge.topic_id = topic.id 
JOIN topic_category_bridge AS sbridge ON topic.id = sbridge.topic_id 
JOIN topic_category AS fcat ON sbridge.category_id = fcat.id 
WHERE cat.id = 2 AND cat.id = 19 AND cat.id = 46 
ORDER BY fcat.name ASC

Open in new window


What am I doing wrong? Thanks for the help in advance!
0
I have a server running Windows 2012 R2, with MySQL 5.7 installed.

I want a specific user to be able to connect to a specific database remotely. I have created that user with '%' in the "from host" setting.

During the installation of MySQL, I requested that port 3306 be opened.

When I try to connect (using MySQL Workbench) to the server, I get an error "Cannot connect to server on xxx.xxx.xxx.xxx (10060)"
0
How to install Apache Mysql On Ubuntu
Install LAMP Stack (Linux, Apache, MySQL, PHP) in Ubuntu 15.10

In this Linux stack, the operating system on which the infrastructure is deployed to create a Web application. MySQL is used to work with databases. Apache is a web server. PHP is used to handle dynamic content. In some other versions of this stack, Perl or Python is used instead of PHP... However, for this tutorial we will install PHP, as this is the most popular choice for this stack.
$ sudo apt-get install apache2

Open in new window

$ sudo nano /etc/apache2/apache2.conf

Open in new window

$ sudo apt-get install mysql-server mysql-client

Open in new window


$ sudo service mysql status

Open in new window


$ sudo apt-get install php5 php5-mysql libapache2-mod-php5

Open in new window


$ sudo nano /var/www/html/test.php

Open in new window



smile.gif
0
I got error when running this code. The code is to process the renewal application.
process.php
0
Hi,
      I have a piece of MySQL code which looks like:

SELECT
    u.id,
    u.user_registered,
    (select meta_value from bbct_usermeta where user_id = u.id and meta_key = '_mgm_cf_title' limit 1) as title,
    (select meta_value from bbct_usermeta where user_id = u.id and meta_key = 'first_name' limit 1) as FirstName,
    (select meta_value from bbct_usermeta where user_id = u.id and meta_key = 'last_name' limit 1) as LastName,
    (select meta_value from bbct_usermeta where user_id = u.id and meta_key = '_mgm_cf_email' limit 1) as Email,
    (select meta_value from bbct_usermeta where user_id = u.id and meta_key = '_mgm_cf_address_line_1' limit 1) as Address1,
    (select meta_value from bbct_usermeta where user_id = u.id and meta_key = '_mgm_cf_address_line_2' limit 1) as Address2,
    (select meta_value from bbct_usermeta where user_id = u.id and meta_key = '_mgm_cf_address_line_3' limit 1) as Address3,
    (select meta_value from bbct_usermeta where user_id = u.id and meta_key = '_mgm_cf_city' limit 1) as town,
    (select meta_value from bbct_usermeta where user_id = u.id and meta_key = '_mgm_cf_county' limit 1) as county,
    (select meta_value from bbct_usermeta where user_id = u.id and meta_key = '_mgm_cf_zip' limit 1) as postcode,
    (select meta_value from bbct_usermeta where user_id = u.id and meta_key = '_mgm_cf_phone' limit 1) as telephone,
    (select meta_value from bbct_usermeta where user_id = u.id and meta_key = '_mgm_cf_children_s_details' 

Open in new window

0
I am looking to create multiple records based on a single value in a field in MySQL.

I have some data that looks like this in a table in MySQL

item_id | grade
12345 | 01
12346 | 05
12347 | 09-12

I need to transform it into something that looks like this:
12345 | 01
12346 | 05
12347 | 09
12347 | 10
12347 | 11
12347 | 12

Where 09-12 is can be a range of values
09-10
11-12
06-08
etc.
0
This is my date format in MySQL database

2017-12-22T13:32:48Z

I want to pull all rows from two week prior to today (minus) 7 days.

For example

today is "02/09/2018" I want to get rows from Jan 18 to Feb 1....
0
I have several CentOS VPS units running cPanel. At regular intervals, the server load goes extremely high (248+) and the server is dead in the water. At this point all customer websites etc. are unavailable. I usually end up resetting the server which is not good for MySQL databases etc. and need to discover what's really going on so I can stop this from happening.

At the time the server is non-responsive, there are hundreds of lines on the console saying kill process ID or sacrifice child. However when this is happening there is not much chance to get into the console as it's too busy going 'round in circles.

I had someone from cPanel support take a look and they say it has nothing to do with cPanel. Here is what the tech wrote:

I was monitoring your server from last 30 minutes and the server load was stable but the site https://www.tgis.co.uk/ is taking time to load.
It indicates that there is an issue with the site scripting/database that is eating resources.
For reference I have checked server old logs and found that the same domain is eating resources

top - 07:40:15 up 13:12,  1 user,  load average: 53.75, 110.05, 170.98
Tasks: 267 total,  49 running, 216 sleeping,   0 stopped,   2 zombie
Cpu(s):  3.8%us,  3.9%sy,  0.0%ni, 30.2%id, 61.3%wa,  0.0%hi,  0.8%si,  0.0%st
Mem:   3925368k total,  3243796k used,   681572k free,    50444k buffers
Swap:  4128764k total,   353920k used,  3774844k free,   662552k cached

  PID USER      PR  NI  VIRT  RES  SHR S …
0
mysql database is on Godaddy.
I want to connect it from remote website.
I tried with following code, but the message is
This page isn’t working
fhiaportal.com is currently unable to handle this request.
HTTP ERROR 500
my connection file is as below.
$user = "xxmyusername";  // actual details are hidden
 $pass = "xxmypassword";
 $db = "xxdatabasename";
 $host = "50.62.209.149";
  $mysqli = mysqli_connect($host,$user, $pass, $db);
$conn = new mysqli($host, $user, $pass, $db);

Open in new window

0
[Webinar] Improve your customer journey
LVL 12
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

I am trying to import an ASCII fixed length data file into mysql.  I have PHPmyAdmin that I am using the console to try and load the file. (I have also tried using the "Import" tab, but that also is not reading the file correctly.  I create a Database, then in the database I create a table using:

CREATE TABLE CountyCodeFile (
CountyCodeFile_County_Code CHAR(2) not null PRIMARY KEY,
CountyCodeFile_County_Name CHAR(25)
);
LOAD DATA INFILE '/var/lib/mysql-files/countycd.lst' INTO TABLE CountyCodeFile FIELDS TERMINATED BY '' ENCLOSED BY '' ESCAPED BY '';

I get an error:

#1262 - Row 1 was truncated; it contained more data than there were input columns

The first two lines of the file to be inputed is:

01ALCONA                  
02ALGER                    

I am working on a Linux (Mint) computer but the files came from our State's Election Board in ASCII format.  I got it working 4-6 years ago but I had problems before it finally "worked" back then.

I have other similar tables and data that have the same problem.
0
I've created a stored procedure in MySQL that: 1.) Creates a temp table, 2.) Populates some of its values 3.) Then attempts to update one of the fields with a statement like the following:

"UPDATE tempTable tt SET tempTablefield = SELECT tableBField WHERE etc...."

The first part of the stored proc fills the temp table with data and then that is followed by an update statement to update one of the fields in the temp table. I've looked at the table to see what it looks like before the update and the field that is getting update is full of the default values (see stored proc below). However, after the update the affected field contains null values in the rows where there is no match and this brings me to my question: Why does my update replace the original default value with null when it updates? Why does the update not only affect the matching rows?

CREATE DEFINER=`root`@`localhost` PROCEDURE `usp_v1_ArchiveReport`()
BEGIN
DROP TABLE IF EXISTS utarchive;

CREATE TABLE utarchive (studentid VARCHAR(8), 
						studentname VARCHAR(50), 
                        weekTotal TIME DEFAULT '00:00:00', 
						reqHrs INT, 
                        sportTeamNm VARCHAR(25))
	ENGINE=INNODB;
    
    INSERT INTO utarchive (studentid, studentname, reqHrs, sportTeamNm)
		SELECT 
			uai.AthleteUid, 
            uai.AthleteFullName,
            uai.RequiredHours,            
            (SELECT utsportteam.SportTeamName FROM utsportteam WHERE utSportTeamUid = uai.Sportuid) AS 

Open in new window

0
We have a partner that has a MySQL / Maria DB Master server.  We have a slave and want to replicate against their master.  Some want to use the builtin SSL replication and others want to tunnel via SSH.  Can anyone provide some recommendations or pros and cons of each.  Any security, maintenance issues to consider?  Also, I assume that since we would be replicating against their master, they would have to run with the ssh port listening, not our slave?  Thanks
0
I'm getting a "newline in constant" error in my C# after adding the TIME_FORMAT function(from MySQL), into an SQL string that I'm building and I'm not sure why or how to fix it. I tried to escape the '%' character that precedes the 'T' for the 2nd parameter of the function, but it didn't solve the problem and gave me a different error as a matter of fact. Why does it think that by adding "TIME_FORMAT("00:00:00", "%T")  to my string, that I'm adding a new line in my constant? Please help.


my MySQL string in C#
sql = String.Format(@"SELECT 
	AthleteUid, AthleteFullName, SEC_TO_TIME((SUM(TIMESTAMPDIFF(MINUTE, TimeIn, TimeOut))) * 60) AS WeekTotal, RequiredHours, SportTeamName AS SportTeam 
FROM 
	utathleteinfo uai 
    	INNER JOIN 
    utinout uio on uai.AthleteUid = uio.StudentID 
    	INNER JOIN 
    utsportteam ust ON ust.utSportTeamUid = uai.SportUid 
WHERE 
	uio.TimeOut IS NOT NULL 
    AND uio.TimeIn >= '2018-01-29 00:00:00' 
    AND uio.TimeOut <= '2018-02-02 23:59:59' 
GROUP BY 
	uai.AthleteUid, AthleteFullName, WeekTotal, RequiredHours, SportTeamName, SemesterTime, Coach 
ORDER BY 
 	AthleteFullName)
UNION
(SELECT 
	AthleteUid, AthleteFullName, TIME_FORMAT("00:00:00", "%T") AS WeekTotal, RequiredHours, SportTeamName AS SportTeam 
FROM 
	utathleteinfo uai 
    	INNER JOIN 
    utinout uio on uai.AthleteUid = uio.StudentID 
    	INNER JOIN 
    utsportteam ust ON ust.utSportTeamUid = uai.SportUid 
WHERE 
	WeekTotal = '0:00'
GROUP BY 
	uai.AthleteUid, 

Open in new window

0
I'm new to PHP and I need to convert two lines of code from sqlsrv to pdo but I do not know the commands or syntax. The code is as follows:

$result = sqlsrv_query($conn,$checkTable);
$data = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC);

Open in new window


What is the PDO equivalent?
0
Somehow my developer had the bright idea to create tables to be used with a web application into the system/default 'mysql' DB?!  I misunderstood his question and he ended up creating tables in the 'mysql' db/schema.  Anyway...  At this point should I just export the entire mysql schema as a .sql file and then import into the new schema?  Then delete the tables I don't need?  This will resort in system tables coming with it that aren't needed.  Will the below work?

use mysql;
mysqldump -u root mysql > dump.sql
mysql -u root newdb < dump.sql

Then I'll use MySQL WorkBench to delete unneeded system/mysql tables from newdb?
0

MySQL Server

47K

Solutions

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.