I have never heard of ‘little bobby tables’ - what must I do, google it? Why should I use prepared statements if this works? I took this query method from examples on w3schools website, so I thought it was correct.
Prepared statements help to improve the security of your site by making it more difficult for a malicious user to supply something in your $link_date that might cause problems for your database. Of course, from your code we don’t know where that has come from, and what checks you have done on it prior to sticking it in your query. Prepared Statements also help out in other ways, for example removing the need for quotes in an INSERT query, and all that brings with it.
Yes, you should google “Little Bobby Tables”, it’s a link to a cartoon which is referred to in a lot of articles discussing why passing plain text into your query is a bad idea. It’s a bit of a big thing to discuss in any detail on the end of another thread, but there are many, many articles out there describing best practice. I certainly wouldn’t presume to tell you what that is.
I read on here that while w3schools is very good at getting high up in the search results, it also contains quite a few old articles that aren’t really best practice any more. I haven’t used the site myself so I can’t say whether that is still the case.
Honestly, this is the closest example I’ve ever got to select prepared statements:
$sql = "SELECT * FROM users WHERE id=?"; // SQL with parameters
$stmt = $conn->prepare($sql);
$stmt->bind_param("i", $id);
$stmt->execute();
$result = $stmt->get_result(); // get the mysqli result
$user = $result->fetch_assoc(); // fetch data
What you may not notice is that there is no checking to see if any rows come up, there is no while statement and my use of $row[‘row_name’] does not work. I did try and couldn’t figure it out and had to give up.
Php is hard enough without having to add all those arrows and nonsensicalness to it all. There are hardly any tutorials on it too. Try search for a prepared statement select statement and see how many hits you get. Imagine now if I am trying to do more difficult stuff.
In that specific example, no. But there could be. That aspect of things hasn’t changed.
In that specific example, no. But then it’s retrieving a user profile based on the id which is presumably unique. So there is no need for a while loop. But the while loop is nothing to do with executing the query, it’s to do with retrieving the results the query returns.
“Does not work” doesn’t give anyone a chance to help you find out what the problem is. Show us the code.
It is difficult for a learner sometimes, that’s true, though I am only presuming you are learning, I don’t know your background. But isn’t it better to learn the “proper” way to do it and go from there? Far better surely than to do it the wrong way and (a) best case - have to re-do it later when you do figure it out; or (b) worst case - have a law suit or complete data loss on your hands.
There are plenty of articles on how to do prepared statement properly, and once you have seen it working, you will wonder what all the fuss was about. I use PDO myself rather than mysqli, but there are plenty of examples on this forum showing how to use prepared statements.
I didn’t find plenty of examples, so that is not true. It is very hard to learn it and there are no concrete tutorials. i couldn’t figure out what it was meant to look like or do. It is unfortunate. I really do what to do it the proper way
That doesn’t mean that there aren’t plenty of articles, it just means you didn’t find them. 
Though of course in reality you only need one, if it explains things clearly enough for you. The documentation pages at php.net do give some sample code that should get things moving for you.
I use PDO as I mentioned, but just doing a bit of a search and reading your previous post allows me to do this to your code:
$sql_total_mass = "
SELECT jobs_assembly.assemble_qty, jobs.mass,
(jobs_assembly.assemble_qty * jobs.mass) AS 'sum'
FROM jobs_assembly
LEFT JOIN jobs on jobs_assembly.jobs_id = jobs.id
LEFT JOIN job_names ON jobs.job_names_id = job_names.job_id
WHERE jobs_assembly.assemble_date = ?
ORDER BY job_names.job_name, jobs.assembly
";
$stmt = $conn->prepare($sql_total_mass);
$stmt->bind_param("s", $link_date);
$stmt->execute();
if ($stmt->num_rows() > 0) { // I think, but it might be $result_total_mass->num_rows()
$result_total_mass = $stmt->get_result();
$grand_total_mass = 0;
while($row_total_mass = $result_total_mass->fetch_array()) {
echo $row_total_mass['sum'].'<br />';
$grand_total_mass += $row_total_mass['sum'];
} //while
} //if
echo $grand_total_mass;
As I said, I don’t use mysqli and the exact sequence is a bit different to PDO, and I can’t test it. Hopefully a mysqli expert will shout out if I’ve done something wrong. All that is based on the code you posted, and a couple of quick looks at php.net.
This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.