YES or NO if query returns correct data ? HOW !!??

gordo2dope · February 23, 2008

if, i have a script here im making to upload a .txt TAb delimited file. It moves and renames the file. Then It reads the file and makes each row into a line to read. Then it gets the rows and makes it into an array with seperate variables. So ... Im trying to get it to get the first number and check for it and if it exists return a true or false or something like that. The point it to search of an existing Part Number. If it exists, update, if not then insert as a new row. Right now I'm just trying to get the inserting part WHICH WORKS BY THE WAY. I just cant get the the IF result is matching to work .... The problem is it RUNS EITHER WAY ... so i need to get a correct IF statement so it will be able to determine to run or not.

If i delete the rows in the TABLE, it runs (as it should), but if they are already in there, it STILL tries to insert and comes back as duplicate entries. But the whole point of the If is to determine if they are in there or not, so it will know NOT to try and insert.

.Im not sure how to go about doing that.Right now ive just been trying crap like " if ($query = NULL) " and a bunch of other things.

Anybody got some suggestions ??

[pre]
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>
<head>
<title>UPLOAD CSV FILE</title>
</head>

<body>
<?php

$csvdirectory = "uploadedcsv";
$currentdir=getcwd();
$target_path = $currentdir."\\".$csvdirectory."\\".basename($_FILES['userfile']['name']);
echo "Target: $target_path<br>";

if (move_uploaded_file($_FILES['userfile']['tmp_name'], $target_path))
{
    echo "The file ".($_FILES["userfile"]["name"])." has been uploaded to ".$currentdir. "\\".$csvdirectory."\\<br><br>";
$uploaded = 'yes';

if (file_exists($target_path))
		{
		echo "<font color='red'><b>FILE FOUND !!</b></font><BR>";
		}
}
else {
    echo "There was an error uploading the file, please try again!<br>";


echo '<br>Here is some more debugging info:';
print_r($_FILES);
print "</pre>";
}

///////////////////////////////////////////////////////////////////
  
  $uploaded_file = $target_path;
  $fcontents = file_get_contents($uploaded_file); // you have to point $uploaded_file at the correct file
  if(!strlen($fcontents))
  {
    echo "file missing info , ERROR!";
  }
  
  
  else { // START OF ELSE TO CONNECT AND TUN
  		echo "<br><br><font color='red' size='+2'> FOUND THE RENAMED FILE !<hr></font>";

echo"STARTED <BR><BR>	";
mysql_connect("XXXXXXX", "XXXXXXX", "XXXXXXX") or die(mysql_error());
mysql_select_db("XXXXXXX") or die(mysql_error());

$fcontents = file($uploaded_file); 

for($i=0; $i<sizeof($fcontents); $i++) 
{  //start of FOR

     	 $line = trim($fcontents[$i]); 
      
		  $arr = explode("\t", $line);
	  
		  $partnum = $arr[0];  // the one we are searching for ... 
		  $second = $arr[1];
		  $description = $arr[2];
		  $onhand = $arr[3];
		  $avail = $arr[4];
		  $oo = $arr[5];
		  $cost = $arr[6];
		  $retail = $arr[7];
		  $sale = $arr[8];
		  $active = $arr[9];
		  
	      #if your data is comma separated
	      # instead of tab separated, 
	      # change the '\t' above to ',' 

	$query = "SELECT * FROM `mczippo1` WHERE PartNum = ".$partnum;
	$data = mysql_query( $query );

// OK HERE IS THE PROBLEM BELOW ... i basically need to say IF query came back positive ... ???

if ($data != NULL)
	{ // START OF IF

		$sql = "INSERT INTO mczippo1 (`PartNum`,`2nd`,`Description`,`OnHand`,`Avail`,`OO`,`Cost`,`Retail`,`Sale`,`Active`) VALUES ('".implode("','", $arr)."')"; 
	      mysql_query($sql);
		  
	      echo $sql ."<br><br>\n";
	      
		  if(mysql_error()) 
		  	{
	         echo mysql_error() ."<br><br>\n";
	      	}
	} // END OF IF
} // END OF FOR
} // END OF ELSE TO CONNECT AND RUN 

