mds1256

basically I want to achieve the following in pseudo code: function contains the prepared statement and returns the array. You call the function and store the returned array in a variable. You then use the while loop / foreach loop to iterate over the results allowing the return of each field as a row variable. Just like you can do using normal mysql where you return the mysql_query() and then use this to iterate over using mysql_fetch_assoc().

No access to Mysqld driver unfortunately, shared hosting

The above, not sure how to return the array and then call the function and iterate over the results.

Hi Sorry for all the questions but just trying to get my head around using prepared statements instead of just normal connections and queries. I have the below code and what I want to do is put it into a function and return the array of results. To then call the function and store the array in a variable and the iterate over the variable to get the values. $category = $_POST['category']; $sql = "select id, name from items where category = ?"; $stmt = $Con->prepare($sql); $stmt->bind_param('s', $category); $stmt->execute(); $stmt->bind_result($id, $name); while($row = $stmt->fetch()) ---------- this is the bit I would like to return from the function { echo $id."<br />"; echo $name."<br />"; } $stmt->close();

Hi Thanks, I was thinking about that but when the table starts getting 1000's of mapping rows and the other table starts getting lots of items will this not slow right down, I know I will need to put indexes on the relevant columns.

Further developments.... It seems that this works when not calling mysql stored procedures but when you are you have to call: $Con->next_result(); This clears the result set even if there are no further results. http://www.php.net/manual/en/mysqli.query.php#102904

Having read a little more about this problem it seems that you need to return all results before a 2nd query can run as it blocks the connection (hence why If I create a new db connection it will work). see: https://bugs.php.net/bug.php?id=54444 So I tried the $stmt->get_result(); BUT my web host doesnt have the mysqld (native driver) and will not support this. Is there any work around for this, plenty people are using prepared statements, someone must have came across this issue?

I believe the error_reporting is set to E_ALL in the php.ini ok here is the code with a little error checking. The error I get is: Commands out of sync; you can't run this command now $username = $_POST['username']; $password = $_POST['password']; $sql = "call sp_checkLoginDetails(?, ?)"; $stmt = $Con->prepare($sql); if($stmt) { $stmt->bind_param('ss', $username, $password); $stmt->execute(); $stmt->store_result(); if($stmt->num_rows != 1) { $error = "<div id=\"formMessages\">Username or Password is incorrect. Please try again.</div>"; if($username == "username...") { $username = ""; } $stmt->close(); } else { $stmt->bind_result($userID, $firstNme); while($row = $stmt->fetch()) { $_SESSION['usrID'] = $userID; $_SESSION['firstName'] = $firstNme; } $_SESSION['isLoggedIn'] = true; $stmt->close(); $sql = "call sp_updateLoginDateTime(?)"; $stmt = $Con->prepare($sql); if($stmt) { $stmt->bind_param('s', $_SESSION['usrID']); $stmt->execute(); $stmt->close(); header("Location: ."); die(); } else { echo $Con->error; } } } else { echo $Con->error; }

If I create a new mysqli object just below the last stmt->close then it works so I am guessing that the mysqli object is being closed / destroyed and will not allow further queries to be ran. Why is this happening and is there a way around it.

Hi all I cannot seem to get the following code to work correctly. Basically I have created a new mysqli() and stored this in a variable called $myCon. I then pass this variable by reference so I can use the same connection into the functions. The first query runs fine but queries after the first one dont work and I get the following error message: Fatal error: Call to a member function bind_param() on a non-object $myCon = new mysqli('localhost', 'surname', 'pass', 'dbHere'); signIn(&$myCon); ----- referenced as the $myCon var is stored in a different PHP file and included function signIn($Con) { $username = $_POST['username']; $password = $_POST['password']; $sql = "call sp_checkLoginDetails(?, ?)"; $stmt = $Con->prepare($sql); $stmt->bind_param('ss', $username, $password); $stmt->execute(); $stmt->store_result(); if($stmt->num_rows != 1) { $error = "<div id=\"formMessages\">Username or Password is incorrect. Please try again.</div>"; if($username == "username...") { $username = ""; } } else { $stmt->bind_result($userID, $firstNme); while($row = $stmt->fetch()) { $_SESSION['usrID'] = $userID; $_SESSION['firstName'] = $firstNme; } $_SESSION['isLoggedIn'] = true; $stmt->close(); $sql = "call sp_updateLoginDateTime(?)"; $stmt = $Con->prepare($sql); $stmt->bind_param('s', $_SESSION['usrID']); ----- this is where it gets the error $stmt->execute(); header("Location: ."); die();}}

I see, will look into prepared statements Thank you!

Hi When post'ing from a form on a signin page I am allowing the user to type in their username and password. If the details cannot be found in the database then I send an error message to the form but populate the input box for username with the value that was submitted. Now the problem is that I run this value through mysqli_real_escape_string when it is posted so if the sql query finds no rows then it displays the signin form again but with the value of $username (which has been mysqli_real_escape_string escaped). So when the value is displayed it puts a slash into the username before the character that needs escaping (as I have put in a single quote to test). What would be the best way to echo out the value of $username without the slash, also do I need to use htmlspecialchars when echoing the $username value back into the input field? Thanks

