CODEWITHSUNDEEP
phpsql
jflay
jflay

Reputation: 514

Comparing Column Values Between Two Tables

So I created a little thing here:

http://sqlfiddle.com/#!2/d7a76/1

And it does what I want there, but doesn't work when applied to my actual PHP. On load, the HTML page displays all cable numbers in a dropdown from what is in the 'bundle_lanes' SQL database. But recently my client now wants the cable numbers to disappear if they exist already in the database 'cabletypes'. So I need some way to tell SQL to look for exact cable number match and don't display them if they exist in both tables. What am I doing wrong? It works in the sqlfiddle. No error, just blank results when I run the cableNumbers.php and an empty drop down box.

HTML (index.php)

 <body onload="cable_numbers(); company_name();">
<select id="cable_no" onchange="update_section_no();" return false;>
                        <option value="">Cable Number</option>
                    </select>
</body>

JAVASCRIPT

//Set Cable Numbers in DropDown
function cable_numbers() {
$.ajax({
    url: './php/cableNumbers.php',
    dataType: 'json',
    success: function(mydata) {

        var combo = document.getElementById("cable_no");

            while (combo.firstChild) {
                combo.removeChild(combo.firstChild);
            };

            var option = document.createElement("option");

            for (var i = 0; i < mydata.length; i++) {
                option = document.createElement("option");
                option.text = mydata[i]['cable_no'];
                option.value = mydata[i]['cable_no'];
                try {
                    combo.add(option, null); //Standard
                } catch (error) {
                    combo.add(option); // IE only
                }
            };
         section_numbers(); 
    }
});
};

PHP cableNumbers.php (where the SQL query lives)

$sql = "SELECT DISTINCT cable_no FROM bundle_lanes 
        WHERE (cable_no) NOT IN (SELECT cable_no FROM cabletypes)
        ORDER BY cable_no ASC";

$result = mysql_query($sql) or die ('Error'.mysql_error());

// If DB query successful then send data to calling routine.
if (mysql_num_rows($result) >= 1)
{
    while ($row = mysql_fetch_array($result)) {
        $array[] = $row;
    }           
    print json_encode($array);
}

Upvotes: 1

Views: 1143

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

It is possible that you have a NULL value for in the cabletypes table. Try this version:

SELECT DISTINCT cable_no FROM bundle_lanes 
        WHERE cable_no NOT IN (SELECT cable_no FROM cabletypes where cable_no is not null)
        ORDER BY cable_no ASC

Upvotes: 2

Orangepill
Orangepill

Reputation: 24645

You could try something like this

select distinct bundle_lanes.cable_no 
from bundle_lanes left outer join cabletypes on 
     (bundle_lanes.cable_no = cabletypes.cable_no) 
where cabletypes.cable_no is null
order by bundle_lanes.cable_no

Upvotes: 1

Related Questions