[SOLVED] If row exists, then update

[nloding]

In short, if a row exists, I want to update it ... if doesn't, I want to insert a row.  Here's my code that doesn't work:

 

<?php
// Check to see if item is already in the basket to update it's quantity

$query = "SELECT count(*) FROM basket WHERE pID=".$item;
$result = mysql_query($query);

     while($row = mysql_fetch_assoc($result)) {
     	if($row >= 1) {
     		$exists = true;
     	}
     }

if(!exists) {	
	$query = "INSERT INTO basket (pID, qty, price) VALUES ('$item', '$qty', '$price')";
	$result = mysql_query($query);
	if($result) echo "success 2";
} else {
	$query = "UPDATE basket SET qty='".$qty."', price='".$qty*$price."' WHERE pID=".$item;
	$result = mysql_query($query);
	if($result) echo "success 3";
}
?>

 

"success 3" gets echo'd, not "success 2" ... because my table is currently completely empty.  Perhaps that is why the query doesn't work?  I've tried the query above, adding "as numrecords" after the count(*) part, but that didn't do anything.  I've added mysql_error() whatnots after every query and they don't error out.  I just need to test if that particular row exists ... I've also tried testing if $result exists, or if it's > 1, or < 0, same with $row using fetch_assoc.  I'm lost.  I googled and every solution I found didn't work.

 

And there are only three columns: pID, qty, and price.  pID is unique.  The rest of my script works fine ... just this part doesn't!

[marcus]

You want to do this:

 

$query = "your query";
$result = mysql_query($query) or die(mysql_error());
$num = mysql_num_rows($result);

if($num >= 1){
//query to update
}else {
//query to insert
}

[roopurt18]

<?php
  $query = "SELECT count(*) FROM basket WHERE pID=".$item;
  $result = mysql_query($query);

  while($row = mysql_fetch_assoc($result)){
    // $row will be an array after the line above, so checking
    // $row >= 1 is like saying Array() >= 1 which doesn't make any logical
    // sense.
    if($row >= 1) {
      $exists = true;
    }
  }
?>

 

Make a small change to your query and your test and it'll work fine:

<?php
  // Add the 'AS Num' to your query
  $query = "SELECT count(*) AS Num FROM basket WHERE pID=".$item;
  $result = mysql_query($query);

  // Assume non-existence
  $Exists = false;
  // Check if the query was successful
  if($result){
    $row = mysql_fetch_assoc($result);
    // Check for existence
    $Exists = is_array($row) && count($row) && $row['Num'] > 0;
  }

  // Rest of code follows
  if($Exists){
    // Update
  }else{
    // Insert
  }
?>

 

It's worth pointing out that if you are using keys and have the proper version of MySQL that you can just use an INSERT ... UPDATE query.

 

It's also worth pointing out that you don't necessarily have to check if the record exists before hand.  Just try and do an UPDATE, if it fails you know the record must not exist and that you need to do an INSERT.

[nloding]

Thank you both for replying, but I found my mistake elsewhere, and I feel DUMB ...

 

if(!exists) ............ shouldn't that be if(!$exists) ..... BAH!

 

All that time, and it wasn't even my query that was failing!

 

I'm aware of the INSERT ... UPDATE and I can do that, but this was supposed to just be a really quick example of Ajax updating a shopping cart ... that, I'm still learning the more advanced features of MySQL.  Anyhow ... thank you both!