php - 检查mysql表是否存在记录

Question

我正在使用以下命令将公司数据插入 mysql 表中。在尝试再次输入之前，是否可以先检查该公司是否已在数据库中？ php 变量 $company 是我想要检查的内容。

   <?php
    require("database.php");
    // Opens a connection to a MySQL server
    $con = mysql_connect("localhost", $username, $password);

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

    mysql_select_db("medicom_wp", $con);

    $pages = get_posts(array(
        'orderby' => 'title', 
        'post_type' => 'members',
        'numberposts' => 300,
        'post_status' => 'any'  
        ));
        foreach($pages as $post) {
        setup_postdata($post);

        $company = get_field('company_name');
        $address = get_field('address');
        $city = get_field('city');
        $post_code = get_field('post_code');

        mysql_query("INSERT INTO markers (`name`, `address`, `lat`, `lng`, `type`) VALUES ('".$company."', '".$address.", ".$city.", ".$post_code."', '0.0', '0.0', '')");
        }
        wp_reset_query();

    mysql_close($con);
    ?>

Answer 1

尝试以下操作。基本上，发送另一个查询来检查具有相同公司名称的重复行，如果查询返回 0 行，则仅运行插入命令。

<?php
require("database.php");
// Opens a connection to a MySQL server
$con = mysql_connect("localhost", $username, $password);

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

mysql_select_db("medicom_wp", $con);

$pages = get_posts(array(
    'orderby' => 'title', 
    'post_type' => 'members',
    'numberposts' => 300,
    'post_status' => 'any'  
    ));
foreach($pages as $post) {
    setup_postdata($post);

    $company = get_field('company_name');
    $address = get_field('address');
    $city = get_field('city');
    $post_code = get_field('post_code');

    // prepare query to check for duplicate company
    $sql = sprintf("select count('x') as cnt from markers where `name` = '%s'", mysql_real_escape_string($company));
    // run query
    $row_dup = mysql_fetch_assoc(mysql_query($sql,$conn));
    // if no row exist
    if ($row_dup['cnt'] == 0) {
        // insert new company
        // consider preparing this query using sprintf() and mysql_real_escape_string() as above
        mysql_query("INSERT INTO markers (`name`, `address`, `lat`, `lng`, `type`) VALUES ('".$company."', '".$address.", ".$city.", ".$post_code."', '0.0', '0.0', '')");
    }
}
wp_reset_query();

mysql_close($con);
?>