ABOUT ME

-

Today
-
Yesterday
-
Total
-
  • [ php ] mysql / mysqli - select / insert / update 문 사용 예시
    프로그래밍/PHP 2017. 8. 4. 09:47
    반응형

     

     

    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();



     

    728x90
    반응형
Designed by Tistory.