?>

</body>
</html>
[/pre]

HERE IS A DOUBLE SCREEN SHOT SPLICED TOGETHER. the first one above green line is inserting, now... the second below the green line should not run because i want it to see the parts exist and therefor NOT run the code.

Bauer418 · February 23, 2008

Since your part numbers are set by you, not your database, it makes this a bit easier. I see that you already have the part number column in your database set to be a unique index. You can run a query such as:

REPLACE INTO table_name SET part_number='partnum', otherdata='otherval'

And continue finishing that SQL query. REPLACE INTO will search to see if a row with that part_number already exists. If it does, it'll delete the old row and replace it with the new data. If none exists, it'll just insert a new row.

gordo2dope · February 23, 2008

well see i do need that for the second part im going to be working on.. BUT , let me explain the problem.

- we have a cashiering system that has say 10 fields we will be exporting to SQL everyday to either insert or update.

- our SQL database has 33 fields. (10 regular and 23 we made up).

- now, the problem with YOUR UPDATE LINE is if we UPDATE like that, it will overwrite existing data with NULLs since we wont have those extra fields populated before we INSERT, ive already tried. This is why i want to split it up into 2 seperate IF's and QUERYS.

IF NO EXIST, insert 33 fields, including the unique indexed PARTNUM.

ELSE exists, only UPDATE these 10 fields. WHERE PartNUM = $partnum (variable from array) (dont have this ELSE showing on the code on the page because i need to get the first part working first, then the second will work easy)

this is what BOTH would LOOK LIKE ... something like this, the else im not sure if i should do insert OVER, or make UPDATE ... Unless you or someone else knows a betterway of going about it...

[pre]

<?php
	$query = "SELECT * FROM `mczippo1` WHERE PartNum = ".$partnum;
	$data = mysql_query( $query );

// OK HERE IS THE PROBLEM BELOW ... i basically need to say IF query came back positive ... ???

if ($data != $partnum)
	{ // START OF IF

		/// REPLACE INTO table_name SET part_number='partnum', otherdata='otherval'

		$sql = "INSERT INTO mczippo1 (`PartNum`,`2nd`,`Description`,`OnHand`,`Avail`,`OO`,`Cost`,`Retail`,`Sale`,`Active`) VALUES ('".implode("','", $arr)."')"; 
	      mysql_query($sql);
		  
	      echo $sql ."<br><br>\n";
	      
		  if(mysql_error()) 
		  	{
	         echo mysql_error() ."<br><br>\n";
	      	}
	} // END OF IF

	else 
	{
	 $sql = "INSERT INTO mczippo1 (`2nd`,`Description`,`OnHand`,`Avail`,`OO`,`Cost`,`Retail`,`Sale`,`Active`) VALUES (`$second`,`$description`,`$onhand`,`$avail`,`$oo`,`$cost`,`$retail`,`$sale`,`$active`) WHERE PartNum = ".$partnum; 
      mysql_query($sql);
	  
      echo $sql ."<br><br>\n";
      
	  if(mysql_error()) 
	  	{
         echo mysql_error() ."<br><br>\n";
      	}
	}
?>[/pre]

Bauer418 · February 23, 2008

You could use SQL's:

INSERT ... ON DUPLICATE KEY UPDATE

Which will perform an update rather than a delete/reinsert

mem0ri · February 23, 2008

You need to replace your 2nd INSERT with an UPDATE statement:

UPDATE (table) SET col1 = 'var1', col2 = 'var2'... WHERE col = var;

gordo2dope · February 23, 2008

mm this seems more like it probably let me mess around with that a little when i do the bottom half.

BUt still the problem im having is the IF. either way its running the code. like the if is always coming back true.

P.S. i dont know about or think of "INSERT ... ON DUPLICATE KEY UPDATE" thanks for that too. this might be a while but ill come back to post my findings, THANKS!

gordo2dope · February 23, 2008

