SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Member
    Join Date
    Oct 2012
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Update statement

    Hello everyone,
    I built a sample e-banking application as part of a project from my school. I wrote the statement below to update two tables from fields submitted from a form and also send a mail to the form owner. when i submit the form it redirects to the finale.php page but does not update the database at all..

    The UPDATE statement is

    $sqlupdate = " UPDATE client, `statement` SET client.account_balance = $balanceaftertransfer,client.checkcode = 1, statement.account_balance = $balanceaftertransfer, statement.statement_details = Online Transfer, statement.statement_credit = 0, statement.staement_debit = $amt2tra, statement.statement_date = CURDATE(), statement.client_id = $client_id
    WHERE client.client_id = `statement`.client_id AND username = '".$_SESSION['MM_Username']."'";
    mysql_query($sqlupdate ) ;

    and the full post.php file is as shown below


    <?php
    if (!isset($_SESSION)) {
    session_start();
    }
    $MM_authorizedUsers = "";
    $MM_donotCheckaccess = "true";

    // *** Restrict Access To Page: Grant or deny access to this page
    function isAuthorized($strUsers, $strGroups, $UserName, $UserGroup) {
    // For security, start by assuming the visitor is NOT authorized.
    $isValid = False;

    // When a visitor has logged into this site, the Session variable MM_Username set equal to their username.
    // Therefore, we know that a user is NOT logged in if that Session variable is blank.
    if (!empty($UserName)) {
    // Besides being logged in, you may restrict access to only certain users based on an ID established when they login.
    // Parse the strings into arrays.
    $arrUsers = Explode(",", $strUsers);
    $arrGroups = Explode(",", $strGroups);
    if (in_array($UserName, $arrUsers)) {
    $isValid = true;
    }
    // Or, you may restrict access to only certain users based on their username.
    if (in_array($UserGroup, $arrGroups)) {
    $isValid = true;
    }
    if (($strUsers == "") && true) {
    $isValid = true;
    }
    }
    return $isValid;
    }

    $MM_restrictGoTo = "log.php";
    if (!((isset($_SESSION['MM_Username'])) && (isAuthorized("",$MM_authorizedUsers, $_SESSION['MM_Username'], $_SESSION['MM_UserGroup'])))) {
    $MM_qsChar = "?";
    $MM_referrer = $_SERVER['PHP_SELF'];
    if (strpos($MM_restrictGoTo, "?")) $MM_qsChar = "&";
    if (isset($_SERVER['QUERY_STRING']) && strlen($_SERVER['QUERY_STRING']) > 0)
    $MM_referrer .= "?" . $_SERVER['QUERY_STRING'];
    $MM_restrictGoTo = $MM_restrictGoTo. $MM_qsChar . "accesscheck=" . urlencode($MM_referrer);
    header("Location: ". $MM_restrictGoTo);
    exit;
    }
    ?>
    <?php
    //To connect to database
    include("Connections/bollingo.php");
    $query_recordset = "SELECT *
    FROM client, `statement`
    WHERE client.client_id = `statement`.client_id AND username = '".$_SESSION['MM_Username']."'";

    // Receiving variables
    $pfw_ip= $_SERVER['REMOTE_ADDR'];
    $client_id = $_POST['client_id'];
    $accname = $_POST['accname'];
    $avafortra = (int)$_POST['avafortra'];
    $amt2tra = (int)$_POST['amt2tra'];
    $email = $_POST['email'];
    $mobile = $_POST['mobile'];
    $tbname = $_POST['tbname'];
    $tbbank = $_POST['tbbank'];
    $tbbankac = $_POST['tbbankac'];
    $tbbranch = $_POST['tbbranch'];
    $tbswift = $_POST['tbswift'];

    $balanceaftertransfer = $avafortra - $amt2tra;

    //Sending Email to form owner
    $pfw_header = "From: $email\n"
    . "Reply-To: $email\n";
    $pfw_subject = "Hello";
    $pfw_email_to = "info@myownsite.com";
    $pfw_message = "Visitor's IP: $pfw_ip\n"
    . "Account Name: $accname\n"
    ;

    mail($pfw_email_to, $pfw_subject ,$pfw_message ,$pfw_header ) ;

    //updating database
    $sqlupdate = " UPDATE client, `statement` SET client.account_balance = $balanceaftertransfer,client.checkcode = 1, statement.account_balance = $balanceaftertransfer, statement.statement_details = Online Transfer, statement.statement_credit = 0, statement.staement_debit = $amt2tra, statement.statement_date = CURDATE(), statement.client_id = $client_id
    WHERE client.client_id = `statement`.client_id AND username = '".$_SESSION['MM_Username']."'";
    mysql_query($sqlupdate ) ;

    header("Location: finale.php");
    ?>

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,394
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Do an echo of $sqlupdate to see if the query is what you think it is. And then copy and paste it into PHPMyAdmin and see what happens.

  3. #3
    SitePoint Member
    Join Date
    Oct 2012
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the tip! I am using Dreamweaver for the scripting. The session variable does not exist in the mysql database so I am sure it won't work on phpmyadmin!
    Can you just peruse the code for me please?

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,394
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by thewebdudemajor View Post
    Thanks for the tip! I am using Dreamweaver for the scripting. The session variable does not exist in the mysql database so I am sure it won't work on phpmyadmin!
    Can you just peruse the code for me please?
    Of course the session variable doesn't exist in the database. That's why you have to do an echo of the variable that contains the entire query, and copy and paste the entire query (including the VALUE of the session variable). The query MUST work in PHPMyAdmin, otherwise it will never work.

  5. #5
    Resident OCD goofball! bronze trophy Serenarules's Avatar
    Join Date
    Dec 2002
    Posts
    1,911
    Mentioned
    26 Post(s)
    Tagged
    0 Thread(s)
    I would wrap the following value assignment...

    statement.statement_details = Online Transfer

    ...with ticks...

    statement.statement_details = 'Online Transfer'

    ...to properly delimit the value.

  6. #6
    SitePoint Member
    Join Date
    Oct 2012
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Already tried that Serenarules
    I am just wondering if the query was right before

    $query_recordset = "SELECT *
    FROM client, `statement`
    WHERE client.client_id = `statement`.client_id AND username = '".$_SESSION['MM_Username']."'";
    Maybe this is where the problem is?
    Any thoughts?

  7. #7
    Resident OCD goofball! bronze trophy Serenarules's Avatar
    Join Date
    Dec 2002
    Posts
    1,911
    Mentioned
    26 Post(s)
    Tagged
    0 Thread(s)
    First, actual SQL is not my strong point.

    Second, try adding the following line to check how many (if any) records are being returned.

    PHP Code:
    die(mysql_row_count($query_recordset)); 
    But what I would do in any case, is look into using the JOIN syntax to link the two tables, rather than rely on the where clause. Also, using quoted identifiers can also lead to hidden problems ('statement' is a quoted identifier). Look into using aliases. Using * can increase load, depending on how much data is being returned. Select specific columns.

    For example:

    SELECT
    c.client_id,
    c.client_name,
    s.statement_id,
    s.statement_balance
    FROM
    client as c
    JOIN
    'statement' as s
    ON
    c.client_id = s.client_id
    WHERE
    c.client_id = 'somevalue'

    Now, I realise, I pulled those fields out of thin air. I have no clue what your table structure looks like. Bear in mind, it was only an example.

    And by all means, if my sample isn't correct, would another mentor with more SQL experience correct it? Thanks!

  8. #8
    SitePoint Member
    Join Date
    Oct 2012
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Let me paste the table structure for you then. Maybe you can come up with a more efficient query.
    In the client and statement table, i have client_id which is the link between the two tables. Please note also that the session variable used by dreamweaver here is MM_Username (username = '".$_SESSION['MM_Username']."'")
    Now i want to select from the client table client_id, account_balance and checkcode
    and from the statement table client_id,account_balance, statement_credit,statement_debit and statement_details


    CREATE TABLE IF NOT EXISTS `client` (
    `client_id` int(11) NOT NULL AUTO_INCREMENT,
    `first_name` varchar(100) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
    `last_name` varchar(100) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
    `dob` date DEFAULT NULL,
    `sex` varchar(10) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
    `email` varchar(50) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
    `address` varchar(255) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
    `state` varchar(30) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
    `postcode` varchar(10) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
    `mobile` varchar(40) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
    `country` varchar(20) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
    `account_type` varchar(100) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT 'Premium',
    `Currency` varchar(10) DEFAULT NULL,
    `open_balance` int(50) DEFAULT NULL,
    `open_date` date DEFAULT NULL,
    `account_pic` varchar(100) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
    `account_number` varchar(100) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
    `account_balance` int(50) DEFAULT NULL,
    `account_status` varchar(100) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
    `username` varchar(55) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT '',
    `password` varchar(55) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT '',
    `first_time` int(10) DEFAULT NULL,
    `checkcode` int(10) DEFAULT NULL,
    `first_code` int(10) DEFAULT NULL,
    `second_code` int(10) DEFAULT NULL,
    `third_code` int(10) DEFAULT NULL,
    `fourth_code` int(10) DEFAULT NULL,
    `Updates` longtext,
    `last_login` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`client_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

    CREATE TABLE IF NOT EXISTS `statement` (
    `client_id` int(10) NOT NULL,
    `statement_id` int(10) NOT NULL AUTO_INCREMENT,
    `statement_details` varchar(200) NOT NULL,
    `statement_date` date NOT NULL,
    `statement_credit` varchar(50) NOT NULL,
    `statement_debit` varchar(50) NOT NULL,
    `account_balance` varchar(50) NOT NULL,
    PRIMARY KEY (`statement_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

  9. #9
    Resident OCD goofball! bronze trophy Serenarules's Avatar
    Join Date
    Dec 2002
    Posts
    1,911
    Mentioned
    26 Post(s)
    Tagged
    0 Thread(s)
    In you original post, your code suggests you are fetching by client.username. You realise this will return the client and all it's statements. Is there a one-to-one constraint on client and statement or can a client have more than one statement? What are you expecting to see, in terms of row counts, with this statement.

  10. #10
    SitePoint Member
    Join Date
    Oct 2012
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Of course, the reason for the client_id in the statement table is so that a client can have multiple statement. just as in a real life application..

  11. #11
    Resident OCD goofball! bronze trophy Serenarules's Avatar
    Join Date
    Dec 2002
    Posts
    1,911
    Mentioned
    26 Post(s)
    Tagged
    0 Thread(s)
    The below should (if I have my sql correct) work. Two fields were commented out to avoid column name ambiguity. It is assumed by me, that these values will be the same anyway. If not, give them aliases (s.account_balance as statement_account_balance) and uncomment them.

    Note: I don't do the work for people, by and large, but everybody gets one freebie.

    PHP Code:
    $username mysql_real_escape_string($_SESSION['MM_Username']);

    $query "SELECT

    c.client_id,
    c.account_balance,
    c.check_code,

    -- s.client_id.
    -- s.account_balance,
    s.statement_credit,
    s.statement_debit,
    s.statement_details

    FROM

    client as c

    JOIN

    'statement' as s

    ON

    c.client_id = s.client_id

    WHERE

    c.username = '
    $username'"

  12. #12
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,394
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by Serenarules View Post
    would another mentor with more SQL experience correct it?
    I'm still waiting for the OP to do what I suggested.

  13. #13
    Resident OCD goofball! bronze trophy Serenarules's Avatar
    Join Date
    Dec 2002
    Posts
    1,911
    Mentioned
    26 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    I'm still waiting for the OP to do what I suggested.
    Touche. Anyway, he has my input and sample. So I think I too am joining you 'on-the-bench' now.


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
  •