sql + php에서 중복값을 삭제하는법 아시는분? 정보
sql + php에서 중복값을 삭제하는법 아시는분?
본문
그림처럼(저그림은 같은이름들어간것을 검색한 데이터) 표기되게 짰는데요...
출력한 데이터에서 이름이 똑같이 들어간 데이터가 있다면 1개만 출력하는 조건이 필요합니다 ㅠㅠ
아래는 소스입니다.
<?
$mysql_host = 'localhost';
$mysql_user = '';
$mysql_password = '';
$mysql_db = '';
// DB 연결
function sql_connect($host, $user, $pass)
{
return @mysql_connect($host, $user, $pass);
}
// mysql_query 와 mysql_error 를 한꺼번에 처리
function sql_query($sql, $error=TRUE)
{
if ($error)
$result = @mysql_query($sql) or die("<p>$sql<p>" . mysql_errno() . " : " . mysql_error() . "<p>error file : $_SERVER[PHP_SELF]");
else
$result = @mysql_query($sql);
return $result;
}
// 쿼리를 실행한 후 결과값에서 한행을 얻는다.
function sql_fetch($sql, $error=TRUE)
{
$result = sql_query($sql, $error);
//$row = @sql_fetch_array($result) or die("<p>$sql<p>" . mysql_errno() . " : " . mysql_error() . "<p>error file : $_SERVER[PHP_SELF]");
$row = sql_fetch_array($result);
return $row;
}
// 결과값에서 한행 연관배열(이름으로)로 얻는다.
function sql_fetch_array($result)
{
$row = @mysql_fetch_assoc($result);
return $row;
}
// DB 선택
function sql_select_db($db, $connect)
{
@mysql_query(" set names euckr ");
return @mysql_select_db($db, $connect);
}
$connect_db = sql_connect($mysql_host, $mysql_user, $mysql_password);
$select_db = sql_select_db($mysql_db, $connect_db);
if (!$select_db)
die("<meta http-equiv='content-type' content='text/html; charset=$g4[charset]'><script type='text/javascript'> alert('DB 접속 오류'); </script>");
unset($my);
function get_paging($write_pages, $cur_page, $total_page, $url, $add="")
{
$str = "";
if ($cur_page > 1) {
$str .= "<a href='" . $url . "1{$add}'>처음</a>";
//$str .= "[<a href='" . $url . ($cur_page-1) . "'>이전</a>]";
}
$start_page = ( ( (int)( ($cur_page - 1 ) / $write_pages ) ) * $write_pages ) + 1;
$end_page = $start_page + $write_pages - 1;
if ($end_page >= $total_page) $end_page = $total_page;
if ($start_page > 1) $str .= " <a href='" . $url . ($start_page-1) . "{$add}'>이전</a>";
if ($total_page > 1) {
for ($k=$start_page;$k<=$end_page;$k++) {
if ($cur_page != $k)
$str .= " <a href='$url$k{$add}'><span>$k</span></a>";
else
$str .= " <b>$k</b> ";
}
}
if ($total_page > $end_page) $str .= " <a href='" . $url . ($end_page+1) . "{$add}'>다음</a>";
if ($cur_page < $total_page) {
//$str .= "[<a href='$url" . ($cur_page+1) . "'>다음</a>]";
$str .= " <a href='$url$total_page{$add}'>맨끝</a>";
}
$str .= "";
return $str;
}
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
$pages_num = "20"; // 페이지당 출력갯수
if($name){
if($mode == "i_su" and $name == "0"){
$db_oder = "s_su == '0'";
}else{
$db_oder = "$mode='$name'";
}
$query = "select * from ig_cms as a, mir_member as b where a.mno=b.no and $db_oder order by name asc";
$result = mysql_query($query);
$num = mysql_affected_rows();
$all_num = $num/$pages_num + 0.2;// 전체 갯수/페이지당 출력갯수
if (!$page) { $page = 1; } // 페이지가 없으면 첫 페이지 (1 페이지)
$from_record = ($page - 1) * $pages_num; // 시작 열을 구함
$write_pages = get_paging($pages_num, $page, $all_num, "./all.php?page=");
$query = "select * from ig_cms as a, mir_member as b where a.mno=b.no and $db_oder order by name asc limit $from_record, $pages_num";
}else{
$query = "select * from ig_cms as a, mir_member as b where a.mno=b.no order by name asc";
$result = mysql_query($query);
$num = mysql_affected_rows();
$all_num = $num/$pages_num + 0.2;// 전체 갯수/페이지당 출력갯수
if (!$page) { $page = 1; } // 페이지가 없으면 첫 페이지 (1 페이지)
$from_record = ($page - 1) * $pages_num; // 시작 열을 구함
$write_pages = get_paging($pages_num, $page, $all_num, "./all.php?page=");
$query = "select * from ig_cms as a, mir_member as b where a.mno=b.no order by name asc limit $from_record, $pages_num";
}
$result = mysql_query($query);
?>
<style>
table{text-align:center}
th{background-color:#f2f6fb; font-size:13px; height:30px; border:1px solid #e5e5e5;}
td{font-size:13px; height:30px; border:1px solid #e5e5e5;}
</style>
<table border="0" style="border:1px solid #e5e5e5;">
<tr>
<th>성명</th>
<th>기수</th>
<th>CMS 누적 금액</th>
<th>기탁 누적 금액</th>
<th>총 기탁 금액</th>
</tr>
<?for ($i = 0; $i < $data = mysql_fetch_array($result); $i++) {
$total = mysql_result(mysql_query("select sum(fi_money) from ig_fund_input as a, mir_member as b where a.mno=b.no and name='{$data[name]}' and fi_type='CMS'"),0); //cms 누적금액
$total2 = mysql_result(mysql_query("select sum(fi_money) from ig_fund_input as a, mir_member as b where a.mno=b.no and name='{$data[name]}' and fi_type='기탁'"),0); //기탁 누적금액
print_r($data);
echo "<hr>";
?>
<tr>
<td width="150px"><?=$data[name]?></td>
<td width="60px"><?=$data[i_su]?>회</td>
<td width="200px" align="right"><?=number_format($total)?>원</td>
<td width="200px" align="right"><?=number_format($total2)?>원</td>
<td width="200px" align="right">총 <?=number_format($total+$total2)."원"?></td>
</tr>
<?}?>
</table>
<div>
<?
// 기본으로 넘어오는 페이지를 아래와 같이 변환하여 이미지로도 출력할 수 있습니다.
//echo $write_pages;
$write_pages = str_replace("처음", "처음", $write_pages);
$write_pages = str_replace("이전", "이전", $write_pages);
$write_pages = str_replace("다음", "다음", $write_pages);
$write_pages = str_replace("맨끝", "맨끝", $write_pages);
$write_pages = preg_replace("/<b>([0-9]*)<\/b>/", "<b><span style=\"color:#4D6185; font-size:12px; text-decoration:underline;\">$1</span></b>", $write_pages);
?>
<?=$write_pages?>
</div>
<div>
<form method="post">
<select name="mode">
<option value="name">성명</option>
<option value="i_su">기수</option>
</select>
<input type=txet name=name>
<input type=hidden name=page value="1">
<input type=submit value='검색'><input type=button value='목록' onclick="location='./all.php?page=1&';">
</form>
</div>
<div align="right" style="padding-top:12px"><input type="BUTTON" style="height: 22px; cursor:pointer" value="인쇄" onclick="window.print(); return false"></div>
출력한 데이터에서 이름이 똑같이 들어간 데이터가 있다면 1개만 출력하는 조건이 필요합니다 ㅠㅠ
아래는 소스입니다.
<?
$mysql_host = 'localhost';
$mysql_user = '';
$mysql_password = '';
$mysql_db = '';
// DB 연결
function sql_connect($host, $user, $pass)
{
return @mysql_connect($host, $user, $pass);
}
// mysql_query 와 mysql_error 를 한꺼번에 처리
function sql_query($sql, $error=TRUE)
{
if ($error)
$result = @mysql_query($sql) or die("<p>$sql<p>" . mysql_errno() . " : " . mysql_error() . "<p>error file : $_SERVER[PHP_SELF]");
else
$result = @mysql_query($sql);
return $result;
}
// 쿼리를 실행한 후 결과값에서 한행을 얻는다.
function sql_fetch($sql, $error=TRUE)
{
$result = sql_query($sql, $error);
//$row = @sql_fetch_array($result) or die("<p>$sql<p>" . mysql_errno() . " : " . mysql_error() . "<p>error file : $_SERVER[PHP_SELF]");
$row = sql_fetch_array($result);
return $row;
}
// 결과값에서 한행 연관배열(이름으로)로 얻는다.
function sql_fetch_array($result)
{
$row = @mysql_fetch_assoc($result);
return $row;
}
// DB 선택
function sql_select_db($db, $connect)
{
@mysql_query(" set names euckr ");
return @mysql_select_db($db, $connect);
}
$connect_db = sql_connect($mysql_host, $mysql_user, $mysql_password);
$select_db = sql_select_db($mysql_db, $connect_db);
if (!$select_db)
die("<meta http-equiv='content-type' content='text/html; charset=$g4[charset]'><script type='text/javascript'> alert('DB 접속 오류'); </script>");
unset($my);
function get_paging($write_pages, $cur_page, $total_page, $url, $add="")
{
$str = "";
if ($cur_page > 1) {
$str .= "<a href='" . $url . "1{$add}'>처음</a>";
//$str .= "[<a href='" . $url . ($cur_page-1) . "'>이전</a>]";
}
$start_page = ( ( (int)( ($cur_page - 1 ) / $write_pages ) ) * $write_pages ) + 1;
$end_page = $start_page + $write_pages - 1;
if ($end_page >= $total_page) $end_page = $total_page;
if ($start_page > 1) $str .= " <a href='" . $url . ($start_page-1) . "{$add}'>이전</a>";
if ($total_page > 1) {
for ($k=$start_page;$k<=$end_page;$k++) {
if ($cur_page != $k)
$str .= " <a href='$url$k{$add}'><span>$k</span></a>";
else
$str .= " <b>$k</b> ";
}
}
if ($total_page > $end_page) $str .= " <a href='" . $url . ($end_page+1) . "{$add}'>다음</a>";
if ($cur_page < $total_page) {
//$str .= "[<a href='$url" . ($cur_page+1) . "'>다음</a>]";
$str .= " <a href='$url$total_page{$add}'>맨끝</a>";
}
$str .= "";
return $str;
}
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
$pages_num = "20"; // 페이지당 출력갯수
if($name){
if($mode == "i_su" and $name == "0"){
$db_oder = "s_su == '0'";
}else{
$db_oder = "$mode='$name'";
}
$query = "select * from ig_cms as a, mir_member as b where a.mno=b.no and $db_oder order by name asc";
$result = mysql_query($query);
$num = mysql_affected_rows();
$all_num = $num/$pages_num + 0.2;// 전체 갯수/페이지당 출력갯수
if (!$page) { $page = 1; } // 페이지가 없으면 첫 페이지 (1 페이지)
$from_record = ($page - 1) * $pages_num; // 시작 열을 구함
$write_pages = get_paging($pages_num, $page, $all_num, "./all.php?page=");
$query = "select * from ig_cms as a, mir_member as b where a.mno=b.no and $db_oder order by name asc limit $from_record, $pages_num";
}else{
$query = "select * from ig_cms as a, mir_member as b where a.mno=b.no order by name asc";
$result = mysql_query($query);
$num = mysql_affected_rows();
$all_num = $num/$pages_num + 0.2;// 전체 갯수/페이지당 출력갯수
if (!$page) { $page = 1; } // 페이지가 없으면 첫 페이지 (1 페이지)
$from_record = ($page - 1) * $pages_num; // 시작 열을 구함
$write_pages = get_paging($pages_num, $page, $all_num, "./all.php?page=");
$query = "select * from ig_cms as a, mir_member as b where a.mno=b.no order by name asc limit $from_record, $pages_num";
}
$result = mysql_query($query);
?>
<style>
table{text-align:center}
th{background-color:#f2f6fb; font-size:13px; height:30px; border:1px solid #e5e5e5;}
td{font-size:13px; height:30px; border:1px solid #e5e5e5;}
</style>
<table border="0" style="border:1px solid #e5e5e5;">
<tr>
<th>성명</th>
<th>기수</th>
<th>CMS 누적 금액</th>
<th>기탁 누적 금액</th>
<th>총 기탁 금액</th>
</tr>
<?for ($i = 0; $i < $data = mysql_fetch_array($result); $i++) {
$total = mysql_result(mysql_query("select sum(fi_money) from ig_fund_input as a, mir_member as b where a.mno=b.no and name='{$data[name]}' and fi_type='CMS'"),0); //cms 누적금액
$total2 = mysql_result(mysql_query("select sum(fi_money) from ig_fund_input as a, mir_member as b where a.mno=b.no and name='{$data[name]}' and fi_type='기탁'"),0); //기탁 누적금액
print_r($data);
echo "<hr>";
?>
<tr>
<td width="150px"><?=$data[name]?></td>
<td width="60px"><?=$data[i_su]?>회</td>
<td width="200px" align="right"><?=number_format($total)?>원</td>
<td width="200px" align="right"><?=number_format($total2)?>원</td>
<td width="200px" align="right">총 <?=number_format($total+$total2)."원"?></td>
</tr>
<?}?>
</table>
<div>
<?
// 기본으로 넘어오는 페이지를 아래와 같이 변환하여 이미지로도 출력할 수 있습니다.
//echo $write_pages;
$write_pages = str_replace("처음", "처음", $write_pages);
$write_pages = str_replace("이전", "이전", $write_pages);
$write_pages = str_replace("다음", "다음", $write_pages);
$write_pages = str_replace("맨끝", "맨끝", $write_pages);
$write_pages = preg_replace("/<b>([0-9]*)<\/b>/", "<b><span style=\"color:#4D6185; font-size:12px; text-decoration:underline;\">$1</span></b>", $write_pages);
?>
<?=$write_pages?>
</div>
<div>
<form method="post">
<select name="mode">
<option value="name">성명</option>
<option value="i_su">기수</option>
</select>
<input type=txet name=name>
<input type=hidden name=page value="1">
<input type=submit value='검색'><input type=button value='목록' onclick="location='./all.php?page=1&';">
</form>
</div>
<div align="right" style="padding-top:12px"><input type="BUTTON" style="height: 22px; cursor:pointer" value="인쇄" onclick="window.print(); return false"></div>
댓글 전체
쿼리문 그룹 바이 아이디로
문 하면 안되나요
문 하면 안되나요
탑스쿨님 말씀처럼
GROUP BY이걸 이용해서
$query = "select * from ig_cms as a, mir_member as b where a.mno=b.no and GROUP BY name and $db_oder order by name asc limit $from_record, $pages_num";
이렇게 하라는 말씀이시죠?
만약그렇게 했을경우
mysql_fetch_array(): supplied argument is not a valid MySQL result resource in.....
처럼 에러가 뜨더라구요..
GROUP BY이걸 이용해서
$query = "select * from ig_cms as a, mir_member as b where a.mno=b.no and GROUP BY name and $db_oder order by name asc limit $from_record, $pages_num";
이렇게 하라는 말씀이시죠?
만약그렇게 했을경우
mysql_fetch_array(): supplied argument is not a valid MySQL result resource in.....
처럼 에러가 뜨더라구요..
distinct 요... ㅎㅎㅎ 이미 해결되셨지만 남깁니다... ㅋ
예제까지 해서!!! 정말 감사합니다.~