Hi I am working on a hobby project (to learn more about PHP and MySQL) and have the following idea but not sure the best way to execute the idea. The scenario: When an item is inserted into the database under a certain category (e.g. Furniture). Everyone that is signed up to the website that has an interest in the category 'Furniture' will see this item in their inbox area on the site. This works fine and uses the category column on the inserted data. (SQL - select * from item where category = 'furniture' ---- not actual script but you get the idea). So my next idea is to create a notification type feature. So on the website there is a button called 'Inbox for Items' with a number beside of this button which is actually the number of items for their category (furniture). This works fine too using a count SQL query. But i want it to be a bit more intelligent and know which items they have looked at so it only shows the count notification for how many they have not looked at. Obviously there are going to be multiple users of this site so I cannot just add a column onto the table where the item is either acknowledged or not as their will affect all users and not just that user who has acknowledged that one item. Any ideas on the best way to accomplish this?

Thanks Psycho! Thanks for helping me on all my other queries too

do a for loop top of my head: for ($i=1; $i<=$user_rating_show; $i++) { echo "<img src='../img/beer_mug_finish-green.jpg' alt='been_m' name='beer_mug' width='34' height='33' />"; }

Hi When you see websites with a url like: test.com/page.php?id=5 What is the best way to make this safe. I was thinking the following: check if $_GET['id'] is set, if not error message check if $_GET['id'] is a integer, if not error message mysqli_real_escape_string on the $_GET['id'] pass the escaped id var into sql where page id = escaped var if no results are found then error message ELSE return page content from database So in theory this should stop someone typing in 99999999999 as the page id in the url. It should also stop any SQL injections and it should also stop someone typing in text rather than a number. And this will also not allow them to return a blank page and should show an appropriate error message e.g. redirect to the custom 404 page? Is this a good way or is there a better way of doing this? Thanks

pass it in as an argument and / or return it from a function e.g. $myVar = 3; function myFunction($myVar) { if($myVar > 1) { //do something } }

I see, so when outputting to textarea use htmlspecialchars and then when you resubmit it turns this back into characters and not the html entities. Thank you to you all for the contributions.

Sorry, it does read a bit funny. Basically if I use mysqli_real_escape_string to take the textarea text and place it into the database, when looking in the database using mysql command line when you do a select it doesnt have any escape characters in the command line select statement result (I am guessing this is correct). So when returing the data from the database into a textarea do I need to escape anything or just return exactly what the database has held (initially escaped using mysqli_real_escape_string). Or would I use htmlspecialchars / html entities on the returned data/text before echo'ing this into the textarea? Also when I re-submit (as this is an edit form) this using mysqli_real_escape_string would the value in the text area then have the htmlentities codes submitted rather than the proper html or would the textarea convert the html entities codes back into proper html code to then store the proper HTML in the database, ready for using htmlentities to display on a webpage again?

Thanks for the detailed reply. a question on the above, I have noticed that when populating the edit fields (so that the input fields have the values from the database), if I use htmlspecialchars() then inside the text area the source it the html special codes. Is this correct or should I be pulling back the raw(unescaped) returned data from the table and putting it into the text area?

Hi all Looking at best practices when submitting forms and running SQL queries in PHP. My findings so far are as follows: Turn off Register Globals Turn off Magic Quotes and use addslashes in the php script. use mysqli_real_escape_string when processing form elements and sql queries use htmlentities (with ENT_QUOTES and UTF8) for displaying text returned from a database or a form message box. use trim when inserting values into the database just to clear white space. Couple of questions: when using addslashes should I always use stripslashes when displaying / returning values with addslashes. E.g. returning text from the database that has had addslashes applied. For htmlentities should I use this or htmlspecialchars. In the example of an edit form where you populate the form text fields with the current values should I return the value from the database with the escaping reversed and then on re-submit just put it back through the escaping process again? Also any more tips for securing / filtering a form / sql queries

Hi, yeah it validates ok although it does show a warning (if you change the option to show all warnings)

Hi I use the following CSS (this is an example of my problem) but the CSS validator (w3c) reports that I have a redefination of top. When looking at the CSS that has parsed through the validator it seems to be removing the right:0px; attribute and changing it to top:0px; instead? Is there any reason why? css: /* CSS Document */ body{ margin-top:50px; } #wrapper{ position:relative; width:300px; height:200px; border:#000000 1px solid; } #test{ position:absolute; border:#000000 1px solid; top:-6px; right:0px; } html: <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "[url="http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"]http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd[/url]"> <html xmlns="[url="http://www.w3.org/1999/xhtml"]http://www.w3.org/1999/xhtml[/url]"> <head> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /> <title>Untitled Document</title> <link href="1.css" rel="stylesheet" type="text/css" /> </head> <body> <div id="wrapper"> <div id="test">tegdsfgsdgdsgst</div> </div> </body> </html>

Hi Thanks for above responses. Looks like cron jobs are the way to go.

Hi The scenario is that on my website I want to be able to run processes in the background so a user doesn't have to wait until it is finished. Real world example would be a Job site where you submit your CV and based on your criteria it then sends an email to all the people who are interested in each of your skills, and potentially there could be thousands. Now, I dont want the user to have to sit and wait for the SQL to be ran and the emails to be sent before they receive a confirmation. What are my options? I am guessing I could run a cron job every 5 minutes but how would I then get it to send some emails etc?