Jump to content
Larry Ullman's Book Forums

Select Statement Using Mysqli Extension


Recommended Posts

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.

  • Upvote 1
Link to comment
Share on other sites

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

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

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

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

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

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

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

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

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

 Share

×
×
  • Create New...