노을지기 노리터

Multi Query

출처: http://php.net/manual/en/function.mysql-query.php

You can put multiple query with PHP && MySQL:

//initialise MySQL users variables
mysql_query("select @a:=0,@b:=0");

//select values of variables
mysql_query("select @a:=sum(rate), @b:=count(*) from ratetable where rated_user_id='$id'");
 
//finally, select for result   
$rate_id_select=mysql_query("select @c:=round(@a/@b) as effective_rate");
      
//..and result $rate_user=mysql_result($rate_id_select,0,"effective_rate");

...

echo $rate_user;

Alain Cloutier
webmaster@saguenay.cc

출처: http://crodrigues.com/trick-mysql-order-string-as-number/

숫자와 문자가 섞어였는 것을 정렬하고 싶었는데, 자료가 없었다.

Trick 이긴하지만 매우 유용한 것

ORDER BY field+0 ASC

+0 이 대박인듯..ㅡㅡ;;

중복 값 찾는 법

SELECT * FROM 테이블 GROUP BY 필드 HAVING COUNT(필드) > 1

중복값 갯수 알기

SELECT count(필드명)-count(distinct 필드명) FROM `gd_member` WHERE 1

출처:

http://blog.naver.com/PostView.nhn?blogId=lecardos&logNo=70123459399&viewDate=&currentPage=1&listtype=0

http://blog.naver.com/x1478?Redirect=Log&logNo=70127581041

출처: http://dev.mysql.com/tech-resources/articles/wizard/page4.html

MySQL에서 엑셀의 피벗테이블같이 사용하고 싶을 때 유용하다.

Some help from SQL itself

The Wizard smiled. "If you like to, you're welcome. However, you could get some help from the database engine itself, provided that you ask nicely." And while he was speaking he typed a very cryptic statement:

mysql> SELECT CONCAT(', SUM(IF(dept = "',dept,'", 1,0)) AS `',dept,'`')
    -> FROM departments;

"You know," he went on, "you can also use SQL to produce SQL code. This is one of the cases. You have in front of you the list of columns that you should include in your query. Now, with some cut-and-paste, we could get the result you want. Here."

mysql> SELECT location
    -> , SUM(IF(dept = "Development", 1,0)) AS `Development` 
    -> , SUM(IF(dept = "Personnel", 1,0)) AS `Personnel`
    -> , SUM(IF(dept = "Research", 1,0)) AS `Research`
    -> , SUM(IF(dept = "Sales", 1,0)) AS `Sales`
    -> , SUM(IF(dept = "Training", 1,0)) AS `Training`
    -> , COUNT(*) AS total
    -> FROM locations INNER JOIN employees USING (loc_code)
    -> INNER JOIN departments USING (dept_code)
    -> GROUP BY location;

The wizard was now unstoppable. He had reached the stage where he simply couldn't help giving away his knowledge.

"Before we go on," he said, taking possession of my keyboard, "let's see how this same method can do more than counting. Let's replace those 1s with a numeric field, and do real summing up."

mysql> SELECT location
    -> , SUM(IF(dept = "Development", salary,0)) AS `Development`
    -> , SUM(IF(dept = "Personnel", salary,0)) AS `Personnel`
    -> , SUM(IF(dept = "Research", salary,0)) AS `Research`
    -> , SUM(IF(dept = "Sales", salary,0)) AS `Sales`
    -> , SUM(IF(dept = "Training", salary,0)) AS `Training`
    -> , SUM(salary) AS total
    -> FROM locations INNER JOIN employees USING (loc_code)
    -> INNER JOIN departments USING (dept_code)
    -> GROUP BY location;

"Don't forget to change also the total field. A simple SUM without IF, and your total is ready."

I was looking at the screen, which was showing what seemed to be the complete solution to my problem, but the wizard was shaking his head. "As a matter of fact," he was saying, there is something that we can improve here. We have two queries, in which we are reading the departments table. So we are reading it twice twice. The whole process could be improved, by querying for department code the first time, and omitting the join with departments the second time."

mysql> SELECT CONCAT(', SUM(IF(dept_code = "',dept_code,'", 1,0)) AS `',dept,'`')
    -> FROM departments;

"Here. Let's get the columns once more. Good. And there it is. This one looks better."

mysql> SELECT location
    -> , SUM(IF(dept_code = "1", 1,0)) AS `Personnel`
    -> , SUM(IF(dept_code = "2", 1,0)) AS `Training`
    -> , SUM(IF(dept_code = "3", 1,0)) AS `Research`
    -> , SUM(IF(dept_code = "4", 1,0)) AS `Sales`
    -> , SUM(IF(dept_code = "5", 1,0)) AS `Development`
    -> , COUNT(*) AS total
    -> FROM locations INNER JOIN employees USING (loc_code)
    -> GROUP BY location;

He changed the previous two SQL statements, executed them, with some cut-and-pasting in the middle, and got exactly the same result. Now he was explaining me why he did it. "The first query is scanning all the departments table, and we know that its results will be used to build the second query containing the employees table, which has already a department code. Therefore, we can skip the join with departments, since the only purpose of that join was to get the names of the departments."

"I see" was the only comment I could offer, Since I was overwhelmed by his continuous insight. I got the idea, and I couldn't help thinking that he must have done that before. All those pieces of information were coming just too fast for me. Luckily, all logs were on, so I knew that I would be able to get all the statements back when he would leave. Which was not the case yet. The wizard was now ready to give me his philosophical view of cross tabulating.

