[ php ] mysql / mysqli - select / insert / update 문 사용 예시

 

 

1. DB 연결하기

$conn = mysqli_connect('localhost', 'username', 'password','dbname');
if (mysqli_connect_errno($conn))
{
  echo "데이터베이스 연결 실패: " . mysqli_connect_error();
}
else
{

// to do something

}

 

2. select 문 사용

mysql_query 결과로 영향받은 row수를 알기 위해서는 mysqli_affected_rows 함수를 사용한다. 

- select 결과를 associative array로 반환받기 

$check_query = "SELECT * from ".$table." where code='".$code."' and period='".$period."'";

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

if(mysqli_affected_rows($conn) > 0)
{

  

 // mysqli_fetch_assoc 함수 

 // Fetch a result row as a numeric array and as an associative array:

 while ($row = mysqli_fetch_assoc($result))
 {
            $id = $row['id']."\t";
            $name = $row['name']."\t";
            $news .= $row['news'];

  }
                                 

}

mysqli_free_result($result);

 참고: http://www.w3schools.com/php/func_mysqli_fetch_array.asp

- sum 함수를 사용한 select 문 예시 

 $sum_query = "select Sum(`scores`) as value_sum from tablename";

        $result = $conn->query($sum_query);

        $row = mysqli_fetch_assoc($result);

        $sum_up_short = $row['value_sum'];

참고: http://www.tizag.com/mysqlTutorial/mysqlsum.php

3. insert 문 사용

$query = "INSERT INTO ".$table."(code, name_kor,name_eng, market, type, period,`".$field."`) VALUES ('".$code."','".$name_kor."','".$name_eng."','".$market."','".$type."','".$period."','".$value."');";

   if(mysqli_query($conn, $query))
       echo "insert successfully";
      else
       echo "Error:".$query."mesage:".mysqli_error($conn);  

3. update 문 사용

 $query = "UPDATE ".$tablename." SET `field0`='".$value0."' ,`field1`='".$value1."' ,`field2`='".$value2."' ,`".$field3."`='".$value3."' WHERE 날짜='".$time."' AND 종목코드='".$stockcode."'";
    
    if(mysqli_query($conn, $query))
       echo "update successfully";
      else
       echo "Error:".$query."mesage:".mysqli_error($conn);  
       

 

 4. DB 연결 닫기 

$conn->close();