MySQL Server

46K

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

Dear Experts,
I use php and mysql.
I have a mysql sentence which is working like this

SELECT uid, bayiadi, SUM(toplamtutar) as toplamtutar FROM siparis WHERE toplamtutar > 10000  and bayidentahsilati is Null group by uid

Open in new window


but I need to control whether the SUM of toplamtutar is bigger than 10000 not the toplamtutar itself
I wrote it like below but it gives me error. What do I do wrong?

SELECT uid, bayiadi, SUM( toplamtutar ) AS toplamtutarim
FROM siparis
WHERE bayidentahsilati IS NULL and  SUM( toplamtutar ) > 10000
GROUP BY uid

Open in new window

0
Optimize your web performance
LVL 1
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

I am trying to write an insert statement.  One of my values contains an & (ampersand) and I keep receiving an error.  The value also contains a ;.  I don' t know if that will be an issue.

How can I insert something similar to below example
insert into mytable
(field1, field2, field3)
values
("MyName", "MyValue", "123&;456")

Open in new window

The error is:  "SQL Error (1064):  You have an error in your SQL syntax; check the manual that corresponds to your MySQL version ....."

Shows the value attempting to be inserted as where the error is.
0
If one EmployeeID is already exists in the phpmyadmin another employee should not enter the data with the same EmployeeID so can any one add alert message to my code???
<?php

$EmployeeID = "";
if(isset($_POST['EmployeeID'])){
    $EmployeeID = $_POST['EmployeeID'];
} 

$FirstName = "";
if(isset($_POST['FirstName'])){
    $FirstName = $_POST['FirstName'];
 } 

$MiddleName = "";
if(isset($_POST['MiddleName'])){
    $MiddleName = $_POST['MiddleName'];
 } 
                                                                                                                                                                                                                                                                                                                                                                                            
$LastName = "";
if(isset($_POST['LastName'])){
    $LastName = $_POST['LastName'];
 } 

$FatherName = "";
if(isset($_POST['FatherName'])){
    $FatherName = $_POST['FatherName'];
 } 

$SpouseName = "";
if(isset($_POST['SpouseName'])){
    $SpouseName = $_POST['SpouseName'];
 } 

$MotherName = "";
if(isset($_POST['MotherName'])){
    $MotherName = $_POST['MotherName'];
 } 

$DOBDay = "";
if(isset($_POST['DOBDay'])){
    $DOBDay = $_POST['DOBDay'];
 } 

 $DOBMonth = "";
if(isset($_POST['DOBMonth'])){
    $DOBMonth = $_POST['DOBMonth'];
 }

$DOBYear = "";
if(isset($_POST['DOBYear'])){
    $DOBYear = $_POST['DOBYear'];
 } 

$DateofBirth 

Open in new window

0
A MySQL  database has three  levels of subtable.
project->snapshot->layer  

Need to duplicate a project record along with all subrecords from snapshot and layer
a project can have many snapshots, a snapshot can have many layers

Would prefer to do this in PHP but stored procedure is OK
All comments and advice welcome and appreciated
0
I need to fetch the results in column but my results are in row how can i modify my code ???
<?php
 $dbhost = "localhost";
 $dbuser = "GCSADMIN";
 $dbpass = "123456";
 $db = "employee_db";

    $conn = mysql_connect($dbhost, $dbuser, $dbpass,$db);

   if(! $conn ) {
      die('Could not connect: ' . mysql_error());
   }

   $sql = "SELECT EmployeeID,FirstName,MiddleName,LastName,FatherName,SpouseName,MotherName,DOB,Age,BloodGroup,MaritalStatus,Gender,ContactNumber,EmergencyContactNumber,Email,DateOfJoiningatGemini,TotalYearOfExperience,ReleventYearOfExperience,PANnumber,PassportNumber,UANnumber,AadharNumber,PresentAddress,PermanentAddress
      FROM employee_information WHERE EmployeeID = 'GI1005'";

   mysql_select_db('employee_db');
   $retval = mysql_query( $sql, $conn );
   if(! $retval ) {
      die('Could not get data: ' . mysql_error());
   }
   
   echo "<table border='5px solid block', width='50%' padding='12px 20px' >";
?>
<tr>
    <th>First Name</th>
    <th>Middle Name</th> 
    <th>Last Name</th>
    <th>Father Name </th>
   </tr>
