Concatenating fields

richardbotw

I've looked everywhere for this but cannot find it so I'm hoping you guys can help...

I am creating a mock booking system, and as part of the registration process, the user submits the usual details including firstname, surname, address, desired password etc etc.

I need a unique username to be created and added to the Member table. I want the unique username to be the users surname together with the MemberID in the database (which is on auto-increment)

ie: davis5
smith6
jones7
taylor8... etc

I do not know how to do this, can anyone help? If it is any use I have included the PHP code in my parsing file called create_entry.php - thank you for you help.

<?php

if (!$surname || !$firstname || !$address || !$town|| !$postcode|| !$telephone|| !$nextkin|| !$password)
{
    echo "You have not entered all the required details.<br>".
    "Please go back and try again.";
    exit;
}

$surname = addslashes($surname);
$firstname = addslashes($firstname);
$address = addslashes($address);
$town = addslashes($town);
$postcode = addslashes($postcode);
$telephone = addslashes($telephone);
$nextkin = addslashes($nextkin);
$password = addslashes($password);

$db_host ="www.sandj.34sp.com";
$db_pass ="cheesetits";
$db_usr ="sandj";

@$db = mysql_connect($db_host, $db_usr, $db_pass);

if (!$db)
{
echo "Error : Could not connect to the database. Please try again later.";
exit;
}

mysql_select_db("pooleflights", $db) or  

    die ("Could not select database"); 

 if ($submit == "Register")
{
   $query = "insert into Member (Surname,Firstname,Address,Town,Postcode,Telephone,Nextkin,Password) values ('$surname', '$firstname', '$address', '$town', '$postcode', '$telephone', '$nextkin', '$password')";
   mysql_query($query) or 
             die (mysql_error());
?>
<h3>You are now registered</h3>
<a href="index.php">Go back to main page</a>
<?php
}
else
{
  include("not.html");
}
?>

nnichols

Use mysql_insert_id() to retrieve the new user_id after the initial insert. Then do an update -

UPDATE Member SET Username=CONCAT(Surname,MemberID) WHERE MemberID=$id;

where $id is the result from mysql_insert_id().

Unfortunately it cannot be done with a single insert as the auto_increment value is not assigned until the INSERT is run, but I will post again if I am wrong about that.

Hope this helps 😉

nnichols

This works fine -

$sql = "INSERT INTO Member  (Surname, Firstname, Address, Town, Postcode, Telephone, Nextkin, Password)
         VALUES ('$surname', '$firstname', '$address', '$town', '$postcode', '$telephone', '$nextkin', '$password')";
mysql_query($sql,$db) or die('MySQL error: '.mysql_error().'<br>Query: '.$sql);
$sql = "UPDATE Member SET Username=CONCAT(Surname,MemberID) WHERE MemberID=LAST_INSERT_ID()";
mysql_query($sql,$db) or die('MySQL error: '.mysql_error().'<br>Query: '.$sql);

richardbotw

Thank you so so much for your help, works a treat - man, you people are intelligent!!

richardbotw

As you can probably see at the website that has been created:

Click here

