Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 12 of 12
  1. #1
    New Coder
    Join Date
    Apr 2013
    Posts
    15
    Thanks
    5
    Thanked 0 Times in 0 Posts

    User and Client ID dynamically added to database

    Okay, what I have done is retrieved a list of Clients that are bound to a particular userid.

    Now, when I go to add a payment, I want BOTH the userid and the client_id added automatically into the database. I am using sessions and session_start(); is at the top of the code. Here's what I have:
    PHP Code:
    $data mysql_query("SELECT * FROM clients WHERE client_id = '"$_SESSION['clientid'] ."'") or die(mysql_error());
    $row mysql_fetch_assoc($data);
    $userid $row['userid'];
    $clientid $row['client_id'];
    $sql="INSERT INTO payments (client_id, userid, pmt_date, pmt_amt) VALUES ('"$userid ."', '"$clientid ."', '$_POST[date]', '$_POST[amount]')"
    It enters the $_POST information no problem, however, the binding doesn't work.

    I have also tried replacing the $userid and $clientid variables in the INSERT statement with {$_SESSION['userid']} and {$_GET['id']} (both with and without curly brackets. I am officially lost. Please help!

  • #2
    Regular Coder
    Join Date
    Jun 2009
    Posts
    144
    Thanks
    3
    Thanked 20 Times in 20 Posts
    Not sure why you are using ' " together,

    try

    PHP Code:
    $sql="INSERT INTO payments (client_id, userid, pmt_date, pmt_amt) VALUES ('$userid', '$clientid', '{$_POST[date]}', '{$_POST[amount]}')"
    One major warning, you are not sanatizing your POST variables before inserting, you are at high risk from SQL injection attack. Consider moving to prepaired statments in mysqli as the php mysql function is going to be unsupported soon...

  • Users who have thanked itxtme for this post:

    aarontbarksdale (04-29-2013)

  • #3
    New Coder
    Join Date
    Apr 2013
    Posts
    15
    Thanks
    5
    Thanked 0 Times in 0 Posts
    @itxtme: You're not the only one who has suggested that to me now, transitioning to mysqli...so I guess I need to research that NOW. But to the suggestion of mysql injection. These are in a user logged in areas that won't be used by highly-tech savvy people...so injection won't be completely necessary for this side, however, for the login and password...perhaps.

    Any suggestions on how to rework the above into mysqli since I'm kind of a novice as it is with sql and know NOTHING about mysqli?

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    You can do this in a single query. Since it is requiring that the client_id exists in the clients table associated to the user_id, a simple INSERT. . . SELECT statement can be used.
    PHP Code:
    $sQry "INSERT INTO payments (client_id, userid, pmt_date, pmt_amt)
        SELECT client_id, user_id, '{$_POST['date']}', '{$_POST['amount']}' FROM clients WHERE client_id = '{$_SESSION['client_id']}'"

    Looks like that would work assuming clients.client_id is the primary key.
    To convert to mysqli, I'd suggest using prepared statements.
    PHP Code:
    if ($con = new MySQLi('localhost''user''password''db'))
    {
        
    $sQry "INSERT INTO payments (client_id, userid, pmt_date, pmt_amt)
        SELECT client_id, user_id, ?, ? FROM clients WHERE client_id = ?"
    ;
        if (
    $stmt $con->prepare($sQry))
        {
            
    $stmt->bind_param('sss'$_POST['date'], $_POST['amount'], $_SESSION['client_id']);
            
    $stmt->execute();
            
    $stmt->close();
        }

    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • Users who have thanked Fou-Lu for this post:

    aarontbarksdale (05-02-2013)

  • #5
    New Coder
    Join Date
    Apr 2013
    Posts
    15
    Thanks
    5
    Thanked 0 Times in 0 Posts
    @Fou-Lu: What are the question marks "?" in the INSERT statement, are they directly related to the code or are they placeholders for certain other variables.

    Also, in the WHERE clause you have "client_id = ?" <<<< THAT is the problem, it's not calling the proper "userid"...will $_SESSION['userid'] work for that???

  • #6
    New Coder
    Join Date
    Apr 2013
    Posts
    15
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Here's what I did...I'm getting an error...okay, here's the code:

    PHP Code:
    if ($con = new MySQLi('localhost''root''admin''webmasterbark')) 

        
    $sQry "INSERT INTO payments (client_id, userid, pmt_date, pmt_amt) 
        SELECT client_id, userid FROM clients WHERE client_id = " 
    $_SESSION['client_id']; 
        if (
    $stmt $con->prepare($sQry)) 
        { 
            
    $stmt->bind_param('sss'$_POST['date'], $_POST['amount'], $_SESSION['client_id']); 
            
    $stmt->execute(); 
            
    $stmt->close(); 
        } 

    Here's the Error that I'm getting:
    Warning: mysqli_query() [function.mysqli-query]: Empty query in /home/content/w/e/b/webmasterbark/html/csac/login/addpmt.php on line 18
    Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 2

  • #7
    New Coder
    Join Date
    Apr 2013
    Posts
    15
    Thanks
    5
    Thanked 0 Times in 0 Posts
    This also follows the above code...is it redundant???

    PHP Code:
    if (!mysqli_query($con,$sql))
    { die(
    'Error: ' .mysqli_error($con) );
     }
    ?> 

  • #8
    New Coder
    Join Date
    Apr 2013
    Posts
    15
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fou-Lu View Post
    You can do this in a single query. Since it is requiring that the client_id exists in the clients table associated to the user_id, a simple INSERT. . . SELECT statement can be used.
    PHP Code:
    $sQry "INSERT INTO payments (client_id, userid, pmt_date, pmt_amt)
        SELECT client_id, user_id, '{$_POST['date']}', '{$_POST['amount']}' FROM clients WHERE client_id = '{$_SESSION['client_id']}'"

    Looks like that would work assuming clients.client_id is the primary key.
    To convert to mysqli, I'd suggest using prepared statements.
    PHP Code:
    if ($con = new MySQLi('localhost''user''password''db'))
    {
        
    $sQry "INSERT INTO payments (client_id, userid, pmt_date, pmt_amt)
        SELECT client_id, user_id, ?, ? FROM clients WHERE client_id = ?"
    ;
        if (
    $stmt $con->prepare($sQry))
        {
            
    $stmt->bind_param('sss'$_POST['date'], $_POST['amount'], $_SESSION['client_id']);
            
    $stmt->execute();
            
    $stmt->close();
        }

    Fou-Lu: Not sure why, but it's not working. It's processing with no errors, but it's not INSERTING anything into anywhere. I wasn't sure about the "?" so I removed them and inserted the correct thing, however, it didn't do anything either. So, I'm not sure what I'm doing wrong and/or what happened

  • #9
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Check to see if it tossed an error out of the execute and bind:
    PHP Code:
    if ($con = new MySQLi('localhost''user''password''db'))
    {
        
    $sQry "INSERT INTO payments (client_id, userid, pmt_date, pmt_amt)
        SELECT client_id, user_id, ?, ? FROM clients WHERE client_id = ?"
    ;
        if (
    $stmt $con->prepare($sQry))
        {
            
    $stmt->bind_param('sss'$_POST['date'], $_POST['amount'], $_SESSION['client_id']);
            
    $stmt->execute();
            if (
    $stmt->errno)
            {
                
    printf("Statement execute error: %s" PHP_EOL$stmt->error);
            }
            
    $stmt->close();
        }
        else
        {
            
    printf("Prepare error: %s" PHP_EOL$con->error);
        }

    And yes the ?'s are required.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • Users who have thanked Fou-Lu for this post:

    aarontbarksdale (05-02-2013)

  • #10
    New Coder
    Join Date
    Apr 2013
    Posts
    15
    Thanks
    5
    Thanked 0 Times in 0 Posts
    No errors...and still no additions anywhere in the database. Not sure what has happened...

  • #11
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Add an else to the statement execute error to see the affected rows:
    PHP Code:
            if ($stmt->errno)
            {
                
    printf("Statement execute error: %s" PHP_EOL$stmt->error);
            }
            else
            {
                
    printf("Statement affected rows: %d" PHP_EOL$stmt->affected_rows);
            } 
    If that shows as 0, than its your data that's incorrect. In particular, it's likely the $_SESSION['client_id'] that finds no match in the clients table to insert. If that's the case, you need to debug each of the $_POST variables and the $_SESSION['client_id'] to make sure they are all valid before attempting to run.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • Users who have thanked Fou-Lu for this post:

    aarontbarksdale (05-02-2013)

  • #12
    New Coder
    Join Date
    Apr 2013
    Posts
    15
    Thanks
    5
    Thanked 0 Times in 0 Posts
    The simplest solutions are always the best.

    After ensuring that session variables were set, in order to call specific ones, I simply added:
    Code:
    <input type="hidden" name="clientid" id="<? echo $clientid; ?>">
    to the input form in order to ensure that it gets added properly. It works.

    I will clean up the code with sqli later, I just need it to WORK right now.

    Thanks for your help!


  •  

    Tags for this Thread

    Posting Permissions

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