please HELP! Display/update data in User Manager from DB

Hi
I would like to build a registration form with non-standard fields (for ex. City) using ChronoForms.
I followed the tutorial 4 about building reg form.
Also I fount another tutorial on how to insert data for custom fields while registering using Joomla Reg form, and display it in Admin User Manager.
http://www.youtube.com/watch?v=dk_86nHu3AM

1. phpMyAdmin
- select jos_users
- add 2 fields, press Go
- 1st field: phone; type: varchar; lenght/values: 20
- 2nd field: fax; type: varchar; lenght/values: 20
- press Save

2. Edit libraries/joomla/database/table/user.php
- after "var $params			= null;" add:
	var $phone			= null;
	var $fax			= null;

3. Edit components/com_user/views/register/tmpl/default.php


After:
	<tr>
		<td height="40">
			<label id="emailmsg" for="email">
				<?php echo JText::_( 'Email' ); ?>:
			</label>
		</td>
		<td>
			<input type="text" id="email" name="email" size="40" value="<?php echo $this->escape($this->user->get( 'email' ));?>" class="inputbox required validate-email" maxlength="100" /> *
		</td>
	</tr>


Insert:
	<tr>
		<td height="40">
			<label id="phonemsg" for="phone">
				<?php echo JText::_( 'Phone' ); ?>:
			</label>
		</td>
		<td>
			<input type="text" id="phone" name="phone" size="40" value="<?php echo $this->escape($this->user->get( 'phone' ));?>" class="inputbox required" maxlength="100" /> *
		</td>
	</tr>

	<tr>
		<td height="40">
			<label id="faxmsg" for="fax">
				<?php echo JText::_( 'Fax' ); ?>:
			</label>
		</td>
		<td>
			<input type="text" id="fax" name="fax" size="40" value="<?php echo $this->escape($this->user->get( 'fax' ));?>" class="inputbox required" maxlength="100" /> *
		</td>
	</tr>
	
4. Edit components/com_user/views/user/tmpl/form.php

After:

	<tr>
		<td>
			<label for="email">
				<?php echo JText::_( 'email' ); ?>:
			</label>
		</td>
		<td>
			<input class="inputbox required validate-email" type="text" id="email" name="email" value="<?php echo $this->escape($this->user->get('email'));?>" size="40" />
		</td>
	</tr>

Insert:

	<tr>
		<td>
			<label for="phone">
				<?php echo JText::_( 'phone' ); ?>:
			</label>
		</td>
		<td>
			<input class="inputbox required" type="text" id="email" name="phone" value="<?php echo $this->escape($this->user->get('phone'));?>" size="40" />
		</td>
	</tr>

	<tr>
		<td>
			<label for="fax">
				<?php echo JText::_( 'fax' ); ?>:
			</label>
		</td>
		<td>
			<input class="inputbox required" type="text" id="fax" name="fax" value="<?php echo $this->escape($this->user->get('fax'));?>" size="40" />
		</td>
	</tr>
	
5. Edit administrator/components/com_users/views/user/tmpl/form.php

After:

				<tr>
					<td class="key">
						<label for="email">
							<?php echo JText::_( 'Email' ); ?>
						</label>
					</td>
					<td>
						<input class="inputbox" type="text" name="email" id="email" size="40" value="<?php echo $this->user->get('email'); ?>" />
					</td>
				</tr>
				
Insert:

				<tr>
					<td class="key">
						<label for="phone">
							<?php echo JText::_( 'Phone' ); ?>
						</label>
					</td>
					<td>
						<input class="inputbox" type="text" name="phone" id="phone" size="40" value="<?php echo $this->user->get('phone'); ?>" />
					</td>
				</tr>
				
				<tr>
					<td class="key">
						<label for="fax">
							<?php echo JText::_( 'Fax' ); ?>
						</label>
					</td>
					<td>
						<input class="inputbox" type="text" name="fax" id="fax" size="40" value="<?php echo $this->user->get('fax'); ?>" />
					</td>
				</tr>

But the point is that I would like to use Chrono Registration Form, but not Joomla reg form like in the tutorial.
So, my question is: I have a non-standard input text field in the Chrono Reg Form - City.

What PHP code should I use to insert that data from City to field city that is inside table jos_users?

My Chrono Form Code is:

<div class="form_item">
  <div class="form_element cf_textbox">
    <label class="cf_label" style="width: 150px;">Name</label>
    <input class="cf_inputbox required" maxlength="150" size="30" title="" id="text_0" name="text_0" type="text" />
  
  </div>
  <div class="cfclear"> </div>
</div>

<div class="form_item">
  <div class="form_element cf_textbox">
    <label class="cf_label" style="width: 150px;">Username</label>
    <input class="cf_inputbox required" maxlength="150" size="30" title="" id="text_1" name="text_1" type="text" />
  
  </div>
  <div class="cfclear"> </div>
</div>

<div class="form_item">
  <div class="form_element cf_textbox">
    <label class="cf_label" style="width: 150px;">Email</label>
    <input class="cf_inputbox required validate-email" maxlength="150" size="30" title="" id="text_2" name="text_2" type="text" />
  
  </div>
  <div class="cfclear"> </div>
</div>

<div class="form_item">
  <div class="form_element cf_textbox">
    <label class="cf_label" style="width: 150px;">City</label>
    <input class="cf_inputbox required" maxlength="150" size="30" title="" id="text_3" name="text_3" type="text" />
  
  </div>
  <div class="cfclear"> </div>
</div>

<div class="form_item">
  <div class="form_element cf_password">
    <label class="cf_label" style="width: 150px;">Password</label>
    <input class="cf_inputbox required" maxlength="150" size="30" title="" id="text_7" name="text_7" type="password" />
    
  </div>
  <div class="cfclear"> </div>
