php/mysql: problem inserting rows already existing in spite of verification

[fomalhaut]

Hello all.

The table is very simple: 1 field type integer and 2 fields type char:

noCROA int(6) unsigned not null
repertoire char(25) not null
compl_name char(120) not null

I've a file containing mysql data save, each line is a row, each field separate by the caracter '|'.

2|CROA/Test|fichier_test.jpg

I wanna insert data from file, only if the couple "repertoire" + "compl_name" does not already exist.

here is the php: ($lgn is the line beeing read from the file)

      case "jyc_astronomie.fichiers_compl":
        //  attention la liste des noms de variable ci-dessous doit être en phase avec le script d'insertion correspondant
        list($noCROAAIns, $repertoireComplAIns, $complNameAIns) = explode("|", $lgn);
        //  on regarde si le duo répertoire/complName existe
        $How = "SELECT count(*) FROM jyc_astronomie.fichiers_compl WHERE repertoire ='".$repertoireComplAIns."' AND compl_name = '".$complNameAIns."'";
        if ($res = $dbh->query($How)) {
          //si le duo répertoire - complName n'existe pas, on crée
          if ($res->fetchColumn() <= 0) {
            $insFichiersCompl->execute();
            $enregIns++;
          }
          else {
            $Tmsg = 'Le fichier compl&eacute;ment <b>'.$repertoireComplAIns.$complNameAIns.'</b> existe d&eacute;j&agrave;, associ&eacute; au CROA n&deg; <b>'.$noCROAAIns.' </b>: non remplac&eacute;<br />';
            echo $Tmsg;
          }
        }
        break; 

But the problem is that when the table row exists that was created by the application (sql insert from another php script), the script above treats it as not the same, and it inserts it. And after that, the 2 rows seems strictely identical through phpMyAdmin.

When the table row doesn't exists, the script inserts it, of course.

When the table row exists that was inserted by the above script itself in a previous run, it doesn't insert it, viewing it's the same.

Thank you for guide me on this problem. I've searched in several ways, without understand anything on that !

[Submariner]

Try changing your table definition to use varchar vice char for both repertoire and compl_name. Since you have them as char it is padding the remaining characters with spaces (' '), your query isn't adding this padding onto the values from the file and a match isn't being located. With varchar the query will find a match and not insert a duplicate row. Also I recommend using the noCROA column as the table's primary key provided the values will be unique, this may best be accomplished by having the database increment the value with each insert and not including it in the flat file being imported.

James

[vv.bbcc19]

Hi..thats strange ..are you using google chrome..I had a similar problem once using chrome but not now any ways.

Heres my connect.php page :

DEFINE ('DB_USER', '********');
DEFINE ('DB_PASSWORD', '********');
DEFINE ('DB_HOST', ********');
DEFINE ('DB_NAME', '********');
/* connect */
$dbc = @mysql_connect (DB_HOST, DB_USER, DB_PASSWORD) OR die ('Could not Connect to MySQL: ' . mysql_error() );
/* select the data base */
@mysql_select_db (DB_NAME) OR die ('Could not select the Database: ' . mysql_error() ); 

I will try and remove the @'s from the above code to see if that helps

heres my simple script which checks the database first then inserts if the records do not exist already:

/* Connecting to the Data Base*/
require('connections/connect.php');

$query = "SELECT * FROM tag"; // build the query
require('includes/query/query.php'); // Run the query
$info = mysql_fetch_array( $result );$num =mysql_num_rows($result);

while ($row = mysql_fetch_array
($result, MYSQL_ASSOC)) {
$db = $row['email'].$row['url'].$row['yourtag'].$row['ip_add'];
$check = $email.$url.$tag.$ip;

if ($check == $db){// checks if this tag is already in the database.... incase people goback to this page
$donotsubit = '1';
}
}

mysql_close(DB_NAME);//close the data base before reopening it
/* Connecting to the Data Base*/
require('connections/connect.php');

if ($donotsubit <> '1'){
$query = "INSERT INTO tag (type, status, date, name, email, yourtag, url, description, descriptionc, fontsize, fontc, borderc, backc, price, paid, ip_add) VALUES ('3', '2', NOW(), '', '$email', '$tag', '$url', '$description', '$descriptionc', '', '$fontc' , '$borderc' , '$backc' , '$totalcost' , '', '' )";
$result = mysql_query ($query); // Run the query
if ($result){ // query went ok
echo '<p><span class="ok">Your Tag has been successfully entered into our database. Thank you.</span><br />';
echo '<p><span class="ok">Confirmation has been sent to your email address.</span></p>';
} else {// query did not go ok
echo '<p class="notok">Could not submit due to a Server Error... Please try again later!</p>
<a href="index.php><< Go Back</a>';$serror = '1';
}
}else {echo 'Already Submitted !!!';} 

Try using similar routines instead..change the code.
Hope this helps...

[callumacrae]

@vv.bbcc19:

Your browser will not have any effect whatsoever on what the server does in this script.

You're missing a quote in your first snippet, and you should not be using error control operators - if there are any errors, you do not want them to be suppressed, and it is also inefficient and slows the code down, as it changes the PHP configuration and back again every time you write it. If you must, put this at the top of your script:

error_reporting(0); 

That will disable all error reporting, and is also easier to debug as you can reenable them easily without having to edit every line of code.


Instead of using the ancient mysql_* libraries, try PDO. Read my article on it here, where I explain the basics.

You should also standardise your code, and implement some basic coding standards. I cannot read your code at all, which means that it is difficult to debug. I would recommend that you stick in some white space.

You're also hugely overcomplicating a very simple task!



@fomalhaut: Please could you paste some more code? I can't really see what is happening / what is meant to be happening.

~Callum

[descalzo]

Wild guess: Are you remembering to strip off the ending newline? (either in this script or in the script that originally populated the table). Then the stored values would look the same in phpMyAdmin but would not be equal.

[fomalhaut]

Hello.

Try changing your table definition to use varchar vice char

Change to varchar does not eliminate the problem. On another hand, noCROA cannot be primary, attending there can be several couples "repertoire/compl_name" using the same noCROA (that is referenced in other tables.)

heres my simple script which checks the database first then inserts if the records do not exist already

It seems fastidious reading the whole table instead of unsing a generic count.

strip off the ending newline

Delscalzo, it's ok, I did not strip the new line from the file. I did the same oversight for the other tables, but luckily, the last field was never affected, so I never saw the problem before.
I just add

$lgn = rtrim($lgn,"\n"); 

upstream of my script... and now, it's works !

Thanks again.