[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

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

MySQL table column names:

column1, column2, column3
1                1               1
2                2               3


a CSV file has these columns names and new values
I just want to update column2 for the time being with new values
I would also like to be able to either update existing values or add (append) new ones to this column2
I use phpmyadmin
0
Acronis True Image 2019 just released!
LVL 1
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

hi,

I am considering how to HA to maxscale for MariaDB as maxscale is the SPOF, and I am suggested to use Pacemaker, Keepalived and/OR Heartbeat, how can those component fit into the maxscale and MariaDB cluster DB environment ?

any topology diagram for it?

which one is better in terms of stability ?
0
Hi,I'm having trouble trying to change the bootstrap .active class state via jquery.I've googled and found several solutions but none of them are working for me.Here is my html code:

[code]<section id="hero">
   			<div class="container-fluid">
               <div class="container">
				   <div class="row">
						<div class="col-md-12">
							<nav class="navbar navbar-toggleable-md navbar-light">
							  <button class="navbar-toggler navbar-toggler-right" type="button" data-toggle="collapse" data-target="#navbarNavDropdown2" aria-controls="navbarNavDropdown2" aria-expanded="false" aria-label="Toggle navigation">
								  <span class="navbar-toggler-icon"></span>
								  <span class="fas fa-times hide" data-wow-delay="2s"></span>
							  </button>
							  <a class="navbar-brand logo" href="index.php"></a>
							  <div class="collapse navbar-collapse" id="navbarNavDropdown2">
								<ul class="navbar-nav ml-auto">
								  <li class="nav-item active">
									<a class="nav-link" href="index.php">Home <span class="sr-only">(current)</span></a>
								  </li>
								  <li class="nav-item">
									<a class="nav-link" href="about.php">About Us</a>
								  </li>
								  <li class="nav-item">
									<a class="nav-link" href="howitworks.php">How it Works</a>
								  </li>
								  <?php
								  if( $_SESSION['loggedInUser'] ) { // if user is logged in
								  ?>
							      <li class="nav-item dropdown">
									<a class="nav-link" 

Open in new window

1
I am not sure if this join is going to be possible but I am asking here in the hope that it will be.

I have an order_detail table:

order_id
product_id
product_qty
product_price

So, some data might look like

1      25      2      299
2      26      1      199

And a coupon table (I have left out the other fields that aren't necessary for this question)

voucher_id
product_id
rep
purchased
order_id

And some data:

#12675      25      14      2018-11-05      1
#12676      25      14      2018-11-05      1
#12677      26      12      2018-11-07      2

I want to show a report of each voucher/coupon sale without duplicating data. My current query ends up showing the same coupon number twice.


 
      $this->db->query("SELECT `serial`, `rep`, `purchased` FROM `vouchers` AS `v`
        INNER JOIN `order_detail` AS `od` ON od.`order_id` = v.`order_id`
        WHERE cast(`purchased` as date) BETWEEN :first_day AND :last_day
        ");
        
        $this->db->bind(":first_day", $first_day);
        $this->db->bind(":last_day", $last_day);
        $results = $this->db->arraySet();
        return $results;

Open in new window

1
Hi,
I'd like to print each one of these tickets on a envelope (#10), so I would like to output a single pdf document with 4 pages, one ticket per page. I don't know if it's possible to print my HTML content on an envelope sized 9.5" x 4.125". If you look at the screenshot attached, you'll see I have all 4 tickets on a single page. I want to create a single PDF document with multiple pages, again one ticket per page.
Here is my code I have so far:
// winning tickets pdf
function fetch_data() {  
      $output = '';
      $userID = ($_SESSION["loggedInUserID"]);
      //$conn = mysqli_connect("localhost", "lormar5_wo693", "sDieYQb752KZc!;", "lormar5_wo693");  connection to theme
      $conn = mysqli_connect("localhost", "root", "root", "db_hockeypoolfundraiser");
	  $sql = "SELECT * FROM participants LIMIT 1";  
      $result = mysqli_query($conn, $sql);  
      while($row = mysqli_fetch_array($result))  
      {       
      $output .= '<tr>
	  			<td style="border-bottom: 1px solid #dfdfe0;">
				<br><br><strong>Winners Name:</strong> '.$_SESSION["participantName"].'
				<br><br><strong>First Goal Time:</strong> '.$_SESSION["firstGoalTime"].'
				<br><br><strong>Game Date:</strong> '.$_SESSION["gameDate"].' <br><br>
				</td>
				
				<td style="border-bottom: 1px solid #dfdfe0;">
				<br><br><strong>Phone:</strong> '.$_SESSION["participantPhone"].'
				<br><br><strong>Amount:</strong> $'.$_SESSION["priceFirstGoal"].'
				<br><br><strong>Player:</strong> 

Open in new window

0
I upgraded Xwiki from enterprise 9.1 version to standard 9.11 version. The database is on another MySQL server.    
But I have two problems now,
1. Click the user profile, it will shows " Failed to execute the [velocity] macro. Cause: [DefaultNotificationFilterPreference is not mapped [select nfp from DefaultNotificationFilterPreference nfp where nfp.owner = :owner order by nfp.id]]. Click on this message for details." Caused by: org.hibernate.hql.ast.QuerySyntaxException: DefaultNotificationFilterPreference is not mapped [select nfp from DefaultNotificationFilterPreference nfp where nfp.owner = :owner order by nfp.id]
profile2. The administrative page shows the buttons' hyperlink.
admin-page.png
Could you please help check and answer these questions?
Thanks!
0
I have a form/page that I'm using to book in stock based on a poNumber that generates the stock items linked to that poNumber/supplier. Currently for each line (there may be 10 items for example on order) the user needs to add qty rec'd, qty outstanding, receipted by and delivery note number in the form for each line.

I want to short cut some of the data entry by having a single entry for receipted by and delivery note number but for this to populate the rows for each entry once submitted.

The code I have at the moment is as follows: -

<div class="container">	
	<form class="form" method="post" action="transit_temp.php">	
	
	<?php
	
	$poNumber = isset($_POST['poNumber']) ? $_POST['poNumber'] : false;
	
	$poNumber = $mysqli->real_escape_string($poNumber);
	
	$index=0;

	$query = 'SELECT * FROM goodsInTrans WHERE poNumber = "'.$poNumber.'"';
			
				if (!$result = $mysqli->query($query)) {
				$err
				= "QUERY FAIL: "
				. $query
				. ' ERRNO: '
				. $mysqli->errno
				. ' ERROR: '
				. $mysqli->error
				;
				trigger_error($err, E_USER_ERROR);
				}		
	
				while ($row = $result->fetch_object())
				
				{ 

				?>
					<div class="form-group row">
						<div class=""><input type="hidden" 				class="form-control" 	name="data[<?php echo $index; ?>][supplier]" 			value="<?php echo $row->supplier ?>"/></div>
						<div class="col-xs-3"><input type="text" 		class="form-control" 	name="data[<?php echo $index; ?>][sku]"	 				value="<?php echo $row->sku 

Open in new window

0
Hello Experts


Need help with code 1064.

mysql> CREATE TABLE BankOfItaly(name char(20) NOT NULL, email varchar(20) NOT NULL, MonetaryValue float(7,2) NOT NULL, Phone int(10) NOT NULL PRIMARY KEY(name));

Open in new window


I have checked over the syntax yet, when creating a new database with an associated table this error occurs. How do I create tables successfully so this error does not occur anymore? The error is issue with assigning the Primary Key to (name))
0
I need to search for an order in my WooCommerce back office by email address.  I can see the email address in wp_users but don't know how to put a name to the email address.  Is there a plugin or mysql code that will accomplish that?

Thanks,
0
I have the following query that is working, however it does not return a b.service_category value when the SUM is 0:
SELECT a.`site_id`, b.service_category 
, SUM( IF(tran_date BETWEEN '2018-01-01' AND '2018-01-31', 1, 0)) AS `interval1`
, SUM( IF(tran_date BETWEEN '2018-02-01' AND '2018-02-28', 1, 0)) AS `interval2`
, SUM( IF(tran_date BETWEEN '2018-03-01' AND '2018-03-31', 1, 0)) AS `interval3`
, SUM( IF(tran_date BETWEEN '2018-04-01' AND '2018-04-30', 1, 0)) AS `interval4`
, SUM( IF(tran_date BETWEEN '2018-05-01' AND '2018-05-31', 1, 0)) AS `interval5`
, SUM( IF(tran_date BETWEEN '2018-06-01' AND '2018-06-30', 1, 0)) AS `interval6`
, SUM( IF(tran_date BETWEEN '2018-07-01' AND '2018-07-31', 1, 0)) AS `interval7`
, SUM( IF(tran_date BETWEEN '2018-08-01' AND '2018-08-31', 1, 0)) AS `interval8`
, SUM( IF(tran_date BETWEEN '2018-09-01' AND '2018-09-30', 1, 0)) AS `interval9`
, SUM( IF(tran_date BETWEEN '2018-10-01' AND '2018-10-31', 1, 0)) AS `interval10`
, SUM( IF(tran_date BETWEEN '2018-11-01' AND '2018-11-30', 1, 0)) AS `interval11`
, SUM( IF(tran_date BETWEEN '2018-12-01' AND '2018-12-31', 1, 0)) AS `interval12`
FROM transaction_data a, code_map b 
WHERE a.site_id = 6 
AND tran_date BETWEEN '2018-01-01' AND '2018-12-31' 
AND impacts = 'P' AND type = 'S' AND status = 'A' 
AND a.service_code = b.service_code 
AND a.site_id = b.site_id 
GROUP BY a.`site_id`, b.service_category 
ORDER BY a.`site_id`, b.service_category ;

Open in new window


I have been attempting to modify the query so it runs well and prints 0 for values.  The query below will return all of the service_category values, however it is not correctly computing the interval values:

Open in new window

0
Acronis Data Cloud 7.8 Enhances Cyber Protection
LVL 1
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

  <?php if ( ! isset($_GET["company"]) ): ?>

            <p>No Company Info has been passed in!</p>

        <?php else: ?>

            <?php
            // Let's run the query
            $query = $conn->prepare("SELECT frm_id, frm_companyname, frm_contact, frm_email, frm_rep, frm_date, frm_return, frm_timefrom, frm_timeto, frm_comment1, frm_comment2, frm_comment3, frm_comment4, frm_comment5, frm_todo1, frm_todo2,  frm_todo3,  frm_todo4,  frm_todo5, frm_updated  FROM siteform WHERE frm_companyname = ?");
            $query->bind_param("s", $_GET['company']);
            $query->execute();
            $results = $query->get_result();
            ?>
     
            <?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_db = $row['frm_date'];
       $current_date = date('Y-m-d', strtotime(" Today"));
        $current_date2 = date('Y-m-d', strtotime(" -1 month"));
        $current_date3 = date('Y-m-d', strtotime(" -2 month"));
        $current_date4 = date('Y-m-d', strtotime(" +1 day"));
        $current_date5 = date('Y-m-d', strtotime(" +1 month"));
         $current_date6 = date('Y-m-d', strtotime(" +2 month"));
    
if ($data_db == $current_date){
    $style = 

Open in new window

0
Hello,
I'm working on a code that can record vocal messages and store them into a database to retrieve them later on, but my PHP script isnt working and I don't have enough expertise in web dev to correct it, can someone help me please ?
index.html
upload.php
app.js
0
How can I turn a single row of 15 columns into 15 rows with 2 columns for ORDER BY?

I have an online form and the SQL to score the form data we have puts everything in a single row. What we need is 15 rows so we can use ORDER BY. I have googled PIVOT mySQL, but that is the opposite of what I want.

What I am doing is summing multiple rows per column from a ninja form:

SELECT  SUM(ACCEPTANCE) as ACCEPTANCE, 
        SUM(ASSOCIATION) as ASSOCIATION, 
        SUM(CURIOSITY) as CURIOSITY,
        SUM(EGO) as EGO, 
        SUM(EMPATHY) as EMPATHY, 
        SUM(ENVY) as ENVY, 
        SUM(EXCITEMENT) as EXCITEMENT, 
        SUM(FAMILY) as FAMILY, 
        SUM(FEAR) as FEAR, 
        SUM(GREED) as GREED, 
        SUM(HUMOR) as HUMOR, 
        SUM(HUNGER) as HUNGER, 
        SUM(LAZY) as LAZY, 
        SUM(SEX) as SEX, 
        SUM(TRUST) as TRUST

FROM v_nf_questions q
JOIN v_nf_answers a on q.q_id=a.a_id
JOIN answers_table ans ON a.f_answer=ans.ARCH_NAME
WHERE nform_id = 348

Open in new window


So here is what I get
what_i_have.jpeg
But here is what I want
what_i_want.jpegwhat_i_have.jpeg
what_i_want.jpeg
0
I am creating an after insert trigger that is supposed to update an existing row in the same table after a new insert.
Scenario:  This table records how long agents were on break.  They can go from break "Outbound" directly to break "Out to Lunch" without the "Outbound" break ever getting a `PauseEnd` timestamp.  This trigger is intended to put the NEW 'PauseStart'  into the existing 'PauseEnd' column of the agent's previous break.  I get no errors, but it doesn't do anything and I can't figure out why.

Here's the table and the trigger:

CREATE TABLE `agent_break_rec` (
  `id` int(10) NOT NULL,
  `AgentID` int(11) DEFAULT NULL,
  `AgentName` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `PauseCode` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `PauseStart` datetime DEFAULT NULL,
  `PauseEnd` datetime DEFAULT NULL,
  `PauseSec` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ixAgetCodeDT` (`AgentID`,`PauseCode`,`PauseStart`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=7144 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE DEFINER=`sysop`@`%` TRIGGER `tgEndBreakAfterInsert` AFTER INSERT ON `agent_break_rec` 
FOR EACH ROW update agent_break_rec set 
     PauseEnd=NEW.PauseStart 
where id=(select id from agent_break_rec where PauseEnd is null and AgentID=NEW.AgentID order by PauseStart desc limit 1);

Open in new window

0
Hello Experts,

I am having issue with .MYSQL file not updating in sync with a parent table class. The code is as following:

Create table Login
(Username varchar(15)NOT NULL,
Firstname char(10) NOT NULL,
Lastname char(10) NOT NULL,
Password varchar(15) NOT NULL,
email varchar(25) NOT NULL,
PRIMARY KEY(Username));

INSERT into Login VALUES ('jason44','Jason','Labor','12345','jason@gmail.com');
INSERT into Login VALUES ('carol39','carol','spice','qwerty','carol@gmail.com');
INSERT into Login VALUES ('jennifer89','jennifer','Cicco','asdfg','jennifer@gmail.com');
INSERT into Login VALUES ('michael27','michael','lutherl','0987654','luther@gmail.com');

Open in new window


This is the synced table below:

create table Historical
(OrderNumber int(100) auto_increment,
Username varchar(15)NOT NULL,
CustomerType char(6),
NumberTickets int(3),
TicketCost float(5,2),
CreditCard int(16),
PRIMARY KEY(OrderNumber),
FOREIGN KEY(Username)REFERENCES Login(Username)
);
INSERT into Historical VALUES (NULL,'jason','Adult',2,7.00,9876543);
INSERT into Historical VALUES (NULL,'carol87','Adult',1,7.00,9876543);
INSERT into Historical VALUES (NULL,'jennifer1234','Adult',3,7.00,1828288);
INSERT into Historical VALUES (NULL,'michael987','Child',2,7.00,1828288);

Open in new window


The  error is as follows:
"ERROR 1452: Cannot add or update a child row: a foreign key constraint fails"

When attempting to insert new rows or values into the child table, this error occurs. While the file runs, it cannot be made updates. I am currently using only the cmd line of MySQL for this issue.
0
Just got done creating a LAMP.  Then installed PHPMYADMIN.

Cannot log into PHPMYADMIN.  I have tried creating mySQL users, changing passwords, granting permissions, etc.

All attempts yield error 1045... access denied.

Thoughts?
0
Hi,
I have a Table called game with 4 columns:
clock_first_goal, clock_last_goal, clock_first_penalty, clock_last_penalty

and a second Table called tickets_one with 5 columns:
clock_one, clock_two, clock_three, clock_four, clock_five
where I've imported random clock times between 0 and 20:00 minutes that of a NHL period.
There are 1200 total times (60 seconds x 20 minutes) in 5 columns so there are 240 records in this Table.

In my application I have a page called enter_games.php. What I want is when the admin submits the times for First Goal, Last Goal, First Penalty and Last Penalty, I want to check if any of these values match the values in the tickets_one Table. If they do I just want to echo "we have a match!", if not I want to echo "no match".

I'm not sure the best way to do this if it should be a Join, if statement or loop etc... I think it can be done with an if statement.
I'm somewhat new to php and mysql. This is the section of the code I'm trying to get to work:

// find matching results
// query the database with client ID
$query = "SELECT * FROM tickets_one";
$result = mysqli_query( $conn, $query );

// if result is returned
if( mysqli_num_rows($result) > 0 ) {

    // we have data!
    // set some variables
    while( $row = mysqli_fetch_assoc($result) ) {
        $time1    = $row['time_one'];
        $time2    = $row['time_two'];
        $time3    = $row['time_three'];
        $time4    = $row['time_four'];
        $time5    = 

Open in new window

0
See the functionalities of both databases SQL and MySQL. And how they are different from each other.
0
I have a column in MySql table which saves multiple values in one column seperated by pipe sign:
i.e: A|B| 
B|C|E|
A|
B|E|
A|E|F|

Open in new window

                                   
Is there any way to retrieve only distinct values from the above data in PHP?

Thanks
0
Build an E-Commerce Site with Angular 5
LVL 12
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

Hi Experts.

My server had this log from malicious IP which is mark as abusive on Internet. I want to know if it is harm to my server or not. If it is, what have it done to my system.

5.188.210.12 - - [06/Nov/2018:00:38:18 +0700] "\x05\x01\x00" 400 173 "-" "-" "-"
5.188.210.12 - - [06/Nov/2018:00:41:01 +0700] "\x04\x01\x00P\x05\xBC\xD2\x0C\x00" 400 173 "-" "-" "-"
5.188.210.12 - - [06/Nov/2018:00:41:43 +0700] "GET http://5.188.210.12/echo.php HTTP/1.1" 404 169 "https://www.google.com/" "PxBroker/0.3.1/8684" "-"

For more information, i found this when checking log files after my database had been dropped in an unusually way.

Thank you for reading this.
Sincerely.
0
I have a wp site that is working fine on my windows production server, site is called jobs.org ( as an example).  I was asked to create the same site on the dev server so I copied over the wp files and called site dev.jobs.org . The same mysql database is still being used.  When going on the dev site I am getting:

PHP Fatal error:  Allowed memory size of 262144 bytes exhausted (tried to allocate 1792 bytes) in \\******\SITES\dev\jobs.org\wp-includes\load.php on line 747

Can someone please explain what I am doing wrong? Is there some config change that has to be made? Is it necessary to create a second mysql database?

Thank you.
0
I have a table like below


country       State.       county.        Address                                 zipcode.
USA              VA             C1               123 Main St                            00000
USA              VA             C1               124 Main St                            00001
USA              VA             C1               126 Main St                            00002
USA              VA             C2               128 Main St                            00011
USA              VA             C2               129 Main St                            00010

how can I write sql to have table as below
country       State.       county.        Address                                 zipcode.
USA              VA             C1               123 Main St                            00000
                                                           124 Main St                            00001
                                                           126 Main St                            00002
USA              VA             C2               128 Main St                            00011
                                                           129 Main St                            00010


I think distinct applies to one complete row, but how to achieve the above
I am using MySql DB.
0
So I'm trying to work out the best table structure and how to interface with the data...

I want to store in a database a list of urls and "tag" them with multiple pre-defined keywords for me to search on later.  The tags on the urls may need to be updated from time to time. ie it's not a one off tagging but an on-going review of each URL.  More tags may be added in the future and I may need to go back to the urls to re-tag them.

I have the list of predefined tags/keywords in a table and I'm now trying to work out what the structure of the resultant table should be in order for it to be easy to update/add/remove the tags for any given url but also to be able to search for a url based on a keyword.

So far I have
tags table
ID | NAME
1 | medical
2 | karate
3 | Technical
4 | Javascript
5 | Shopping

For the resultant table, my initial structure:
URL & TAG_ID are a UNIQUE combination
ID | URL | TAG_ID
1 | www.e-e.com | 4
2 | www.e-e.com | 3
3 | www.google.com | 1
4 | www.google.com | 2
5 | www.amazon.com | 5

So you can see there's multiple entries for one url so that there's a one-to-one match with a tag.  I'm not sure if this is the best approach?

BTW The urls and tags given above are for example only and haven't determined the ones I'll use yet.  I'm more interested in the structure I would use.

If the above table structure is sound, then how do I go about adding/removing tags?  The server will receive a set of tags from the
0
Hi,
I'd like to find matching results between 4 columns in one table and 5 columns in another table.
I have one table called game with 4 columns:
clock_first_goal, clock_last_goal, clock_first_penalty, clock_last_penalty

and a table called participants with 5 columns:
clock_one, clock_two, clock_three, clock_four, clock_five

Everything in these columns are numbers like 4:16, 8:47, 13:02 etc clock times of a period of an NHL hockey game. I think I should be using an Inner Join for this however, it's not working, it's just displaying all of the times in the database instead of just the matching results within those columns.

Please take a look at the screen shot attached to see what I mean.

Here is the code I have so far:


$query = "SELECT * FROM participants AS p INNER JOIN game AS a ON p.game_date = a.game_date WHERE a.user_ID='$userID'";

 

$result = mysqli_query( $conn, $query );

 

                    ?>

                                                               

                                                                                <?php

                                                                                if( mysqli_num_rows($result) > 0 ) {

 

                                                                                                // we have data!

                                                                                                // output the data

 

                                             

Open in new window

0
Hi Experts

Could you clarify if internal queries from PHP code could slow the performance of the site?

The old legacy project that I recently started to participate in extensively queries within the PHP code, users often claim there is
loss of performance

Considering queries could be optimized or not, does the data traffic in these cases between MS-SQL Server (I guess on MySQL is identical) and PHP cause this performance loss?

Thanks in advance!
1

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.