MySQL table with similar column info - HELP!!!

Question

I have a DB with a table that is named "victim". The form that dumps the info into the table has room for two victims and therefore there is vic1_fname, vic1_lname, vic2_fname, vic2_lname, etc.. (business name, person first, person last, address, city, state, zip) a "1" and "2" of each. Now I want to search the DB and locate listed victims.

This is what I have so far:

$result = mysql_query(
                  "SELECT victim.*
                    FROM victim
                    WHERE vic1_business_name OR vic2_business_name  LIKE '%$search_vic_business_name%'
                    AND vic1_fname OR vic2_fname      LIKE '%$search_vic_fname%'
                    AND vic1_lname OR vic2_lname      LIKE '%$search_vic_lname%'
                    AND vic1_address OR vic2_address  LIKE '%$search_vic_address%'
                    AND vic1_city OR vic2_city        LIKE '%$search_vic_city%'
                    AND vic1_state OR vic2_state      LIKE '%$search_vic_state%'
                    AND vic1_dob OR vic2_dob          LIKE '%$search_vic_dob%'

                  ");

<table width="960" style="border: groove;" border=".5">
<tr><th colspan=10>You search results are listed below:</th></tr>
<tr>
<th>Case Number</th>
<th>Business Name</th>
<th>First Name</th>
<th>Last Name</th>
<th>DOB / Age</th>
<th>Address</th>
<th>City</th>
<th>State</th>
</tr>

<?php

while($row = mysql_fetch_array($result))
  { ?>

<tr>
<td align="center"><?php print $row['vic_business_name']; ?></td>
<td align="center"><?php print $row['vic_fname']; ?></td>
<td align="center"><?php print $row['vic_lname']; ?></td>
<td align="center"><?php print $row['vic_dob']; ?></td>
<td align="center"><?php print $row['vic_adress']; ?></td>
<td align="center"><?php print $row['vic_city']; ?></td>
<td align="center"><?php print $row['vic_state']; ?></td>
</tr>

<?php  }    ?>
</table>

The info did not display in the table until I changed the table to this:

<tr>
<td align="center"><?php print $row['vic1_business_name']; ?></td>
<td align="center"><?php print $row['vic1_fname']; ?></td>
<td align="center"><?php print $row['vic1_lname']; ?></td>
<td align="center"><?php print $row['vic1_dob']; ?></td>
<td align="center"><?php print $row['vic1_adress']; ?></td>
<td align="center"><?php print $row['vic1_city']; ?></td>
<td align="center"><?php print $row['vic1_state']; ?></td>
</tr>

<tr>
<td align="center"><?php print $row['vic2_business_name']; ?></td>
<td align="center"><?php print $row['vic2_fname']; ?></td>
<td align="center"><?php print $row['vic2_lname']; ?></td>
<td align="center"><?php print $row['vic2_dob']; ?></td>
<td align="center"><?php print $row['vic2_adress']; ?></td>
<td align="center"><?php print $row['vic2_city']; ?></td>
<td align="center"><?php print $row['vic2_state']; ?></td>
</tr>

Now it displays both rows, even if its empty. It doesn't matter if the victim was listed originally as vic1 or vic2, i just want to know if they are a victim.

I hope this makes sense. I can't get it to display the way I want, line-by-line, irregardless of whether you are vic1 or vic2.

Answer 1

You should possibly look at getting this into first normal form. At some point an incident will happen with 3 victims! This will make your search queries easier as well.

So your Victim table would be structured like

caseid (or whatever)
victimid
business_name 
fname
lname
address
city
state
dob

Edit Following discussion in comments.

You say "When I seach your name I discover all the times you were a victim." This implies that presumably you are storing the victim's details multiple times and each time they might be slightly different.

This is definitely far from ideal and again seems like it could benefit from having normalisation principles applied but it really depends upon the scope of your application.

If it is essentially just a system for recording values entered on paper forms (whatever they may be) and doesn't try and link these together and resolve any such discrepancies then you may be fine with what you've got and using the syntax in Ignacio's answer. Which will resolve the question you actually asked!

The alternative is more flexible but might add quite a lot of unneeded complexity.

Answer 2

You can't write queries like that.

WHERE ((vic1_business_name LIKE '%$search_vic_business_name%') OR
  (vic2_business_name  LIKE '%$search_vic_business_name%')) AND
 ....

Answer 3

I think OR is a strictly boolean operator and therefore cannot be used to return a string for LIKE to match. You'll have to duplicate all the expressions, i.e. change

AND vic1_fname OR vic2_fname      LIKE '%$search_vic_fname%'

into

AND vic1_fname LIKE '%$search_vic_fname%' OR vic2_fname LIKE '%$search_vic_fname%'

BUT, as Martin Smith and knittl remark so correctly, this is a very inflexible and hard to use solution! What you could better try is to create separate incidents and victims tables and give the victims table a incident_id field which connects it to an incident. That way, you can assign any number of victims to an incident. (It's also possible to craft a system that allows a victim to appear in more than one incident, but that involves an additional table.)

Answer 4

Those victims should really have seperate rows in a table...

At the moment, the only way to get it working fast is using:

 SELECT ...
 FROM victim
 WHERE vic1_business_name LIKE '%$search_vic_business_name%' -- etc.
 UNION 
 SELECT ...
 FROM victim
 WHERE vic2_business_name LIKE '%$search_vic_business_name%' -- etc.

But it's cumbersome and not advisable. A seperate table victims, with a victim_relations table joining the 2 together would do the trick.

Answer 5

You could try getting your victims into a more useable form first:

((SELECT vic1_business_name, vic1_fname, vic1_lname, vic1_address, vic1_city, vic1_state, vic1_dob)
UNION
(SELECT vic2_business_name, vic2_fname, vic2_lname, vic2_address, vic2_city, vic2_state, vic2_dob))

Using that in a FROM expression should give you a list of all victims, one per line.