delphi programming forums mysql charset mget recursive synonimos
free ventrilo servers hosting cs javascript delay python find in list
Back Forum New

Rank: 1
1# Jump to »
DraygonPost at 2010-6-21 14:47 | Show This Author's Posts Only
abstract:

and to reset the auto incrementing it is
Code:
ALTER TABLE  AUTO_INCREMENT=1
I have 10 tables that will have to be reset and started over and just want to do it all at once.
I would appreciate any help that can be given to me in order to make this work.


I am a beginner when it comes to the php program language and have been put in charge of a database for a rather large game mod that will be launching soon.  I have created a database but will need to be erasing data in it from time to time (when a war is over it will need to be reset to start again).
I figure I can somehow create a function that will do this with a click of a button, just not sure how to implement that.
I know the SQL commands for erasing the tables is:
Code:
  1. DELETE FROM [tablename]
Copy Code
and to reset the auto incrementing it is
Code:
  1. ALTER TABLE [tablename] AUTO_INCREMENT=1
Copy Code
I have 10 tables that will have to be reset and started over and just want to do it all at once.
I would appreciate any help that can be given to me in order to make this work.

TOP

Rank: 1
2#
Mark BakerPost at 2010-6-21 14:47 | Show This Author's Posts Only

PHP Code:
$tableList
= array(
'table1'
,
'table2'
,
'table3'
);
foreach(
$tableName in $tableList
) {
$sql
=
'DELETE FROM '
.
$tableName
;
$result
=
mysql_query
(
$sql
);
$sql
=
'ALTER TABLE '
.
$tableName
.
' AUTO_INCREMENT=1'
;
$result
=
mysql_query
(
$sql
);
}

TOP

Rank: 1
3#
DraygonPost at 2010-6-21 14:47 | Show This Author's Posts Only
Thanks guys I really appreciate the help.
So the Truncate table will erase all data and reset the auto increment at the same time, so I could just use the array with the truncate statement to do it all. Making it a function will allow me to run this with a click of a button from a webpage correct?
PHP Code:
function
war_reset
(){
$tableList
= array(
'table1'
,
'table2'
,
'table3'
);
foreach(
$tableName in $tableList
) {
$sql
=
'TRUNCATE TABLE'
.
$tableName
;
$result
=
mysql_query
(
$sql
);
  }
}

TOP

Rank: 1
4#
srisaPost at 2010-6-21 14:47 | Show This Author's Posts Only
Looks fine but for this line
$sql = 'TRUNCATE TABLE'.$tableName;
Leave a space between keyword "TABLE" and the actual table name. It should be like
$sql = 'TRUNCATE TABLE
[space here]
'.$tableName;

TOP

Rank: 1
5#
DraygonPost at 2010-6-21 14:47 | Show This Author's Posts Only
abstract:

and to reset the auto incrementing it is
Code:
ALTER TABLE  AUTO_INCREMENT=1
I have 10 tables that will have to be reset and started over and just want to do it all at once.
I would appreciate any help that can be given to me in order to make this work.


alright...thanks guys really appreciate the help.  Im sure I will be back with another question later .

TOP

Rank: 1
6#
DraygonPost at 2010-6-21 14:47 | Show This Author's Posts Only
Ok so after I wrote the script and uploaded it I am getting a parse error.  It does not like the "in" in the foreach.  My exact function is below, created this as a test to see if it would work:
PHP Code:
<?php
function
war_reset
(){
$tableList
= array(
'territories'
,
'playerRB'
);
foreach(
$tableName in $tableList
) {
$sql
=
'TRUNCATE TABLE '
.
$tableName
;
$result
=
mysql_query
(
$sql
);
  }
$sql
=
'UPDATE territories Allies_Win=1000'
;
$sql
=
'UPDATE territories Axis_Win=1000'
;
$sql
=
'UPDATE profile SET VP=5'
;
$result
=
mysql_query
(
$sql
);
}
?>
in my editor I have a red line under the in and doing a mouseover it says "Parse error, unexpected 'in'."
So once again, any help in solving this is much appreciated.

TOP

Rank: 1
7#
DraygonPost at 2010-6-21 14:47 | Show This Author's Posts Only
My editor is not picking them up and I am not good at just "eyeing" them.  Could you give me an idea of where to start with the mySql syntax errors?

TOP

Rank: 1
8#
DraygonPost at 2010-6-21 14:47 | Show This Author's Posts Only

Code:
  1. SQL query:
  2. $sql = 'UPDATE territories Allies_Win=1000; $sql = ' UPDATE territories Axis_Win =1000;
  3. MySQL said: Documentation
  4. #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '$sql = 'UPDATE territories Allies_Win=1000;
  5.     $sql = 'UPDATE territories Axis' at line 1
Copy Code
That was when I ran this:
Code:
  1.     $sql = 'UPDATE territories Allies_Win=1000;
  2.     $sql = 'UPDATE territories Axis_Win=1000;
  3.     $sql = 'UPDATE profile SET VP=5;
Copy Code
I understand I need to do some quote closing, but I tried in various places and none worked

TOP

Rank: 1
9#
DraygonPost at 2010-6-21 14:47 | Show This Author's Posts Only

Originally Posted by r937
the error message is telling you that mysql doesn't understand the
$sql =
part
which makes sense, because that's php!!
the query itself starts with the keyword UPDATE
Right, I understand that, but since the query is being run from a php page within a function...shouldnt I have that in there?
Or are you saying run the query in sql without the php part? If so I ran one and this is what I got:
Code:
  1. SQL query:
  2. UPDATE territories Allies_Win =1000
  3. MySQL said: Documentation
  4. #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '=1000' at line 1
Copy Code

TOP

Rank: 1
10#
DraygonPost at 2010-6-21 14:47 | Show This Author's Posts Only
abstract:

and to reset the auto incrementing it is
Code:
ALTER TABLE  AUTO_INCREMENT=1
I have 10 tables that will have to be reset and started over and just want to do it all at once.
I would appreciate any help that can be given to me in order to make this work.



Originally Posted by r937
you're catching on
now what i'd like you to do next is compare your query --
UPDATE territories Allies_Win =1000
with the allowed syntax for the UPDATE statement --
UPDATE [LOW_PRIORITY] [IGNORE]
tbl_name
    SET
col_name1
=
expr1
[,
col_name2
=
expr2
...]
    [WHERE
where_definition
]
    [ORDER BY ...]
    [LIMIT
row_count
]
if we remove the optional keywords and clauses that you aren't using, the allowed syntax is --
UPDATE
tbl_name
SET
col_name1
=
expr1
notice any difference between this and your query?
Aye, the set command, will making this change fix the "in" parse error I am getting above though?
Edit: Also do I have to take the ' out, or add one to the end of the statment?
Code:
  1. $sql = 'UPDATE territories SET Allies_Win=1000';
Copy Code
I am not sure where to close that at.

TOP

Back Forum