User ID and Client ID dynamically added to database

aarontbarksdale

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:

$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!

Weedpacket

You seem to have left some code out (specifically, the bit where you try to execute the INSERT command).
Two other important points:
1) The MySQL extension has been deprecated in favour of MySQLi or PDO.
2) You're embedding user-supplied data directly into SQL without checking that it is safe to do so or otherwise protecting your database.

aarontbarksdale

The only other code that I have that refers to anything is:

echo "1 record added for Client:". $_SESSION['client_id'];
echo "<br />";
echo "Add Another Payment";
echo "<form action='addpmt.php' method='post' name='add_pmt' target='' id='add_pmt'>";
echo "        <table width=\"450\" border=\"1\" cellspacing=\"1\" cellpadding=\"1\">";
echo "          <tr>";
echo "            <td width=\"186\">Date*:";
echo "<input type=\"text\" name=\"date\" id=\"date\" /></td>";
echo "            <td width=\"193\">Amount:";
echo "            <input type=\"text\" name=\"amount\" id=\"amount\" /></td>";
echo "          </tr>";
echo "          <tr>";
echo "            <td colspan=\"2\" align=\"center\"><input type=\"submit\" name=\"button\" id=\"button\" value=\"Submit\" /> ";
echo "            <input type=\"reset\" name=\"button2\" id=\"button2\" value=\"Reset\" /></td>";
echo "          </tr>";
echo "          <tr>";
echo "            <td colspan=\"2\" align=\"center\">*Format Date like 2013-01-01 (YYYY-MM-DD)</td>";
echo "          </tr>";
echo "        </table>";
echo "      </form>";

And that's merely the code that creates the input table and calls itself back to itself.

If you see something I'm missing, please direct me to it.

Also, with regards to your "other important points"
1. How would you suggest I alter to code for MySQLi or PDO?
2. I will add coding that strips slashes later, for now, it's not live, just testing and that's all I need since it's just me accessing the pages.

Please provide me with either guidance or assistance not just suggestion of my failure...it's already not working...I know that...how can I fix it?

Weedpacket

aarontbarksdale wrote:
If you see something I'm missing, please direct me to it.

That would be difficult if it's missing. However, I have already pointed out that you never try to execute the INSERT command (by which I mean, send it to the database). It's not enough to just make a string with SQL in it, you have to do something with it. You managed to do so with the earlier SELECT.

1. How would you suggest I alter to code for MySQLi or PDO?

See the manual. For MySQLi, the shortest way to convert it is to use the MySQLi equivalents. But that would miss out on

2. I will add coding that strips slashes later, for now, it's not live, just testing and that's all I need since it's just me accessing the pages.

Okay, but remember that after you've added code that "strips slashes" (you'd actually need to add slashes, and that's if you don't use the prepared query interface provided by the other extensions), you'll need to test all over again.

Please provide me with either guidance or assistance not just suggestion of my failure

And I was asking where the rest of the code was, because it looked incomplete, as I described, as if you'd accidentally dropped a line when you were copying and pasting. And then I gave some extra guidance about better approaches which I thought you'd be able to follow up on yourself.

aarontbarksdale

Is there a way to do it with MySQL? I'm still learning SQL and PHP and I'm really pushing myself to get this done. If I can make the code cleaner later, I will, but until then, I really would like to get this project done.

Weedpacket

Have you tried using [man]mysql_query[/man]? You managed to do so with the earlier SELECT.

aarontbarksdale

Okay, allow me to amend my question with another question. Since the previous way that I retreived the client_id was using $GET['id'] because it was a link that brought me to the detail page, but no session or other variable set the client_id for further usage...what if I were to add a hidden input field that calls the client_id and another that calls the userid? That way in my (dangerous) insert method when adding payment information I can use the successful $GET['id'] function that I used previously to associate with adding the client's information into the database...

Just wondering...would that work?

aarontbarksdale

Another person on another forum suggested the following:

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(); 
    } 
}

I tweaked it for my own purposes and I'm getting an error...suggestions?

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(); 
    } 
}

And here's the error:

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

Weedpacket

Warning: mysqli_query() [function.mysqli-query]: Empty query in /home/content/w/e/b/webmasterbark/html/csac/login/addpmt.php on line 18

Which is line 18? I don't see any use of mysqli_query in what you've posted (then again, I don't use MySQL, let alone the MySQLi extension, so maybe one of those methods there has a bogus error message.)

I tweaked it for my own purposes and I'm getting an error...suggestions?

First of all: why did you tweak it? What was it about the original query that meant it wouldn't do what you wanted? For example, why did you remove all the parameters (the [font=monospace]?[/font] characters) that your values were to be bound to, and replaced the parameter for the client_id with the value embedded directly into the statement (and then attempting to bind all three values to the parameters that you removed). Also, after your change you're trying to fill four fields in the row being inserted with only two values.

If you took the [font=monospace]?[/font] out because you thought they were the problem (instead of a crucial part of the whole idea), maybe the driver's parser was expected column names there and got confused. In that case, giving aliases to them might help: [font=monospace]? as posted_date[/font] for example.

Oh, and in passing, you might want to review the types you're binding the parameters as: are they all strings, as you've got written there, or is, for example, [font=monospace]$_SESSION['client_id'][/font] an integer?