SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    915
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    UPDATE database but leave out any that have 0 as any of the data

    What I'm doing is adding another level of security into a clients website, that means any of the entries contain a zero then its left behind and not updated.

    This is what I got, pretty basic:

    Code:
    $sql = mysql_query("UPDATE hazzard SET Name='$name', Employerofperson='$employer', Contactphone='$telephone', NearMissDetails='$detail', Anyimmediateactions='$action', HazardorNearMiss='$hazardornearmiss', Primarycause='$primarycause3', Primarycause2='$primarycause1', Rottcause='$rootcause', Rottcause2='$primarycause2', Behaviours='$behaviours', PotentialOutcome='$potentialoutcome', Likelihood='$likelihood', Comments='$comments', report_Completed_By='$completedByManager' WHERE UniqueIdentifier='$report_ID'") or die (mysql_error());
    So how do you add so that if any of those variables contains '0' then skip it.

  2. #2
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,077
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Before you get any further, please be aware that the mysql_* extension is now deprecated as of the current version of PHP and will very likely be removed from the next 5.x version and will likely not be in PHP 6.x (when it eventually is released). You should migrate over to either the mysqli_* extension or to PDO. PDO is a better choice as it doesn't tie you down so much to a particular database server software.

    Once you have migrated you should use Prepared Statements to prevent SQL Injection attacks. Have a read of this article from the PHP manual, it shows how to use prepared statements with PDO and also explains the principle.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  3. #3
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    915
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi SpacePhoenix,

    Thanks for the advice, and have looked into it and have changed it to below, could you see if I have done the right thing.

    Code PHP:
    $sql = $mysqli->prepare("UPDATE hazzard SET Name = ?,
    					Employerofperson = ?,
    					Contactphone = ?,
    					NearMissDetails = ?,
    					Anyimmediateactions = ?,
    					HazardorNearMiss = ?,
    					Primarycause = ?,
    					Primarycause2 = ?,
    					Rottcause = ?,
    					Rottcause2 = ?,
    					Behaviours = ?,
    					PotentialOutcome = ?,
    					Likelihood = ?,
    					Comments = ?,
    					report_Completed_By = ?,
    			WHERE UniqueIdentifier = ?");
    $sql->bind_param('$name',
    		'$employer',
    		'$telephone',
    		'$detail',
    		'$action',
    		'$hazardornearmiss',
    		'$primarycause3',
    		'$primarycause1',
    		'$rootcause',
    		'$completedByManager',
    		'$report_ID');
    $sql->execute();
    $sql->close();

    If this is fine, then I'm thinking I have got alot of work to do next week as I have a lot of updating to get through too.

    If good, could you look at what I was trying to do on the first post too.
    Last edited by Mittineague; Mar 1, 2014 at 16:58. Reason: reformatting bbcode tags

  4. #4
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    915
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Maybe there not a way of checking it quite that easy in the update statement, so I'm guessing the best way to do it is by adding an if else statement to the post values before moving to the update.

    Code:
    $name = mysql_real_escape_string($_POST["txtname"]);
    $employer = mysql_real_escape_string($_POST["txtemployer"]);
    $telephone = mysql_real_escape_string($_POST["txtphone"]);
    $detail = mysql_real_escape_string($_POST["txtdetails"]);
    $action = mysql_real_escape_string($_POST["txtaction"]);
    $hazardornearmiss = mysql_real_escape_string($_POST["hazardornearmiss"]);
    $primarycause= mysql_real_escape_string($_POST["primarycause"]);
    $primarycause1= mysql_real_escape_string($_POST["primarycause"]);
    $rootcause = mysql_real_escape_string($_POST["rootcause"]);
    $behaviours = mysql_real_escape_string($_POST["behaviours"]);
    $potentialoutcome = mysql_real_escape_string($_POST["potentialoutcome"]);
    $likelihood = mysql_real_escape_string($_POST["likelihood"]);
    $comments = mysql_real_escape_string($_POST["comments"]);
    $completedByManager = mysql_real_escape_string($_POST["completedBy"]);
    So I would so something like this on every one.

    Code:
    if ($name=="0") {
    $message = "This value contains 0 and is not a valid answer.";
    echo "<script type='text/javascript'>alert('$message');</script>"; 
    echo"<script type='text/javascript'>window.location = '/some/url';</script>";
    } else {
    
    }

  5. #5
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,271
    Mentioned
    197 Post(s)
    Tagged
    3 Thread(s)
    First, you're going to have trouble with that query. You have 15 placeholders in the SET but only 11 in the bind.

    And you should not mix (or even use at all) deprecated mysql_ with the mysqli_

    I don't think you need complex PHP code for this, but that you can do it all with a properly constructed query.

    By "0", do you mean only the integer or also false. empty and Null?

    I'll move this to the Database forum for now.

  6. #6
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    915
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What I mean by 0 is just literally the number 0 as that is a value of one of the drop downs, and basically they should have selected a different value.

    So its just a value.

    I also did later notice the 15 - 11 issue and rectified it on the site, but didnt on the forum sorry.

    Also what do you mean by:

    And you should not mix (or even use at all) deprecated mysql_ with the mysqli_
    Thanks for getting back to me.

  7. #7
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,271
    Mentioned
    197 Post(s)
    Tagged
    3 Thread(s)
    And all 15 fields could potentially be 0, or only some of them.

    I think you could put WHERE poss_0_field != 0 AND another_poss_0_field != 0 AND ......for each.

    Your code example in post#4 has mysql_real_escape_string(... instead of mysqli_

    .... back to PHP ....

  8. #8
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    915
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK I have added the extra ones in to make it 15 & 15, and wondered if the extra AND I have added in is right, and in the right place before i put the other 14 in.

    PHP Code:
    $sql $mysqli->prepare("UPDATE hazzard SET Name = ?, Employerofperson = ?, Contactphone = ?, NearMissDetails = ?, Anyimmediateactions = ?, HazardorNearMiss = ?, Primarycause = ?, Primarycause2 = ?, Rottcause = ?, Rottcause2 = ?, Behaviours = ?, PotentialOutcome = ?, Likelihood = ?, Comments = ?, report_Completed_By = ?, WHERE UniqueIdentifier = ? AND Name != ?" );
    $sql->bind_param('$name''$employer''$telephone''$detail''$action''$hazardornearmiss''$primarycause3''$primarycause1''$rootcause''$primarycause2''$behaviours''$potentialoutcome''$likelihood''$comments''$completedByManager''$report_ID''0');
    $sql->execute();
    $sql->close(); 
    Last edited by Mittineague; Mar 2, 2014 at 14:17.

  9. #9
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,271
    Mentioned
    197 Post(s)
    Tagged
    3 Thread(s)
    Better. Now you should put in the "types" bind_param
    types

    A string that contains one or more characters which specify the types for the corresponding bind variables:
    Type specification chars
    Character Description
    i corresponding variable has type integer
    d corresponding variable has type double
    s corresponding variable has type string
    b corresponding variable is a blob and will be sent in packets
    add the "not zero"s

    Then you'll be all set to test it.


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
  •