Jump to content

insert and update row in mysql


tobitimac

Recommended Posts

how can i make this code to insert a single, multiple and update rows in the database. The code only insert new rows in the database.

 


if (($handle = fopen('http://mlkishigo.com/sageonline/inventorylist.csv', "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 100000, ",")) !== FALSE) {
        $num = count($data);


       $recordCount = @mysql_num_rows("Select * from inventory where itemNumber=".$data[0]);

	if ($recordCount > 0) {

	  $sql1 = "UPDATE inventory SET itemNumber='$data[1]' AND itemDesc='$data[2]' AND itemDesc='$data[3]' AND quantityHand='$data[4]' AND category='$data[5]' AND Whse='$data[6]'  WHERE itemNumber='$data[1]' ";
  mysql_query($sql1) or die(mysql_error());

	}

	else {

	  $sql="INSERT into inventory(itemNumber,itemDesc,quantityHand,category,Whse) values('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]')";
      mysql_query($sql) or die(mysql_error());

	}


    }
    fclose($handle);

        }
?>

Link to comment
Share on other sites

 

This is what i changed it to but still not updating, its only insert new rows.

 


if (($handle = fopen('http://mlkishigo.com/sageonline/inventorylist.csv', "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 100000, ",")) !== FALSE) {
        $num = count($data);


       $recordCount = @mysql_num_rows("Select * from inventory where itemNumber=".$data[0]);

	if ($recordCount > 0) {

	  $sql1 = "UPDATE inventory SET itemNumber='$data[1]',itemDesc='$data[2]',itemDesc='$data[3]',quantityHand='$data[4]',category='$data[5]',Whse='$data[6]'  WHERE itemNumber='$data[1]' ";
  mysql_query($sql1) or die(mysql_error());

	}

	else {

	  $sql="INSERT into inventory(itemNumber,itemDesc,quantityHand,category,Whse) values('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]')";
      mysql_query($sql) or die(mysql_error());

	}


    }
    fclose($handle);

        }
?>

Link to comment
Share on other sites

Well yes I guess I didn't notice that this line is invalid:

 

$recordCount = @mysql_num_rows("Select * from inventory where itemNumber=".$data[0]);

 

What you need to do is do a query and assign that to a variable that will be the result.  Then you can use mysql_num_rows() on the result to see if there is one.  Just in terms of advice, don't use the @ until you are sure you know what you're doing as it hides errors.

Link to comment
Share on other sites

You do something like:

 

$result = mysql_query("SELECT * FROM inventory WHERE itemNumber=$data[0]");

if ($result) {
  $recordCount = mysql_num_rows($result);
  if ($recordCount > 0) { // rest of your current code loop here.
  } else {
    // your current code.
  }
} else {
  die(mysql_error());
}

Link to comment
Share on other sites

 

 

Here is my new code

 


$result = mysql_query("SELECT * FROM inventory WHERE itemNumber=".$data[0]);
if ($result) {
  $recordCount = mysql_num_rows($result);

  echo "$recordCount Rows\n";

  if ($recordCount > 0) {
  // rest of your current code loop here.
  $sql1 = "UPDATE inventory SET itemNumber='$data[0]',itemDesc='$data[1]',quantityHand='$data[2]',category='$data[3]',Whse='$data[4]'  WHERE itemNumber='$data[0]' ";
  mysql_query($sql1) or die(mysql_error());

  } else {
    // your current code.
    $sql="INSERT into inventory(itemNumber,itemDesc,quantityHand,category,Whse) values('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]')";
    mysql_query($sql) or die(mysql_error());
  }
} else {
  die(mysql_error());
}

Link to comment
Share on other sites

Well, your first query can not succeed based on what you have right now, because I don't see anyplace that $data would be getting values from.  You used to have:

 

 

if (($handle = fopen('http://mlkishigo.com/sageonline/inventorylist.csv', "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 100000, ",")) !== FALSE) {
        $num = count($data);

 

Did you leave that out?  I'd actually like to see your entire script at present rather than a snippet.

Link to comment
Share on other sites

Hi

 

Thanks for your quick response, here is the entire code, its really small code..

 

<?

session_start();


//db connection
$db = mysql_connect("xxx", "xxx", "xxx") or die("Could not connect.");

if(!$db)

die("no db");

if(!mysql_select_db("db70481_mlkishigo",$db))

	die("No database selected.");


//$row = 0;
if (($handle = fopen('http://mlkishigo.com/sageonline/inventorylist.csv', "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 900000, ",")) !== FALSE) {
        $num = count($data);


$result = mysql_query("SELECT * FROM inventory WHERE itemNumber=".$data[0]);
if ($result) {
  $recordCount = mysql_num_rows($result);

  echo "$recordCount Rows\n";

  if ($recordCount > 0) {
  // rest of your current code loop here.
  $sql1 = "UPDATE inventory SET itemNumber='$data[0]',itemDesc='$data[1]',quantityHand='$data[2]',category='$data[3]',Whse='$data[4]'  WHERE itemNumber='$data[0]' ";
  mysql_query($sql1) or die(mysql_error());

  } else {
    // your current code.
    $sql="INSERT into inventory(itemNumber,itemDesc,quantityHand,category,Whse) values('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]')";
    mysql_query($sql) or die(mysql_error());
  }
} else {
  die(mysql_error());
}



    }
    fclose($handle);


        }
?>

Link to comment
Share on other sites

I editted your post to take out your pw's, and here I reformatted your code a little bit, and changed the mysql_error() call in the loop to echo rather than die in the outer loop.  I don't see anything overtly incorrect here.  Are you getting any errors?  What is the output?

 


session_start();
//db connection
$db = mysql_connect("xxx", "xxx", "xxx") or die("Could not connect.");
if(!$db)
die("no db");

if(!mysql_select_db("db70481_mlkishigo",$db))
die("No database selected.");

//$row = 0;
if (($handle = fopen('http://mlkishigo.com/sageonline/inventorylist.csv', "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 900000, ",")) !== FALSE) {
        $num = count($data);
	$result = mysql_query("SELECT * FROM inventory WHERE itemNumber=".$data[0]);
	if ($result) {
		$recordCount = mysql_num_rows($result);
		echo "$recordCount Rows\n";
		if ($recordCount > 0) {
			// rest of your current code loop here.
			$sql1 = "UPDATE inventory SET itemNumber='$data[0]',itemDesc='$data[1]',quantityHand='$data[2]',category='$data[3]',Whse='$data[4]' WHERE itemNumber='$data[0]'";
			mysql_query($sql1) or die(mysql_error());
		} else {
			// your current code.
			$sql="INSERT into inventory(itemNumber,itemDesc,quantityHand,category,Whse) values ('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]')";
			mysql_query($sql) or die(mysql_error());
		}
	} else {
		echo mysql_error();
	}
    }
    fclose($handle);
}
?>

Link to comment
Share on other sites

Right at the top under the line:

 

while (($data = fgetcsv($handle, 900000, ",")) !== FALSE) {

 

Add

 

var_dump($data);

 

So you can see if the $data array elements match up to what you expect.  I'm assuming that if the problem you pointed out above is related to data, it's that when you use itemNumber=, if itemNumber is a numeric data type as defined in your database, then it expects a number.  '2X' would not be a number.  If it's a character type like a varchar, then you need the quotes around it for the sql to be valid.

 

$result = mysql_query("SELECT * FROM inventory WHERE itemNumber='$data[0]'");

 

Link to comment
Share on other sites

Right at the top under the line:

 

while (($data = fgetcsv($handle, 900000, ",")) !== FALSE) {

 

Add

 

var_dump($data);

 

So you can see if the $data array elements match up to what you expect.  I'm assuming that if the problem you pointed out above is related to data, it's that when you use itemNumber=, if itemNumber is a numeric data type as defined in your database, then it expects a number.  '2X' would not be a number.  If it's a character type like a varchar, then you need the quotes around it for the sql to be valid.

 

$result = mysql_query("SELECT * FROM inventory WHERE itemNumber='$data[0]'");

 

 

After adding  var_dump($data);. i added two two to '$data[0]'" still is not updating or inserting a new row, here is my code below.

 

if (($handle = fopen('http://mlkishigo.com/sageonline/inventorylist.csv', "r")) !== FALSE) {

    while (($data = fgetcsv($handle, 900000, ",")) !== FALSE) {
    	//var_dump($data);
        $num = count($data);
	$result = mysql_query("SELECT * FROM inventory WHERE itemNumber='$data[0]'");
	if ($result) {
		$recordCount = mysql_num_rows($result);
		echo "$recordCount Rows\n";
		if ($recordCount > 0) {
			// rest of your current code loop here.
			$sql1 = "UPDATE inventory SET itemNumber='$data[0]',itemDesc='$data[1]',quantityHand='$data[2]',category='$data[3]',Whse='$data[4]' WHERE itemNumber='$data[0]'";
			mysql_query($sql1) or die(mysql_error());
		} else {
			// your current code.
			$sql="INSERT into inventory(itemNumber,itemDesc,quantityHand,category,Whse) values ('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]')";
			mysql_query($sql) or die(mysql_error());
		}
	} else {
		echo mysql_error();
	}
    }
    fclose($handle);
}
?>

Link to comment
Share on other sites

 

 

Here is what i have, and anytime the value of quantityHand changes, it will insert all the rows again into the mysql, making the mysql to have double row of the same record.

 


if (($handle = fopen('http://mlkishigo.com/sageonline/inventorylist.csv', "r")) !== FALSE) {

    while (($data = fgetcsv($handle, 900000, ",")) !== FALSE) {
    	var_dump($data);
        $num = count($data);
	$result = mysql_query("SELECT * FROM inventory WHERE itemNumber='$data[0]' AND itemDesc='$data[1]'AND quantityHand='$data[2]' AND category='$data[3]' AND Whse='$data[4]'");
	if ($result) {
		$recordCount = mysql_num_rows($result);
		//echo "$recordCount Rows\n";
		if ($recordCount > 0) {
			// rest of your current code loop here.
			$sql1 = "UPDATE inventory SET itemNumber='$data[0]',itemDesc='$data[1]',quantityHand='$data[2]',category='$data[3]',Whse='$data[4]' WHERE itemNumber='$data[0]' AND itemDesc='$data[1]'AND quantityHand='$data[2]' AND category='$data[3]'AND Whse='$data[4]'";
			mysql_query($sql1) or die(mysql_error());
		} else {
			// your current code.
			$sql="INSERT into inventory(itemNumber,itemDesc,quantityHand,category,Whse) values ('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]')";
			mysql_query($sql) or die(mysql_error());
		}
	} else {
		echo mysql_error();
	}
    }
    fclose($handle);
}
?>

Link to comment
Share on other sites

In your WHERE clause, if there is some combination of columns that can be used to uniquely identify one row from another, then you should use that, omitting the 'AND quantityHand='$data[2]' from the SELECT since it seems that is not what you want.  With that said, it seems like this question is morphing, and that we've solved your initial issues.  If you're now onto some new problem, please make a new thread with a new title that better reflects what your struggling with, and mark this one solved.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.