<?php

   while($row = mysql_fetch_assoc($retval, MYSQL_ASSOC)){

   	 ?>

      <tr>
            <td><?php echo $row['FirstName']; ?></td> 
            <td><?php echo $row['MiddleName']; ?></td> 
            <td><?php echo $row['LastName']; ?></td>
            <td><?php echo $row['FatherName']; ?></td> 
      </tr>

        <?php 
        }
         echo "</table></br></br>";  

Open in new window

Untitled.png
0
I'm trying to figure out what the correct way is to modify a date format before inserting when using Laravel 5.4.

I have:
public function store(){

        $trip = new Trip;
        $trip->tripName = request('tripName');
        $trip->departing = request('departing');
        $trip->returning = request('returning');
        $trip->save();

        return $trip;

        //return redirect('/home');
    }

Open in new window


My database mySQL field in datetime, but the date that's passed in the variable is '07/17/2017 3:20 PM'.  How/where would I update that before insertion?
0
I understand the exposure of having a public facing website with username root.

So, I have created a new username and changed my WordPress site to use that new username.

Shall I delete the "root" username?

Thanks.
1
I have create Database in my Localhost in mysql Server by phpmyadmin. where i have define no any privileges for the database. when i made live this db on the Hostech Server provider phpmyadmin al tables where successfully imported and  in the Case of views it is showing  me the following error.
"#1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation".

This is the example of the views query that i have to upload on my live DB server from my local server .
"CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `crqry_bomoils`  AS  select
`qry_bom`.`FormulaID` AS `FormulaID`,`qry_bom`.`Code` AS `Code`,`qry_bom`.`MaxOfRevision` AS `MaxOfRevision`,`qry_bom`.`Revision` AS
`Revision`,`qry_bom`.`FormulaName` AS `FormulaName`,`qry_bom`.`Group` AS `Group`,`qry_bom`.`FragranceID` AS
`FragranceID`,`qry_bom`.`Qty` AS `Qty`,`qry_bom`.`FragranceName` AS `FragranceName`,`qry_bom`.`Flag` AS `Flag`,`qry_bom`.`CAS1` AS
`CAS1` from `qry_bom` where (`qry_bom`.`Group` = 'ESSENTIAL OIL') order by `qry_bom`.`FragranceName` ".

1:Main Question is that how can i make live my database with my views that i have created on my Localhost server.??
2:How can i import my  database with views  from my localhost server that should be import on any other mysql server without any privileges errors ??

Thanks In Advance!
0
Hello!

Is it still OK to use this older version of MySQL on a production web server (LAMP on Ubuntu 14.04)? It's a low traffic volume business website. Loads fast enough. So far, no serious issues (like being hacked or something). Also we use older version of PhpMyAdmin (ver. 4.0), so my question also regarding this too.
0
Hi, i wish if everyone is having a good day.
However i have an problem in mysql i would like to get the count of ids column in my table and plus 1 to the number how ever when i do like that it types +1 as string for example the result of counting was 3 it will add 3 to 1 like this 31 not 3+1=4 so i don't know how to solve this here's my code :

		String^ constring = L"datasource=...;port=...;username=...;password=..."; // Giving informations to the server
		MySqlConnection^ conDataBase = gcnew MySqlConnection(constring);
		MySqlCommand ^GetCount = gcnew MySqlCommand("Select Count(ID) from newdatabase.my_table;", conDataBase);
		MySqlDataReader^ myReader;
		MySqlCommand ^cmdDataBase;

		try {
			conDataBase->Open();
			myReader = GetCount->ExecuteReader();
			while (myReader->Read()) {
				int Got = myReader->GetInt32(0);
				cmdDataBase = gcnew MySqlCommand("Insert into newdatabase.my_table (ID) Values ('" + Got+1 + "');", conDataBase);//queries
			}
			myReader->Close();
			myReader = cmdDataBase->ExecuteReader();
		}
		catch (Exception ^Ex) {
			MessageBox::Show(Ex->Message);
		}
	}

Open in new window

0
Get 15 Days FREE Full-Featured Trial
LVL 1
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Hello,
  I have a profile update page that is displaying the changes after the update button is clicked but it is not updating the actual table data. Here is what I have
