Jump to content

help appreciated: getting sql error

andikam

By andikam
in PHP Coding Help

Recommended Posts

andikam Newbie

andikam

Posted
Posted

Hello guys,

 

I have tried to solve this one for a while and I just dont get it..

 

everything works fine in the code for registering a new user until:

$sql = mysql_query("INSERT INTO `users`

( `user_id` , `last_name` , `first_name` , `username` , `email` , `password` , `admin` , `houseno` ,

`streetname` , `city` , `county` , `postcode` , `contactno`)

 

VALUES (NULL , '$last_name', '$first_name', '$username', '$email', '$password', '0', '$houseno',

'$streetname', '$city', '$county', '$postcode', '$contactno'")

        or die (mysql_error()); //mysql_error will display error info for debugging purposes.

 

now the database connection is fine I know...

 

Do you see anything wrong with the code there?

 

I get the error:

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 6

 

The database is set up as follows:

 

Field  Type  Collation  Attributes  Null  Default  Extra  Action

user_id int(5) UNSIGNED No auto_increment Browse distinct values Change Drop Primary Unique Index Fulltext

last_name varchar(50) latin1_swedish_ci No Browse distinct values Change Drop Primary Unique Index Fulltext

first_name varchar(50) latin1_swedish_ci No Browse distinct values Change Drop Primary Unique Index Fulltext

username varchar(20) latin1_swedish_ci No Browse distinct values Change Drop Primary Unique Index Fulltext

email varchar(80) latin1_swedish_ci No Browse distinct values Change Drop Primary Unique Index Fulltext

password varchar(20) latin1_swedish_ci No Browse distinct values Change Drop Primary Unique Index Fulltext

admin tinyint(3) UNSIGNED Yes 0 Browse distinct values Change Drop Primary Unique Index Fulltext

houseno varchar(20) latin1_swedish_ci No Browse distinct values Change Drop Primary Unique Index Fulltext

streetname varchar(30) latin1_swedish_ci No Browse distinct values Change Drop Primary Unique Index Fulltext

city varchar(30) latin1_swedish_ci No Browse distinct values Change Drop Primary Unique Index Fulltext

county varchar(30) latin1_swedish_ci No Browse distinct values Change Drop Primary Unique Index Fulltext

postcode varchar(7) latin1_swedish_ci No Browse distinct values Change Drop Primary Unique Index Fulltext

contactno varchar(11) latin1_swedish_ci No Browse distinct values Change Drop Primary Unique Index Fulltext

activated tinyint(1) Yes 0

 

Any suggestions? Many thanks,

 

andikam

Link to comment
https://forums.phpfreaks.com/topic/47092-help-appreciated-getting-sql-error/
Share on other sites
flappy_warbucks Newbie

flappy_warbucks

Posted
Posted

your setting the userID element to null in the query, get rid of that.

 

that could be what tripping you up, as you set it as a primary key that auto_increment.

 

change from this:

$sql = mysql_query("INSERT INTO `users` 
( `user_id` , `last_name` , `first_name` , `username` , `email` , `password` , `admin` , `houseno` , 
`streetname` , `city` , `county` , `postcode` , `contactno`)

VALUES (NULL , '$last_name', '$first_name', '$username', '$email', '$password', '0', '$houseno', 
'$streetname', '$city', '$county', '$postcode', '$contactno'")
        or die (mysql_error());

 

to this:

$sql = mysql_query("INSERT INTO `users` 
(`last_name` , `first_name` , `username` , `email` , `password` , `admin` , `houseno` , 
`streetname` , `city` , `county` , `postcode` , `contactno`)

VALUES ( '$last_name', '$first_name', '$username', '$email', '$password', '0', '$houseno', 
'$streetname', '$city', '$county', '$postcode', '$contactno'")
        or die (mysql_error());

 

:)

andikam Newbie

andikam

Posted
  • Author
Posted

If I do that it then says:

 

Parse error: syntax error, unexpected ';' in /home/.ditzy/itandweb/registration.php on line 145

 

$sql = mysql_query("INSERT INTO `users`

(`last_name` , `first_name` , `username` , `email` , `password` , `houseno` ,

`streetname` , `city` , `county` , `postcode` , `contactno`)

 

VALUES ( '$last_name', '$first_name', '$username', '$email', '$password', '$houseno',

'$streetname', '$city', '$county', '$postcode', '$contactno')"

 

    or die (mysql_error()); //mysql_error will display error info for debugging purposes line 145

 

 

andikam Newbie

andikam

Posted
  • Author
Posted
<?php

require_once 'php/database.php'; // usual include

?>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

<title>Register to use the system</title>

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">

<link rel="stylesheet" type="text/css" href="includes/style.css"/>

</head>

 

<body>

<p class="style1"><strong>Welcome to the User Registation Page</strong></p>

<form name="form" method="post" action="registration.php">

  <table width="100%" border="0" cellpadding="0" cellspacing="0">

        <tr>

          <td width="28%">Username:</td>

          <td width="72%" style="padding-left: 10px; padding-bottom: 10px;"><input name="username" type="text" maxlength="20" size="20" /></td>

        </tr>

<tr>

          <td width="28%">First name:</td>

          <td width="72%" style="padding-left: 10px; padding-bottom: 10px;"><input name="first_name" type="text" maxlength="20" size="20" /></td>

        </tr>

<tr>

          <td width="28%">Surname:</td>

          <td width="72%" style="padding-left: 10px; padding-bottom: 10px;"><input name="last_name" type="text" maxlength="20" size="20" /></td>

        </tr>

        <tr>

          <td width="28%">Password:</td>

          <td width="72%" style="padding-left: 10px; padding-bottom: 10px;"><input name="password" type="password" maxlength="20" size="20" /></td>

        </tr>

        <tr>

          <td width="28%">Password again:</td>

          <td width="72%" style="padding-left: 10px; padding-bottom: 10px;"><input name="password2" type="password" maxlength="20" size="20" /></td>

        </tr>

        <tr>

          <td width="28%">Email:</td>

          <td width="72%" style="padding-left: 10px; padding-bottom: 10px;"><input name="email" type="text" size="20" maxlength="40"/></td>

        </tr>

        <tr>

          <td width="28%">Email again:</td>

          <td width="72%" style="padding-left: 10px; padding-bottom: 10px;"><input name="email2" type="text" size="20" maxlength="40"/></td>

        </tr>

<tr>

          <td width="28%">House Number or Name:</td>

          <td width="72%" style="padding-left: 10px; padding-bottom: 10px;"><input name="houseno" type="text" size="40" maxlength="40"/></td>

        </tr>

<tr>

          <td width="28%">Street name:</td>

          <td width="72%" style="padding-left: 10px; padding-bottom: 10px;"><input name="streetname" type="text" size="60" maxlength="60"/></td>

        </tr>

<tr>

          <td width="28%">City/Town:</td>

          <td width="72%" style="padding-left: 10px; padding-bottom: 10px;"><input name="city" type="text" size="60" maxlength="60"/></td>

        </tr>

<tr>

          <td width="28%">County:</td>

          <td width="72%" style="padding-left: 10px; padding-bottom: 10px;"><input name="county" type="text" size="60" maxlength="60"/></td>

        </tr>

<tr>

          <td width="28%">Post Code:</td>

          <td width="72%" style="padding-left: 10px; padding-bottom: 10px;"><input name="postcode" type="text" size="10" maxlength="7" /></td>

        </tr>

<tr>

          <td width="28%">Contact Number:</td>

          <td width="72%" style="padding-left: 10px; padding-bottom: 10px;"><input name="contactno" type="text" size="11" maxlength="12"/></td>

        </tr>

        <tr>

          <td width="28%"></td>

          <td width="72%" style="padding-left: 10px; padding-bottom: 10px;">

            <input type="submit" name="register" value="Confirm" />

            <input type="reset" value="Reset Registation" />

          </td>

        </tr>

        </table>

        </form>

 

<?php

 

if(isset($_POST['register'])) //if the form is clicked then validate the data.

    {

$last_name = $_POST['last_name'];

$first_name = $_POST['first_name'];

$username = $_POST['username'];

$email = $_POST['email'];

$email2 = $_POST['email2'];

$password = $_POST['password'];

$password2 = $_POST['password2'];

$houseno = $_POST['houseno'];

$streetname = $_POST['streetname'];

$city = $_POST['city'];

$county = $_POST['county'];

$postcode = $_POST['postcode'];

$contactno = $_POST['contactno'];

 

// Validation

 

// If one of or all of the fields are missing then notice is given that error has been detected

if((!$first_name) || (!$last_name) || (!$username) || (!$email) || (!$email2)

|| (!$password) || (!$password2)  || (!$houseno) || (!$streetname) || (!$city)  || (!$county)

  || (!$postcode) || (!$contactno)){

    echo '<p class="alert">One or more fields where left blank. Please complete the entire form</p>';

 

// Here specific error message given about each problem

    if(($email) != ($email2)) {

        echo "<p class='alert'>The email address you have entered does not match</p>";

    }

if(($password) != ($password2)) {

        echo "<p class='alert'>The password you have entered does not match</p>";

    }

if (!preg_match ("/^[A-z0-9\._-]+"

        . "@"

        . "[A-z0-9][A-z0-9-]*" // Validates the email address is in the correct format.

        . "(\.[A-z0-9_-]+)*"

        . "\.([A-z]{2,6})$/", $email)) {

echo '<p class="alert">Email address is not entered in the correct format.</p>';

}

    // End of error checking

    exit();

// Exit only if failure to pass check.

}

   

// Check against existing users

 

$sql_username_check = mysql_query("SELECT username FROM users

            WHERE username='$username'"); // Selects the number of users with the username entered

 

$username_check = mysql_num_rows($sql_username_check); // Returns the number of rows found

 

if($username_check > 0){ // If the number of rows of users already using that username then error message returned.

        echo "<b>Error: </b>The username you selected is already in use. Please choose another. Note: You are allowed to use numbers in your username.<br><br>";   

echo 'To go back to your form please press your browser back button.';

    exit();

}

 

$sql = mysql_query("INSERT INTO users(

'last_name' , 'first_name' , 'username' , 'email' , 'password' , 'houseno' ,

'streetname' , 'city' , 'county' , 'postcode' , 'contractno')

VALUES ( '$last_name', '$first_name', '$username', '$email', '$password', '$houseno',

'$streetname', '$city', '$county', '$postcode', '$contactno')"

 

    or die (mysql_error()); //mysql_error will display error info for debugging purposes

 

if(!$sql){

  echo 'Error detected. SQL';

} else {

    $userid = mysql_insert_id();

// User is emailed their password. This can either be mailed to the user or to the administator that joint sets up a new user account.

    $subject = ">>> - Welcome new user";

    $message = "Ref: $first_name $last_name registration,

    Registation successful.

   

Please follow the following steps to finalise registation:

   

    1. Copy and paste or click on the below link to continue:

 

>>>

   

Note please make a safe note of the following information:

    Username: $username

    Password: $password

   

>>> - CD, Vinyl, Tickets";

   

    mail($email, $subject, $message,

        "From: Rounder Records Admin <>>>>>>>>>\n

        X-Mailer: PHP/" . phpversion());

    echo '<font face="Verdana" size="3">The form was successfully submitted please check

your inbox with the email address you supplied. The email entitled "Rounder Records -

Welcome new user" will contain the final stage of your registation.</font><br><br>

<a href="/products.php">Click here</a> to go back to the products page.';

}

} // end function

 

?>

 

</body>

</html>

 

any ideas anyone, really seems straightforward but not working for an uknown reason

rameshfaj Regular Member

rameshfaj

Posted
Posted

Ur query:

$sql = mysql_query("INSERT INTO `users`

( `user_id` , `last_name` , `first_name` , `username` , `email` , `password` , `admin` , `houseno` ,

`streetname` , `city` , `county` , `postcode` , `contactno`)

 

VALUES (NULL , '$last_name', '$first_name', '$username', '$email', '$password', '0', '$houseno',

'$streetname', '$city', '$county', '$postcode', '$contactno'")

        or die (mysql_error()); //mysql_error will display error info for debugging purposes.

 

Suggestion:

$sql="INSERT INTO `users`

( `user_id` , `last_name` , `first_name` , `username` , `email` , `password` , `admin` , `houseno` ,

`streetname` , `city` , `county` , `postcode` , `contactno`)

 

VALUES ( '".$last_name."', '".$first_name."', '".$username."', '".$email."', '".$password."', '0', '."$houseno."',

'".$streetname."', '".$city."', '".$county."', '".$postcode."', '".$contactno."'";

$result=mysql_query($sql) or die(mysql_error);

 

Note the auto increamenting field can be omitted ex:user_id  here---------

 

 

Archived

This topic is now archived and is closed to further replies.

Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.