Simple SQL Search Multiple table problem

[kerukelw]

hi I have a problem with a multiple table  simple sql search.

 

I made it such that it can search on two tables. but it came out as a blank instead. I tried without the  " INNER JOIN medi On jsprofile.icno = medi.icno " and it works fine!

 

 

---

 

 

  if (count($error) < 1) {

      $searchSQL = "SELECT  * FROM jsprofile    INNER JOIN medi On jsprofile.icno = medi.icno WHERE ";

     

      // grab the search types.

      $types = array();

      $types[] = isset($_GET['sicno'])?"`name` LIKE '%{$searchTermDB}%'":'';

      $types[] = isset($_GET['sname'])?"`icno` LIKE '%{$searchTermDB1}%'":'';

      $types[] = isset($_GET['sgender'])?"`gender` LIKE '%{$searchTermDB2}%'":'';

     

      $types = array_filter($types, "removeEmpty"); // removes any item that was empty (not checked)

     

      if (count($types) < 1)

        $types[] = "`jsprofile.icno` LIKE '%{$searchTermDB}%'"; // use the body as a default search if none are checked

     

          $andOr = isset($_GET['matchall'])?'AND':'AND';

      $searchSQL .= implode(" {$andOr} ", $types) . "  ORDER BY `icno`"; // order by title.

 

      $searchResult = mysql_query($searchSQL) or trigger_error("There was an error.<br/>" . mysql_error() . "<br />SQL Was: {$searchSQL}");

     

 

 

[GingerRobot]

1.) We're probably going to need to see how you're trying to read the data. I presume you're reading based on column names -- if those names are common on both tables, there's going to be an issue

2.) Have you tried the query inside a MySQL console/PHPMyAdmin and verified it outputs what you expect?

3.) Try using

tags around your code

4.) Welcome to the forums

[kerukelw]

Thank you for the welcome!

 

I tried on SQL, it works.. it actually works without the join and the on.

 

 

somehow on the

 $types[] = isset($_GET['sname'])?"`icno` LIKE '%{$searchTermDB1}%'":''; 

 

when i try using jsprofile.icno instead of icno, it doesnt work

 

 

This is how my table looks like

 

JSPROFILE

 

icno (primary)

name

gender

address

 

MEDI

 

icno

status

comments