php inserting into mysql table tricky question

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
jasondavis
Forum Commoner
Posts: 60
Joined: Sat Feb 04, 2006 5:35 pm

php inserting into mysql table tricky question

Post 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";
	}
}
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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])");
                } 
            }
?>
jasondavis
Forum Commoner
Posts: 60
Joined: Sat Feb 04, 2006 5:35 pm

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
jasondavis
Forum Commoner
Posts: 60
Joined: Sat Feb 04, 2006 5:35 pm

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
jerry987
Forum Newbie
Posts: 6
Joined: Fri Feb 17, 2006 5:35 pm

Post by jerry987 »

you guys seem smart, can someone help me:

Please see the new post PHP Date

:roll:
jasondavis
Forum Commoner
Posts: 60
Joined: Sat Feb 04, 2006 5:35 pm

Post 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?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
jasondavis
Forum Commoner
Posts: 60
Joined: Sat Feb 04, 2006 5:35 pm

Post 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?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
Post Reply