if(isset($_POST['Update'])) {
	mysql_connect('xxxxxx', 'xxx', 'xxx') or
    die("Could not connect: " . mysql_error());
	mysql_select_db("xxx");
	
	if(!empty($_POST[FirstName]))  {
    $first_name = $_POST['FirstName'];
}
if(!empty($_POST[LastName]))  {
    $last_name = mysql_real_escape_string($_POST['LastName']);
}
if(!empty($_POST[Email]))  {
    $email = mysql_real_escape_string($_POST['Email']);
}
if(!empty($_POST[Password]))  {
    $password = mysql_real_escape_string($_POST['Password']);
}
if(!empty($_POST[Add1]))  {
    $address_1 = mysql_real_escape_string($_POST['Add1']);
}
if(!empty($_POST[Add2]))  {
    $address_2 = mysql_real_escape_string($_POST['Add2']);
}
if(!empty($_POST[City]))  {
    $city = mysql_real_escape_string($_POST['City']);
}
if(!empty($_POST[County]))  {
    $county = mysql_real_escape_string($_POST['County']);
}
if(!empty($_POST[State]))  {
    $state = mysql_real_escape_string($_POST['State']);
}
if(!empty($_POST[Zip]))  {
    $zip_code = mysql_real_escape_string($_POST['Zip']);
}
if(!empty($_POST[PhoneNumber]))  {
    $phone_num = mysql_real_escape_string($_POST['PhoneNumber']);
}
	$sql = "UPDATE 'users' SET 
       FName = '.$first_name.', 
       LName = '.$last_name.', 
       Email = '.$email.', 
       city = '.$city.', 
       state = '.$state.',
	   Zip = 

Open in new window

0
I want to fetch the data but while fetching the data i'm getting the whole data but i want only single employee details.
for example if employee id's are GI1006,Gi1007,GI1008 are in my database but i want only GI1008 so how can i chnage my code ??

<?php
 $dbhost = "localhost";
 $dbuser = "root";
 $dbpass = "Gemini@123";
 $db = "employee database";

    $conn = mysql_connect($dbhost, $dbuser, $dbpass,$db);

   if(! $conn ) {
      die('Could not connect: ' . mysql_error());
   }

   $sql = 'SELECT EmployeeID,FirstName,MiddleName,LastName,FatherName,SpouseName,MotherName,DOB,Age,BloodGroup,MaritalStatus,Gender,ContactNumber,EmergencyContactNumber,Email,DateOfJoiningatGemini,TotalYearOfExperience,ReleventYearOfExperience,PANnumber,PassportNumber,UANnumber,AadharNumber,PresentAddress,PermanentAddress
      FROM employee_inforamtion';

   mysql_select_db('employee database');
   $retval = mysql_query( $sql, $conn );
   if(! $retval ) {
      die('Could not get data: ' . mysql_error());
   }

   while($row = mysql_fetch_assoc($retval, MYSQL_ASSOC)){

   echo "<tr>";
   
   
      echo " <tr>";
      echo  "Employee ID :{$row['EmployeeID']}</br></br> ";
      echo "<tr>";
      echo  "First Name :{$row['FirstName']}</br></br> ";
      echo "<tr>";    
      echo  "Middle Name :{$row['MiddleName']}</br></br>";
      echo " <tr>";
      echo  "Last Name :{$row['LastName']}</br></br>";
      echo " <tr>";
      echo     "Father Name :{$row['FatherName']}</br></br>";
      

Open in new window

0
Hi,

I need a faster way to check for duplication of data in my dataset. I'm using the following query: -
$query = "
			SELECT 
				masterFinal.salesRecordNumber, 
				masterFinal.catID, 
				masterFinal.dateImport, 
				masterFinal.userID 
			FROM masterFinal 
			WHERE masterFinal.salesRecordNumber 
			in 
			(select salesRecordNumber from masterFinal as tmp group by salesRecordNumber, catID having count(*)>1 and catID=masterFinal.catID) AND (masterFinal.dateImport >= now() - INTERVAL 7 DAY) 
			ORDER BY dateImport DESC;";

Open in new window


This query works but as the records in my table grow and grow the query takes longer each day ... currently takes 7mins to loop through the dataset of 13,000 records.

Is there a faster alternative?

J
0
Hello!

I have three tables called languages, languages_have_persons, and  persons. The contents are a follows:

Table languages:
	ID	name
	1	Arabic
	4	German
	5	English

Open in new window


Table languages_have_persons:
	Languages_ID	Persons_ID
		4				1
		4				2
		5				2

Open in new window


Table persons:
	ID	firstName	lastName
	1	Joe		Fraizer
	2	Arnold		Schwarzenegger

Open in new window


What I want is a result of the form

	ID	firstName	lastName		…
	1	Joe		Fraizer			English
	2	Arnold		Schwarzenegger	German, English

Open in new window


The SQL statement for MySQL I have written is

SELECT DISTINCT p1.ID
    , p1.firstName
    , p1.lastName
    , CONCAT_WS(', '
        , l2ar.name
        , l2de.name
        , l2en.name
    )
    FROM
        languages_have_persons AS lp, persons AS p1
    LEFT JOIN languages AS l2ar ON
        l2ar.ID = 1 -- Arabic
    LEFT JOIN languages AS l2de ON
        l2de.ID = 4 -- German
    LEFT JOIN languages AS l2en ON
        l2en.ID = 5 -- English
WHERE p1.ID = lp.Persons_ID
ORDER BY lastName, firstName;

Open in new window


What I get is

	ID	firstName	lastName		…
	1	Joe		Fraizer			Arabic, German, English
	2	Arnold		Schwarzenegger	Arabic, German, English

Open in new window


I tried also the command

SELECT p1.ID
    , p1.firstName
    , p1.lastName
    , CONCAT_WS(', '
        , l2de.name
        , l2en.name
    )
    FROM
    persons p1
    INNER JOIN languages_have_persons lp on p1.ID=lp.Persons_ID 
    INNER JOIN languages l2ar on l2ar.ID=lp.Languages_ID 
	INNER JOIN languages l2de on l2de.ID = lp.Languages_ID 
	INNER JOIN languages l2en on l2en.ID = lp.Languages_ID 
WHERE l2de.ID IN (4,5)
ORDER BY lastName, firstName;

Open in new window


which results in

	ID	firstName	lastName		…
	1	Joe		Fraizer			German, German
	2	Arnold		Schwarzenegger	English, English

Open in new window


Can you see what I have done wrong? Also, the DISTINCT statement is currently required, as I get twice the number of rows otherwise. Do you know what causes that? I lost several hours on this issue, but cannot see where I am doing wrong.

Thank you for your replies!
0
Server windows 2012 with MYSQL 5.7 standard version.

Want to backup my database using the following schedule, Full backup ones a week and differential on daily basis.

So far got a script running a full backup using MySqldump schedule to run on a weekly basis.

Now I would like to add a differential to it.
0
Hi Experts

Could you give an explanation on what is causing this MySQL error "exceeded  the max_questions resource" ?



I'm using MySQL 5.6 and before I had configured it out with:

GRANT USAGE ON  database_name.*
TO 'root'@'localhost'
WITH MAX_QUERIES_PER_HOUR 1000
MAX_CONNECTIONS_PER_HOUR 100;

Open in new window


That wasn't sufficient.

So, what  differentes:

max_questions resource that couldn't be resolved with:   MAX_QUERIES_PER_HOUR and  MAX_CONNECTIONS_PER_HOUR ?

How to manage this situation?

Thanks in advance.
0
I'm getting the errors message like Empid does not exists is at top of the layer but i want that message on lower.can any one modify my code
<?php

 include 'connect.php';
 include 'fun.php';


	/*
	if(logged_in())
	{
		header("location:profile.php");
		exit();
	}
	*/
	$error = "";

	if(isset($_POST['submit']))
	{
		$emp_id = mysqli_real_escape_string($con, $_POST['emp_id']);
	    
	    $password = mysqli_real_escape_string($con, $_POST['password']);
		
		 
		if(emp_exists($emp_id, $con))
		{
			$result = mysqli_query($con, "SELECT * FROM users WHERE EmployeeID='$emp_id' and password='$password'");
			$retrievepassword = mysqli_fetch_assoc($result);

			
			if($password!=$retrievepassword['password'])
			{
				$error = "Password is incorrect";
			}
			else
			{
				$_SESSION['emp_id'] = $emp_id;
				
				
				
				header("location: change.php");
			}
			
			
		}
		else
		{
			$error = "Employee Id Does not exists ";
		}
		
	
	}

?>



<!doctype html>

<html>

<head>
<style>
input[type=text],input[type=password], select {
    width: 100%;
    padding: 12px 20px;
    margin: 8px 0;
    display: inline-block;
    border: 1px solid #ccc;
    border-radius: 4px;
    box-sizing: border-box;
}

input[type=submit] {
    width: 100%;
    background-color: #4CAF50;
    color: white;
    padding: 14px 20px;
    margin: 8px 0;
    border: none;
    border-radius: 4px;
    cursor: pointer;
}

input[type=submit]:hover {
    background-color: #45a049;
}
div {
    border-radius: 20px;

Open in new window

0
Hi

Please check reference link

https://www.letzbank.com/ifsc-codes

I want to make same like search engine and proper url  Name, State ,City, Branch

reference url - https://www.letzbank.com/ifsc-codes/abhyudaya-cooperative-bank/gujarat/ahmedabad/ghatlodiya
0
I have a msql table, a field is a datetime.

When I browse the fields it uses a 12 hour format and I can't tell if it is a or pm.. how can I tell?
0
Free Tool: SSL Checker
LVL 9
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

I have a web page that is divided into different sections. Each section has to show different results. These results are gotten from the database.
e.g
Classroom A
john doe
jane doe
damy doe
winner doe

Classroom B
rumy doe
pala doe
penny doe

Classroom C
... etc.. up  Classroom Z

The following code is what comes to my mind but I'm afraid that it might somehow overload the server when this page is accessed multiple times by different people

$sectionA = $connect->query("SELECT * FROM Main_Section WHERE section = `A` ");
while ($row = $sectionA->fetch_array(MYSQLI_BOTH))
{
    $id = $row["id"];
    $name = $row["name"];
    $sec_result_a = $sec_result.'<p>'.$id.'</p><h3>'.$name.'</h3>';
}

$sectionB = $connect->query("SELECT * FROM Main_Section WHERE section = `B` ");
while ($row = $sectionB->fetch_array(MYSQLI_BOTH))
{
    $id = $row["id"];
    $name = $row["name"];
    $sec_result_b = $sec_result.'<p>'.$id.'</p><h3>'.$name.'</h3>';
}

$sectionC = $connect->query("SELECT * FROM Main_Section WHERE section = `C` ");
while ($row = $sectionC->fetch_array(MYSQLI_BOTH))
{
    $id = $row["id"];
    $name = $row["name"];
    $sec_result_c= $sec_result.'<p>'.$id.'</p><h3>'.$name.'</h3>';
}
UP TO section Z

Please, Is there a way I can Optimise this properly.
0
can any leave me a code for changing the password after login with TINY INT
my code:
<html>
<head>
<title>Change Password</title>
</head>
<body>
<form name="frmChange" method="post" action="login1.php" onSubmit="return validatePassword()">
<tr>
<td><label>Current Password</label></td>
<td><input type="password" name="currentPassword" class="txtField"/><span id="currentPassword"  class="required"></td>
</tr>
<tr>
<td><label>New Password</label></td>
<td><input type="password" name="newPassword" class="txtField"/><span id="newPassword" class="required"></td>
</tr>
<td><label>Confirm Password</label></td>
<td><input type="password" name="confirmPassword" class="txtField"/><span id="confirmPassword" class="required"></td>
</tr>
<tr>
<td><input type="submit" name="submit" value="Submit" class="btnSubmit"></td>
</tr>
</table>
</div>
</form>
<script>
function validatePassword() {
var currentPassword,newPassword,confirmPassword,output = true;

currentPassword = document.frmChange.currentPassword;
newPassword = document.frmChange.newPassword;
confirmPassword = document.frmChange.confirmPassword;

if(!currentPassword.value) {
currentPassword.focus();
document.getElementById("currentPassword").innerHTML = "required";
output = false;
}
else if(!newPassword.value) {
newPassword.focus();
document.getElementById("newPassword").innerHTML = "required";
output = false;
}
else if(!confirmPassword.value) {
confirmPassword.focus();
document.getElementById("confirmPassword").innerHTML = 

Open in new window

0
I had to force restart my mysql server and getting a series of errors such as the one below..  What should I do?  MySQL seems to work for my DB's are loaded and websites work..  

Native table 'performance_schema'.'session_account_connect_attrs' has the wrong structure
0
can we automatically calculate the age after giving date of birth???
if yes please leave a code for me in comment.
0
For contact Number details i added type as INT but in my data base data is not passing ???
0
Hi Experts


Could you point how to workaround this MySQL problem Message: mysqli::real_connect(): [2002] ?

Accordingly to:
 img001

Apparently from a day to another it subtly appeared. I checked that  login/ passaword  hadn't changed.

Thanks in advance
0

MySQL Server

46K

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.