</div>

<div class="form_item">
  <div class="form_element cf_password">
    <label class="cf_label" style="width: 150px;">Confirm Password</label>
    <input class="cf_inputbox required" maxlength="150" size="30" title="" id="text_8" name="text_8" type="password" />
    
  </div>
  <div class="cfclear"> </div>
</div>

<div class="form_item">
  <div class="form_element cf_captcha">
    <label class="cf_label" style="width: 150px;">Image Verification</label>
    <span>{imageverification}</span> 
    
    </div>
  <div class="cfclear"> </div>
</div>

<div class="form_item">
  <div class="form_element cf_button">
    <input value="Register" name="button_13" type="submit" />
  </div>
  <div class="cfclear"> </div>
</div>

I found this kind of code in order to insert some data to joomla table:

<?php
$db =& JFactory::getDBO();
$query = "/* some valid sql string */";
$db->setQuery($query);
$result = $db->query();
?>

But I am not sure what SQL query string should I use in order to insert the data from City input box to field city that is inside table jos_users.

1. Can you please help me with that PHP code?
2. where that code must be inserted?
On Submit code - before sending email?
or
On Submit code - after sending email?

Thank you!

If editing jos_users is not a good idea, I thought that maybe i should create a separate table "jos_users_extended" and store there all fields: Joomla Standard (username, name, pass) + additional fields (city, zip..), and in User Manager form I should display that custom fields.

For that I am editing \administrator\components\com_users\views\user\tmpl\form.php

				<tr>
					<td class="key">
						<label for="city">
							<?php echo JText::_( 'City' ); ?>
						</label>
					</td>
					<td>
						<input class="inputbox" type="text" name="city" id="city" size="40" value="
						<?php
$user =& JFactory::getUser();
$db = JFactory::getDBO();

$query = "SELECT text_3"
. " FROM jos_users_extended"
. " WHERE cf_user_id = $id"
;
$db->setQuery($query);
$rows = $db->loadObjectList();						
						?>" />
					</td>
				</tr>	

in phpMyAdmin text_3 field has a Value, but in UserManager it is empty. I am sure the code is wrong, I don't know php..
What code should I have to display text_3 value in user manager?

Thanks!

Hi ihtus,

You are correct that it's not a good idea to add extra columns to jos_users. Equally it's not a good idea to hack the core Joomla! files in com_user. This makes upgrading more complex as you have to make sure that your changes are preserved.

Joomla! does support template overrides so you could probably add a file to your template 'html' folder to do this if you need to follow that route.

Using ChronoForms it's simpler just to create a ChronoForm to do what you want and to make sure that the user_id is included so that you can link the results to the user. (ChronoForms will do this automatically if you use the ChronoForms Create Table to create the new table and then use a DB Connection to save the results.)

Bob

Bob, as always thanks for your fast reply!


Editing this file (or template override) - \administrator\components\com_users\views\user\tmpl\form.php

Can you please tell me what code should I use to display a value from a field (text_3) from the table (jos_users_extended) which was created by CF, and also to change the value in Chrono DB Table if I will enter another value in the Joomla User Manager and save it.

The code below doesn't work for me (it even doesn't show the current value, and for sure doesn't update the table with a new entered value in user manager)

input class="inputbox" type="text" name="city" id="city" size="40" value="
                  <?php
$user =& JFactory::getUser();
$db = JFactory::getDBO();

$query = "SELECT text_3"
. " FROM jos_users_extended"
. " WHERE cf_user_id = $id"
;
$db->setQuery($query);
$rows = $db->loadObjectList();                  
                  ?>" />

Thanks a bunch!

Hi ihtus,

This isn't a ChronoForms question and I don't recommend that you do things this way.

Your code should be something like

<?php
$user =& JFactory::getUser();
$db = JFactory::getDBO();

$query = "
  SELECT text_3
    FROM jos_users_extended
    WHERE cf_user_id = $id"
;
$db->setQuery($query);
$text_3 = $db->loadResult();                 
?>
<input class="inputbox" type="text" name="city" id="city" size="40" value="<?php echo $text_3; ?>" />

Bob

Thanks for your reply
Sorry for that offtopic question..

I don't recommend that you do things this way

But what do you recommend?

The code doesn't work...😟

Here is what I get when debug is On

JDatabaseMySQL::query: 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 '' at line 3 SQL=SELECT text_3 FROM jos_users_extended WHERE cf_user_id =

Thank you

Hi ihtus,

Using ChronoForms it's simpler just to create a ChronoForm to do what you want and to make sure that the user_id is included so that you can link the results to the user. (ChronoForms will do this automatically if you use the ChronoForms Create Table to create the new table and then use a DB Connection to save the results.)

Bob

The code doesn't work...😟

Here is what I get when debug is On

JDatabaseMySQL::query: 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 '' at line 3 SQL=SELECT text_3 FROM jos_users_extended WHERE cf_user_id =

Using ChronoForms it's simpler just to create a ChronoForm to do what you want and to make sure that the user_id is included so that you can link the results to the user. (ChronoForms will do this automatically if you use the ChronoForms Create Table to create the new table and then use a DB Connection to save the results.)

Dear Bob, I have created a new form, and applied to it Joomla Registration Plugin. Also I have created a new table - jos_users_extended
And made DB connection inside the form to jos_users_extended.

I don't know how "to make sure that the user_id is included so that you can link the results to the user". Can you please explain what should I do to have user_id included?
From my description above what I did - is that right? Or you meant something else?

Sorry for misunderstanding.

Thank you very much!

Hi ihtus,

Please see this post.

Bob