Changing table feild properties

[excursion]

I am wanting change the information inside of a table. Right now it has a table for profiles with alot of feilds. One feild has countries in it and I want to put states.'

How can I do this?

Thanks in advance

In my sql file that I uploaded for the database looks like this for countries.

`
# Table structure for table `Profiles`
#

CREATE TABLE `Profiles` (
  `ID` bigint(8) unsigned NOT NULL auto_increment,
  `NickName` varchar(48) NOT NULL default '',
  `Password` varchar(48) NOT NULL default '',
  `RealName` varchar(40) NOT NULL default '',
  `Country` smallint(5) unsigned NOT NULL default '0',
  `City` varchar(30) default NULL,
  `Children` tinyint(3) unsigned NOT NULL default '0',
  `WhereChildren` enum('I will tell you later','living with me','not living with me','sometimes living with me') NOT NULL default 'I will tell you later',
  `Headline` varchar(255) NOT NULL default '',
  `DescriptionMe` mediumtext NOT NULL,
  `DescriptionYou` mediumtext NOT NULL,

[kasab]

If I read you correctly, you want to insert an extra field into your existing Profiles table?

In MySQL, you run the following command:

ALTER TABLE Profiles ADD State <field definition> AFTER Country;

assuming you want the state field after the country field. Replace <field definition> with the properties you want the State field to have.

If you want the State instead of the Country country field, you could modify your existing Country field using CHANGE:

ALTER TABLE Profiles CHANGE Country
State <field definition>;

If you are using Oracle, or some other DB, the syntax may vary.

HTH

[excursion]

I want to use the feild country but want it to have the states names instead of countries names. Reason being is i dont want to have to change the rest of the pages to compinsate that.

I am new to this so if I I sound like I dont know what I am talking about its because I dont. I just need when a member goes to sign up the can input their state not a country.

Thanks for the help.

[excursion]

kasab wrote:

If you want the State instead of the Country country field, you could modify your existing Country field using CHANGE:

ALTER TABLE Profiles CHANGE Country
State <field definition>;

I could not find this.

[kasab]

It looks like running this query should do what you want:

ALTER TABLE Profiles CHANGE Country
State smallint(5) unsigned NOT NULL default '0';

This keeps the column but renames it from "Country" to "State". You run this query the same way you ran the table-creation statement.

When you say "I could not find this" what do you mean? That the alter table didn't work? If so, could you post any error messages or what happened?

[excursion]

Yeah but will the names on the site change from countries to states.

Have a look where the members join It has countries. I want it to be the states.

Look Here

Now let me point out that it is an adult dating site. No Nudity is shown though.

Thanks again for all your help

[kasab]

It changes the database, but you will need to repopulate your entries in the table to states.

What I was looking at (via your link) was a php page that codes a drop down list of all countries. You will need to redo this page also so that the available options are in the list. This is distinct from the database (which only stores the data you collect from your page).

I am assuming that you did your own php page. If not, you will need to find the option box in your php code, rename that, and design the options with their values.

About one third of the way down your page (as displayed in the browser), you have an options box with name of "Country". This is the one you will have to change. You will also need to change the label for this.

The HTML code for this would start like:
<SELECT name='State'>
<OPTION value="0" selected>Whatever state you like</OPTION>

...

</SELECT>

It's up to you whether you embed this within PHP or have it as HTML with the PHP elsewhere on the page (I can't tell from my browser's View Source).

You may also need to change the INSERT statement in your code that puts the data into the table.

Once this is uploaded to your server, you should see the change.

Is this what you mean?

[excursion]

Thats exactly what I need. I will try that and hope I dont mess the site up. Will have to make a back up of the page.

Thanks for the help. Greatly appreciated.

David

[excursion]

Here is my php code for the join page and no where do I see where I can change the values?

<?

require_once( "inc/header.inc.php" );
require_once( "$dir[inc]db.inc.php" );
require_once( "$dir[inc]design.inc.php" );
require_once( "$dir[inc]profiles.inc.php" );
require_once( "$dir[inc]profile_disp.inc.php" );

// --------------- page variables and login

$_page[name_index] = 3;

if ( !( $logged[admin] = member_auth( 1, false ) ) )
    if ( !( $logged[member] = member_auth( 0, false ) ) )
        $logged[aff] = member_auth( 2, false );

$_page['header'] = _t( "_JOIN_H" );
$_page['header_text'] = _t( "_JOIN_H" );

// --------------- GET/POST actions

$min_nick   = 2;
$max_nick   = 10;
$min_passwd = 4;
$max_passwd = 8;
$min_desc   = 50;

$page = $_POST[page];


// --------------- [ END ] GET/POST actions

// --------------- page components

$_ni = $_page['name_index'];
$_page_cont[$_ni]['page_main_code'] = PageCompPageMainCode();


// --------------- [END] page components

PageCode();

// --------------- page components functions

/**
 * page code function
 */
function PageCompPageMainCode()
{
    global $content_w;
	global $page;
	global $p_arr;
	global $_page;
	global $en_aff;

ob_start();
session_start();

switch ( $page )
{
    case 2:

// fil array with POST values
$respd = db_res("SELECT * FROM ProfilesDesc WHERE `visible` & 2 AND ( FIND_IN_SET('0',show_on_page) OR FIND_IN_SET('".(int)$_page[name_index]."',show_on_page)) ORDER BY `order` ASC");
while ( $arrpd = mysql_fetch_array($respd) )
{
    $fname = get_input_name ( $arrpd );

	switch ($arrpd[type])
    {
    case 'set': // set of checkboxes 
	    $vals = preg_split ("/[,\']+/", $arrpd[extra], -1, PREG_SPLIT_NO_EMPTY);
		foreach ( $vals as $v )
	    {
			if ( strlen(trim($v)) <= 0 ) continue;
	        $p_arr[$fname."&".$v] = $_POST[$fname."&".$v];
	    }
        break;
	default:
		if ( $arrpd[get_value] )	
		{
		    $funcbody = $arrpd[get_value];
			$func = create_function('$arg0',$funcbody);
			$p_arr[$fname] = $func($_POST);
		}
		else
		{
			$p_arr[$fname] = $_POST[$fname];
		}
		break;
	}
}

// ckeck values


$respd = db_res("SELECT * FROM ProfilesDesc WHERE `visible` & 2 AND ( FIND_IN_SET('0',show_on_page) OR FIND_IN_SET('".(int)$_page[name_index]."',show_on_page)) ORDER BY `order` ASC");
while ( $arrpd = mysql_fetch_array($respd) )
{
	if ( !strlen($arrpd[check]) ) continue;
	$fname = get_input_name ( $arrpd );

    $funcbody = $arrpd[check];
	$func = create_function('$arg0', $funcbody);
	if ( !$func($p_arr[$fname]))
	{

		$page = 1;
		$add_on .= report_err( _t($arrpd[because],$arrpd[min_length],$arrpd[max_length]) );
	}

}

break;

}

switch( $page )
{
    case 2:
	$page_text = _t( "_JOIN3" ) . $add_on;
	$page_text .= activation_mail( $IDnormal );
	$page_text .= "<br><br><br><br><center>" . _t( "_UPLOAD_WHILE_WAITING" ) . "</center>";
    break;

default:
$page_text = _t( "_JOIN1" ) . $add_on;
break;
}

switch ( $page )
{ 	
	case 2:
	// new profile creation

$cl_values = "INSERT INTO Profiles SET ";
$cl_first = 0;

$respd = db_res("SELECT * FROM ProfilesDesc WHERE `visible` & 2 AND `to_db` = 1 AND ( FIND_IN_SET('0',show_on_page) OR FIND_IN_SET('".(int)$_page[name_index]."',show_on_page)) ORDER BY `order` ASC");
while ( $arrpd = mysql_fetch_array($respd) )
{
    $fname = get_input_name ( $arrpd );
    $dbname = get_field_name ( $arrpd );
	$fval = $p_arr[$fname];

if ($dbname == 'zip') $fval = strtoupper($fval);
    switch ($arrpd[type])
    {
    case 'set': // set of checkboxes
        $vals = preg_split ("/[,\']+/", $arrpd[extra], -1, PREG_SPLIT_NO_EMPTY);
		$cl_tmp_total = 0;
		$cl_tmp = 1;
        foreach ( $vals as $v )
        {
            if ( strlen(trim($v)) <= 0 ) continue;
            if ( strlen($p_arr[$fname."&".$v]) ) 
				$cl_tmp_total |= $cl_tmp;
			$cl_tmp *= 2;
        }
		$cl_values .= " `$dbname` = $cl_tmp_total";
		$cl_values .= ", "; 
        break;
    case 'r': // reference to array for combo box
		$cl_values .= " `$dbname` = ".(int)$fval;
		$cl_values .= ", ";
        break;
    case 'a': // text Area
    case 'c': // input box
    case 'p': // input box password
		$fval = str_replace_mysql ( $fval );
    case 'rb': // radio buttons
    case 'e': // enum combo box			
    case 'en': // enum combo box with numbers			
    case 'eny': // enum combo box with numbers			
		$cl_values .= " `$dbname` = '$fval'";
		$cl_values .= ", ";
        break;
    }
}

$cl_values .= " `LastReg` = NOW(),";

$seed = rand();
$cl_values .= " `seed` = ".$seed;


$create_result = db_res( $cl_values );
$IDnormal = db_arr ("SELECT ID FROM Profiles WHERE `seed`=$seed");
$IDnormal = $IDnormal[ID];
db_res ("UPDATE Profiles SET `seed` = 0 WHERE `ID`=$IDnormal");


$IDcrypt = crypt( $IDnormal, "secret_string" );  // encrypted ID for security purposes
setcookie( "IDc", $IDcrypt, 0 , "/" );
$_COOKIE[IDc] = $IDcrypt;

// -------------------------


	if ( $en_aff && $_COOKIE[idAff] )
	{
		$res = db_res("SELECT ID FROM aff WHERE ID=$_COOKIE[idAff] AND `Status`='active'");
		if ( mysql_num_rows($res) )
        {
			$res = db_res("INSERT INTO aff_members (`idAff`,`idProfile`) VALUES ($_COOKIE[idAff],$IDnormal)");
		}
	}


// ---------------
if ( strcmp( crypt( $IDnormal, 'secret_string' ), $_COOKIE[IDc] ) != 0 )
{
	ob_end_clean();

	$_page[header] = _t( "_Error" );

	$ret = "<table width=".($content_w-20)." cellpadding=4 cellspacing=4><td align=center class=text2>";
    $ret .= _t( "_MUST_HAVE_COOKIES" );
	$ret .= "</td></table>";

    return $ret;
}

$page_text = _t( "_JOIN3" ) . $add_on;
$page_text .= activation_mail( $IDnormal );
$page_text .= "<br><br><br><br><center>" . _t( "_UPLOAD_WHILE_WAITING" ) . "</center>";


// ----------------------------------------------------------

echo "<table width=".($content_w-20)." cellpadding=4 cellspacing=4><td align=center class=text2>";
echo "<div align=justify>$page_text</div>";
break;

default:

echo "<table width=".($content_w-20)." cellpadding=4 cellspacing=4><td align=center class=text2>";
?>

<script language=javascript> 
<!--        

    function validateJoinForm()
    {   

        if ( document.forms['jform'].elements['i_agree'].checked ) return true;
        alert('<?php echo _t("_CLICK_AGREE"); ?>');
        return false;
    }
-->
</script>

<form name="jform" method=post action="<? echo $_SERVER[PHP_SELF]; ?>" onSubmit="return validateJoinForm();">
<input type=hidden name=page value=2>
<input type=hidden name=ID value="<? echo $IDnormal; ?>">
<div align=justify><?	echo $page_text; ?></div>
<br><br>
<table width=100% cellspacing=0 cellpadding=0 class=text2>

<?
	$first_row = 1;
	$respd = db_res("SELECT * FROM ProfilesDesc WHERE `visible` & 2 AND ( FIND_IN_SET('0',show_on_page) OR FIND_IN_SET('".(int)$_page[name_index]."',show_on_page)) ORDER BY `order` ASC");
	while ( $arrpd = mysql_fetch_array($respd) )
	{
		$fname = get_input_name ( $arrpd );

    if ( $arrpd[get_value] && $arrpd[to_db] == 0 )
    {
        $funcbody = $arrpd[get_value];
        $func = create_function('$arg0',$funcbody);
        $p_arr[$fname] = $func($p_arr);

    }

	$not_first_row = 0;
	switch ($arrpd[type])
	{
    case 'set': // set of checkboxes 
        echo print_row_set ( $first_row, $arrpd, $p_arr[$fname], "table" );
        break;
    case 'rb': // radio buttons 
        echo print_row_radio_button ( $first_row, $arrpd, $p_arr[$fname], "table" );
        break;
	case 'r': // reference to array for combo box
		echo print_row_ref ( $first_row, $arrpd, $p_arr[$fname], "table" );
		break;
	case '0': // divider
		echo print_row_delim( $first_row, $arrpd, "panel", 3 );
        $not_first_row = 1;
        $first_row = 1;
		break;
	case 'e': // enum combo box
		echo print_row_enum( $first_row, $arrpd, $p_arr[$fname], "table" );
		break;
	case 'en': // enum combo box with numbers	
		echo print_row_enum_n( $first_row, $arrpd, $p_arr[$fname], "table" );
		break;
	case 'eny': // enum combo box with years
		echo print_row_enum_years( $first_row, $arrpd, $p_arr[$fname], "table" );
		break;
	case 'a': // text Area
		$p_arr[$fname] = stripcslashes($p_arr[$fname]);
		echo print_row_area( $first_row, $arrpd, $p_arr[$fname], "table" );
		break;
	case 'c': // input box
		$p_arr[$fname] = stripcslashes($p_arr[$fname]);
		echo print_row_edit( $first_row, $arrpd, $p_arr[$fname], "table" );
		break;
    case 'p': // input box password
		$p_arr[$fname] = stripcslashes($p_arr[$fname]);
        echo print_row_pwd( $first_row, $arrpd, $p_arr[$fname], "table" );
        break;
    default:
        $not_first_row = 1;
    }
    if ( !$not_first_row && $first_row == 1 )  $first_row = 0;
}

?>

</table>
<br><center>

<input type=checkbox name=i_agree id=i_agree ><label for=i_agree><?php echo _t("_I AGREE"); ?></label>&nbsp;&nbsp;
<input class=no type="submit" value="<? echo _t( "_Next>>" ); ?>" class="text"></center></form>
<?
    break;
}
	echo "</td></table>";

$ret = ob_get_contents();
ob_end_clean();

return $ret;
}

/**
 * print error message
 */
function report_err( $str )
{
    return "<font color=\"#880000\"><b>" . _t( "_Error" ) . ":</b> $str</font><br>";
}

?>

[kasab]

At a guess, the code is in the profiles.inc.php file. Look for the

print "<SELECT name='Country'
... (more print statements here) ...
print " </SELECT>";

statement and change that. If you don't find it in this file, try the profile_disp.inc.php file.

I'm not sure, but the files are possibly in the inc directory of your site. It'll be somewhere on your site. (I'm fairly new to this myself, and don't know much about referencing files using variables - so I'd be interested in knowing where you DO find the file).

Kathi

[excursion]

I figured it out.

Thanks to all for helping me. I would have been lost with ya. thanks Sitepointers.