PHP & MySQL question.

Question

I was wondering how can I check a value from an array to see if its in the database if it is don't added to the database again. How would I be able to do this using PHP & MySQL?

PHP code.

for ($x = 0; $x < count($cat_id); $x++){
    $cat_query = "INSERT INTO posts_categories (category_id, post_id, date_created) VALUES ('" . mysqli_real_escape_string($mysqli, strip_tags($cat_id[$x])) . "', '" . mysqli_real_escape_string($mysqli, strip_tags($post_id)) . "', NOW())";
}

Revise PHP code.

    if(isset($cat_id)){

        for($x = 0; $x < count($cat_id); $x++){
            $check_query = mysqli_query($mysqli,"SELECT category_id FROM posts_categories WHERE category_id = '" . $cat_id[$x] ."' AND post_id = '" . $post_id . "'");

            if ($check_query == TRUE) {
                unset($cat_id[$x]);
            }
        }


        for($x = 0; $x < count($cat_id); $x++){
            $cat_query = "INSERT INTO posts_categories (category_id, post_id, date_created) VALUES ('" . mysqli_real_escape_string($mysqli, strip_tags($cat_id[$x])) . "', '" . mysqli_real_escape_string($mysqli, strip_tags($post_id)) . "', NOW())";
        }
    }

Answer 1

You can use INSERT .. ON DUPLICATE UPDATE.

Answer 2

$sql = "SELECT * FROM your_table WHERE your_column='".$yourArray['value']."'";
if(!mysql_num_rows(mysql_query($sql))){
    // no rows so add it to the database...
}

Answer 3

As you're using mysqli you should probabaly use prepared statements (from both a security and performance standpoint)

$stmt   =   $mysqli->prepare("INSERT IGNORE INTO posts_categories (category_id, post_id, date_created) VALUES (?, ?, ?)");

// Should use a prepared statement here.
foreach ($categories as $key => $cat) {

    // Bind params
    $stmt->bind_param('iis', $cat, $post_id, 'NOW()');

    // Exectute the query
    $stmt->execute();
}

// Close the connection!
$mysqli->close();

NOTE: I also used INSERT IGNORE, so the insert will silently fail if the key exists.