search one table then another (PHP mysql)

getyourbiglobst posted this at 01:52 — 23rd July 2002.

Joined: Oct 2001

my code works, but i want it to do more. currently it returns all information in one table (bills02) based on user input from an html form.

what i want to be able to do is have the user input a number(apn) and have the php search one table(excluded) in the database and if their number is found print a message(your apn is excluded). if their number is not found, then it goes and searches the other table in the database (bills02) and displays the relevant information (what it basically does now).

i appreciate your thoughts on this.

<?php
mysql_connect (localhost, login, \"pass\");
   
mysql_select_db (remediation);

if ($apn == \"\")
{$apn = '%';}

$result = mysql_query (\"SELECT bills02.co, 
bills02.apn, bills02.ann, bills02.fee, bills02.contam
                        FROM bills02
                        WHERE bills02.apn = '$apn%'                     
                       \");

if ($row = mysql_fetch_array($result)) {

do {
  print (\"<h5>APN (Parcel Number)</h5>\");
  print $row[\"apn\"];
  print (\"<p>Annualized Water Bill</p>\");
  print $row[\"ann\"];
  print (\"<h2>Remediation Fee</h2>\");
  print $row[\"fee\"];
  print (\"<p>Contaminated?</p>\");
  print $row[\"contam\"];
  print (\" \");
  
} while($row = mysql_fetch_array($result));

} else {print \"Sorry, no records were found!\";}
?>

Mark Hensler posted this at 17:06 — 23rd July 2002.

He has: 4,048 posts

Joined: Aug 2000

try something like this

<?php
function Query_Error($file, $line, $query, $silent=TRUE) {
    
    echo \"\n\n\";
    echo ($silent == TRUE) ? \"<!--\n\" : \"<textarea cols=\\"60\\" rows=\\"7\\">\n\";
    echo \"FILE: $file\n\";
    echo \"LINE: $line\n\";
    echo \"\n\";
    echo \"QUERY ERROR:\n\";
    echo mysql_error() . \"\n\";
    echo \"\n\";
    echo \"QUERY:\n\";
    echo $query . \"\n\";
    echo ($silent == TRUE) ? \"-->\n\" : \"</textarea>\n\";
    echo \"\n\n\";
    
}



mysql_connect (localhost, login, \"pass\");
mysql_select_db (remediation);

if ($apn == \"\") {
    $apn = '%';
}


$query = \"SELECT * FROM excluded WHERE apn='$apn%'\";
$result = mysql_query ($query);
if (!$result) {
    Query_Error(__FILE__, __LINE__, $query);
}

if ($result && mysql_num_rows($result) > 0) {
    print \"Sorry, that number has been excluded.\";
}
else {
    
    $query = \"SELECT bills02.co, bills02.apn, bills02.ann, bills02.fee, bills02.contam\"
            .\" FROM bills02\"
            .\" WHERE bills02.apn='$apn%'\";
    $result = mysql_query ($query);
    if (!$result) {
        Query_Error(__FILE__, __LINE__, $query);
    }
    
    if ($result && mysql_num_rows($result) == 0) {
        print \"Sorry, no records were found!\";
    }
    
    while ($result && $row=mysql_fetch_array($result)) {
        print (\"<h5>APN (Parcel Number)</h5>\");
        print $row[\"apn\"];
        print (\"<p>Annualized Water Bill</p>\");
        print $row[\"ann\"];
        print (\"<h2>Remediation Fee</h2>\");
        print $row[\"fee\"];
        print (\"<p>Contaminated?</p>\");
        print $row[\"contam\"];
        print (\" \");
    }
    
}
?>

Mark Hensler
If there is no answer on Google, then there is no question.