AJAX Database Example

The following example will demonstrate how a web page can fetch information from a database with AJAX:

Example Explained - The MySQL Database

The database table we use in the example above looks like this:

Example Explained - The HTML Page

When a user selects a user in the dropdown list above, a function called "showUser()" is executed. The function is triggered by the "onchange" event:

The showUser() function does the following:

• Check if a person is selected
• Create an XMLHttpRequest object
• Create the function to be executed when the server response is ready
• Send the request off to a file on the server
• Notice that a parameter (q) is added to the URL (with the content of the dropdown list)

The PHP File

The page on the server called by the JavaScript above is a PHP file called "getuser.php".

The source code in "getuser.php" runs a query against a MySQL database, and returns the result in an HTML table:

Explanation: When the query is sent from the JavaScript to the PHP file, the following happens:

1. PHP opens a connection to a MySQL server
2. The correct person is found
3. An HTML table is created, filled with data, and sent back to the "txtHint" placeholder

I'm using the W3 Schools example: http://www.w3schools.com/PHP/php_ajax_database.asp to display a table of database data given the choice from a single select box. How can I modify this to include another select box, say for example I wanted records matching a name and an age? php mysql ajax select link|edit|flag asked Apr 7 '10 at 14:35 Robert 134 33% accept rate

up vote 0 down vote accepted

Change <select name="users" onchange="showUser()" id="name_select"> ADD age drop down from 1 to 100 <select name="age" onchange="showUser()" id="age_select">   <option value="1">1</option>   <option value="2">2</option>                    |   <option value="100">100</option> </select> Change function showUser(str) { xmlhttp=GetXmlHttpObject(); if (xmlhttp==null)   {   alert ("Browser does not support HTTP Request");   return;   } name= document.getElementByID('name_select').value age= document.getElementByID('age_select').value var url="getuser.php"; url=url+"?q="+str+"&age="+age; url=url+"&sid="+Math.random(); xmlhttp.onreadystatechange=stateChanged; xmlhttp.open("GET",url,true); xmlhttp.send(null); } change in php $age=$_GET["age"]; $sql="SELECT * FROM user WHERE id = '".$q."' and age=".$age; link|edit|flag answered Apr 7 '10 at 14:54 Salil 6,8892725

Won't this be a problem if they don't select an age? – dclowd9901 Apr 7 '10 at 15:00 I'm having problems, now nothing happens if I change the selections – Robert Apr 7 '10 at 15:14 @Robert sorry dear your showUser(str) now should be showUser() – Salil Apr 8 '10 at 3:56 Works great now, thanks! – Robert Apr 8 '10 at 8:39

<html>

<head>

<script type="text/javascript" src="selectuser.js"></script>

</head>

<body>


<form>

Select a User:

<select name="users" id="users">

<option value="1">Peter Griffin</option>

<option value="2">Lois Griffin</option>

<option value="3">Glenn Quagmire</option>

<option value="4">Joseph Swanson</option>

</select>


Select an age:

<select name="age" id="age">

<option value="39">39</option>

<option value="40">40</option>

<option value="41">41</option>

<option value="42">42</option>

</select>


<input type='button' value='Refresh Data' onclick="showUser(document.getElementById('users').value,document.getElementById('age').value)">

</form>

<br />

<div id="txtHint"><b>Person info will be listed here.</b></div>


</body>

</html>

var xmlhttp;


function showUser(str,age)

{

xmlhttp=GetXmlHttpObject();

if (xmlhttp==null)

  {

  alert ("Browser does not support HTTP Request");

  return;

  }

var url="getuser.php";

url=url+"?q="+str+"&a="+age;

url=url+"&sid="+Math.random();

xmlhttp.onreadystatechange=stateChanged;

xmlhttp.open("GET",url,true);

xmlhttp.send(null);

}


function stateChanged()

{

if (xmlhttp.readyState==4)

{

document.getElementById("txtHint").innerHTML=xmlhttp.responseText;

}

}


function GetXmlHttpObject()

{

if (window.XMLHttpRequest)

  {

  // code for IE7+, Firefox, Chrome, Opera, Safari

  return new XMLHttpRequest();

  }

if (window.ActiveXObject)

  {

  // code for IE6, IE5

  return new ActiveXObject("Microsoft.XMLHTTP");

  }

return null;

}

<?php

$q=$_GET["q"];

$a=$_GET["a"];


$con = mysql_connect('localhost', 'peter', 'abc123');

if (!$con)

  {

  die('Could not connect: ' . mysql_error());

  }


mysql_select_db("ajax_demo", $con);


$sql="SELECT * FROM user WHERE id = '".$q."' and age='".$a."'";


$result = mysql_query($sql);


echo "<table border='1'>

<tr>

<th>Firstname</th>

<th>Lastname</th>

<th>Age</th>

<th>Hometown</th>

<th>Job</th>

</tr>";


while($row = mysql_fetch_array($result))

  {

  echo "<tr>";

  echo "<td>" . $row['FirstName'] . "</td>";

  echo "<td>" . $row['LastName'] . "</td>";

  echo "<td>" . $row['Age'] . "</td>";

  echo "<td>" . $row['Hometown'] . "</td>";

  echo "<td>" . $row['Job'] . "</td>";

  echo "</tr>";

  }

echo "</table>";


mysql_close($con);

?>