PHP & MySQL - If content is not in one table, check another

Question

I am trying to set an if/else case to see if a value is in one table and then display said value, and if it's not in one table, to check the other table.

I am working with PHP and MySQL in Cloud 9. The email is obtained from a form which is not shown.

Searching the table "Customer" with the correct email displays the password_enc, but if the email is not found in the Customer table, and it searches the "Company" table, is doesn't display anything. I checked PHPMyAdmin and the email is present and valid

I've also checked my tables, and "email" and "rep_email" are valid.

Is there an easier way to do this, or does my current code need tweaking? Thank you for your help.

$email              = !empty($_POST ['email']) ? $_POST['email'] : "";
$email2             = $email;


$query  = "SELECT * FROM Customer WHERE email = '$email'";
$result = $db->query($query);

if ($result){
    $row            = $result->fetch_assoc(); /* These lines query the dataabse when user enters email */
    $password_enc   = $row['password_enc'];
}
elseif(!$result){
    $query2         = "SELECT * FROM Company WHERE rep_email = '$email2'";
    $result2        = $db->query($query2);
    $row2           = $result2->fetch_assoc(); /* These lines query the dataabse when user enters email */
    $password_enc   = $row2['password_enc'];
}

echo "password_enc: ".$password_enc;

Prevent SQL injection https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1 — Raymond Nijland, Jun 15 '18 at 12:04
I am preventing SQL Injection. I just haven't included it as it's outside the scope of the question. — TattooedJoey, Jun 15 '18 at 12:05
`if ($result){} elseif(!$result){}` That is very clever. Tell us, why the `elseif` check? — Ron van der Heijden, Jun 15 '18 at 12:06
You do not check `$result2` - what if `$query2` failed? What if the email is not in the `Company` table? Do you use a debugger? — Mawg says reinstate Monica, Jun 15 '18 at 12:10
Well, I thought the `!$result` would mean like if it was false or NULL, or ' '. So if the `$result` was not valid, it should do the elseif. — TattooedJoey, Jun 15 '18 at 12:10
I’d say, fix this on the level of your data model. If both customers and companies are supposed to have accounts they can login with - then why not place the info related to _that_ in an `account` table to begin with? No need any more to search two tables at this point then. (The other info/fields, that differ between customers and companies, should still go into their own dedicated table each.) — CBroe, Jun 15 '18 at 12:18
I am working in a group and is not my own Schema. I explained that to the creator of the schema and they said "It's fine the way it is". — TattooedJoey, Jun 15 '18 at 12:24
Is it not easier to just JOIN the tables and search both of them at the same time? — Stephen, Jun 15 '18 at 12:59
Yes, definitely, but in the group we're using the Scrum methodology for this Project, and the ScrumMaster has decided not to do that. I did suggest that before. — TattooedJoey, Jun 15 '18 at 15:14

score 1 · Answer 1 · answered Jun 15 '18 at 12:15

You should use num_rows to retrieve the number of rows from a result set.

$email              = !empty($_POST ['email']) ? $_POST['email'] : "";
$email2             = $email;


$query  = "SELECT * FROM Customer WHERE email = '$email'";
$result = $db->query($query);

if ($result->num_rows > 0){ //check if results is bigger then 0 
    $row            = $result->fetch_assoc(); /* These lines query the dataabse when user enters email */
    $password_enc   = $row['password_enc'];
} else {
    $query2         = "SELECT * FROM Company WHERE rep_email = '$email2'";
    $result2        = $db->query($query2);
    $row2           = $result2->fetch_assoc(); /* These lines query the dataabse when user enters email */
    $password_enc   = $row2['password_enc'];
}

echo "password_enc: ".$password_enc;

score 0 · Accepted Answer · answered Jun 15 '18 at 12:17

For successful SELECT, SHOW, DESCRIBE or EXPLAIN queries mysqli_query() will return a mysqli_result object

Php mysqli Manual

So instead of checking the response you can actually check number of rows returned like this

$query  = "SELECT * FROM Customer WHERE email = '$email'";
$result = $db->query($query);

if ($result->num_rows > 0){
   $row            = $result->fetch_assoc(); /* These lines query the dataabse when user enters email */
   $password_enc   = $row['password_enc'];
}else{
   $query2         = "SELECT * FROM Company WHERE rep_email = '$email2'";
   $result2        = $db->query($query2);
   if($result2->num_rows>0){
      $row2           = $result2->fetch_assoc(); /* These lines query the dataabse when user enters email */
      $password_enc   = $row2['password_enc'];
   }
}

if(isset($password_enc)){
   echo "password_enc: ".$password_enc;
}else{
   echo "Password Not Available";
}

I was unsure of how the $result worked. I did look up the manual, but I found it too confusing. Thank you for your help — TattooedJoey, Jun 15 '18 at 12:33

Jaakko Mäkelä · Answer 3 · 2018-06-20T04:57:33.847

$email = !empty($_POST ['email']) ? $_POST['email'] : "";

$query = "SELECT result.* FROM( SELECT IFNULL((SELECT password_enc FROM Customer WHERE email = '$email'), (SELECT password_enc FROM Company WHERE rep_email = '$email')) as password_enc) as result";

$result = $db->query($query);
$password_enc = '';

if ($result->num_rows > 0){
    $row            = $result->fetch_assoc();
    $password_enc   = $row['password_enc'];
}

echo "password_enc: ".$password_enc;

PHP & MySQL - If content is not in one table, check another

3 Answers3