Page 1 of 1

php inserting into mysql table tricky question

Posted: Fri Feb 17, 2006 3:16 pm
by jasondavis
This has been driving me nuts for 2 weeks now, the below code works 95% of the time the other 5% of the time
this part "// add payed users" doesnt get inserted anyone can think of why it wouldn't more importanly how to resolve this

Code: Select all

if ($uscnt[0]>=$users_in_train)
		{
			mysql_query("insert into trains (intId) values ('')");
			$trainid[0]++;

			// add payed users
			$res = mysql_query("select intMSId from users where intPriority>0");
			while ($arr = mysql_fetch_array($res))
			{
				mysql_query("insert into aboard (intTrainId, intMSUserId) values ($trainid[0], $arr[0])");
			}
		}
here is the full code

Code: Select all

if (isset($_POST[Add]))
{
	// check if id is integer
	if (!is_numeric($_POST[intMSUserId]))
		$error = "Invalid MySpace User ID";

	if (is_blocked($_POST[intMSUserId]))
		$error = "Your User ID has been blocked from joining the train.";

	if ($error =="")
	{
		// get latest train id
		$res = mysql_query("select MAX(intId) from trains");
		$trainid = mysql_fetch_array($res);

		// check users in the train
		$res = mysql_query("select COUNT(*) from aboard where intTrainId='$trainid[0]'");
		$uscnt = mysql_fetch_array($res);

		if ($uscnt[0]>=$users_in_train)
		{
			mysql_query("insert into trains (intId) values ('')");
			$trainid[0]++;

			// add payed users
			$res = mysql_query("select intMSId from users where intPriority>0");
			while ($arr = mysql_fetch_array($res))
			{
				mysql_query("insert into aboard (intTrainId, intMSUserId) values ($trainid[0], $arr[0])");
			}
		}

		// check if id is already in train
		$res = mysql_query("select * from aboard where intTrainId=$trainid[0] and intMSUserId='$_POST[intMSUserId]'");
		$intrain = mysql_fetch_array($res);
		if ($intrain[0] == "")
		{
			// check if user data is loaded from myspace
			//$res = mysql_query("select * from users where intMSId='$_POST[intMSUserId]'");
			//$msuser = mysql_fetch_array($res);
			// load data from myspace
			//if ($msuser[0] == "")
			//{
				// http://profile.myspace.com/index.cfm?fu ... dID=426705
				$html = 	file_get_contents("http://profile.myspace.com/index.cfm?fuseaction=user.viewprofile&friendID=$_POST[intMSUserId]");
				preg_match("/<span class=\"nametext\">(.*)<\/span>/", $html, $msname);
				preg_match("/<td class=\"text\" width=\"193\" bgcolor=\"#ffffff\" height=\"75\" align=\"left\">\"(.*)\"<br>/", $html, 	$msdata);
				preg_match("/has <span class=\"redbtext\">(\d+)<\/span> friends/", $html, $msfc);
				preg_match("/(\d+) years old/", $html, $msyo);
				preg_match("/ctl00_Main_ctl00_.*<img src=\"(.*)\" border=\"0\" \/><\/a>/", $html, $msimg);
                preg_match('#<\s*br(?:\s*/)?>\s*((?:fe)?male)\s*<\s*br(?:\s*/)?>#is',$html,$sex);
				
				$msdata[1] = mysql_escape_string($msdata[1]);
				$msname[1] = mysql_escape_string($msname[1]);

				if ($msname[1] != "")
				{
					mysql_query("delete from users where intMSId='$_POST[intMSUserId]'");
					$res = mysql_query("insert into users (intMSId, txtMSName, intMSAge, intMSFriends, txtMSText, txtMSImg, sex) values ('$_POST[intMSUserId]', '$msname[1]', '$msyo[1]', '$msfc[1]', '$msdata[1]', '$msimg[1]', '$sex[1]')");
			
					$res = mysql_query("select * from users where intMSId='$_POST[intMSUserId]'");
					$msuser = mysql_fetch_array($res);
				} else
				{
					$error = "User doesnt exists";
				}
			//}
			// add user into train
			if ($error == "")
			{
				mysql_query("insert into aboard (intTrainId, intMSUserId) values ($trainid[0], $_POST[intMSUserId])");
				setcookie("usermysid", $_POST[intMSUserId], time()+60*60*24*30);


			}
		} else
		{
			// already in the train
			$error = "You are already in this train";
		}
	} else
	{
		//$error = "Invalid MySpace User ID";
	}
}

Posted: Fri Feb 17, 2006 3:44 pm
by RobertGonzalez
Error check the part you are having problems with. If you don't, you'll never know what is happening.

Code: Select all

<?php
            // add payed users
            if ( !$res = mysql_query("select intMSId from users where intPriority>0") )
            {
                die("There was a problem: " . mysql_error());
            }
            else
            {
                while ($arr = mysql_fetch_array($res))
                {
                    mysql_query("insert into aboard (intTrainId, intMSUserId) values ($trainid[0], $arr[0])");
                } 
            }
