SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    You want what? By when?? Milamber's Avatar
    Join Date
    Jan 2001
    Location
    California
    Posts
    342
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is probably a very basic question, but I haven't learned it yet so I need to ask.


    Up until now I have been "modifying" my database entries (in the scripts I make) by deleting the old one and then putting in the new one.

    What is the actual SQL statement to replace/modify an entry?

    Thanks Much!
    -Milamber

  2. #2
    imagine no limitations exbabylon's Avatar
    Join Date
    Dec 2000
    Location
    Idaho, USA
    Posts
    452
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes there is... and it's VERY easy! And at the same time it will speed up your scripts...

    use this query:

    Code:
    $table = "your_table_name";
    
    $sql = "UPDATE $table SET $city = '$city', state = '$state', zip = '$zip' WHERE ID = '$id';
    if(!mysql_query($sql)){
         echo("An error has occured with the database");
    } else {
    echo("Thank you for updating your contact information");
    that should do it!

    God Bless
    Blamestorming: Sitting around in a group discussing why a deadline was missed or a project failed and who was responsible.

    Exbabylon- Professional Internet Services

  3. #3
    SitePoint Enthusiast
    Join Date
    Oct 2000
    Location
    Location:
    Posts
    94
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey I'm looking for a similar script which will only allow authorized users to updtae their own records!!!

    I have created a form which will ask for users for their username and password. If the username and password matches any of those within the database, it populates a table showing the user the details of the record based on the username entered

    However where I am stuck is, how could I allow these authorized users to UPDATE/modify their records????

    Please help!

    Thanx!!


    Koncise

  4. #4
    AdSpeed.com Son Nguyen's Avatar
    Join Date
    Aug 2000
    Location
    Silicon Valley
    Posts
    2,241
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Put a form for them to modify, then have a script to update those fields.
    - Son Nguyen
    AdSpeed.com - Ad Serving and Ad Management Made Easy

  5. #5
    imagine no limitations exbabylon's Avatar
    Join Date
    Dec 2000
    Location
    Idaho, USA
    Posts
    452
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok I am going to make this fast, and am assuming you already have a working knowledge of basic PHP

    Code:
    <?php
    
    if(!$edit){
    /* your password verification */
    $sql = "SELECT * FROM user WHERE username = '$username' and password = '$password'";
    $result = mysql_query($sql);
    
    if(!$result){
       echo("an error has occured");
       exit();
    } if(mysql_num_rows($result) != "1"){
       echo("Wrong username or password entered");
    } else {
    while($row = mysql_fetch_array($result)){
    $city = $row['city'];
    $state = $row['state'];
    $zip = $row['zip'];
    $id = $row['id'];
    ?>
    <form action="<?php echo $PHP_SELF ?" method="post">
    <div align="center"><?php echo $username; ?>
    </div>
    <p><input type="text" name="city" value="<?php echo $city; ?><br>
    <input type="text" name="state" value="<?php echo $state; ?><br>
    <input type="text" name="zip" value="<?php echo $zip; ?><br>
    <input type="submit" value="Update My Profile" name="edit">
    <input type=hidden" value="<?php echo $id; ?>" name="id">
    </form>
    <?php
    }
    }
    }
    
    //If they submit to change.
    if(isset($edit){
    
    $sql = "UPDATE user SET $city = '$city', state = '$state', zip = '$zip' WHERE ID = '$id';
    if(!mysql_query($sql)){
         echo("An error has occured with the database");
    } else {
    echo("Thank you for updating your profile information.");
    }
    } else {
    
    echo("No action specified");
    }
    freddy and Commish, go ahead and correct me where I'm wrong... but I think this should at least get you somewhere where you can start from.
    Blamestorming: Sitting around in a group discussing why a deadline was missed or a project failed and who was responsible.

    Exbabylon- Professional Internet Services

  6. #6
    SitePoint Enthusiast
    Join Date
    Oct 2000
    Location
    Location:
    Posts
    94
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you very much.....!

    I'll see what I can do with the script.


    Koncise!

  7. #7
    SitePoint Enthusiast
    Join Date
    Oct 2000
    Location
    Location:
    Posts
    94
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Greetings All!!!

    I have devised a user update form, which is designed to provide a update form based upon the Username and password entered in the initial authentication form.

    However when I attempt to submit the changes by pressing 'submit', the following error message appears:

    "You are not authorized! Please try again."

    This of course is the else if statement which is echoed if the submition fails, at the end of the script.:

    <?php
    endwhile;
    else :

    echo "You are not authorized! Please try again.</p>";
    endif;



    ?>



    Below is the script I am using for this:


    <?php

    /* This is a script which will perform a select query on the 'Love_to_sing database based upon the User_name and Password entered. The results are neatly presented in a table according to column and field names! */


    $connection = mysql_connect("localhost", "root", "******") or die ("Couldn't connect to server.");

    mysql_select_db("Love_to_sing", $connection) or die ("Couldn't select databagjse");

    //Check if form has been submitted to update record.
    //If so, perform database update

    if (isset($update)) {
    $query = "UPDATE Groups set where User_name='$User_name', Password='$Password',
    Category='$category', Group_name='$group_name', Contact_name='$contact_name', Region='$region'

    where ID='$rec_id' ";

    //Perform Update
    mysql_query($query);
    }

    $sql = "SELECT ID, User_name, Password, Category, Group_name, Contact_name,
    Region FROM Groups
    WHERE User_name='$User_name' and Password='$Password'";
    $result = mysql_query($sql,$connection)
    or die("Couldn't execute query.");
    $num = mysql_numrows($result);

    if ($num):
    echo "<P>Thank you!<br><p>";
    echo "Welcome back $User_name.<br>";
    echo "Please feel free to update your group details below, hit 'submit'
    when your done!</p>";

    mysql_connect("localhost", "root", "******")
    or die ("Couldn't connect to server.");
    mysql_select_db("Love_to_sing", $connection)
    or die ("Couldn't select database.");
    $result = mysql_query($sql,$connection)
    or die("Couldn't execute query.");
    ?>

    <TABLE BORDER=8>
    <tr><th>User_name</th><th>Password</th><th>Category</th><th>Group_name</th>
    <th>Contact_name</th><th>Region</th></tr>
    <form method=post action="<?php echo $PHP_SELF; ?>">

    <?php

    //PUT THE ROWS OF THE RESULT INTO AN ARRAY, THEN GO THROUGH ROW BY ROW,
    //ECHOING WHAT IS IN EACH ONE

    while ($row = mysql_fetch_array($result) ):

    $ID = $row["ID"];
    $Username = $row["User_name"];
    $Password = $row["Password"];
    $Category = $row["Category"];
    $Group_name = $row["Group_name"];
    $Contact_name = $row["Contact_name"];
    $Region = $row["Region"];
    ?>

    <tr>
    <td><input type=text name="username" value="<?php echo $Username; ?>"></td>
    <td><input type=text name="password" value="<?php echo $Password; ?>"></td>
    <td><input type=text name="category" value="<?php echo $Category; ?>"></td>
    <td><input type=text name="group_name" value="<?php echo $Group_name; ?>"></td>
    <td><input type=text name="contact_name" value="<?php echo $Contact_name; ?>"></td>
    <td><input type=text name="region" value="<?php echo $Region; ?>"></th>
    </tr>

    </table>
    <br>
    <input type=hidden name="rec_id" value="<?php echo $ID; ?>">
    <input type=submit value="Submit" name="update">
    </form>

    <?php
    endwhile;
    else :

    echo "You are not authorized! Please try again.</p>";
    endif;



    ?>

    I'm trying to figure out why the error message appears, I'll keep trying to sort it out. If there is a mistake within the script, I certainly cannot locate it!!

    I would be extremely greatful if anybody could glance over the script to see if there are any mistakes!!

    Thank you very, very much!!

    Koncise.


    [Edit: masked your password for you --Kevin Yank]
    Last edited by Kevin Yank; Feb 20, 2001 at 14:18.

  8. #8
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    First, this query has a spurious 'where' in it:

    Code:
    if (isset($update)) { 
    $query = "UPDATE Groups set where User_name='$User_name', Password='$Password', 
    Category='$category', Group_name='$group_name', Contact_name='$contact_name', Region='$region' 
    
    where ID='$rec_id' "; 
    
    //Perform Update 
    mysql_query($query); 
    }
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  9. #9
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    This is probably the cause. It's mysql_num_rows, not mysql_numrows:

    Code:
    $num = mysql_numrows($result);
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  10. #10
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    The hilighted code seems like it is performing an additional, and unneccessary database connection and query:

    Code:
    if ($num): 
    echo "<P>Thank you!<br><p>"; 
    echo "Welcome back $User_name.<br>"; 
    echo "Please feel free to update your group details below, hit 'submit' 
    when your done!</p>"; 
    
    mysql_connect("localhost", "root", "******") 
    or die ("Couldn't connect to server."); 
    mysql_select_db("Love_to_sing", $connection) 
    or die ("Couldn't select database."); 
    $result = mysql_query($sql,$connection) 
    or die("Couldn't execute query.");
    ?>
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  11. #11
    SitePoint Enthusiast
    Join Date
    Oct 2000
    Location
    Location:
    Posts
    94
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you very much Kevin, that sorted out the problems, however there is still one more:

    When I press 'submit' nothin happens; the screen does'nt change. The submit button does'nt work for some reason.

    Here is the final script I can't see anything wrong with the submit command, have I missed something again??

    <?php


    $connection = mysql_connect("localhost", "root", "******") or die ("Couldn't connect to server.");

    mysql_select_db("Love_to_sing", $connection) or die ("Couldn't select databagjse");

    //Check if form has been submitted to update record.
    //If so, perform database update

    if (isset($update)) {
    $query = "UPDATE Groups set User_name='$User_name', Password='$Password',
    Category='$category', Group_name='$group_name', Contact_name='$contact_name', Region='$region'

    where ID='$rec_id' ";

    //Perform Update
    mysql_query($query);
    }

    $sql = "SELECT ID, User_name, Password, Category, Group_name, Contact_name,
    Region FROM Groups
    WHERE User_name='$User_name' and Password='$Password'";
    $result = mysql_query($sql,$connection)
    or die("Couldn't execute query.");
    $num = mysql_num_rows($result);

    if ($num):
    echo "<P>Thank you!<br><p>";
    echo "Welcome back $User_name.<br>";
    echo "Please feel free to update your group details below, hit 'submit'
    when your done!</p>";

    //PUT THE ROWS OF THE RESULT INTO AN ARRAY, THEN GO THROUGH ROW BY ROW,
    //ECHOING WHAT IS IN EACH ONE

    while ($row = mysql_fetch_array($result) ):

    $ID = $row["ID"];
    $Username = $row["User_name"];
    $Password = $row["Password"];
    $Category = $row["Category"];
    $Group_name = $row["Group_name"];
    $Contact_name = $row["Contact_name"];
    $Region = $row["Region"];
    ?>

    <tr>
    <td><input type=text name="username" value="<?php echo $Username; ?>"></td>
    <td><input type=text name="password" value="<?php echo $Password; ?>"></td>
    <td><input type=text name="category" value="<?php echo $Category; ?>"></td>
    <td><input type=text name="group_name" value="<?php echo $Group_name; ?>"></td>
    <td><input type=text name="contact_name" value="<?php echo $Contact_name; ?>"></td>
    <td><input type=text name="region" value="<?php echo $Region; ?>"></th>
    </tr>

    </table>
    <br>
    <input type=hidden name="rec_id" value="<?php echo $ID; ?>">
    <input type=submit value="Submit" name="update">
    </form>

    <?php
    endwhile;
    else :

    echo "You are not authorized! Please try again.</p>";
    endif;



    ?>


    Thank you for your help and patience!!!

    Koncise.

  12. #12
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Where is your <form> tag?
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  13. #13
    SitePoint Enthusiast
    Join Date
    Oct 2000
    Location
    Location:
    Posts
    94
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ahh yes this was something I noticed also Kevein, thanx.

    However when I did enter the <form> tag, a strange thing happened:

    When I pressed 'submit' on the form, the changes were saved to the database, great! But it clears what ever values are in the 'Username' and 'Password fields' - even though I made no change to them!

    I am trully puzzled as to why this is happening!

    Without a doubt, updating records has been the most difficult operation I've come across yet.....


    Koncise.

  14. #14
    imagine no limitations exbabylon's Avatar
    Join Date
    Dec 2000
    Location
    Idaho, USA
    Posts
    452
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    simple fix... you have your control structures goofed!

    here's the proper way:

    PHP Code:
    $query "UPDATE Groups SET Category='$category', Group_name='$group_name', Contact_name='$contact_name', Region='$region' where ID='$rec_id'"
    before you had not declared $User_name and $Password as anything, so it just inserted those as blank.

    try the code I gave above.
    Blamestorming: Sitting around in a group discussing why a deadline was missed or a project failed and who was responsible.

    Exbabylon- Professional Internet Services

  15. #15
    SitePoint Enthusiast
    Join Date
    Oct 2000
    Location
    Location:
    Posts
    94
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you very much...

    I'll try that code out when I get home from Uni!

    K.


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
  •