I have a problem with mysql and php

Question

Hi

I have a problem, this is my code:

   $db = new mysqli("localhost", "root", "", "blah");

$result1 = $db->query("select * from c_register where email = '$eml' and password = '$pass'");

if($result1->fetch_array())

  {

        $auth->createSession();

        $_SESSION['user'] = 'client';

        promptUser("You have successfully logged in!!!","index.php");
  }

$db = new mysqli("localhost", "root", "", "blah");

$result2 = $db->query("select * from b_register where email = '$eml' and password = '$pass'");
  if($result2->fetch_array())

  {

         $auth->createSession();

         $_SESSION['user'] = 'business';

         promptUser("You have successfully logged in!!!","index.php");
  }
$db = new mysqli("localhost", "root", "", "blah");

$result3 = $db->query("select * from g_register where email = '$eml' and password = '$pass'");
  if($result3->fetch_array())

  {

        $auth->createSession();

        $_SESSION['user'] = 'employee';

        promptUser("You have successfully logged in!!!","index.php");
  }

$db = new mysqli("localhost", "root", "", "blah");

$result4 = $db->query("select * from k_register where email = '$eml' and password = '$pass'");
  if($result4->fetch_array())

  {

        $auth->createSession();

        $_SESSION['user'] = 'super';

        promptUser("You have successfully logged in!!!","index.php");
  }
  else

  {

        promptUser("Username/Password do not match.  Please try again!!!","");
  }

Funny enough this code works, but I no that I went about it the wrong way. I am new with php and mysql, so please help. I also tried e.gresult4->free(); for all the variable that save the data, and I got this error: Fatal error: Call to a member function free() on a non-object in...

Answer 1

mysqli::query() returns a result object only after a successful query.

You need to build in a check:

if(($result1 != false) and ($result1->fetch_array()))  // The same for 2,3,4...

you should get the error message using

echo $db->error;

Answer 2

Don't repeat yourself. You already made your mysqli object, so reuse it. For example:

$db = new mysqli("localhost", "root", "", "blah");
$result1 = $db->query("select * from c_register...");
$result2 = $db->query("select * from d_register...");
$result3 = $db->query("select * from e_register...");

This will make your code more legible, and easier to modify later.

Answer 3

From PHP Manual: mysqli::query returns TRUE on success or FALSE on failure. For SELECT, SHOW, DESCRIBE or EXPLAIN mysqli_query() will return a result object.

So you should test it:

if ($result != false) {
  ...
} else {
  // print error or whatever
}

Btw. it is VERY DANGEROUS not to escape variables like $eml and $pass - if a user type as $pass something like: bleh' OR 1 = 1 OR password = 'bleh, then the whole query will look like:

select * from b_register where email = '[email protected]' and password = 'bleh' OR 1 = 1 OR password = 'bleh'

and the user will get logged without knowing the password!

Therefore you should use:

mysql_real_escape_string($eml)

and the same for $pass.

Or even better: use statement preparing and parameters binding - see: http://pl2.php.net/manual/en/mysqli.prepare.php