(Tap in some random suff it doesn't matter)....

I've got the data entered to be displayed, however I'm now trying to figure how to display the updated username on this page - any idea how? Do I have to assign Username as a variable or what?

Cheers

Richard Terry (J)

nnichols

Can you please post the code for the page, then I can have a look and see how best to do it.

richardbotw

<?php

if (!$surname || !$firstname || !$address || !$town|| !$postcode|| !$telephone|| !$nextkin|| !$password)
{
    echo "You have not entered all the required details.<br>".
    "Please go back and try again.";
    exit;
}

$surname = addslashes($surname);
$firstname = addslashes($firstname);
$address = addslashes($address);
$town = addslashes($town);
$postcode = addslashes($postcode);
$telephone = addslashes($telephone);
$nextkin = addslashes($nextkin);
$password = addslashes($password);

$db_host ="www.sandj.34sp.com";
$db_pass ="cheesetits";
$db_usr ="sandj";

@$db = mysql_connect($db_host, $db_usr, $db_pass);

if (!$db)
{
echo "Error : Could not connect to the database. Please try again later.";
exit;
}

mysql_select_db("pooleflights", $db) or  

    die ("Could not select database"); 

 if ($submit == "Register")
{
$sql = "INSERT INTO Member  (Surname, Firstname, Address, Town, Postcode, Telephone, Nextkin, Password,)
         VALUES ('$surname', '$firstname', '$address', '$town', '$postcode', '$telephone', '$nextkin', '$password',)";
mysql_query($sql,$db) or die('MySQL error: '.mysql_error().'<br>Query: '.$sql);
$sql = "UPDATE Member SET Username=CONCAT(Surname,MemberID) WHERE MemberID=LAST_INSERT_ID()";
mysql_query($sql,$db) or die('MySQL error: '.mysql_error().'<br>Query: '.$sql);
?>

<STYLE type="text/css">
<!-- A { text-decoration:none } A:hover { color:#FFFFFF } -->
</STYLE>
</head>

<body bgcolor="#FFFFFF">
<div align="center"><center>

<table border="0" cellpadding="0" cellspacing="0" width="750">
    <tr>
        <td width="255"><a href="index.php" target="_top"><img
        src="img/plane.jpg" border="0" width="255" height="95"></a></td>
        <td align="center" width="505" bgcolor="#2C08E2"><p
        align="center"><a href="index.php" target="_top"><img
        src="img/banner.jpg" border="0" width="400" height="60"></a></p>
        </td>
    </tr>
</table>
</center></div><div align="center"><center>

<table border="0" cellpadding="0" cellspacing="0" width="750">
    <tr>
        <td bgcolor="#AFA4F2"><p align="center"><font
        color="#5445DC" size="1" face="Arial"><strong>REGISTERED!</strong></font></p>
        </td>
    </tr>
</table>
</center></div><div align="center"><center>

<table border="1" cellpadding="0" cellspacing="0" width="750"
bordercolor="#AFA4F2">
    <tr>
        <td bgcolor="#FDFDE8"><table border="0" cellpadding="0"
        cellspacing="0" width="100%">
            <tr>
                <td><img src="img/ytopleft.jpg" width="27"
                height="21"></td>
                <td>&nbsp;</td>
                <td align="right"><img src="img/ytopright.jpg"
                width="27" height="21"></td>
            </tr>
        </table>
        <div align="center"><center><table border="0"
        cellpadding="0" cellspacing="0" width="80%">
            <tr>
                <td><font color="#FDFDE8"><img
                src="img/register.jpg" align="left" hspace="25"
                width="108" height="150"></font></td>
                <td><font color="#0000A0" size="2" face="Arial">Thank
                you for registering with us here at Poole
                Flights. Below is your unique username and
                password - keep it safe because you'll need it
                later! If you ever forget it you'll need to
                contact support - you may be charged an
                administration fee for retreival of your details.</font></td>
            </tr>
        </table>
        </center></div><div align="center"><center><table
        border="0" cellpadding="0" cellspacing="0" width="100%">
            <tr>
                <td><form action="create_entry.php" method="post">
                    <p align="center"><font color="#0000A0"
                    size="2" face="Arial"><strong>Hello <?php echo $firstname; ?>!</strong><br>
                    You signed up on <?php echo date("l, F jS Y")." at ".date("g:ia"); ?>.<br>
                    These are your details.</font></p>
                    <p align="center"><font color="#800000"
                    size="2" face="Arial">Name: <strong><?php echo $firstname; ?> <?php echo $surname; ?></strong></font></p>
                    <p align="center"><font color="#800000"
                    size="2" face="Arial">Address: <strong><?php echo $address; ?></strong></font></p>
                    <p align="center"><font color="#800000"
                    size="2" face="Arial">Town: <strong><?php echo $town; ?></strong></font></p>
                    <p align="center"><font color="#800000"
                    size="2" face="Arial">Postcode: <strong><?php echo $postcode; ?></strong></font></p>
                    <p align="center"><font color="#800000"
                    size="2" face="Arial">Telephone: <strong><?php echo $telephone; ?></strong></font></p>
                    <p align="center"><font color="#800000"
                    size="2" face="Arial">Next of Kin: <strong><?php echo $nextkin; ?></strong></font></p>
                    <p align="center"><font color="#800000"
                    size="2" face="Arial"><strong>YOUR USERNAME:<?php echo $username; ?></strong></font></p>
                    <p align="center"><font color="#800000"
                    size="2" face="Arial"><strong>YOUR PASSWORD: <?php echo $password; ?></strong></font></p>
                </form>
                </td>
            </tr>
        </table>
        </center></div><p align="center"><a
        href="mailto:info@samjdavis.co.uk"><font color="#94830C"
        size="1" face="Arial">Forgot your details? Then contact
        Support</font></a></p>
        <table border="0" cellpadding="0" cellspacing="0"
        width="100%">
            <tr>
                <td><img src="img/ybotleft.jpg" width="27"
                height="21"></td>
                <td>&nbsp;</td>
                <td align="right"><img src="img/ybotright.jpg"
                width="27" height="21"></td>
            </tr>
        </table>
        </td>
    </tr>
</table>
</center></div><div align="center"><center>

<table border="1" cellpadding="0" cellspacing="0" width="750"
bordercolor="#AFA4F2">
    <tr>
        <td bgcolor="#DDF8DA"><table border="0" cellpadding="0"
        cellspacing="0" width="100%">
            <tr>
                <td><img src="img/gtopleft.jpg" width="27"
                height="21"></td>
                <td>&nbsp;</td>
                <td align="right"><img src="img/gtopright.jpg"
                width="27" height="21"></td>
            </tr>
        </table>
        <div align="center"><center><table border="0"
        cellpadding="0" cellspacing="0" width="500"
        bgcolor="#DDF8DA">
            <tr>
                <td bgcolor="#DDF8DA"><p align="center"><font
                color="#0000A0" size="2" face="Arial">Poole
                Flights is <strong>Dorset's most reputable
                aviation company</strong>. We provide scenic
                pleasure flights around Poole Bay and the Isle of
                White. We operate six days a week with three
                daily flights flying across the bay area. We have
                been featured on local radio station Fire107.6FM
                and 2CR FM.</font></p>
                <p align="left"><img src="img/bayfront.jpg"
                align="middle" width="591" height="80" loop="0"></p>
                </td>
            </tr>
        </table>
        </center></div><table border="0" cellpadding="0"
        cellspacing="0" width="100%">
            <tr>
                <td><img src="img/gbotleft.jpg" width="27"
                height="21"></td>
                <td>&nbsp;</td>
                <td align="right"><img src="img/gbotright.jpg"
                width="27" height="21"></td>
            </tr>
        </table>
        </td>
    </tr>
</table>
</center></div><div align="center"><center>

<table border="0" cellpadding="0" cellspacing="0" width="750">
    <tr>
        <td align="center" background="img/fade.jpg"><p
        align="center"><font color="#0000A0" size="1"
        face="Arial"><br>
        © Poole Flights 2003<br>
        <br>
        </font></p>
        </td>
    </tr>
</table>
</center></div>

<?php
}
else
{
  include("not.html");
}
?>

nnichols

$sql = "INSERT INTO Member  (Surname, Firstname, Address, Town, Postcode, Telephone, Nextkin, Password,)
         VALUES ('$surname', '$firstname', '$address', '$town', '$postcode', '$telephone', '$nextkin', '$password',)";
mysql_query($sql,$db) or die('MySQL error: '.mysql_error().'<br>Query: '.$sql);

// note I have changed from using the MySQL LAST_INSERT_ID() function to the PHP mysql_insert_id() function
$id = mysql_insert_id($db);

$sql = "UPDATE Member SET Username=CONCAT(Surname,MemberID) WHERE MemberID=$id";
mysql_query($sql,$db) or die('MySQL error: '.mysql_error().'<br>Query: '.$sql);

// as we know that username is surname.id we can use
$username = $surname.$id;

Hope this does the trick 😉

richardbotw

Hi again, thanks for this, I inserted the followign code...

$sql = "INSERT INTO Member  (Surname, Firstname, Address, Town, Postcode, Telephone, Nextkin, Password,)
         VALUES ('$surname', '$firstname', '$address', '$town', '$postcode', '$telephone', '$nextkin', '$password',)";
mysql_query($sql,$db) or die('MySQL error: '.mysql_error().'<br>Query: '.$sql);

$id = mysql_insert_id($db);

$sql = "UPDATE Member SET Username=CONCAT(Surname,MemberID) WHERE MemberID=$id";
mysql_query($sql,$db) or die('MySQL error: '.mysql_error().'<br>Query: '.$sql);

$username = $surname.$id;

?>

However got this error back after submitting my data....

MySQL error: You have an error in your SQL syntax near ') VALUES ('Steven', 'John', '23 Wigan Way', 'Fernheath', 'BH6 7TH', '0' at line 1
Query: INSERT INTO Member (Surname, Firstname, Address, Town, Postcode, Telephone, Nextkin, Password,) VALUES ('Steven', 'John', '23 Wigan Way', 'Fernheath', 'BH6 7TH', '01202 555444', 'Jean Stevens', 'flippant',)

Any ideas??

nnichols

There's an extra comma at the end of both the field and value lists.