SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Enthusiast
    Join Date
    Nov 2012
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile MySQL_select code not working well in php script.

    Dearest Friends, brothers and fellow pro programmers,

    Please I have this headache that with using mysql_select() function in php.
    I create a register.php page and a confirmReg.php as the action page. The code below is the action page to process the register.php form elements.
    Before any user can be recorded in mysql database, I want to first check whether such username or email address exists. If true, then return to register.php else, record the user and redirect to success.php page.
    But php does not return any errors even when the same user is already present in the database.

    Please kindly help me review the codes

    PHP Code:

    <?php session_start();

    $server="localhost";
    $server_user="root";
    $server_pass="";
    $db_name="dce";

    $pword_error="Your passwords did not match.";
    $name=$_POST['name'];
    $uname=$_POST['uname'];
    $pword=$_POST['pword'];
    $email=$_POST['email'];
    $con_pword=$_POST['con_pword'];

    $to=$email;
    $from="info@dce.com";
    $subject="User Registration Confirmation!";
    $body=
    "You have received this mail as a result of the registration process you initiated on our webpage. <br>
    Kindly activate your email to start using your account. This activation link <a href='http://dce.com/user/email_activation.php'> " 
    .session_id(). "</a> will expire in 15 minutes.";

    if (empty(
    $uname) || empty($name) || empty($pword) || empty($con_pword) || empty($email)) {
    //echo "Invalid registration details.";
    die("You must complete all fields!"); }
    elseif(
    $pword!=$con_pword) {
    die(
    "Unmatched Passwords: ".$pword_error); } 
    elseif (!
    $con=mysql_connect($server,$server_user,$server_pass)) {
    die(
    'Could not connect: ' mysql_error()); } 
    elseif(!
    mysql_select_db($db_name$con)) {
    die(
    "Could not connect to the database: " mysql_error()); }
    elseif(
    mysql_query("SELECT uname,email FROM userslogin WHERE email=$email ORDER BY email ASC")) {
    die(
    "This user already exists."); }
    elseif(!
    mysql_query("INSERT INTO userslogin(name,uname,pword,email) VALUES('$name', '$uname', '$pword', '$email')")) {
    die(
    "Records could not be inserted: " mysql_error()); }
    elseif(!
    mail($to$subject$body$from)) { //send a mail to the registrants.
    die();
    header ("Location: registerError.php");}
    else {
    //redirect the user either to the login page or create a user cookie and redirect to main page.
    header("Location: ../Accounts/regSuccess.php"); 
    }
    mysql_close($con); //close connection
    ?>

  2. #2
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,136
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    First off, you definitely need to use filter_var on ALL of your POST variables (if you plan to keep using mysql functions). So please use it.

    Examples:
    PHP Code:
    $name=filter_var($_POST['name'], FILTER_SANITIZE_STRING); 
    $uname=filter_var($_POST['uname'], FILTER_SANITIZE_STRING);
    $pword=filter_var($_POST['pword'], FILTER_SANITIZE_STRING);
    $email=filter_var($_POST['email'], FILTER_SANITIZE_EMAIL); 
    $con_pword=filter_var($_POST['con_pword'], FILTER_SANITIZE_STRING); 
    Even that though likely won't protect against ALL SQL injections.

    Next you need to put your variables in quotes (since they are all strings) in your SQL query.
    PHP Code:
    mysql_query("SELECT uname,email FROM userslogin WHERE email='$email' ORDER BY email ASC"

  3. #3
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,815
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    As the mysql_ calls are about to be deleted from PHP you should also consider switching to either mysqli_ or PDO
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  4. #4
    SitePoint Enthusiast
    Join Date
    Nov 2012
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you very much...I haven't tried this yet...cos I just read the reply. I'll get back to asap.

    Thank you and God bless you!

  5. #5
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,136
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    I should also mention that mysql_query will always return true even if it returns zero rows. You really need to use mysql_num_rows on the mysql_query result.

    PHP Code:
    mysql_num_rows(mysql_query("SELECT uname,email FROM userslogin WHERE email='$email' ORDER BY email ASC")) !== 

  6. #6
    SitePoint Enthusiast
    Join Date
    Nov 2012
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Dear cpRadio,

    thanks for your code correction.
    However, as you said, I have used your own version of the code and I tested it with a new database without any initial user.
    But there is a problem::: the php die("The user already exists.") function is what executes instead of registering a new user.
    Please see the codes below and help your bro

    PHP Code:

    <?php session_start();
    //ob_start();
    //include ("../db/connStrings.php");
    $server="localhost";
    $server_user="root";
    $server_pass="";
    $db_name="dce";
    /*
    $pword_error="Your passwords did not match.";
    $name=$_POST['name'];
    $uname=$_POST['uname'];
    $pword=$_POST['pword'];
    $email=$_POST['email'];
    $con_pword=$_POST['con_pword'];
    */
    $name=filter_var($_POST['name'], FILTER_SANITIZE_STRING); 
    $uname=filter_var($_POST['uname'], FILTER_SANITIZE_STRING);
    $pword=filter_var($_POST['pword'], FILTER_SANITIZE_STRING);
    $email=filter_var($_POST['email'], FILTER_SANITIZE_EMAIL); 
    $con_pword=filter_var($_POST['con_pword'], FILTER_SANITIZE_STRING);

    $to=$email;
    $from="info@dce.com";
    $subject="User Registration Confirmation!";
    $body=
    "You have received this mail as a result of the registration process you initiated on our webpage. <br>
    Kindly activate your email to start using your account. This activation link <a href='http://dce.com/user/email_activation.php'> " 
    .session_id(). "</a> will expire in 15 minutes.";

    if (empty(
    $uname) || empty($name) || empty($pword) || empty($con_pword) || empty($email)) {
    //echo "Invalid registration details.";
    die("You must complete all fields!"); }
    elseif(
    $pword!=$con_pword) {
    die(
    "Unmatched Passwords: ".$pword_error); } 
    elseif (!
    $con=mysql_connect($server,$server_user,$server_pass)) {
    die(
    'Could not connect: ' mysql_error()); } 
    elseif(!
    mysql_select_db($db_name$con)) {
    die(
    "Could not connect to the database: " mysql_error()); }
    elseif(
    mysql_query("SELECT uname,email FROM userslogin WHERE email='$email' ORDER BY email ASC")) {
    die(
    "This user already exists."); }
    elseif(!
    mysql_query("INSERT INTO userslogin(name,uname,pword,email) VALUES('$name', '$uname', '$pword', '$email')")) {
    die(
    "Records could not be inserted: " mysql_error()); }
    elseif(!
    mail($to$subject$body$from)) { //send a mail to the registrants.
    die();
    header ("Location: registerError.php");}
    else {
    //redirect the user either to the login page or create a user cookie and redirect to main page.
    header("Location: ../Accounts/regSuccess.php"); 
    }
    mysql_close($con); //close connection
    ?>

  7. #7
    SitePoint Enthusiast
    Join Date
    Nov 2012
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh! I never saw this last post of yours. Anyway, thanks all the same. I gonna try it now...Lets see how it goes...I will get back to you.
    Thank you!

  8. #8
    SitePoint Enthusiast
    Join Date
    Nov 2012
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Dear cpRadio, I tried your code mysql_num_rows(mysql_query()) but it did not go as expected... with an empty database, it allows INSERT only once, after that, it returns an error. Please refer to the codes below... thank you.

    PHP Code:

    <?php session_start();
    //ob_start();
    //include ("../db/usersTable.php");
    $server="localhost";
    $server_user="root";
    $server_pass="";
    $db_name="dce";
    /*
    $pword_error="Your passwords did not match.";
    $name=$_POST['name'];
    $uname=$_POST['uname'];
    $pword=$_POST['pword'];
    $email=$_POST['email'];
    $con_pword=$_POST['con_pword'];
    */
    $name=filter_var($_POST['name'], FILTER_SANITIZE_STRING); 
    $uname=filter_var($_POST['uname'], FILTER_SANITIZE_STRING);
    $pword=filter_var($_POST['pword'], FILTER_SANITIZE_STRING);
    $email=filter_var($_POST['email'], FILTER_SANITIZE_EMAIL); 
    $con_pword=filter_var($_POST['con_pword'], FILTER_SANITIZE_STRING);

    $to=$email;
    $from="info@dce.com";
    $subject="User Registration Confirmation!";
    $body=
    "You have received this mail as a result of the registration process you initiated on our webpage. <br>
    Kindly activate your email to start using your account. This activation link <a href='http://dce.com/user/email_activation.php'> " 
    .session_id(). "</a> will expire in 15 minutes.";

    if (empty(
    $uname) || empty($name) || empty($pword) || empty($con_pword) || empty($email)) {
    //echo "Invalid registration details.";
    die("You must complete all fields!"); }
    elseif(
    $pword!=$con_pword) {
    die(
    "Unmatched Passwords: ".$pword_error); } 
    elseif (!
    $con=mysql_connect($server,$server_user,$server_pass)) {
    die(
    'Could not connect: ' mysql_error()); } 
    elseif(!
    mysql_select_db($db_name$con)) {
    die(
    "Could not connect to the database: " mysql_error()); }
    //elseif(mysql_query("SELECT uname,email FROM userslogin WHERE email='$email' ORDER BY email ASC")) {
    elseif (mysql_num_rows(mysql_query("SELECT uname,email FROM userslogin WHERE email='$email' or user='$uname' ORDER BY email ASC")) !== 0) {
    //die("This user already exists."); }
        
    header("Location: useralreadyexistserror.php"); }

    elseif(!
    mysql_query("INSERT INTO userslogin(name,uname,pword,email) VALUES('$name', '$uname', '$pword', '$email')")) {
    die(
    "Records could not be inserted: " mysql_error()); }
    elseif(!
    mail($to$subject$body$from)) { //send a mail to the registrants.
    die();
    header ("Location: registerError.php");}
    else {
    //redirect the user either to the login page or create a user cookie and redirect to main page.
    header("Location: ../Accounts/regSuccess.php"); 
    }
    mysql_close($con); //close connection
    ?>

  9. #9
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,136
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    First off, you have user='$uname' but in your insert you use uname as the column name.

    Secondly, run your query in phpmyadmin and see how many records it returns for your input.

  10. #10
    SitePoint Enthusiast
    Join Date
    Nov 2012
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey, thank you very much... I made a mistake in the field name. The actual field name is 'uname' and not 'user'. I have corrected it and it worked. But, you know, this mysql code is very stupid.... it has been recording before even when I used a wrong field name, 'user'. It recorded it into the 'uname' field. This is strange. What do you think?

    Thank you all the same.

  11. #11
    SitePoint Enthusiast
    Join Date
    Nov 2012
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Please can somebody help me here? How do I find out whether a table exists in the database ?
    Thank you!

  12. #12
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,136
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    The only way I've done that in the past is using SHOW TABLES IN <DATABASE> LIKE 'name_of_table' then checking the number or rows returned.

  13. #13
    SitePoint Enthusiast
    Join Date
    Nov 2012
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your comment and code cpRadio.... I do not want to display the tables.... I want to create tables... but before the code creates tables, let it first of all, check for an existing table. If true, then exit() else create table $tab_name...etc.
    Thanks once again...I really need info on this.

    however, for now, this is what I do at the moment pending when I find solution to my quest... check the code below:

    PHP Code:

    <?php
    $server
    ="localhost";
    $server_user="root";
    $server_pass="";
    $db_name="dce";

    //include ("../db/connStrings.php");
    $tbl_name="userslogin";
    $create_tbl_users="CREATE TABLE userslogin("."name VARCHAR(20) NOT NULL,"."uname VARCHAR(20) NOT NULL,"."pword VARCHAR(20) NOT NULL, email VARCHAR(60) NOT NULL".")";
    $create_tbl_region="CREATE TABLE region("."region_name VARCHAR(20) NOT NULL, region_number INT(3) NOT NULL PRIMARY KEY,"."region_uname VARCHAR(20) NOT NULL,"."region_pword VARCHAR(20) NOT NULL".")";
    $create_tbl_province="CREATE TABLE province("."province_location VARCHAR(20) NOT NULL, province_number INT(3) NOT NULL PRIMARY KEY,""province_uname VARCHAR(20) NOT NULL,""province_pword VARCHAR(20) NOT NULL".")";
    $connect=mysql_connect($server,$server_user,$server_pass);

    if (!
    $connect) {  //if there is no connection
    die ("There is no database present in this server."); }
    else { 
    //if there is connection 
    //echo "there is a connection.<br />";
    $connect_db mysql_select_db($db_name,$connect); } //select the database.

    if($connect_db) { //if there is database to select
    //echo "the database already exists.<br />"; 
    die(); } 
    //if there is no database to select
    elseif (!mysql_query("CREATE DATABASE $db_name",$connect)) {//create a database. but if there is failure
    die("Error creating Database: " mysql_error()); 

    elseif (!
    mysql_select_db($db_name)) { //if there is no error in creating the database
    die("Error selecting database: ".mysql_error()); }

    //create the table usersLogin in the database.
    elseif(!mysql_query($create_tbl_users)) { //if the table did not create
    die("Error creating table: "mysql_error()); } //if the table was created then

    //create the table regionLogin in the database.
    elseif (!mysql_query($create_tbl_region)) { //if the table did not create
    die("Error creating table: "mysql_error()); } 

    elseif (!
    mysql_query($create_tbl_province)) {//if the table was created then create another table
    die("Error Creating Table: ".mysql_error()); } 

    else {

        
    mysql_close($connect);
    exit();
        }

    ?>

  14. #14
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,136
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    You have to use SHOW TABLES IN <databasename> LIKE 'tablename' to find if a table already exists. So I stand by my prior response.

  15. #15
    SitePoint Enthusiast
    Join Date
    Nov 2012
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    okay guy!

    Thanks so much.

    Meanwhile, do not mind if am very disturbing o! Please!! I am queried a mysql_database and I want to pass the value that I get into a <SELECT>-<OPTION>-</OPTION></SELECT> fields on the form.

    I am having returned errors like: Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in c:\....file.php Line 131.
    Please review the following code.... thank you.

    PHP Code:
    <?php session_start();
    $server="localhost";
    $server_user="root";
    $server_pass="";
    $db_name="dce";

    if (!
    $con=mysql_connect($server,$server_user,$server_pass)) {
    die(
    'Could not connect: ' mysql_error()); } 
    elseif(!
    mysql_select_db($db_name$con)) {
    die(
    "Could not connect to the database: " mysql_error()); }

    else {

    }
    /*
    elseif (!$load_region=mysql_fetch_array(mysql_query("SELECT region_number FROM region"))) {

        header("Location: ../Accounts/loginerror.php"); }

    else {
    //extract the column values of the database into variables.
    $_regnum=$load_region['region_number']; }//region_number is the database field 

    mysql_close($con); //close connection*/
    ?>


    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
    <title>DCE - New Region Registration</title>
    <link href="../Style/Site.css" rel="stylesheet" type="text/css" />

    <form name="form1" action="confirm_province.php" method="post" style="margin-left:15px; margin-right:20px;">
            <p><b>Select Region: </b><br />
    <?php echo "<Select name='reg_name' size='1' class='passwordEntry'> ";
    $load_region mysql_query("SELECT region_number FROM region ORDER BY region_number"$con);

    while (
    $row mysql_fetch_array($load_region)) { 
    echo 
    "<option value='$row['region_number']'>" $row['region_number']."</option></select>";
    }
    ?>
    <br /><br />
            <b>Location of Province:</b> <br />
            <input type="text" name="pro_location" value="" class="passwordEntry" /> 
            <br />
            <br />
              <b>Province Number:</b><br />
            <input type="text" name="pro_num" value="" class="passwordEntry" />
            <br />
            <br />
            <b>Username:</b> <br />
            <input type="text" name="pro_uname" value="" class="passwordEntry" />
            <br />
            <br />
            <b>Password:</b> <br />
            <input type="password" name="pro_pword" value="" class="passwordEntry" />
            <br />
            <br />
            <b>Confirm Password:</b> <br />
            <input type="password" name="pro_con_pword" value="" class="passwordEntry" />
            <br />
            <br />
            <!--<b>Email Address:</b><br />
    <input type="text" name="email" value="" class="passwordEntry" /><br /><br />-->
            <input type="submit" name="btnRegister" value="Register" class="submitButton" />
          </p>
      </form>


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •