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

[d22552000]

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]

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]

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]

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]

<?php

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

?>

[d22552000]

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]

again, how would I act upon it..?

 

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

[d22552000]

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]

(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]

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]

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]

<?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]

 
$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]

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]

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]

*BUMP

 

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

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