SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 31
  1. #1
    SitePoint Guru mmarif4u's Avatar
    Join Date
    Dec 2006
    Location
    /dev/swat
    Posts
    619
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Where to modify this query to work

    Hi everybody...

    I facing some problem regarding to mysql query.
    May some bady help out from this...

    Code:
    dbConnect('db');
          
        if ($_POST['icnumber']=='' or $_POST['acccode']=='' or $_POST['username']==''or $_POST['fullname']==''
          or $_POST['email']=='' or $_POST['hp']=='')  { 
             error ('One or more required fields were left blank.\\n'.
                  'Please fill them in and try again.');
        }
        
        // Check for existing user with the new id
        $sql = "SELECT * FROM m_users WHERE icnumber = '$_POST[icnumber]' AND acccode = '$_POST[acccode]'";
        $result = mysql_query($sql);
        if (!$result==0) {	
            error('A database error occurred in processing your '.
                  'submission.\\nIf this error persists, please '.
                 'contact you@example.com.');
        }
        if (mysql_result($result) < 1) {
            error('Your entered Ic number and Access code does not match\\n'.
                 'to the database data. Please try agian.');
        }
        
        $newpass = substr(md5(time()),0,6);
        
        $sql = "update m_users where icnumber = '$_POST[icnumber]'AND acccode = '$_POST[acccode]' SET
                 password = PASSWORD('$newpass'),
                 email = '$_POST[email]',
                 h/p = '$_POST[hp]',
                 username = '$_POST[username]',
                 fullname = '$_POST[fullname]'";
        if (!mysql_query($sql))
            error('A database error occurred in processing your '.
                  'submission.\\nIf this error persists, please '.
                  'contact admin@straight-a.com.my .\\n' . mysql_error());


    I have this query but it doesn't work completely..
    I want that it 1st check the database for icnumber and access code,,
    if these two values exists than query update the table with name,email,
    username etc..
    But problem is this wuery only checks the icnumber,access code if it exists
    then the rest of query cannot store other values to the table,,,

    Plzz some body help me out...
    Thanks...

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    So you want to add a record if one doesn't already exist?

  3. #3
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    first, this is a PHP question and needs to be moved to the PHP forum. i will ask to have it moved.

    second, i see at least one syntax error in there: mysql_result() takes two arguments and you only specified one.

    also, slashes in a column name is a bad idea. rename the h/p column to something else.

    and finally, you need to read the thread about SQL injection as you code is extremely vulnerable.

  4. #4
    SitePoint Guru mmarif4u's Avatar
    Join Date
    Dec 2006
    Location
    /dev/swat
    Posts
    619
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for reply..
    I want to insert record but to that row in which icnumber exists,,
    for example icnumber is 123 and access code 456 ,user enter these with
    name=arif,email=mmarif@domain.com,username=mmarif..
    if 123 and 456 exists in database then insert into that table :

    icnumber=123
    access code=456
    name=arif
    email=mmarif@domain.com
    username=mmarif

    But if 123 and 456 does not exists in database error msg to user
    that ic number & access code not found in the databse..

  5. #5
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    OK, let's try this, assuming you defined error() as a function somewhere else and it exits the script after doing whatever it does:

    PHP Code:
    dbConnect('db');

    $required_fields = array('icnumber''acccode''username''fullname''email''hp');
    foreach (
    $required_fields as $field) {
      if (!isset(
    $_POST[$field]) || empty($_POST[$field])) {
        
    error("One or more required fields were left blank. Please fill them in and try again.");
      } else {
        
    $cleaned_for_sql[$field] = mysql_real_escape_string($_POST[$field]);
      }
    }

    $sql "UPDATE `m_users` SET ";
    $sql .= "`password` = PASSWORD('" substr(md5(time()),0,6) . "') ";
    $sql .= "`email` = '" $cleaned_for_sql['email'] . "', ";
    $sql .= "`h/p` = '" $cleaned_for_sql['hp'] . "', ";
    $sql .= "`username` = '" $cleaned_for_sql['username'] . "', ";
    $sql .= "`fullname` = '" $cleaned_for_sql['fullname'] . "' ";
    $slq .= "WHERE `icnumber` = '" $cleaned_for_sql['icnumber'] . "' ";
    $sql .= " AND `acccode` = '" $cleaned_for_sql['acccode'] . "'";
    $sql_result mysql_query($sql);

    //will be positive if any row changes
    if (mysql_affected_rows($sql_result) == 0) {
      
    error("Your entered IC number and Access Code do not match any database data. Please try again.");


  6. #6
    SitePoint Guru mmarif4u's Avatar
    Join Date
    Dec 2006
    Location
    /dev/swat
    Posts
    619
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Dan Grossman

    It gives error like: "One or more required fields were left blank.
    Please fill them in and try again."

    Dan i already have the icnumber ,access code.
    here i put only for confirmation..
    i have other pages in which admin give a user icnumber,
    access code.User enter icnumber access code with thier
    details, if icnumber exists in the database,than update table
    with name,hp,email etc...

  7. #7
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I understand. This code conforms to those assumptions and the general flow you tried to get with your own code. It requires the icnumber and access code of an already existing record be provided and fills in the other fields on that row.

    The error is due to a typo I made on the following line (corrected):

    PHP Code:
    if (!isset($_POST[$field]) || empty($_POST[$field])) { 
    Please do try to read the code and understand what it does so that you know and can verify it's accomplishing your goal. If you have any questions about how it works feel free to ask.

  8. #8
    SitePoint Guru mmarif4u's Avatar
    Join Date
    Dec 2006
    Location
    /dev/swat
    Posts
    619
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for correction..

    Now it says that:''Your entered IC number and Access Code do not
    match any database data. Please try again."

    And also this error:"Warning: mysql_affected_rows():supplied arguments
    is not avalid mysql MYSQL-Link resource on line 132".

    Ok DAN

    i have to show my complete code:
    PHP Code:
    <?php //
    include("common.php");
    include(
    "db.php");

    if (!isset(
    $_POST['submitok'])):
        
    // Display the user signup form
        
    ?>
    <!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>
       <title> Access Code Generator Form </title>
      <meta http-equiv="Content-Type"
        content="text/html; charset=iso-8859-1
    </head><link href="style.css" rel="stylesheet" type="text/css">
    <body bgcolor="slate"><center>

    <h3> <font color="#FFFFFF" face="verdana"><u>Registration Form For New Users</u><p>Please Fill The Form Below Carefully
    </font>
    </h3></body>

    <form name="reg" method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">

    <table align="center" border="0" cellpadding="0" cellspacing="5">
    <hr color="blue" />
        <tr>
            <td align="right">
                <p><font color="#FFFFFF" face="verdana">IC Number :</font></p>
            </td>
            <td>
                <input name="icnumber" type="text" maxlength="100" size="25" />
                <font color="orangered" size="+1"><tt><b>*</b></tt></font>
            </td>
        </tr>
        
        <tr>
            <td align="right">
                <p><font color="#FFFFFF" face="verdana">Access Code :</font></p>
            </td>
            <td>
                <input name="acccode" type="text" maxlength="100" size="25" />
                <font color="orangered" size="+1"><tt><b>*</b></tt></font>
            </td>
        </tr>
        <tr>
            <td align="right">
                <p><font color="#FFFFFF" face="verdana">Username :</font></p>
            </td>
            <td>
                <input name="username" type="text" maxlength="100" size="25" />
                <font color="orangered" size="+1"><tt><b>*</b></tt></font><td align="left">Enter a valid user name.</td>
            </td>
        </tr>
        <tr>
            <td align="right">
                <p><font color="#FFFFFF" face="verdana">Full Name :</font></p>
            </td>
            <td>
                <input name="fullname" type="text" maxlength="100" size="25" />
                <font color="orangered" size="+1"><tt><b>*</b></tt></font>
            </td>
        </tr>
        <tr>
            <td align="right">
                <p><font color="#FFFFFF" face="verdana">Email :</font></p>
            </td>
            <td>
                <input name="email" type="text" maxlength="100" size="25" />
                <font color="orangered" size="+1"><tt><b>*</b></tt></font><td>Email must be valid for password retreival.</td>
            </td>
        </tr>
        <tr>
            <td align="right">
                <p><font color="#FFFFFF" face="verdana">Hand Phone :</font></p>
            </td>
            <td>
                <input name="hp" type="text" maxlength="100" size="25" />
                <font color="orangered" size="+1"><tt><b>*</b></tt></font>
            </td>
        </tr>
        <tr><td>
        <font  face="verdana" color="orangered" size="+1"><tt><b>*</b></tt></font>
        <u><font face="arial" size="2" color="#FFFFFF">indicates a required field</u></font>
        </td>
        </tr>
        
        <tr>  
            <td align="right" colspan="3">
               <hr color="blue" />
                
                <input type="reset" value="Reset" />
                <input type="submit" name="submitok" value="Register"  />
                
            </td>
        </tr>
    </table>
    </form>

    </body>
    </html>

        <?php 
       
    else:
        
    // Process signup submission
        
    dbConnect('db');
          


    $required_fields = array( 'icnumber','acccode','username''fullname''email''hp');
    foreach (
    $required_fields as $field) {

      if (!isset(
    $_POST[$field]) || empty($_POST[$field])) {
        
    error("One or more required fields were left blank. Please fill them in and try again.");
      } else {
          
    $cleaned_for_sql[$field] = mysql_real_escape_string($_POST[$field]);
      }
    }

    $sql "UPDATE `m_users` SET ";
    $sql .= "`password` = PASSWORD('" substr(md5(time()),0,6) . "') ";
    $sql .= "`email` = '" $cleaned_for_sql['email'] . "', ";
    $sql .= "`hp` = '" $cleaned_for_sql['hp'] . "', ";
    $sql .= "`username` = '" $cleaned_for_sql['username'] . "', ";
    $sql .= "`fullname` = '" $cleaned_for_sql['fullname'] . "' ";
    $slq .= "WHERE `icnumber` = '" $cleaned_for_sql['icnumber'] . "' ";
    $sql .= " AND `acccode` = '" $cleaned_for_sql['acccode'] . "'";
    $sql_result mysql_query($sql);

    if (
    mysql_affected_rows($sql_result) == 0) {
      
    error("Your entered IC number and Access Code do not match any database data. Please try again.");
    }
                  
        
    // Email the new password to the person.
        
    $message "G'Day!

    Your personal account for the Straight-a program
    has been created! To log in, proceed to the
    following address:

        http://www.straight-a.com.my/

    Your personal login ID and password are as
    follows:

        username: 
    $_POST[username]
        password: 
    $newpass

    You aren't stuck with this password! Your can
    change it at any time after you have logged in.

    If you have any problems, feel free to contact me at
    <admin@straight-a.com.my>.

    -Arif
     Webmaster
    "
    ;

        
    mail($_POST['email'],"Your Password for the Straight-a Program",
             
    $message"From:Arif <admin@straight-a.com.my>");
             
        
    ?>
        <!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>
          <title> Registration Complete </title>
          <meta http-equiv="Content-Type"
            content="text/html; charset=iso-8859-1" />
        </head>
        <body>
        <p><strong>User registration successful!</strong></p>
        <p>Your userid and password have been emailed to
           <strong><?=$_POST['email']?></strong>, the email address
           you provided in your registration form. To log in,
           go to your email and click the link provided in your email.</p>
        </body>
        </html>
        <?php
    endif;
    ?>

  9. #9
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    That probably means the query is failing to execute properly then, which might be because I had to infer the schema from your queries and am not sure it's correct. Time to debug.

    Change this:
    PHP Code:
    $sql_result mysql_query($sql); 
    To this:
    PHP Code:
    $sql_result mysql_query($sql) or error("Error executing query [$sql]: " mysql_error()); 

  10. #10
    SitePoint Guru mmarif4u's Avatar
    Join Date
    Dec 2006
    Location
    /dev/swat
    Posts
    619
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Dan for showing me the right way..

    Now it gives the error like:

    "Error executing query [update 'm_users' set 'password' =PASSWORD('124d4
    d') 'email' ='arif@yahoo.com' , 'hp' = '016', 'username' = 'arif4u',
    AND 'accesscode' ='123456']:You have an error in your sql syntax;check
    the manual that corresponds to your mysql server version for the
    rigth syntax to use near 'email'='arif@yahoo.com' , 'hp' = '016', 'username' = 'arif4u', 'fullname'='ari' at line 1"

    The error may be from we forgot some thing in the query...

  11. #11
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    That's not the query I wrote at all

  12. #12
    SitePoint Guru mmarif4u's Avatar
    Join Date
    Dec 2006
    Location
    /dev/swat
    Posts
    619
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I make changes like add this , to the end of the query,
    Code:
    $sql .= "`hp` = '" . $cleaned_for_sql['hp'] . "', ";
    $sql .= "`username` = '" . $cleaned_for_sql['username'] . "', ";
    $sql .= "`fullname` = '" . $cleaned_for_sql['fullname'] . "', ";
    $slq .= "WHERE `icnumber` = '" . $cleaned_for_sql['icnumber'] . "', ";
    $sql .= "AND `acccode` = '" . $cleaned_for_sql['acccode'] . "' ";
    Bcoz in ur query u forgot the , but at this level now give error:
    Code:
    $sql .= "AND `acccode` = '" . $cleaned_for_sql['acccode'] . "' ";
    I dont know what to add , or " or ; or others.

  13. #13
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    There shouldn't be a comma before that WHERE. The commas were separating the list of fields for the SET part of the UPDATE query; none appear after the last element.

    One of those lines starts with $slq instead of $sql, fix that and see if it helps. The error you gave in your last post showed a query that looked nothing like what's in the code I wrote.

  14. #14
    SitePoint Guru mmarif4u's Avatar
    Join Date
    Dec 2006
    Location
    /dev/swat
    Posts
    619
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I edit the query as u say but this time also same error
    which it give before..

  15. #15
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Do you still have single quotes around the column names?
    You need to either use nothing around the col names, or `backticks` (beside the 1).

  16. #16
    SitePoint Guru mmarif4u's Avatar
    Join Date
    Dec 2006
    Location
    /dev/swat
    Posts
    619
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    $sql = "UPDATE `m_users` SET ";
    $sql .= "`password` = PASSWORD('" . substr(md5(time()),0,6) . "'), ";
    $sql .= "`email` = '" . $cleaned_for_sql['email'] . "', ";
    $sql .= "`hp` = '" . $cleaned_for_sql['hp'] . "', ";
    $sql .= "`username` = '" . $cleaned_for_sql['username'] . "', ";
    $sql .= "`fullname` = '" . $cleaned_for_sql['fullname'] . "' ";
    $sql .= "WHERE `icnumber` = '" . $cleaned_for_sql['icnumber'] . "' ";
    $sql .= "AND `acccode` = '" . $cleaned_for_sql['acccode'] . "' ";
    $sql_result = mysql_query($sql) or error("Error executing query [$sql]: " . mysql_error()); 
    
    if (mysql_affected_rows($sql_result) == 0) {
      error("Your entered IC number and Access Code do not match any database data. Please try again.");
    i just put , after PASSWORD('" . substr(md5(time()),0,6) . "') and ok...
    but now it give error that ur icnumber and access code does not match to
    the dtabase, and this"Warning:mysql_affected_rows():Supplied argument
    is not a valid MYSQL-link resource on line 131"

  17. #17
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    http://au2.php.net/mysql_affected_rows
    The mysql_affected_rows function requires your connection resource as an argument.
    You have passed the query resource instead.

    Consider this example:
    PHP Code:
    <?php
    $connection 
    mysql_connect('localhost''root''my_password');
    mysql_select_db('ninja_db');

    $query "UPDATE `table` SET blah = 1";
    echo 
    mysql_affected_rows($connection) . " rows were changed";
    ?>

  18. #18
    SitePoint Guru mmarif4u's Avatar
    Join Date
    Dec 2006
    Location
    /dev/swat
    Posts
    619
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks cranial-bore for reply..
    Where to modify the script or query can u explain it with
    my script.. i mention my whole script before..
    plz help out of this coz its very important to me..
    thanks.

  19. #19
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    My bad. The resource is optional so if you just give mysql_affected_rows() no arguments it should work. Don't know where the resource is stored since your connection code is in another file. However that means the query ran already, so next time you try to update the row if nothing changes (values are all there) the number of affected rows may be zero anyway. Have to look at the data itself to know.

  20. #20
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    somewhere in common.php or db.php you must be connecting to the MySQL DB (look for code using the mysql_connect function).

    When connecting you need to create a connection resource (a variable that identifies the connection to the DB.)

    This is optional, but needs to be done to use the affected_rows and various other functions. Look at my code to see how to do it.
    $connection was the connection resource in my example.

    Use your connection as the argument for mysql_affected_rows

    Edit
    As Dan says the connection is optional. If you can't figure out how to assign a connection resource when connecting to MySQL just take his advice and leave it out.

  21. #21
    SitePoint Guru mmarif4u's Avatar
    Join Date
    Dec 2006
    Location
    /dev/swat
    Posts
    619
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is my db.php code

    PHP Code:
    <?php // db.php

    $dbhost 'localhost';
    $dbuser 'root';
    $dbpass 'adil';


    function 
    dbConnect($db="") {
        global 
    $dbhost$dbuser$dbpass;
        
        
    $dbcnx = @mysql_connect($dbhost$dbuser$dbpass)
            or die(
    'The site database appears to be down.');

        if (
    $db!='' and !@mysql_select_db($db))
            die(
    'The site database is unavailable.');
        
        return 
    $dbcnx;
    }
    ?>

  22. #22
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't want to say it any more explicitly because you won't learn anything.


    Let's recap the clues I've given you:

    1) You are looking for the mysql_connect function
    2) You are looking for a variable that will contain the connection as a result of the mysql_connect function
    3) I showed you example code where I assigned $connection to perform this function
    4) I showed you how to use your connection resource with the mysql_affected_rows function

    All you have to do is identify your connection resource
    It will be returned by the dbConnect function you just posted. Find the code that executes this function and you're away

  23. #23
    SitePoint Guru mmarif4u's Avatar
    Join Date
    Dec 2006
    Location
    /dev/swat
    Posts
    619
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Every body has thier own perpective,u say that i dont want to learn
    man i have to learn,
    so according to ur suggestion i pass the argument

    Code:
    if (mysql_affected_rows($dbcnx) == 0)
    but still that problem.
    I want to learn more and new..
    But this problem is out of my reach...

  24. #24
    SitePoint Guru mmarif4u's Avatar
    Join Date
    Dec 2006
    Location
    /dev/swat
    Posts
    619
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Plz help me out of this problem..

    I will be thankful...

  25. #25
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    $dbcnx is a variable that was declared inside your db connection function, and is only available within that scope. It doesn't exist in the scope of your main program. However, your function returns that variable to its caller so you can bring it into scope by replacing...

    PHP Code:
    dbConnect('db'); 
    ...with...
    PHP Code:
    $dbcnx dbConnect('db'); 


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
  •