PHP/MySQL problem from hell

PHP/Mysql webgame

  • (2 Pages)
  • +
  • 1
  • 2

17 Replies - 3012 Views - Last Post: 18 March 2009 - 12:01 AM Rate Topic: -----

#1 Rikarus  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 22
  • Joined: 11-March 09

PHP/MySQL problem from hell

Post icon  Posted 11 March 2009 - 08:22 PM

Hi there, it is my first time posting on these forums, everyone here seems so nice! :D

I've been lurking and learning and developing this stuff, but I can't seem to figure out a few things with this stuff.

I am trying to make a simple webgame. There are 3 units and 2 resources. The units are Recruits, Chefs, and Miners. The resources are food and ore. Every 5 minutes each Chef produces 1 food, and each Miner produces 1 ore. A recruit may be purchased for 5 food, and a recruit can be turned into a Chef or a Miner.

I'm going to post my code first, then ask my questions


member.php
<?php
session_start();
if($_SESSION['s_logged_n'] == 'true'){
include 'process.php';
include 'config.php';
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Welcome to Kombat Kingdom!</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="style.css" rel="stylesheet" type="text/css">
</head>

<body>

<div id="wrapper">
<div id="head">Kombat Kingdom</div><br>
<div id="container">
<?php 
include 'menus.php' 
?>
</div>
<div id="spacer">&nbsp;</div>
<div id="memmain">

<?php
	$username = $_SESSION['s_username'];

	$miners = mysql_query("
	SELECT miners 
	FROM Users
	WHERE Username='$username' 
	LIMIT 1");		

	$chefs = mysql_query("SELECT chefs FROM Users
	WHERE Username='$username'");

	$ore = mysql_query("SELECT ore FROM Users
	WHERE Username='$username'");			

	$food = mysql_query("SELECT food FROM Users
	WHERE Username='$username'");		

	$recruits = mysql_query("SELECT recruits FROM Users
	WHERE Username='$username'");

echo '<p> Welcome to Nerdworks, this is the beta for Kombat Kingdom! It is still a work in progress, so if something does not work it will either be working soon or we has no clue it is broken! Please contact admin@nerdworks.info if anything is malfunctioning <br>Thanks,<br> Frodo</p>';
switch($_GET['change']){
	case 'Hire+Chef':
	{
		if($recruits > 0)
		{
		$recruits = $recruits - 1;		
		$chefs=$chefs+1;
		mysql_query ("UPDATE Users
		SET chefs = '$chefs'
		WHERE id='$id'");
		mysql_query ("CREATE EVENT $chefs ON SCHEDULE EVERY 5 MINUTES DO 
		$food = $food+1;
		UPDATE Users SET food = '$food' 
		WHERE id='$id'");
		echo '<p>You have successfully hired 1 chef.</p>';
		}
		else
		{
		echo '<p>Sorry, you need atleast one recruit!</p>';
		}
		break;
	}
	case 'Hire+Miner':
	{
		if($recruits > 0)
		{
		$recruits = $recruits - 1;		
		$chefs=$chefs+1;
		mysql_query ("UPDATE Users
		SET chefs = '$chefs'
		WHERE id='$id'");
		mysql_query ("CREATE EVENT $chefs ON SCHEDULE EVERY 5 MINUTES DO 
		$food = $food+1;
		UPDATE Users SET food = '$food' 
		WHERE id='$id'");
		echo '<p>You have successfully hired 1 miner.</p>';
		}
		else
		{
		echo '<p>Sorry, you need atleast one recruit!</p>';
		}
		break;
	}
	case 'Hire+Recruit':
	{
		if($recruits > 0)
		{
		$recruits = $recruits - 1;		
		$chefs=$chefs+1;
		mysql_query ("UPDATE Users
		SET chefs = '$chefs'
		WHERE id='$id'");
		mysql_query ("CREATE EVENT $chefs ON SCHEDULE EVERY 5 MINUTES DO 
		$food = $food+1;
		UPDATE Users SET food = '$food' 
		WHERE id='$id'");
		echo '<p>You have successfully hired 1 recruit.</p>';
		}
		else
		{
		echo '<p>Sorry, you need atleast one recruit!</p>';
		}
		break;
	}
	case 'password':
	{
		echo '<form action="'.$_SERVER['PHP_SELF'].'" method="post">
		<p>Current password:<br>
		<input name="current" type="password" class="textBox"></p>
		<p>New password:<br>
		<input name="new" type="password" class="textBox"></p>
		<p>Confirm new password:<br>
		<input name="confirm" type="password" class="textBox"></p>
		<p><input type="submit" value="Change Password" name="changepassword"></p>
		</form>
		';
		
		break;
	}	
	case 'email':
	{
		echo '<p>
		WARNING! - By changing your email address you will have to re-validate. Make sure you email address is 100% correct.</p>
		<form action="'.$_SERVER['PHP_SELF'].'" method="post">
		<p>Current email:<br>
		<input name="current" type="text" class="textBox"></p>
		<p>New email:<br>
		<input name="new" type="text" class="textBox"></p>
		<p>Confirm new email:<br>
		<input name="confirm" type="text" class="textBox"></p>
		<p><input type="submit" value="Change Email" name="changeemail"></p>
		</form>
		';
		
		break;
	}	
	case 'account':
	{
		echo '<p>
		WARNING - By closing your account, you will not be able to login again under this account. Press the button below to confirm you wish to close your account</p>
		<form action="'.$_SERVER['PHP_SELF'].'" method="post">
		<p><input type="submit" value="Close Account" name="closeaccount"></p>
		</form>
		';
		
		break;
	}	
	case 'play':
	{
		echo '<p>You have '; 
		echo $miners;
		echo ' miners
			<img src="miner.gif">
			<form>
			<input type = "submit" value = "Hire Miner" name = "change" action="member.php" method="post" ></p>
			</form>';	
		echo '<p>You have '; 
		echo $chefs;
		echo ' chefs
			<img src="chef.gif">
			<form>
			<input type = "submit" value = "Hire Chef" name = "change" action="member.php" method="post" ></p>
			</form>';

		echo '<p>You have '; 
		echo $recruits;
		echo ' recruits';
		echo '	<img src="recruit.gif">
			<form>
			<input type = "submit" value = "Hire Recruit" name = "change" action="member.php" method="post" ></p>
			</form>';
			echo'<p><img src="food.bmp">'; 
			echo $food;
			echo '<p><img src="ore.bmp">';
			echo $ore;
		break;
	}
	default:
	{
	}	
}}

?>

</div>

</body>
</html>



menus.php
<?php session_start(); 

if($_GET['set'] > 0)
{
	
	if($_SESSION['whattoclose'] == NULL)
	{
	
		$_SESSION['whattoclose'] = $_GET['set'];
		
	} else {
	
		$_SESSION['whattoclose'] = $_SESSION['whattoclose'] . '|' . $_GET['set'];
		
	}

}

if($_GET['undo'] > 0)
{
	
	if(!empty($_SESSION['whattoclose'])){
	
		$_SESSION['whattoclose'] = str_replace("|".$_GET['undo'] , "" , $_SESSION['whattoclose']);
		$_SESSION['whattoclose'] = str_replace($_GET['undo'] , "" , $_SESSION['whattoclose']);

	}

}

function showTitle($id)
{

	$title = array(
		"Navigation",
		"Login information",
		);
		
	$data = $_SESSION['whattoclose'];

	$not = explode( "|" , $data );
	if(!in_array( $id , $not ))
	{
		
		echo '<a href="member.php?set='.$id.'">close</a> - ';
		echo $title[$id-1];
			
	} else {
		
		echo '<a href="member.php?undo='.$id.'">open</a> - ';
		echo $title[$id-1];

	}		

}

function checkIt($id)
{

	$data = array(
		'<p><a href="logout.php">Logout</a></p>
		 <p><a href="member.php?change=password">Change Password</a></p>
		 <p><a href="member.php?change=email">Change Email</a></p>
		 <p><a href="member.php?change=account">Close account</a></p>
		 <p><a href="member.php?change=play">Click here to play!</a></p>
		 <p>Welcome to the members only page '.$_SESSION['s_name'].'!</p>
		<p>You are logged in as '.$_SESSION['s_username'].' from the IP address '.$_SERVER['REMOTE_ADDR'].'</p>',
		
		);
	
	$str = $_SESSION['whattoclose'];

	$not = explode( "|" , $str );
	if(!in_array( $id , $not ))
	{
	
		echo '<div class="table">';
		echo $data[$id-1];
		echo '</div>';
	
	}

}

?>

<div class="header">
<? showTitle(1) ?>
</div>
<? checkIt(1) ?>
<br>
<div class="header">
<? showTitle(2) ?>
</div>
<? checkIt(2) ?>




So those are the major portions of the code. If theres any other parts you need please let me know so I can post them!

So here are my problems, if anyone can help with one or two of them please post, even if you can't help with ALL of them.

1. When I load up members.php, it is supposed to display the variables from the MySql database, but instead it displays some jargon about "Resource id" instead of the values. I think it is because they aren't declaring properly, even though I declared them here:

	$username = $_SESSION['s_username'];

	$miners = mysql_query("
	SELECT miners 
	FROM Users
	WHERE Username='$username' 
	LIMIT 1");		

	$chefs = mysql_query("SELECT chefs FROM Users
	WHERE Username='$username'");

	$ore = mysql_query("SELECT ore FROM Users
	WHERE Username='$username'");			

	$food = mysql_query("SELECT food FROM Users
	WHERE Username='$username'");		

	$recruits = mysql_query("SELECT recruits FROM Users
	WHERE Username='$username'");



Other problem, when I click one of the unit hiring buttons, it leads me back to the post URL like it should, but it doesn't display the Successful message, and it isn't updating the value when I view the Sql database, leading me to believe it isnt triggering the case in the switch at all.

Please, PLEASE help me! I will love you forever if you help me! Thankyou!

~Frodo of nerdworks.info

Is This A Good Question/Topic? 0
  • +

#3 Valek  Icon User is offline

  • The Real Skynet
  • member icon

Reputation: 542
  • View blog
  • Posts: 1,713
  • Joined: 08-November 08

Re: PHP/MySQL problem from hell

Posted 11 March 2009 - 08:40 PM

I don't have a lot of time, but I can tell you you're getting the data from MySQL incorrectly. First, I'm going to consolidate your queries. Then, I'm going to show you a function taht will place those results into the variables you wanted to use. The following is a rewrite of your last code block:

$username = $_SESSION['s_username'];
//Runs the query to get all of the data.  If the fields listed are the only ones in the table, replace the list with an asterisk instead (*).
$result = mysql_query("SELECT miners, chefs, ore, food, recruits FROM Users WHERE Username='$username'");
//Fetch the data from the result resource and put it into an array named $row.
$row = mysql_fetch_assoc($result);
//Extract the data from $row, putting each field's data into a variable named after it (so you'll have $miners, $chefs, $ore, $food, and $recruits with the appropriate data applied).  EXTR_OVERWRITE tells it to overwrite the data if those variables have already been declared.
extract($row,EXTR_OVERWRITE);


If you have any questions, feel free to ask. If you're still having trouble when I get off work tomorrow, I'd be happy to assist you further :)
Was This Post Helpful? 1
  • +
  • -

#4 Rikarus  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 22
  • Joined: 11-March 09

Re: PHP/MySQL problem from hell

Posted 11 March 2009 - 08:46 PM

View PostValek, on 11 Mar, 2009 - 07:40 PM, said:

I don't have a lot of time, but I can tell you you're getting the data from MySQL incorrectly. First, I'm going to consolidate your queries. Then, I'm going to show you a function taht will place those results into the variables you wanted to use. The following is a rewrite of your last code block:

$username = $_SESSION['s_username'];
//Runs the query to get all of the data.  If the fields listed are the only ones in the table, replace the list with an asterisk instead (*).
$result = mysql_query("SELECT miners, chefs, ore, food, recruits FROM Users WHERE Username='$username'");
//Fetch the data from the result resource and put it into an array named $row.
$row = mysql_fetch_assoc($result);
//Extract the data from $row, putting each field's data into a variable named after it (so you'll have $miners, $chefs, $ore, $food, and $recruits with the appropriate data applied).  EXTR_OVERWRITE tells it to overwrite the data if those variables have already been declared.
extract($row,EXTR_OVERWRITE);


If you have any questions, feel free to ask. If you're still having trouble when I get off work tomorrow, I'd be happy to assist you further :)



Thankyou so much! I looked over the code you put there, researched a little bit on that "fetch_assoc" function (so I have a better understanding of what you're doing) it made sense, so I'm putting it in my website now. I knew that all those lines looked like I was brute forcing it!

$Thankyou=$Thankyou*10^9999999999999999999999999;
Was This Post Helpful? 0
  • +
  • -

#5 Valek  Icon User is offline

  • The Real Skynet
  • member icon

Reputation: 542
  • View blog
  • Posts: 1,713
  • Joined: 08-November 08

Re: PHP/MySQL problem from hell

Posted 11 March 2009 - 08:49 PM

Yeah, the code you had wouldn't cause a lot of issues, but it's unnecessary to run 4 or 5 queries when you can run one and get the same result. The 'resource ID' issue you were referring to was that you were trying to directly call information from the result resource without telling PHP how to process it, so it got confused.
Was This Post Helpful? 0
  • +
  • -

#6 Rikarus  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 22
  • Joined: 11-March 09

Re: PHP/MySQL problem from hell

Posted 11 March 2009 - 09:38 PM

I've got a whole new can of worms dealing with the MySQL schedule stuff.

So I'm using Mysql 4.0, because it it was the reccomended one for Godaddy, and I'm wondering if thie scheduler stuff is enabled/ if it can be enabled?

Also, can I do this part?

mysql_query
("CREATE EVENT $miners 
ON SCHEDULE
EVERY 5 MINUTES DO 
$food = $food+1;
UPDATE Users SET food = '$food' 
WHERE username='$username'
");



The part I'm concerned about is the "$food = $food + 1;" line, because it is a PHP function within a MySQL query, I'm not sure how to do this, because as of now it isnt doing the recurring event, and it isnt giving an error, So I am rather confused.

Any help at all would be appreciated, thanks much!
Was This Post Helpful? 0
  • +
  • -

#7 Valek  Icon User is offline

  • The Real Skynet
  • member icon

Reputation: 542
  • View blog
  • Posts: 1,713
  • Joined: 08-November 08

Re: PHP/MySQL problem from hell

Posted 12 March 2009 - 01:17 AM

Events were not supported until MySQL version 5.1.

If you are able, however, you might consider setting a cron job to handle it. You could have a script that runs a query for that, and you wouldn't even need to use the WHERE clause, as it would update all users at precisely the same time.
Was This Post Helpful? 0
  • +
  • -

#8 Auzzie  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 43
  • View blog
  • Posts: 573
  • Joined: 20-January 09

Re: PHP/MySQL problem from hell

Posted 12 March 2009 - 03:50 AM

and just to clarify a bit on Valek's post you might want to take a look at this cron job tutorial http://www.aota.net/...s/cronhelp.php3
Was This Post Helpful? 0
  • +
  • -

#9 Rikarus  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 22
  • Joined: 11-March 09

Re: PHP/MySQL problem from hell

Posted 12 March 2009 - 06:08 PM

Its just not my day eh?

Cron is for linux only, and my godaddy server is Windows. :(

Its really a shame godaddy doesn't just update to 5.1 or something. :((

Is there any way I can do cron jobs with windows?
Was This Post Helpful? 0
  • +
  • -

#10 Valek  Icon User is offline

  • The Real Skynet
  • member icon

Reputation: 542
  • View blog
  • Posts: 1,713
  • Joined: 08-November 08

Re: PHP/MySQL problem from hell

Posted 12 March 2009 - 09:37 PM

This is on Drupal's site, but has applications outside of that particular piece of software. Seeing as it's not your server, you might consider seeing if you can make one of these work.
Was This Post Helpful? 0
  • +
  • -

#11 Rikarus  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 22
  • Joined: 11-March 09

Re: PHP/MySQL problem from hell

Posted 13 March 2009 - 03:33 PM

View PostValek, on 12 Mar, 2009 - 08:37 PM, said:

This is on Drupal's site, but has applications outside of that particular piece of software. Seeing as it's not your server, you might consider seeing if you can make one of these work.


I really appreciate all your help, and I need some more of it! LOL!

So, I am trying to figure out how to access the command line from PHP or some scripting language. Is this possible? (IE, the person clicks the 'hire miner' button, then it sends a command to the server telling it to create the event.)

Thanks.
Was This Post Helpful? 0
  • +
  • -

#12 Valek  Icon User is offline

  • The Real Skynet
  • member icon

Reputation: 542
  • View blog
  • Posts: 1,713
  • Joined: 08-November 08

Re: PHP/MySQL problem from hell

Posted 13 March 2009 - 03:47 PM

Check out system()
Was This Post Helpful? 0
  • +
  • -

#13 Rikarus  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 22
  • Joined: 11-March 09

Re: PHP/MySQL problem from hell

Posted 13 March 2009 - 05:54 PM

View PostValek, on 13 Mar, 2009 - 02:47 PM, said:

Check out system()


Valek, I cannot thank you enough. I think I'm going to start a "Thankyou" page on my website, and put you and a couple other people that've helped me in it. My sincerest thanks to you mate!
Was This Post Helpful? 0
  • +
  • -

#14 Valek  Icon User is offline

  • The Real Skynet
  • member icon

Reputation: 542
  • View blog
  • Posts: 1,713
  • Joined: 08-November 08

Re: PHP/MySQL problem from hell

Posted 13 March 2009 - 06:05 PM

My pleasure. Also, as a side note for you for the future, if you ever work on a variable where it's going to receive a new value equivalent to its new value times, divided by, minus, or plus something, instead of this:

$Thankyou=$Thankyou*10^9999999999999999999999999;

You can shorten it by doing this:

$Thankyou *= pow(10,9999999999999999999999999);

pow() is the function for an exponent. The carat is used for other things. Primarily know as a start-of-string match in regular expressions :)

As a side note, $Thankyou's value will probably be INF after that, unless $Thankyou equaled 0 or NULL before that ;)

This post has been edited by Valek: 13 March 2009 - 06:08 PM

Was This Post Helpful? 0
  • +
  • -

#15 Rikarus  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 22
  • Joined: 11-March 09

Re: PHP/MySQL problem from hell

Posted 14 March 2009 - 12:21 PM

I think this should be the last problem...

I switched my whole server to linux so I can use cron ( It's well worth it I think)

Now I'm facing a problem.

This is the script that runs every 30 minutes, it is supposed to increase the food of each user by 1 for every chef they have, this should be working but I don't know why it isn't. Please help me!

<?php
include 'config.php';
$id = 1;
$result = mysql_query("SELECT * FROM Users");
$num_rows = mysql_num_rows($result);
do
{	
	$food	= mysql_query("SELECT food  FROM Users WHERE id = '$id'");
	$foodinc = mysql_query("SELECT chefs FROM Users WHERE id = '$id'");
	$foodadd = $food + $foodinc;
	mysql_query("UPDATE Users food = '$foodadd'");
	$id++;
}
while($id <= $num_rows);




PS: config.php connects to my database and Users is the table! ^_^
Was This Post Helpful? 0
  • +
  • -

#16 mocker  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 50
  • View blog
  • Posts: 466
  • Joined: 14-October 07

Re: PHP/MySQL problem from hell

Posted 14 March 2009 - 01:03 PM

Your update query should be:
UPDATE Users SET food = '$foodadd' WHERE id = '$id'



I'd also really recommend doing some basic checks to make sure your query worked before trying to run updates on the database.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2