see it needs to be something like this ... added the FETCH ARRAY LINE. ive NEVER had a problem with an If or ELSE, so i think the problem is that its inside the FOR , which ive never used.

[pre]
<?php
                                                       // this following array is supplied above from a tab dilimited file
		  $partnum = $arr[0];  // the one we are searching for ... 
		  $second = $arr[1];
		  $description = $arr[2];
		  $onhand = $arr[3];
		  $avail = $arr[4];
		  $oo = $arr[5];
		  $cost = $arr[6];
		  $retail = $arr[7];
		  $sale = $arr[8];
		  $active = $arr[9];
		  	
	$query = "SELECT * FROM `mczippo1` WHERE PartNum=".$partnum;
	$data = mysql_query( $query );
	$part = mysql_fetch_array( $data );

// OK HERE IS THE PROBLEM BELOW ... i basically need to say IF query came back negative... ???

if ($partnum != $part['PartNum'])  //if txt file PartNum does not = partnum from query
	{ // START OF IF

		$sql = "INSERT INTO mczippo1 (`PartNum`,`2nd`,`Description`,`OnHand`,`Avail`,`OO`,`Cost`,`Retail`,`Sale`,`Active`) VALUES ('".implode("','", $arr)."')"; 
	      mysql_query($sql);
		  
	      echo $sql ."<br><br>\n";
	      
		  if(mysql_error()) 
		  	{
	         echo mysql_error() ."<br><br>\n";
	      	}
	} // END OF IF
} // END OF FOR LOOP
} // END OF ELSE TO CONNECT AND RUN 

?>
[/pre]

Bauer418 · February 23, 2008

Did you give up on the single line INSERT ON DUPLICATE KEY UPDATE?

gordo2dope · February 23, 2008

no im working on it on the side also ... just dont want to mix too much stuff in this post.

mem0ri · February 23, 2008

Instead of checking if $partnum = $part['PartNum'] you need to check if you got an array result at all.

if($part == FALSE) would do the trick.

Bauer418 · February 23, 2008

