bahaa Posted June 29, 2011 Share Posted June 29, 2011 Can any one tell me how to write the select statement using mysqli extension? I know the insert and update ones but I don't know the select and delete ones. Thanks in advance Link to comment Share on other sites More sharing options...
HartleySan Posted June 29, 2011 Share Posted June 29, 2011 I think the SELECT statement is based on SQL, not the MySQL extension, but anyway, here are some examples: SELECT * FROM users WHERE id=3; SELECT car_model, car_color FROM cars WHERE type='Porsche'; SELECT field-names-separated-by-commas FROM table-name WHERE field-name=value ORDER BY field-name ASC/DESC LIMIT x, y; Also, you can add JOIN statements into the mix, etc., but for basic SELECT statements, the above should suffice. 1 Link to comment Share on other sites More sharing options...
bahaa Posted June 29, 2011 Author Share Posted June 29, 2011 I think the SELECT statement is based on SQL, not the MySQL extension, but anyway, here are some examples: SELECT * FROM users WHERE id=3; SELECT car_model, car_color FROM cars WHERE type='Porsche'; SELECT field-names-separated-by-commas FROM table-name WHERE field-name=value ORDER BY field-name ASC/DESC LIMIT x, y; Also, you can add JOIN statements into the mix, etc., but for basic SELECT statements, the above should suffice. I think I read in the book that we can write select statement mysqli extension so we don't use the mysqli_real_escape_string to escape values. Not sure if I am write Link to comment Share on other sites More sharing options...
Josee Posted June 29, 2011 Share Posted June 29, 2011 The mysqli_real_escape_string() function is used for an INSERT or an UPDATE — or similar queries that involve inserting data into the database — so as to avoid problematic characters. You don't need it to retrieve data with a SELECT query. Were you thinking of prepared statements that avoid the use of the mysqli_real_escape_string() function? (That's covered in chapter 12, "Preventing SQL Injection Attacks".) Link to comment Share on other sites More sharing options...
bahaa Posted June 29, 2011 Author Share Posted June 29, 2011 The mysqli_real_escape_string() function is used for an INSERT or an UPDATE — or similar queries that involve inserting data into the database — so as to avoid problematic characters. You don't need it to retrieve data with a SELECT query. Were you thinking of prepared statements that avoid the use of the mysqli_real_escape_string() function? (That's covered in chapter 12, "Preventing SQL Injection Attacks".) I am making a registration page and I need to use the select statement to check if username exists or not, so in this case I need to escape the value coming from the user. Link to comment Share on other sites More sharing options...
bahaa Posted June 29, 2011 Author Share Posted June 29, 2011 I am sorry , I really did not explain what I need. I want to use the prepared select statement Link to comment Share on other sites More sharing options...
HartleySan Posted June 30, 2011 Share Posted June 30, 2011 If you want to use a prepared statement, then please follow the explanation in Larry's book, or Google something like "PHP prepared statements". You'll get plenty of good, relevant hits. Link to comment Share on other sites More sharing options...
bahaa Posted June 30, 2011 Author Share Posted June 30, 2011 If you want to use a prepared statement, then please follow the explanation in Larry's book, or Google something like "PHP prepared statements". You'll get plenty of good, relevant hits. I already know how to use the insert and update prepare statement, but for some reason I can't use the select one. I searched on the internet and followed the instruction but it fives me an error. This is what I want to do $Q = " SELECT username FROM users Where username = '$username' "; $R = mysqli_query($con, $Q); if(mysqli_num_rows($R) !=0){ echo "The username you have chosen already taken"; } Can any one rewrite it using the prepared statement? Link to comment Share on other sites More sharing options...
Josee Posted June 30, 2011 Share Posted June 30, 2011 If you want to check that mysqli_num_row($R) is not equal to zero, you should use != 0 or > 0; not !== 0. Link to comment Share on other sites More sharing options...
bahaa Posted June 30, 2011 Author Share Posted June 30, 2011 If you want to check that mysqli_num_row($R) is not equal to zero, you should use != 0 or > 0; not !== 0. Thanks it was a typo but how to rewrite it using prepared statement? Link to comment Share on other sites More sharing options...
chuflasky Posted June 30, 2011 Share Posted June 30, 2011 Hi bahaa, i'm not sure is this is what you are looking for but give a try. $q = " SELECT username FROM users WHERE username = ? "; $stmt = mysqli_prepare ($con, $q); mysqli_stmt_bind_param($stmt, 's', $username); mysqli_stmt_execute($stmt); if(mysqli_stmt_num_rows($stmt) != 0){ echo "<p class='error'> The username you have chosen is already taken.</p>"; }else{ //something else } Hope this help or get you on the right track. Link to comment Share on other sites More sharing options...
bahaa Posted June 30, 2011 Author Share Posted June 30, 2011 Hi bahaa, i'm not sure is this is what you are looking for but give a try. $q = " SELECT username FROM users WHERE username = ? "; $stmt = mysqli_prepare ($con, $q); mysqli_stmt_bind_param($stmt, 's', $username); mysqli_stmt_execute($stmt); if(mysqli_stmt_num_rows($stmt) != 0){ echo "<p class='error'> The username you have chosen is already taken.</p>"; }else{ //something else } Hope this help or get you on the right track. i get this error mysqli_bind_param() expects parameter 1 to be mysqli_stmt, boolean given Link to comment Share on other sites More sharing options...
chuflasky Posted July 1, 2011 Share Posted July 1, 2011 Do you using a proper connection to the database for example. $q = " SELECT username FROM user WHERE username = ?"; $stmt = mysqli_prepare($dbc, $q); . Link to comment Share on other sites More sharing options...
bahaa Posted July 1, 2011 Author Share Posted July 1, 2011 Do you using a proper connection to the database for example. $q = " SELECT username FROM user WHERE username = ?"; $stmt = mysqli_prepare($dbc, $q); . Yes, I use the proper connection. Link to comment Share on other sites More sharing options...
chuflasky Posted July 1, 2011 Share Posted July 1, 2011 If the connection is properly defined, you can use echo mysqli_connect_error() to see if something went wrong and you are not getting connected to the database. Link to comment Share on other sites More sharing options...
bahaa Posted July 1, 2011 Author Share Posted July 1, 2011 If the connection is properly defined, you can use echo mysqli_connect_error() to see if something went wrong and you are not getting connected to the database. I don't have any problem with the connection. I have other queries on the same page and they work fine. Link to comment Share on other sites More sharing options...
Larry Posted July 1, 2011 Share Posted July 1, 2011 So your $stmt variable probably has a FALSE value, meaning that mysqli_prepare() failed. This is either because there's a problem with your connection or with the query. I don't know that this would cause the problem, but I'd start by removing the extraneous spaces you have in the query string. You can then include code that checks $stmt and if it has a false value, you call the mysqli_error() function. Link to comment Share on other sites More sharing options...
Recommended Posts