what is wrong with my if statement??

[Darkmatter5]

Why is the following code outputing "John Smith" if the CompanyName field is empty?  My desired outputs should be if CompanyName is empty output "FirstName LastName" and if CompanyName is not empty output "FirstName LastName of CompanyName".

 

<?php
include 'library/dbconfig.php';
include 'library/opendb.php';

if(isset($_POST['fullname'])) {
  $query="SELECT Firstname, LastName, CompanyName, FullName
              FROM byrnjobdb.clients";
  $result=mysql_query($query);
  $row=mysql_fetch_array($result);
  while ($row=mysql_fetch_array($result)) {
    if(empty($row['CompanyName'])) {
      mysql_query("UPDATE byrnjobdb.clients SET FullName=CONCAT_WS(' ', FirstName, LastName)");
    } else {
      mysql_query("UPDATE byrnjobdb.clients SET FullName=CONCAT_WS(' ', FirstName, LastName, 'of', CompanyName)");
    }
  }
  echo "FullName in clients updated...";
}

include 'library/closedb.php';
?>

[Barand]

try putting

 

var_dump($row['CompanyName']);

 

in the loop to see what's in there

[Barand]

Whoa! I re-read the question

 

You are telling it to output John Smith (assuming firstname = john and lastname = smith) when the company name is blank. ???

[Psycho]

This is what I call a Doh! moment (we all have them).

 

The problem is you have no WHERE clause on your sub queries. So on every iterration of the loop it is updating ALL THE RECORDS in the table - not just that current record. I am assuming John Smith is the values for first and last name of the LAST record in that table.

 

A better approach would be to do a single query. I know you can do a type of IF/ELSE in a query. You just need to run a single query with an if/else to determine if you want full name to be just first and last or first, last & company.

[Barand]

Not the best description of the problem.

 

But why bother with this update anyway? It just means you are now storing Firstname, Lastname, Companyname twice. Waste of time and space.

[Darkmatter5]

Well the thing is I really don't care what is in the field except for CompanyName.

 

Say I have the following examples in my clients table.

 

ID    FirstName    LastName    CompanyName

001  John          Smith

002  Joe            Blow          Joe Bloe, Inc.

003                                    Jim Beam, Inc.

 

Okay now for the results I would like to have put into the FullName field in my clients table.

FullName for 001 should be "John Smith"

FullName for 002 should be "Joe Blow of Joe Blow, Inc."

FullName for 003 should be "    of Jim Beam, Inc."

 

I'm basically using this as an identifier to tell our secretary that enters data into the database, that records like 003 don't have a FirstName or LastName entry which need to be entered. So she can go back and call the client for their data to complete the database.  So as far as I can tell I should just be using an if statement to do "X" is CompanyName is empty or "Y" is CompanyName is not empty.  CompanyName being empty or having a value is the only deciding factor as to which form to construct FullName.  Unless I'm thinking about this wrong.  Thanks for the replies and suggestions!!

[Darkmatter5]

Barand, good point didn't think of it that way.  I guess with times I need to have a list of Clients in the form of FullName I can just generate it "on-the-fly" instead of storing it in the table.  Thanks for your input!

[Psycho]

Not the best description of the problem.

 

Sorry, but that IS the problem isn't it?

 

@Darkmatter5,

 

As Barand suggested there should be no reason to create a Full Name field. You already have the data you can always determine the FullName dynamically when displaying your results (a small function would work great).

 

If you are wanting to find records that are missing first or last name then I would create a display/search for exactly those purposes. Just creating a "full name" value means the secretary needs to back and read each full name to determine is a name is missing. Why not create a report showing just the records with missing required data?

 

Although I also would not suggest creating a full name field, I thought I would post the followng query for learning purposes. The following single query would update all the records in the table in one shot as per your first attempt.

 

UPDATE byrnjobdb.clients

SET `FullName` = IF(CompanyName='',
      CONCAT_WS(' ', FirstName, LastName),
      CONCAT_WS(' ', FirstName, LastName, 'of', CompanyName)
   )

[Darkmatter5]

Also a great suggestion, sorry this is my first attempt a PHP/MySQL implementation and really my first MySQL database.  I don't know much about typical practices on how to accomplish this stuff.  I am definitely going to start working on a report to find records missing first or last names.  Thanks for the suggestion and thanks for the code also!

[Psycho]

The following will generate a report of records missing first or last names (not tested so there may be syntax errors):

 

<?php

$query = "SELECT * FROM byrnjobdb.clients
          WHERE Firstname IS null OR Firstname=''
             OR Lastname IS null OR Lastname=''";

$result = mysql_query($query) or die (mysql_error());

if (mysql_num_rows($result)==0) {

    echo "All records contain first and last names.";

} else {

    echo "Records missing first or last name:<br>\n";
    echo "<table>\n";
    echo "<tr><td>No.</td><td>First Name</td><td>Last Name</td><td>Company</td></tr>\n;"

    $no = 0;
    while ($client = mysql_fetch_Assoc($result)) {

        $no++;
        echo "  <tr>\n";
        echo "    <td>$no</td>\n;"
        echo "    <td>".$client['Firstname']."</td>\n;"
        echo "    <td>".$client['Lastname']."</td>\n;"
        echo "    <td>".$client['Company']."</td>\n;"
        echo "  </tr>\n";
    }

    echo "</table>\n";
}

?>

[Barand]

Not the best description of the problem.

 

Sorry, but that IS the problem isn't it?

 

 

 

@mjdamato,

I was referring to the problem description in the opening post. You were right in your assertion that all rows will be updated without a WHERE clause - I competely overlooked that one.