Best way to write PHP Query for MySQL

[Stefano Mtangoo]

Hi all,
I have been running errors too much that I realized that I have learned 'bad' behaviour in writting PHP query.
So I decided to change. Please tell me what is good behaviour of writting queries that involve PHP variables as well as functions like one below.

$query = "SELECT * FROM logintable WHERE username={$this->EncryptPassword($password)} AND password=$password";

Thanks

[qazplm114477]

It really depends on how you call your query.

you can use mysqli_query($link, $query) or you can use mysqli_prepare($link, $query).

mysqli_prepare is a little different. go to http://php.net/manual/en/mysqli.prepare.php To see examples.

If you are talking about programming practices then it always good to validate your queries by using

mysqli->affected_rows - For checking if your update/insert/delect went through

mysqli_result->num_rows - For select statements

mysqli_close - Close the database after each function

mysqli_result::free - Frees the memory associated with the result

you may want to also look into transactions when updating several tables in one go.

also some validations like

$example = trim($_POST['example'])

//or

//ex:
$city = $mysqli->real_escape_string($city);

mysqli::real_escape_string

[diafol]

I take it you're asking whether to include class variables or php function calls within your sql string?

Personally, I'd place these values into simple php variables before placing them into raw sql strings, for the simple reason that the SQL is more flexible. $variable can be assigned via a number of different conditionals, whereas $this->EncryptPassword($password) is a specific case (pertaining to a function).

[Stefano Mtangoo]

I think I have not well explained it.
I have:
$this->EncryptPassword($password) which does password encryption
and variable $username. Then I need to write a query to select the matched rows. How would you write that query if it were you?

[diafol]

$username = $this->EncryptPassword($password);
$query = "SELECT * FROM logintable WHERE username='$username' AND password='$password'";

I have to admit, I don't understand what the EncryptPassword() function is trying to do. You've placed this as a value for username in your sql. If $password is the raw password (e.g.) from a form. It's a bit odd.

[Stefano Mtangoo]

Cool!
Thanks Ardav

[diafol]

I know this is solved, but I can't say that this IS the way to do things. Just the way that I'd do it. I'm far from being an expert. Anybody else with a view?

[Stefano Mtangoo]

My Stupid old method

$query = "SELECT * FROM logintable WHERE username='{ $this->EncryptPassword($password)}' AND password='$password'";