?>

Posted: Fri Feb 17, 2006 4:13 pm
by jasondavis
thanks I will try that unfortunately it is hard to test because it isnt a constant problem usually like once every 20 minutes just guessing

Posted: Fri Feb 17, 2006 5:18 pm
by RobertGonzalez
I am an absolute nut when it comes to error checking. Especially if there are multiple queries that need to be run. I find it useful to error check every query to make sure your SELECTS, INSERTS, UPDATES and DELETES all work the way they are supposed to. This does add overhead to your scripts (a little bit) but in my opinion the trade-off is worth it to keep the site running smooth and to keep the data clean.

Posted: Fri Feb 17, 2006 5:35 pm
by jasondavis
I just had an issue with the new code inserted, it still didnt add the ppl in but unfortuantle I am unbale to see an error so somebody else might of seen the error for a slpit second, see there are a lot of people hitting this script at the same time could this cause it to skip that part? Also is there a way to log the error to a log so then if somebody does get it to show I can view it

Posted: Fri Feb 17, 2006 5:51 pm
by RobertGonzalez
Sorry, forgot to add in a second error checker...

Code: Select all

<?php
            // add payed users
            if ( !$res = mysql_query("select intMSId from users where intPriority>0") )
            {
                die("There was a problem: " . mysql_error());
            }
            else
            {
                while ($arr = mysql_fetch_array($res))
                {
                    if ( !mysql_query("insert into aboard (intTrainId, intMSUserId) values ($trainid[0], $arr[0])") )
                    {
                        die("Could not perform insert: " . mysql_error());
                    }
                } 
            }
?>
If the insert is what is not happening then that is where the problem lies. I would guess (this is just an off the cuff assumption) that the script is trying to insert data that MySQL doesn't like. See what comes of this error checker.

Posted: Fri Feb 17, 2006 6:00 pm
by jerry987
you guys seem smart, can someone help me:

Please see the new post PHP Date

:roll:

Posted: Fri Feb 17, 2006 6:00 pm
by jasondavis
ok the problem with the error checker is that more than likely someone else will be the one to see it, see it add 50 people to the train then starts over so I would have to be like the 51st person to see the error, so is there a way to log the error to a file somewhere?

Posted: Fri Feb 17, 2006 6:05 pm
by RobertGonzalez
jerry987 wrote:you guys seem smart, can someone help me:

Please see the new post PHP Date

:roll:
Try not to cross post. That will get in you in deep soup really fast. I am sure your post will get the same attention as anyone else's (in fact I think you already have a response to it).

Just a heads up to keep things friendly around here.

Posted: Fri Feb 17, 2006 6:07 pm
by RobertGonzalez
jasondavis wrote:ok the problem with the error checker is that more than likely someone else will be the one to see it, see it add 50 people to the train then starts over so I would have to be like the 51st person to see the error, so is there a way to log the error to a file somewhere?
I suppose you could. Instead of putting a call to die() for the insert query insert a small script to write the mysql_error output to a file on your server. It might slow things down a little bit, but at least you would be able to capture your error outputs.

Posted: Fri Feb 17, 2006 6:16 pm
by jasondavis
I appreciate your help

Code: Select all

// add payed users 
            if ( !$res = mysql_query("select intMSId from users where intPriority>0") ) 
            { 
                die("There was a problem: " . mysql_error()); 
            } 
            else 
            { 
                while ($arr = mysql_fetch_array($res)) 
                { 
                    if ( !mysql_query("insert into aboard (intTrainId, intMSUserId) values ($trainid[0], $arr[0])") ) 
                    { 
							$fp = fopen("mysqlerror.txt", "a") or die("Could not open mysqlerror.txt");
							fwrite($fp, "[" . date("M-d-Y H:i:s") . "]  mysql_error();
							fclose($fp);
                    } 
                }  
            }
the 5th line up starting from the bottom going up, does that look correct around the error part or would I need some sort of quotes?

Posted: Fri Feb 17, 2006 6:26 pm
by RobertGonzalez
Check out that same line modified a little bit here...

Code: Select all

<?php
            // add payed users 
            if ( !$res = mysql_query("select intMSId from users where intPriority>0") ) 
            { 
                die("There was a problem: " . mysql_error()); 
            } 
            else 
            { 
                while ($arr = mysql_fetch_array($res)) 
                { 
                    if ( !mysql_query("insert into aboard (intTrainId, intMSUserId) values ($trainid[0], $arr[0])") ) 
                    { 
							$fp = fopen("mysqlerror.txt", "a") or die("Could not open mysqlerror.txt");
							fwrite($fp, "[" . date("M-d-Y H:i:s") . "] " . mysql_error());
							fclose($fp);
                    } 
                }  
            } 
?>
Just needed a little cleaning up. It should do the trick now.