Cannot get mysql to insert

FeelingStupid

I am having a real problem here and have tried multiple ways to code this, but nothing is working.. I am working on a small facebook app for a non-profit and the very first thing I need to do on the index page is to pull the facebook uid into my database. (Oh, I am using PHP4 and MySQL 4.1.18)

I have created the following code:

$facebook = new Facebook($api_key, $secret);
$facebook->require_frame();
$user = $facebook->require_login();

if ($action == "ADD") {
	$sql = 'SELECT COUNT(userID) FROM `table1` WHERE userID=$user'; 
	mysql_select_db($database, $connection);
	$result=mysql_query($sql, $connection) or die(mysql_error());
	$theCount=$result[0];

if ($theCount == 0) {
$insertSQL="INSERT INTO `table1`(userID) VALUES($user)";
mysql_select_db($database, $connection);
$result2=mysql_query($insertSQL, $connection) or die(mysql_error());
	}

if ($theCount == 1) {
$updateSQL="UPDATE `table1` SET money=money+50 WHERE userID=$user";		
mysql_select_db($database, $connection);
$result3=mysql_query($updateSQL, $connection) or die(mysql_error());
}
}

Now, just so you understand the different things I have done and tested, I have tried if and else instead of both if statements for count. When I had the UPDATE set as "else" and I removed the WHERE part, it did do the update in my database and gave all rows 50 more money, so I know my connection is working well. And then if I add a redirect at the bottom of the page such as this

$facebook->redirect("$canvas_page"."main.php?userID=$user");

it would redirect and show the userID in the URL and everything so I know the $user variable is working, but would not add the userID to the database.

Should I be using if and Else or what can I do to make this work? Facebook does allow us to store uid so that shouldn't be a problem. I am just learning php and mysql (although I do have experience with CF and MSSQL so I'm not new to coding) but I took on this small "should be simple" app for facebook as an effort to learn php and mysql and its not going very well at all. 😕

Thanks for any suggestions, direction, or help!

FeelingStupid

NOTE: the missing $ in the variable $theCount

if (theCount == 1) {

was an error when copying over the code - it is in the original.

FeelingStupid

Ok, I found something in another thread that may work for me, but I am not sure how to do it..
The thread is http://www.phpbuilder.com/board/showthread.php?t=10348311&highlight=Insert

and the answer given by one of the mods is this:

tomhath wrote:
There's no reason to read all the existing appointments into php variables.

All you need to do is create $newdate, then see if it exists in your table by executing a SELECT with it.

But a much better way would be to create $newdate and try to insert it. Make sure you have a UNIQUE KEY on the table so the insert will fail if the date already exists. Check for an error and try again with another date when it happens.
Why is the second way better? Otherwise you would need to wrap the check for an existing appointment and the insert in a transaction to ensure you wouldn't get duplicates.

So....if I make my userID column unique (which it is) and I just create an INSERT, (instead of counting when I know there is only going to be 0 or 1) which will fail if there is already that userID in my table how can I stop an error or from it showing "duplicate userID" but instead just continuing with the rest of my app? Is that possible? If so, that would be great!!!

FeelingStupid

I SOLVED THE PROBLEM!!!! Thanks to all the great people out there who answered other people's questions, I really got to thinking about a lot of different issues and answers and I ran some tests and found that by reducing the crap I was trying to do into just a tiny little bit of code, it works PERFECTLY!!!! You see the first post which is what my code DID look like, now here is what it DOES look like and it works perfectly!!

$facebook = new Facebook($api_key, $secret);
	$facebook->require_frame();
	$user = $facebook->require_login();

$insertSQL = "INSERT INTO table1(userID) VALUES($user) ON DUPLICATE KEY UPDATE money=money+50";
	mysql_select_db($database, $connection);
	$result= mysql_query($insertSQL, $connection);

I can't believe thats all there is to it!

Not feeling so stupid anymore....

laserlight

Good to see you solved the problem 🙂
Remember to mark this thread as resolved using the thread tools.