This should be a working example (don't currently have a testing server to put it on) which I'll post without using INSERT ON DUPLICATE KEY UPDATE so you can clear up your problems.

Text file (parts.txt - this file isn't tab delimited...it's actually 5 spaces, so the code won't work through copy/paste without changing the spaces to a tab):

prt-1535     My Part     This part has 3 parts
prt-1536     My Part 2     This part has 4 parts

Script file (parts.php - there are spaces in the fopen and fgetcsv commands because the forum won't let me post otherwise):

<?php
$csv = f open('parts.txt', 'r');
while ($line = f getcsv($csv, 0, "\t")) {
     list($part_num, $part_name, $part_desc) = $line;
     $query = "SELECT * FROM parts WHERE part_num='" . $part_num . "'";
     $result = mysql_query($query) or die(mysql_error());
     if (mysql_num_rows($result))
     {
          mysql_query("UPDATE parts SET part_name='" . $part_name . "', part_desc='" . $part_desc . "' WHERE part_num='" . $part_num . "'") or die(mysql_error());
          print "Existing part updated in the database";
     }
     else
     {
          mysql_query("INSERT INTO parts SET part_num='" . $part_num . "', part_name='" . $part_name . "', part_desc='" . $part_desc . "'") or die(mysql_error());
          print "New part added to the database";
     }
}
?>

gordo2dope · February 23, 2008

Instead of checking if $partnum = $part['PartNum'] you need to check if you got an array result at all.

if($part == FALSE) would do the trick.

well memOri, this is what i WANT, but it still rungs the IF either way. damnit, i hate computers. Im telling you, it i think it has to do with the fact the IF is inside a FOR statement.

Bauer - im working on 1 with yorus also right now. gonna take a bit to modify the fields and stuff though, thanks

gordo2dope · February 23, 2008

awww well the updating works, which is great. but i get an error cause its searching for a PartNum that doesnt exist and comes back as false so the code DIES

heres the error code..

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE PartNum='8001-3'' at line 1

[pre]
<?php

mysql_connect("XXXX", "XXXX", "XXXX") or die(mysql_error());
mysql_select_db("XXXX") or die(mysql_error());

$csv = f  open('updatefile.txt', 'r');    
while ($line = f  getcsv($csv, 0, "\t")) 
{
     list($part_num, $part_2nd, $part_desc, $part_onhand, $part_avail, $part_oo, $part_cost, $part_retail, $part_sale, $part_active) = $line;
     $query = "SELECT * FROM mczippo1 WHERE PartNum='" . $part_num . "'";
     $result = mysql_query($query) or die(mysql_error());
     if (mysql_num_rows($result))
     {
          mysql_query("UPDATE mczippo1 SET 2nd='" . $part_2nd . "', Description='" . $part_desc . "', OnHand='" . $part_onhand . "', Avail='" . $part_avail . "', OO='" . $part_oo . "', Cost='" . $part_cost . "', Retail='" . $part_retail . "', Sale='" . $part_sale . "', Active='" . $part_active . "' WHERE PartNum='" . $part_num . "'") or die(mysql_error());
          print "Existing part updated in the database<BR><BR>";
     }
     else
     {
          mysql_query("INSERT INTO mczippo1 SET PartNum='" . $part_num . "', 2nd='" . $part_2nd . "', Description='" . $part_desc . "', OnHand='" . $part_onhand . "', Avail='" . $part_avail . "', OO='" . $part_oo . "', Cost='" . $part_cost . "', Retail='" . $part_retail . "', Sale='" . $part_sale . "', Active='" . $part_active . "' WHERE PartNum='" . $part_num . "'") or die(mysql_error());
          print "New part added to the database<BR><B>";
     }
}

?>
[/pre]

Bauer418 · February 23, 2008

Take the "WHERE PartNum..." off of the end of the insert statement and you should be good.

gordo2dope · February 23, 2008

HAH! duhh ! why didnt i see that, its cause i copied and pasted the line from the IF so I wouldnt have to type it all out again. And didnt take the WHERE off.

this works great, thanks alot. now i just need to get it working with my file uploader and renamer thing. THANKS!!

heres what i get when i change the file and upload it...

Existing part updated in the database

New part added to the database

New part added to the database

Existing part updated in the database

New part added to the database

Existing part updated in the database

gordo2dope · February 23, 2008

just 1 question ...

what is the R for ??

$csv = f open('updatefile.txt', 'r');

Bauer418 · February 23, 2008

That specifies what type of file handle should be created.

'r' Open for reading only; place the file pointer at the beginning of the file.
'r+' Open for reading and writing; place the file pointer at the beginning of the file.

'w' Open for writing only; place the file pointer at the beginning of the file and truncate the file to zero length. If the file does not exist, attempt to create it.

'w+' Open for reading and writing; place the file pointer at the beginning of the file and truncate the file to zero length. If the file does not exist, attempt to create it.

'a' Open for writing only; place the file pointer at the end of the file. If the file does not exist, attempt to create it.

'a+' Open for reading and writing; place the file pointer at the end of the file. If the file does not exist, attempt to create it.

'x' Create and open for writing only; place the file pointer at the beginning of the file. If the file already exists, the fopen() call will fail by returning FALSE and generating an error of level E_WARNING. If the file does not exist, attempt to create it. This is equivalent to specifying O_EXCL|O_CREAT flags for the underlying open(2) system call.

'x+' Create and open for reading and writing; place the file pointer at the beginning of the file. If the file already exists, the fopen() call will fail by returning FALSE and generating an error of level E_WARNING. If the file does not exist, attempt to create it. This is equivalent to specifying O_EXCL|O_CREAT flags for the underlying open(2) system call.

gordo2dope · February 23, 2008

cool, thanks for all the help and info. theres no way to mark this topic as SOLVED huh, like that other page does. THANKS EVERYONE!

especially you Bauer.

Bauer418 · February 23, 2008

Glad to help. Currently, there's no topic solved button...I think they're working on getting a new one put in.

Sign In

YES or NO if query returns correct data ? HOW !!??

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information