PHP Mysql - Select * from * where *... $time=$time-1... HOW TO WRITE CHANGES?

d22552000 · July 13, 2007

I am creating a script to read a table called "jobs" and reading columb "Action TIme".

This program will read "Action Time" and subtract 1, then write the changes to the db.

$sql = "SELECT *  FROM `jobs` WHERE `Action Time` >= 1"
$result = mysql_query($sql);

while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
   		foreach ($row['Action Time'] as $Time) {
	$Time = $Time - 1;
	}
}

now that I have subtracted from the values... how do I update the rows in the same order?

pocobueno1388 · July 13, 2007

Why don't you just do one update query?

<?php

$query = "UPDATE `jobs` SET `Action Time`=`Action Time`-1 WHERE `Action Time` >= 1";
$result = mysql_query($query)or die(mysql_error());

?>

trq · July 13, 2007

Replace all your code with...

<?php

 $sql = "UPDATE `jobs` SET `Action Time` = `Action Time`-1 WHERE `Action Time` >= 1"
 if (mysql_query($sql)) {
   echo "UPDATE success";
 }

?>

d22552000 · July 13, 2007

I had no idea you could do this...

how should I incorperate the deletion (and acting upon) of jobs with time 1 or 0?

DROP `jobs' WHERE `Action Time` <= 1

???

pocobueno1388 · July 13, 2007

<?php

$query = "DELETE FROM `jobs` WHERE `Action Time` <= 1";
$result = mysql_query($query)or die(mysql_error());

?>

d22552000 · July 13, 2007

again, how would I act upon it..?

I am making a game LIKE oGame. (http://www.ogame.org).

I am making this one tick based instead of real-time. This file I am editing... is the DOTICK page.

would I sitll need to do:

foreach if ($row['action time']<=1) {

if ($row['action type'] == "build") {

update 'BUIDLINGS' where 'USERNAME' = $row['Owner']

}

I know that that syntax sucks, bt you get my point.

trq · July 13, 2007

again, how would I act upon it..?

Explain? The code posted replaces your need for allot of unnecessary loops.

d22552000 · July 13, 2007

I have programmed the foloowing:

(do you think it will do what I want?)


<?PHP

$link = mysql_connect('localhost', 'root', ''); $db = mysql_select_db('test',$link);

$sql = "SELECT *  FROM `jobs` WHERE `Action Time` >= 1"
$result = mysql_query($sql);

	while($row = mysql_fetch_array($result, MYSQL_ASSOC))
	{
		foreach ($row['Action Time'] as $Time) {
			if ($row['Action Type'] == "Build") {
				$sql = "UPDATE 'Buildings' SET '" . $row['Action Name'] . "'='" . $row['Action Name'] . "'+1 WHERE 'Owner' = " . $row['Owner'];
			}
			if ($row['Action Type'] == "Research") {
				$sql = "UPDATE 'Research' SET '" . $row['Action Name'] . "'='" . $row['Action Name'] . "'+1 WHERE 'Owner' = " . $row['Owner'];
			}
			if ($row['Action Type'] == "Ship") {
				$sql = "UPDATE 'fleets' SET '" . $row['Action Name'] . "'='" . $row['Action Name'] . "'+1 WHERE 'Owner' = " . $row['Owner'];
			}
		mysql_querry($sql)or die(mysql_error());
		}
	} 


$query = "UPDATE `jobs` SET `Action Time`=`Action Time`-1 WHERE `Action Time` >= 1";
$result = mysql_query($query)or die(mysql_error());

?>

trq · July 13, 2007

(do you think it will do what I want?)

NO. If your going to ask questions, its best to read the replies. What makes you believe you still need to use loops for this?

pocobueno1388 · July 13, 2007

You can replace all that code with this:

<?php

$sql = "UPDATE 'Buildings' SET '" . $row['Action Name'] . "'='" . $row['Action Name'] . "'+1 WHERE 'Owner' = " . $row['Owner']." AND `Action Type`='Build'";
$result = mysql_query($sql)or die(mysql_error());


$sql = "UPDATE 'Research' SET '" . $row['Action Name'] . "'='" . $row['Action Name'] . "'+1 WHERE 'Owner' = " . $row['Owner']." AND `Action Type`='Research'";
$result = mysql_query($sql)or die(mysql_error());


$sql = "UPDATE 'fleets' SET '" . $row['Action Name'] . "'='" . $row['Action Name'] . "'+1 
WHERE 'Owner' = " . $row['Owner']." AND `Action Type`='Ship'";
$result = mysql_query($sql)or die(mysql_error());	


$query = "UPDATE `jobs` SET `Action Time`=`Action Time`-1 WHERE `Action Time` >= 1";
$result = mysql_query($query)or die(mysql_error());

?>

d22552000 · July 13, 2007

You can replace all that code with this:

<?php

$sql = "UPDATE 'Buildings' SET '" . $row['Action Name'] . "'='" . $row['Action Name'] . "'+1 WHERE 'Owner' = " . $row['Owner']." AND `Action Type`='Build'";
$result = mysql_query($sql)or die(mysql_error());


$sql = "UPDATE 'Research' SET '" . $row['Action Name'] . "'='" . $row['Action Name'] . "'+1 WHERE 'Owner' = " . $row['Owner']." AND `Action Type`='Research'";
$result = mysql_
query($sql)or die(mysql_error());


$sql = "UPDATE 'fleets' SET '" . $row['Action Name'] . "'='" . $row['Action Name'] . "'+1 
WHERE 'Owner' = " . $row['Owner']." AND `Action Type`='Ship'";
$result = mysql_query($sql)or die(mysql_error());	


$query = "UPDATE `jobs` SET `Action Time`=`Action Time`-1 WHERE `Action Time` >= 1";
$result = mysql_query($query)or die(mysql_error());

?>

Will this do the update for type for EVERY column in the database, or just the first one of each typpe?

I am only using the foreach and while loops because that Is the only way that I know how to retrieve multiple rows form a database in an array.

trq · July 13, 2007

<?php

 $link = mysql_connect('localhost', 'root', ''); $db = mysql_select_db('test',$link);

 $arr = array('Buildings','Research','fleets');

 foreach($arr as $v) {
   $sql = "UPDATE `$v` SET `Action Name` = `Action Name`+1 WHERE `Action Time` >= 1";
   if (mysql_query($sql)) {
     echo "$v updated";
   }
 }

 $sql = "UPDATE `jobs` SET `Action Time`=`Action Time`-1 WHERE `Action Time` >= 1";
 if (mysql_query($sql)) {
   echo "jobs updated";
 }

?>

d22552000 · July 13, 2007

 
$link = mysql_connect('localhost', 'root', ''); $db = mysql_select_db('test',$link);

  $arr = array('Buildings','Research','fleets');

  foreach($arr as $v) {
    $sql = "UPDATE `$v` SET `Action Name` = `Action Name`+1 WHERE `Action Time` >= 1";
    if (mysql_query($sql)) {
      echo "$v updated";
    }
  }

  $sql = "UPDATE `jobs` SET `Action Time`=`Action Time`-1 WHERE `Action Time` >= 1";
  if (mysql_query($sql)) {
    echo "jobs updated";
  }

updates the jobs correctly and leaves them at 0 (good thing.)

but DOES NOT update the metal mine or others in "buildings" "research" or "ship"

I never get "$v updated"..

can you please help me incorperate (echo *mysql_error* ?

=========edit

how can I put:

DELETE FROM `jobs` WHERE `jobs`.`Action Time` = 0;
DELETE FROM `jobs` WHERE `jobs`.`Action Time` = 1;

into the source AFTER the updating times and buidlings step?

trq · July 13, 2007

Man... did you design this database? It wreaks of poor design. Why do you have mulitple tables that hold the same type of data? You probably will need to use allot of loops and multiple queries to work around this poor design. I should imagine the app is going to get pretty slow pretty quickly though.

May I suggest you look into database normalization?

d22552000 · July 13, 2007

No tables have the same data, except that every table has the column (Owner) which is both the primary and what I use to associate fleet 2 with account 2...

d22552000 · July 13, 2007

*BUMP

+You are not helping me with the last post, just criticizing my database.

I also am grateful you suggested a normalization site :ty:.

Sign In

PHP Mysql - Select * from * where *... $time=$time-1... HOW TO WRITE CHANGES?

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information