MySQL/PHP check if data is in database table, if not insert it

Question

I'm having an issue checking if the data I entered is already in the database table and if it's not then inserting it into the table. What I can't figure out is how to get the id (it is primary and auto increment) of the data just inserted into the database.

I don't think it's possible to insert it if it is not already in the database and then get the id of that data to be inserted into another table all from the same script. I could be wrong though. So what I have now is one script to check if it's inserted already and inserting it if it's not, then redirecting to another php script to get the id of the information.

I'm having many errors with this, it inserts it even if it's in the database and when it gets redirected to the second script I am receiving a server error. Any help would be great, thanks in advance!

EDIT: I replaced the two files with one, the new code is below. I am still getting problems though, it inserts duplicate interests in the first table with my interests list

<?php

require_once '../scripts2/app_config.php';
require_once '../scripts2/database_connection.php';
require_once '../scripts2/authorize.php';

session_start();

// Authorize any user, as long as they're logged in
authorize_user();

$user_id = $_SESSION['user_id'];

$interest = trim($_REQUEST['interest']);


$get_interests = "SELECT COUNT(*) AS cnt FROM interests WHERE name = " . $interest;
$query_interests = mysql_query($get_interests);
if($query_interests) {
$hjk = mysql_fetch_array($query_interests);
$cnt = $hjk['cnt'];
}
if($cnt < 1) {
// $num_interests = mysql_num_rows($query_interests);

// insert the interest if it is not already in the database
$insert_sql = sprintf("INSERT INTO interests " .
                              "(name) " .
    "VALUES ('%s');",
         mysql_real_escape_string($interest));


//insert the user into the database
$insert_query = mysql_query($insert_sql);
$id = mysql_insert_id();

// From the newly inserted interest get the id of the interest
$insert_user_interests = sprintf("INSERT INTO user_interests " .
                                                 "(user_profile_id, interest_id) " .
                            "VALUES (%d, %d);",
                             mysql_real_escape_string($user_id),
                             mysql_real_escape_string($id));

mysql_query($insert_user_interests);
//Redirect this user to the page that displays user information
$url = 'show_profile_user_interests.php';
header('Location: '. $url);
exit();
} else {
/* If the interest is already in the table, just insert the id of the interest and
user_profile_id */

$get_interests2 = "SELECT id FROM interests WHERE name = " . $interest;
$query_interests2 = mysql_query($get_interests2);
if($query_interests2) {
$hye = mysql_fetch_array($query_interests2);
$interest_id = $hye['id'];
}
$insert_user_interests = sprintf("INSERT INTO user_interests " .
                                                 "(user_profile_id, interest_id) " .
                            "VALUES (%d, %d);",
                             mysql_real_escape_string($user_id),
                             mysql_real_escape_string($interest_id));

mysql_query($insert_user_interests);

//Redirect this user to the page that displays user information
$url = 'show_profile_user_interests.php';
header('Location: '. $url);
exit();
}
?>

What I think is causing the main problem is below, But I could be wrong...

$get_interests = "SELECT COUNT(id) AS cnt FROM interests WHERE name = " . $interest;
$query_interests = mysql_query($get_interests);
if($query_interests) {
$hjk = mysql_fetch_array($query_interests);
$cnt = $hjk['cnt'];
}
if($cnt > 0) {

score 3 · Accepted Answer · edited May 23 '17 at 11:56

The NOT EXISTS keyword could help:

The docs:

If a subquery returns any rows at all, EXISTS subquery is TRUE, and NOT EXISTS subquery is FALSE. For example:

SELECT column1 FROM t1 WHERE EXISTS (SELECT * FROM t2);

In your case:

"INSERT INTO user_interests
    WHERE NOT EXISTS
        (SELECT * FROM interests
            WHERE name = '" . $interest . "')
    (user_profile_id, interest_id) 
VALUES 
    (...,...)"

Or the short way

"INSERT INTO user_interests
    IF NOT EXISTS name = '" . $interest . "'
    (user_profile_id, interest_id) 
VALUES (...,...)"

Because this way you're doing only 1 query instead of 2, means:

one request to the db-server instead of two
less traffic
more performance

EDITED (not directly related to the question)

There is an error in your select query

"SELECT COUNT(id) AS cnt FROM interests WHERE name = " . $interest;

you need to add ' like this:

...name = '". $interest . "'"; if name is a varchar

EDIT II

You really shouldn't use mysql_ functions anymore!

Please do read Why shouldn't I use mysql_ functions in PHP?!

There is an error in your select query "SELECT COUNT(id) AS cnt FROM interests WHERE name = " . $interest; you need to add ' like name = '". $interest . "'"; if name is a varchar — toesslab, Feb 01 '14 at 21:32

score 0 · Answer 2 · edited Oct 23 '14 at 22:50

0

You can do that in single file itself..

In insert_interest.php, you can get the recently data inserted id by using,

$insert_query = mysql_query($insert_sql);
$id = mysql_insert_id($insert_query);
// Write your second table insert code here

By using the id you can insert the data to second table.

edited Oct 23 '14 at 22:50

Gottlieb Notschnabel

9,408
18
74
116

answered Feb 01 '14 at 07:23

user3259856

44
2

I changed it but now even if the interest is in the interest table it will insert it anyway and in the `user_interest` table it doesn't insert the `id` of the interest. – nikito2003 Feb 01 '14 at 16:38
How would I go about getting the id if the data is already in the table, to just insert it in `user_interest`? – nikito2003 Feb 01 '14 at 16:50
It should just be `$id = mysql_insert_id();` I believe – nikito2003 Feb 01 '14 at 21:11

score 0 · Answer 3 · answered Feb 01 '14 at 21:44

0

First of all use mysqli instead of mysql

Also there is an error

 $get_interests = "SELECT COUNT(*) AS cnt FROM interests WHERE name = " . $interest;

Use

 $get_interests = "SELECT COUNT(*) AS cnt FROM interests WHERE name = '".$interest."'";

answered Feb 01 '14 at 21:44

Hamza

1,593
2
19
31

MySQL/PHP check if data is in database table, if not insert it

3 Answers3