Ravens PHP Scripts: Forums
 

 

View next topic
View previous topic
Post new topic   Reply to topic    Ravens PHP Scripts And Web Hosting Forum Index -> PHP
Author Message
Donovan
Client



Joined: Oct 07, 2003
Posts: 735
Location: Ohio

PostPosted: Thu Jun 15, 2006 12:25 pm Reply with quote

I want to release my upgrade to my MILPACS module soon to RC2 from RC1. I have done a scipt to add fields to some tables but I have a problem I can't find answers on. My initial table design had some fields that ended up being the wrong datatype (tinyint should have been larger). How can I change the datatype of around 4 or 5 fields in 3 or 4 tables?

For example:

Code:
-- Table structure for table `nuke_milpacs_service_record`

--

CREATE TABLE `nuke_milpacs_service_record` (
  `record_id` tinyint(4) NOT NULL auto_increment,
  `uniqueid` tinyint(4) default NULL,
  `record_dt` date default NULL,
  `details` varchar(255) default NULL,
  PRIMARY KEY  (`record_id`)
) TYPE=MyISAM;


should have been

Code:


-- Table structure for table `nuke_milpacs_service_record`
--

CREATE TABLE `nuke_milpacs_service_record` (
  `record_id` smallint(4) NOT NULL auto_increment,
  `uniqueid` smallint(4) default NULL,
  `record_dt` date default NULL,
  `details` varchar(255) default NULL,
  PRIMARY KEY  (`record_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


tinyint ended up being way to small.

Could I SELECT INTO a temp table, DROP TABLE, CREATE TABLE and INSERT back into a new table with the proper datatypes? Is this my only option?

I need this all contained inside a milpacs_upgrade.php
 
View user's profile Send private message Visit poster's website ICQ Number
kguske
Site Admin



Joined: Jun 04, 2004
Posts: 6432

PostPosted: Thu Jun 15, 2006 1:31 pm Reply with quote

You should be able to change the data type in phpMyAdmin without losing the data. Of course, I'd back up the tables first!

_________________
I search, therefore I exist...
nukeSEO - nukeFEED - nukePIE - nukeSPAM - nukeWYSIWYG
 
View user's profile Send private message
Donovan







PostPosted: Thu Jun 15, 2006 3:10 pm Reply with quote

kguske wrote:
You should be able to change the data type in phpMyAdmin without losing the data. Of course, I'd back up the tables first!


True, but this wont help the handful of people that have RC1 and are waiting for me to release RC2. Is there no way to change datatypes thru a PHP script?
 
kguske







PostPosted: Thu Jun 15, 2006 3:51 pm Reply with quote

When you make the change in phpMyAdmin, it will generate the SQL statements, which you could copy into an update script.
 
montego
Site Admin



Joined: Aug 29, 2004
Posts: 9457
Location: Arizona

PostPosted: Fri Jun 16, 2006 12:28 am Reply with quote

What kguske said! Laughing It should be able to be done with an ALTER and with no data loss as long as the types are compatible and going from "smaller" to "larger". At least that has been my experience anyways.

_________________
Where Do YOU Stand?
HTML Newsletter::ShortLinks::Mailer::Downloads and more... 
View user's profile Send private message Visit poster's website
Donovan







PostPosted: Fri Jun 16, 2006 6:05 am Reply with quote

tinyint to smallint should work. That gives me 32,767 I think. I don't know what I was thinking with tinyint.
 
Donovan







PostPosted: Thu Jun 29, 2006 10:47 am Reply with quote

I can't have this script written this way can I?

It seems $sql would be overwritten each time before the query would execute.

Code:
$sql = "ALTER TABLE ".$prefix."_milpacs_members ADD 'nukeusername' TEXT NOT NULL AFTER 'u_name'"; 

$sql = "ALTER TABLE ".$prefix."_milpacs_members CHANGE 'uniqueid' 'uniqueid' INT( 11 ) NOT NULL AUTO_INCREMENT";
$sql = "ALTER TABLE ".$prefix."_milpacs_service_record CHANGE 'record_id' 'record_id' INT( 4 ) NOT NULL AUTO_INCREMENT";
$sql = "ALTER TABLE ".$prefix."_milpacs_members ADD 'ismember' SMALLINT( 1 ) NOT NULL AFTER 'uniqueid'";
$sql = "ALTER TABLE ".$prefix."_milpacs_units DROP INDEX 'unit_motto'";
$sql = "ALTER TABLE ".$prefix."_milpacs_members DROP INDEX 'uniqueid'";
$sql = "ALTER TABLE ".$prefix."_milpacs_main ADD 'unit_name' VARCHAR( 50 ) NOT NULL";
$sql = "ALTER TABLE ".$prefix."_milpacs_main ADD 'unit_tag' VARCHAR( 20 ) NOT NULL";
$sql = "ALTER TABLE ".$prefix."_milpacs_main ADD 'unit_nick' VARCHAR( 20 ) NOT NULL";
$sql = "ALTER TABLE ".$prefix."_milpacs_main ADD 'recruitemail' VARCHAR( 50 ) NOT NULL";
$sql = "DROP TABLE IF EXISTS ".$prefix."_milpacs_sotm";
$sql = "DROP TABLE IF EXISTS ".$prefix."_milpacs_points";
$sql = "DROP TABLE IF EXISTS ".$prefix."_milpacs_points_lkup";
$sql = "DROP TABLE IF EXISTS ".$prefix."_milpacs_pass";
$sql = "DROP TABLE IF EXISTS ".$prefix."_milpacs_access";
$sql = "DROP TABLE IF EXISTS ".$prefix."_milpacs_hof";

$result = $db->sql_query($sql);
 
gregexp
The Mouse Is Extension Of Arm



Joined: Feb 21, 2006
Posts: 1497
Location: In front of a screen....HELP! lol

PostPosted: Thu Jun 29, 2006 6:18 pm Reply with quote

no but you could through all of it into one statement or variable then apply this code:


$buffer1 = 'START TRANSACTION;' . $buffer . '; COMMIT;';

$query_split = preg_split ("/[;]+/", $buffer2);
foreach ($query_split as $command_line) {
$command_line = trim($command_line);
if ($command_line != '') {

$query_result = mysql_query($command_line);
if ($query_result == 0) {
echo "$command_line";
}

}

}
return $query_result;

$buffer is being used as the variable for ALL the sql insertions, the script breaks it all down at the ; in all statements, now I've seen raven and other use arrays and not buffer it and I'm not sure why, so I'll continue to use this until I learn why they use it instead as perhaps there is a problem or inconvienence.
This will work on any database as long as you set the script to include mainfile and set globals$db
then change line from mysql_query to $db->sql_query

Hope this helps.

_________________
For those who stand shall NEVER fall and those who fall shall RISE once more!! 
View user's profile Send private message Send e-mail Visit poster's website AIM Address Yahoo Messenger MSN Messenger ICQ Number
montego







PostPosted: Wed Jul 05, 2006 3:36 pm Reply with quote

I just execute them separately, one-by-one, with my installers. Some mySQL setups will allow more than one statement per call, but it is not an adviseable "setup", so I would expect that most Web Hosts would not have mySQL configured this way. (Opens the door even further for SQL injection issues.)
 
gregexp







PostPosted: Wed Jul 05, 2006 9:04 pm Reply with quote

Are you saying that mysql_query can run more then one statement at once?

I thought in php that mysql_query was only allowed to run one mysql_query at a time.

Thus the reason for my script.
 
Donovan







PostPosted: Fri Jul 21, 2006 1:53 pm Reply with quote

I can't seem to get this last statement to work

This it what it looks like on the screen.

Quote:
Upgrading the MILPACS tables!

--------------------------------------------------------------------------------
Operation Results:
--------------------------------------------------------------------------------
Alter nuke_milpacs_members succeeded
Alter nuke_milpacs_members succeeded
Alter nuke_milpacs_service_record succeeded
Alter nuke_milpacs_service_record succeeded
Alter nuke_milpacs_award_lkup succeeded
Alter nuke_milpacs_members succeeded
Alter nuke_milpacs_units succeeded
Alter nuke_milpacs_members succeeded
Alter nuke_milpacs_main succeeded
Alter nuke_milpacs_main succeeded
Alter nuke_milpacs_main succeeded
Alter nuke_milpacs_main succeeded
Alter nuke_milpacs_main succeeded
Alter nuke_milpacs_main succeeded
Alter nuke_milpacs_main succeeded
Alter nuke_milpacs_main succeeded
Drop nuke_milpacs_sotm succeeded
Drop nuke_milpacs_points succeeded
Drop nuke_milpacs_points_lkup succeeded
Drop nuke_milpacs_pass succeeded
Drop nuke_milpacs_access succeeded
Drop nuke_milpacs_hof succeeded
Information inserted into milpacs_main was Unsuccessful!

--------------------------------------------------------------------------------
Operation Complete!
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
REMOVE THIS SCRIPT WHEN FINISHED!

My milpacs_main just has some information that I moved from a language file, and placed in the database. The table is empty.

My upgrade script actually creates these earlier.

Code:
$result = $db->sql_query("ALTER TABLE ".$prefix."_milpacs_main ADD unit_name VARCHAR( 50 ) NOT NULL");

if (!$result) { echo "Alter ".$prefix."_milpacs_main failed<br>\n"; } else { echo "Alter ".$prefix."_milpacs_main succeeded<br>\n"; }

$result = $db->sql_query("ALTER TABLE ".$prefix."_milpacs_main ADD unit_tag VARCHAR( 20 ) NOT NULL");
if (!$result) { echo "Alter ".$prefix."_milpacs_main failed<br>\n"; } else { echo "Alter ".$prefix."_milpacs_main succeeded<br>\n"; }


and so on.

But at the end I want to insert data into these fields and it won't work.

Code:
$result = $db->sql_query("INSERT INTO ".$prefix."_milpacs_main VALUES('Welcome to the Insert Unit Here personnel administration system. We are utilizing MILPACS, a module for PHPNuke, developed by 1Lt Donovan of the 3rd Infantry Division.  The MILPACS development site is located at http://milpacs.3rd-infantry-division.net.  Though still under developement, it is a substantial improvement over other methods of unit administration and is currently the only module of its kind available. Please feel free to comment and provide feedback that will help us contribute to the development of this tool.', 'Change Me', '[Your Tag]', 'Change Me', 'recruitment@yourdomain.net'");

   if (!$result ) {echo "Information inserted into <i>milpacs_main</i> was Unsuccessful!<br>\n";} else {echo "Information inserted into <i>milpacs_main</i> was Successful!<br>\n";}


Here is my entire upgradeRC1-RC2.php

Code:
<?php

/********************************************************/
/* MILPACS                                              */
/* By: 3rd ID (donovan@3rd-infantry-division.net)       */
/* http://www.3rd-infantry-division.net                 */
/* Copyright © 2006 by Steven Donovan                   */
/********************************************************/

/************************************************************************
* Original Installer Design / Structure
* By: NukeScripts Network (webmaster@nukescripts.net)
* http://www.nukescripts.net
* Copyright © 2000-2005 by NukeScripts Network
************************************************************************/

if ( !defined( 'MILPACS_LOADED' ) ) { die( "Illegal File Access" ); }
//Upgrade script for MILPACS beta 1.0 RC2

require_once("mainfile.php");
global $prefix, $db;
OpenTable();

echo "Upgrading the MILPACS tables!<br />\n";
echo "<hr>\n";
echo "<b>Operation Results:</b><hr>\n";
$result = $db->sql_query("ALTER TABLE ".$prefix."_milpacs_members ADD nukeusername TEXT NOT NULL AFTER u_name");
if (!$result) { echo "Alter ".$prefix."_milpacs_members failed<br>\n"; } else { echo "Alter ".$prefix."_milpacs_members succeeded<br>\n"; }

$result = $db->sql_query("ALTER TABLE ".$prefix."_milpacs_members CHANGE uniqueid uniqueid INT( 11 ) NOT NULL AUTO_INCREMENT");
if (!$result) { echo "Alter ".$prefix."_milpacs_members failed<br>\n"; } else { echo "Alter ".$prefix."_milpacs_members succeeded<br>\n"; }

$result = $db->sql_query("ALTER TABLE ".$prefix."_milpacs_service_record CHANGE record_id record_id INT( 4 ) NOT NULL AUTO_INCREMENT");
if (!$result) { echo "Alter ".$prefix."_milpacs_service_record failed<br>\n"; } else { echo "Alter ".$prefix."_milpacs_service_record succeeded<br>\n"; }

$result = $db->sql_query("ALTER TABLE ".$prefix."_milpacs_service_record CHANGE uniqueid uniqueid INT( 11 ) NOT NULL");
if (!$result) { echo "Alter ".$prefix."_milpacs_service_record failed<br>\n"; } else { echo "Alter ".$prefix."_milpacs_service_record succeeded<br>\n"; }

$result = $db->sql_query("ALTER TABLE ".$prefix."_milpacs_award_lkup CHANGE uniqueid uniqueid INT( 11 ) NOT NULL");
if (!$result) { echo "Alter ".$prefix."_milpacs_award_lkup failed<br>\n"; } else { echo "Alter ".$prefix."_milpacs_award_lkup succeeded<br>\n"; }

$result = $db->sql_query("ALTER TABLE ".$prefix."_milpacs_members ADD ismember SMALLINT( 1 ) AFTER uniqueid");
if (!$result) { echo "Alter ".$prefix."_milpacs_members failed<br>\n"; } else { echo "Alter ".$prefix."_milpacs_members succeeded<br>\n"; }

$result = $db->sql_query("ALTER TABLE ".$prefix."_milpacs_units DROP INDEX unit_motto");
if (!$result) { echo "Alter ".$prefix."_milpacs_units failed<br>\n"; } else { echo "Alter ".$prefix."_milpacs_units succeeded<br>\n"; }

$result = $db->sql_query("ALTER TABLE ".$prefix."_milpacs_members DROP INDEX uniqueid");
if (!$result) { echo "Alter ".$prefix."_milpacs_members failed<br>\n"; } else { echo "Alter ".$prefix."_milpacs_members succeeded<br>\n"; }

$result = $db->sql_query("ALTER TABLE ".$prefix."_milpacs_main ADD unit_name VARCHAR( 50 ) NOT NULL");
if (!$result) { echo "Alter ".$prefix."_milpacs_main failed<br>\n"; } else { echo "Alter ".$prefix."_milpacs_main succeeded<br>\n"; }

$result = $db->sql_query("ALTER TABLE ".$prefix."_milpacs_main ADD unit_tag VARCHAR( 20 ) NOT NULL");
if (!$result) { echo "Alter ".$prefix."_milpacs_main failed<br>\n"; } else { echo "Alter ".$prefix."_milpacs_main succeeded<br>\n"; }

$result = $db->sql_query("ALTER TABLE ".$prefix."_milpacs_main DROP COLUMN drillpass");
if (!$result) { echo "Alter ".$prefix."_milpacs_main failed<br>\n"; } else { echo "Alter ".$prefix."_milpacs_main succeeded<br>\n"; }

$result = $db->sql_query("ALTER TABLE ".$prefix."_milpacs_main DROP COLUMN u_email");
if (!$result) { echo "Alter ".$prefix."_milpacs_main failed<br>\n"; } else { echo "Alter ".$prefix."_milpacs_main succeeded<br>\n"; }

$result = $db->sql_query("ALTER TABLE ".$prefix."_milpacs_main DROP COLUMN name");
if (!$result) { echo "Alter ".$prefix."_milpacs_main failed<br>\n"; } else { echo "Alter ".$prefix."_milpacs_main succeeded<br>\n"; }

$result = $db->sql_query("ALTER TABLE ".$prefix."_milpacs_main DROP COLUMN color");
if (!$result) { echo "Alter ".$prefix."_milpacs_main failed<br>\n"; } else { echo "Alter ".$prefix."_milpacs_main succeeded<br>\n"; }

$result = $db->sql_query("ALTER TABLE ".$prefix."_milpacs_main ADD unit_nick VARCHAR( 20 ) NOT NULL");
if (!$result) { echo "Alter ".$prefix."_milpacs_main failed<br>\n"; } else { echo "Alter ".$prefix."_milpacs_main succeeded<br>\n"; }

$result = $db->sql_query("ALTER TABLE ".$prefix."_milpacs_main ADD recruitemail VARCHAR( 50 ) NOT NULL");
if (!$result) { echo "Alter ".$prefix."_milpacs_main failed<br>\n"; } else { echo "Alter ".$prefix."_milpacs_main succeeded<br>\n"; }

$result = $db->sql_query("DROP TABLE IF EXISTS ".$prefix."_milpacs_sotm");
if (!$result) { echo "Drop ".$prefix."_milpacs_sotm failed<br>\n"; } else { echo "Drop ".$prefix."_milpacs_sotm succeeded<br>\n"; }

$result = $db->sql_query("DROP TABLE IF EXISTS ".$prefix."_milpacs_points");
if (!$result) { echo "Drop ".$prefix."_milpacs_points failed<br>\n"; } else { echo "Drop ".$prefix."_milpacs_points succeeded<br>\n"; }

$result = $db->sql_query("DROP TABLE IF EXISTS ".$prefix."_milpacs_points_lkup");
if (!$result) { echo "Drop ".$prefix."_milpacs_points_lkup failed<br>\n"; } else { echo "Drop ".$prefix."_milpacs_points_lkup succeeded<br>\n"; }

$result = $db->sql_query("DROP TABLE IF EXISTS ".$prefix."_milpacs_pass");
if (!$result) { echo "Drop ".$prefix."_milpacs_pass failed<br>\n"; } else { echo "Drop ".$prefix."_milpacs_pass succeeded<br>\n"; }

$result = $db->sql_query("DROP TABLE IF EXISTS ".$prefix."_milpacs_access");
if (!$result) { echo "Drop ".$prefix."_milpacs_access failed<br>\n"; } else { echo "Drop ".$prefix."_milpacs_access succeeded<br>\n"; }

$result = $db->sql_query("DROP TABLE IF EXISTS ".$prefix."_milpacs_hof");
if (!$result) { echo "Drop ".$prefix."_milpacs_hof failed<br>\n"; } else { echo "Drop ".$prefix."_milpacs_hof succeeded<br>\n"; }

$result = $db->sql_query("INSERT INTO ".$prefix."_milpacs_main VALUES('Welcome to the Insert Unit Here personnel administration system. We are utilizing MILPACS, a module for PHPNuke, developed by 1Lt Donovan of the 3rd Infantry Division.  The MILPACS development site is located at http://milpacs.3rd-infantry-division.net.  Though still under developement, it is a substantial improvement over other methods of unit administration and is currently the only module of its kind available. Please feel free to comment and provide feedback that will help us contribute to the development of this tool.', 'Change Me', '[Your Tag]', 'Change Me', 'recruitment@yourdomain.net'");
   if (!$result ) {echo "Information inserted into <i>milpacs_main</i> was Unsuccessful!<br>\n";} else {echo "Information inserted into <i>milpacs_main</i> was Successful!<br>\n";}

echo "<hr><b>Operation Complete!</b><hr>\n";
echo "<hr><b>REMOVE THIS SCRIPT WHEN FINISHED!</b><hr>\n";
CloseTable();
include("footer.php");   
?>
 
montego







PostPosted: Fri Jul 21, 2006 3:06 pm Reply with quote

darklord wrote:
Are you saying that mysql_query can run more then one statement at once?

I thought in php that mysql_query was only allowed to run one mysql_query at a time.

Thus the reason for my script.


Yes, it can, but like I said, it is a risky thing to have on as it would allow even more opportunities for SQL injection if you could do many in ONE call. I would bet that this feature is turned OFF in most installations.
 
montego







PostPosted: Fri Jul 21, 2006 3:09 pm Reply with quote

Quote:

But at the end I want to insert data into these fields and it won't work.


I cannot see anything wrong with this insert statement structure as you have it, however, could you provide the full table structure for this one table (from phpMyAdmin) as I can only see the new fields that you have added.
 
gregexp







PostPosted: Fri Jul 21, 2006 4:05 pm Reply with quote

Perhaps the commas are causing the database to think there were too many inserts.
 
Donovan







PostPosted: Fri Jul 21, 2006 6:11 pm Reply with quote

montego wrote:
Quote:

But at the end I want to insert data into these fields and it won't work.


I cannot see anything wrong with this insert statement structure as you have it, however, could you provide the full table structure for this one table (from phpMyAdmin) as I can only see the new fields that you have added.


Here is what becomes of my table after the ALTER commands:

motd text
unit_name varchar(50)
unit_tag varchar(20)
unit_nick varchar(20)
recruitemail varchar(50)

The INSERT at the end does not load the values I need.


Code:
$result = $db->sql_query("INSERT INTO ".$prefix."_milpacs_main VALUES('Welcome to the Insert Unit Here personnel administration system. We are utilizing MILPACS, a module for PHPNuke, developed by 1Lt Donovan of the 3rd Infantry Division.  The MILPACS development site is located at http://milpacs.3rd-infantry-division.net.  Though still under developement, it is a substantial improvement over other methods of unit administration and is currently the only module of its kind available. Please feel free to comment and provide feedback that will help us contribute to the development of this tool.', 'Change Me', '[Your Tag]', 'Change Me', 'recruitment@yourdomain.net'");

   if (!$result ) {echo "Information inserted into <i>milpacs_main</i> was Unsuccessful!<br>\n";} else {echo "Information inserted into <i>milpacs_main</i> was Successful!<br>\n";}
 
montego







PostPosted: Sat Jul 22, 2006 8:15 am Reply with quote

Donovan, I am stumped. Looks fine to me. Try copying and pasting these EXACT values into that table via phpMyAdmin and see if you get any errors.

Is your IF statement right after the insert properly catching that an error occurred?

Also you can try to add this line right in-between these two lines:

print_r ($db->sql_error());

If that gives you an error, than replace it with this:

$sql_error = $db->sql_error();
echo $sql_error['message'];

We need to find some better clues here...
 
Display posts from previous:       
Post new topic   Reply to topic    Ravens PHP Scripts And Web Hosting Forum Index -> PHP

View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You can attach files in this forum
You can download files in this forum


Powered by phpBB © 2001-2007 phpBB Group
All times are GMT - 6 Hours
 
Forums ©