CODEWITHSUNDEEP
phpmysqldirectory
rackemup420
rackemup420

Reputation: 1567

What would be the correct check syntax for these strings?

I am writting a script that checks a folder K:/Comics and inserts each name + number into a database, table name = comics. Now what i would like to do would be to check to see if this comic already exists before we run the insert queries.

Table Structure:

CREATE TABLE IF NOT EXISTS `comics` (
  `id` int(100) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `issue` varchar(4) DEFAULT NULL,
  `bio` longtext NOT NULL,
  `pages` int(10) NOT NULL,
  `size` varchar(100) NOT NULL,
  `price` varchar(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 ;

Code:

<?php
    $main_folder = 'K:/Comics/'; // should be K:\Comics\ but I changed it because of the highlighting issue
    $folders = glob($main_folder.'* [0-9]*', GLOB_ONLYDIR);

    $comics_series = array();
    foreach($folders as $folder){
        $comics_series[] = preg_split('/(.+)\s(\d+)/', str_replace($main_folder, '', $folder), -1, PREG_SPLIT_DELIM_CAPTURE|PREG_SPLIT_NO_EMPTY);
    }

    $values = array();
    foreach($comics_series as $pair){
        $values[] = "('".mysql_real_escape_string($pair[0])."', '".((int) $pair[1])."')";
    }

    $query = 'INSERT IGNORE INTO comics (name, issue) VALUES '.implode(',', $values);
    $result = mysql_query($query);
    echo ($result) ? 'Inserted successfully' : 'Failed to insert the values';
?>

What I thought would work but doesn't (still adds comics to the db that are already there):

$query = 'INSERT IGNORE INTO comics (name, issue) VALUES '.implode(',', $values);
$result = mysql_query($query);
echo ($result) ? 'Inserted successfully' : 'Failed to insert the values';

What did I forget?!? The documentation said just to add IGNORE in there and it would work...

Upvotes: 1

Views: 113

Answers (5)

Shef
Shef

Reputation: 45589

<?php
$main_folder = 'K:/Comics/';
$folders = glob($main_folder.'* [0-9]*', GLOB_ONLYDIR);

$comics_series = array();
foreach($folders as $folder){
    $comics_series[] = preg_split('/(.+)\s(\d+)/', str_replace($main_folder, '', $folder), -1, PREG_SPLIT_DELIM_CAPTURE|PREG_SPLIT_NO_EMPTY);
}

$values = array();
foreach($comics_series as $pair){
    // clean the values to protect against SQL injection
    $pair = array(
        mysql_real_escape_string($pair[0]),
        mysql_real_escape_string($pair[1])
    );
    // add it to the values array, for insert
    $values[] = "('".$pair[0]."', '".$pair[1]."')";
}

$query = 'INSERT INTO comics (name, issue) VALUES '.implode(',', $values).' '.
         'ON DUPLICATE KEY UPDATE `issue` = VALUES(`issue`)';
$result = mysql_query($query);
echo ($result) ? 'Inserted successfully' : 'Failed to insert the values';
?>

Upvotes: 1

Tommy Bravo
Tommy Bravo

Reputation: 532

I would place a combined unique index on the name and issue columns of the table to enforce the combination of these values to be distinct within the table.

You could then do the following and not worry about double records:

$query = 'INSERT INTO comics (name, issue) VALUES '.implode(',', $values).'
          ON DUPLICATE KEY UPDATE id=id'; //makes sure you don't get an error

You could also use the INSERT IGNORE statement (as stated by @Dereleased) to avoid inserts of duplicates (in the before specified unique index).

Also see the documentation for the ON DUPLICATE KEY UPDATE statement, it might be helpfull in situations where you want to update the record if it was found as a duplicate while inserting.

Upvotes: 1

erenon
erenon

Reputation: 19118

doesn't work is not too informative.

  • Use mysql_num_rows instead of == '0', it's really meaningless
  • There are no surrounding quotes around the $values.
  • Wrap the values with parentheses

Upvotes: 1

Marc B
Marc B

Reputation: 360702

mysql_query returns a statement handle if the query succeeds, and boolean FALSE if the query fails. You'd need to do:

$check_query_result = mysql_query(...) or die(mysql_error());

if(mysql_num_rows($check_query_result) == 0) {
   ... comic doesn't exist ...
}

Note that a query returning no rows is NOT a failure condition. It's simply a result that happened to have no rows

Upvotes: 0

Dereleased
Dereleased

Reputation: 10087

mysql_query() returns a result resource, not an integer or set of values or anything else. Try changing

if ($check_query == '0'){

to

if (mysql_num_rows($check_query) == 0){

However, I would probably just use:

INSERT IGNORE INTO ...

Since it probably doesn't matter. Check out the MySQL Insert Syntax for more info.

Upvotes: 1

Related Questions