Jump to content

Archived

This topic is now archived and is closed to further replies.

tobitimac

insert and update row in mysql

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);

        }
?>

Share this post


Link to post
Share on other sites

Your update syntax is wrong. 

 

It should be UPDATE inventory SET itemNumber='$data[1]', itemDesc='$data[2]', .... WHERE...

Share this post


Link to post
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);

        }
?>

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites

Am not so sure how you want to do it am really new to PHP.. Can you help with this, its really frustrating.

Share this post


Link to post
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());
}

Share this post


Link to post
Share on other sites

Hi,

 

I used the script but its not updating or inserting new records. What can i do now???

Share this post


Link to post
Share on other sites

Let's see the current version of your script.

Share this post


Link to post
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());
}

Share this post


Link to post
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.

Share this post


Link to post
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);


        }
?>

Share this post


Link to post
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);
}
?>

Share this post


Link to post
Share on other sites

 

i used the new code but am getting this error. Unknown column '2X' in 'where clause'

 

i believe its has to do with

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

Share this post


Link to post
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]'");

 

Share this post


Link to post
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);
}
?>

Share this post


Link to post
Share on other sites

I actually wanted you to run the var_dump and paste the results here so we can see what the data array contains.

Share this post


Link to post
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);
}
?>

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites

×
×
  • 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.