searching and updateing mysql

darkness

I want to search to see if a field is in a table and if it is update the row but if its not in the table then i want it to add a new row. Ive tried various different things but cant get it to work.

mysql_query("SELECT value FROM counter WHERE page='$page'") or die ("Invalid query");

$query = "UPDATE counter SET value = '$value+1' WHERE page = '$page'";
$result = mysql_query( $query );
if ( ! $result )
{
mysql_query("INSERT INTO counter ( page, value ) values ('$page', '1')");
echo "Inserting $page";
}

and,

mysql_query("SELECT value FROM counter WHERE page='$page'") or die ("Invalid query");

mysql_query("UPDATE counter SET value = '$value+1' WHERE page = '$page'");

if(mysql_affected_rows() == 0)

{
mysql_query("INSERT INTO counter ( page, value ) values ('$page', '1')");
echo "Inserting $page";
}

are 2 ive tried, the first 1 the screen just stays blank and nothing seems to happen in the db and the second it keeps adding rows to the db.

Does any1 have any ides?

cgraz

Looking at your first attempt. . .

// First I'd echo $page to make sure there is a value for it
echo "Page equals " . $page;

// if that outputs a value, then continue on
$select = mysql_query("SELECT value FROM counter WHERE page='$page'"); 

// You need to grab $value out of the database before you can start adding 1 to it
$a_row = mysql_fetch_array($select);

$value = $a_row["value"];

$query = "UPDATE counter SET value = '$value+1' WHERE page='$page'"; 
$result = mysql_query($query); 

if (!$result) { 
   mysql_query("INSERT INTO counter (page, value) values ('$page', '1')"); 
   echo "Inserting $page"; 
}

My guess is $value didn't have a value assigned to it, that's why the database appeared to not be updating. It's hard to tell without testing, so give it a try and let me know if that solves the problem.

Cgraz

nnichols

To minimize on the database overhead I would modify cgraz's solution slightly -

/*it's a minor change but significant if you have large volumes of
traffic.  Make the most of your db - if you can run one query
instead of two, then do it.*/

/*SET value to (value + 1) instead of retrieving the value
and incrementing it in php and then setting it*/
$query = "UPDATE counter SET value = value+1 WHERE page='$page'"; 
$result = mysql_query($query); 

//if could not update do insert
if (!$result) { 
	mysql_query("INSERT INTO counter (page, value) values ('$page', '1')"); 
}

Hope this helps 😉

darkness

well i tried both ideas and nothing is displayed on the screen, its as if its updateing the db but when i check the db there is no row for it to update.

nnichols

Post the code you are trying along with your db structure again and put the code between

 tags and I'll have another look.

darkness

mysql_query("SELECT value FROM counter WHERE page='$page'") or die ("Invalid query"); 


$query = "UPDATE counter SET value = '$value+1' WHERE page = '$page'"; 
$result = mysql_query( $query ); 
if ( ! $result ) 
{ 
mysql_query("INSERT INTO counter ( page, value ) values ('$page', '1')"); 
echo "Inserting $page"; 
}

and

mysql_query("SELECT value FROM counter WHERE page='$page'") or die ("Invalid query"); 

mysql_query("UPDATE counter SET value = '$value+1' WHERE page = '$page'"); 

if(mysql_affected_rows() == 0) 

{ 
mysql_query("INSERT INTO counter ( page, value ) values ('$page', '1')"); 
echo "Inserting $page"; 
}

Thats the code i had, ive tried a few other was as well the ones you 2 suggested.

DB structue:

nnichols

Try the following code and see what output it gives -

//check that your script has a value for $page
echo 'Page is "' . $page . '"<br><br>';

$query = "UPDATE counter SET value=value+1 WHERE page='$page'";
$result = mysql_query($query);

//if could not update do insert
if (mysql_affected_rows() == 0) {
	echo 'UPDATE failed!<br>attempting INSERT ...<br>';
	mysql_query("INSERT INTO counter (page, value) VALUES ('$page', 1)");
	if (mysql_affected_rows() == 0) {
		echo 'INSERT failed<br><br>';
	}
	else {
		echo 'INSERT successful<br><br>';
	}
}

else {
	echo 'UPDATE successful';
}

darkness

well i tried that code and it keeps saying:

Page is "/~darkness/Alpha/c.php"

UPDATE failed!
attempting INSERT ...
INSERT successful

it says INSERT successful everytime although it only gets added once, probably because the page column is the primary key so the value cant be the same

nnichols

The only problem I can think of is with the database. I have created this script on my local machine and it works fine.

Check the length of the field that you are using to store 'page'. If this is too short then it would cause the results you are getting.

Hope this helps 😉