hopefully this is a quick and easy answer for someone

how do you insert a new column with default values into a MySQL table?

i'd assume it'd be some variation of ALTER TABLE tablename ADD COLUMN columnname INT(3), but what would i have to add/change so that i could insert the new column between certain existing columns and to set the column to have a default value?

8/14/2006 10:30:19 PM

you're on the right track

http://mysql.com/doc/refman/5.0/en/alter-table.html

ALTER TABLE tablename ADD COLUMN column_definition AFTER col_name

where column_definition is from http://mysql.com/doc/refman/5.0/en/create-table.html

column_definition:
col_name data_type [NOT NULL | NULL] [DEFAULT default_value]
[AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
[COMMENT 'string'] [reference_definition]

[Edited on August 14, 2006 at 10:36 PM. Reason : default]

8/14/2006 10:34:37 PM

how would i code that column_definition part in php?

would i just be like

$cd="column_definition:
columnname INT(3) DEFAULT 5"

8/14/2006 10:55:58 PM

why are you splitting up the string? just do

$sql = "ALTER TABLE tablename ADD COLUMN columnname INT(3) DEFAULT 5 AFTER col_name";

or if you do need to piece it together

$cd = "columnname INT(3) DEFAULT 5";
$sql = "ALTER TABLE tablename ADD COLUMN $cd AFTER col_name";

8/14/2006 11:01:47 PM

oic, i just misunderstood the psuedocode

i hate psuedocode

OK I'M GOOD NOW THX QNTMFRED

8/14/2006 11:04:15 PM

ok NEW QUESTION

if i have $ROW = mysql_fetch_object($poop);

and let's say there is a column named cats in there

and $variab = 'cats';

then you do $temp = 'ROW->'.$variab;

NOW,

$ROW->cats should be the same as $$temp, right?

[Edited on September 13, 2006 at 9:50 PM. Reason : because it's not goddamn working]

9/13/2006 9:23:00 PM

nevermind, just doing $ROW->{$variab} works

i guess it WAS the -> part

9/13/2006 10:12:33 PM

np

9/13/2006 11:01:50 PM

PHP + mySQL - A Quick Lesson

Here's a breakdown of some code that will connect you to a database & spit out some data...

// Establish Database Connection --------------------------------------

$hostname_connection= "db.dbserver.com"; -- Database server host name
$database_connection= "thisdatabase"; -- Name of database you are connecting to
$username_connection = "admin"; -- Username used to connect to database
$password_connection = "password"; -- Password used to connect
$connection = mysql_pconnect($hostname_connection, $username_connection, $password_connection) or trigger_error(mysql_error(),E_USER_ERROR);

// Select Query to Retrieve Records ------------------------------------

mysql_select_db($database_connection, $connection); -- Select the database
$query_getRecords = "SELECT * FROM table WHERE field1 = 'value1'"; -- Query statement
$getRecords = mysql_query($query_getRecords, $connection) or die(mysql_error()); -- Execute query using the database connection.
$row_getRecords = mysql_fetch_assoc($getRecords); -- Creates array of data in each row.
$totalRows_getRecords = mysql_num_rows($getRecords); -- Find the total number of rows


// Finding out the values

$value1 = $row_getRecords['field1'];
$value2 = $row_getRecords['field2'];

Let's say your database tabke is setup like this

field1 | field2 | field3
---------------------
Mike | Tom | Gary <---------- Row1
--------------------
Dave | John | Kelly <---------- Row2

In this example, $value1 will equal Mike, and $value2 will equal Tom.

Using the same setup, you can set $value3 = $row_getRecords['field3'] (Tom). If you print these values to the screen like so:

<?php echo $value1; ?> // Will print 'Mike'
<?php echo $value2; ?> // Will print 'Tom'
<?php echo $value3; ?> // Will print 'Gary'


You can do a do/while statment to make your program loop through the entire database and print out all values:

<?php

do {
echo $value1."<br>";
echo $value2."<br>";
echo $value3."<br>"

} while ($row_getResults = mysql_fetch_assoc($getReults));;
?>

9/13/2006 11:06:05 PM

straight out of dreamweaver Makes it pretty easy ehh.

9/14/2006 7:55:54 AM

Right as rained - it's how I learned.

9/14/2006 10:31:00 AM