MySQL to return one field with non existing values

[CamaroLT]

I'm trying to wrap my mind around how I can populate a combo box on an HTML form. I've got one table thats got an unsigned TINYINT field, and what I want to do is populate the Combo box with values that are NOT in the database. Short of hitting the database more than a couple hundred times, I'm perplexed as to how I can hit the database once to get a result set.

Anyone have any ideas?

-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=
NEVER send the boss to do a techs job

 

[SimonEireconsulting]

I take it you mean you have a lookup table and another table that hold records with assigned values from the lookup table and you want to see a list of all the values that have not been assigned.

To do this, use a SQL statement linking the lookup table to the other table using left outer join and only show values that have NULL in the joined table.

eg
table tLookup holds all values, tData holds all records with assigned values from tLookup. Use the SQL Statement:

SELECT LookupID, LookupValue
FROM tLookup LEFT OUTER JOIN tData ON tLookup.LookupID = tData.LookupID
WHERE tData.LookupID IS NULL

 

[CamaroLT]

I did end up coming up against having to examine another table, yes, but what I did was create a single field table, populated it with numbers from 1 to 254, then used the following:

select distinct OneToTwoFiftyFour.ipid from OneToTwoFiftyFour where OneToTwoFiftyFour.ipid not in (select tblIPList.ipid from tblIPList) and OneToTwoFiftyFour.ipid <> {}

OneToTwoFiftyFour is the table with all the numbers I entered, tblIPList is what holds the active data. Where {} is the currently selected IP I want (Scripted into place). IPID should be read as IP Identifier.

What I'm basically doing is writing an ASP/SQL page that will give me a better visual idea of where all my equipment lives on the network, including IP printers, additional routers, computers, and virtual machines. It was getting difficult to remember whats where and keep things organized. I'm the type that organizes a class C network so that servers get one end of the spectrum, routers/network appliances on the other, DHCP gets the center of the spectrum. I've tried to use Google Docs and its just become so cumbersome to see things all at once, and have it maintained easily. So, I'm writing a small DB app. For this particular section, I needed the ability so when I want to move a particular host to another IP (In a 255.255.255.0 netmask) I needed to show all the available IPs that are currently not assigned, instead of allowing myself to duplicate an already used IP.


-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=
NEVER send the boss to do a techs job

 

[SimonEireconsulting]

in this case you do not need a second table (OneToTwoFiftyFour).

Simply select tblIPList.ipid and return to an array and then loop from 1 to 254. If the value is in the array then don't add it to the drop down list. If it is not in the array, the add it.

SQL Statement:

SELECT ipid FROM tblIPList GROUP BY ipid ORDER BY ipid

return this to a recordset (either using SET oRS = oCon.Execute("...") or by calling a stored procedure

Read the recordset into an array:
aIPs = oRS.GetRows()

loop from 1 to 254, testing if the value is in the array

Dim lArrayIndex, bUsed
lArrayIndex = 0
FOR lLoop = 1 TO 254
bUsed = FALSE
IF lArrayIndex <= UBOUND(aIPs, 2) THEN
DO UNTIL aIPs(lArrayIndex) > lLoop
IF aIPs(lArrayIndex) = lLoop THEN
bUsed = TRUE
EXIT DO
END IF
lArrayIndex = lArrayIndex + 1
IF lArrayIndex > UBOUND(aIPs, 2) THEN
EXIT DO
END IF
LOOP
END IF
IF NOT bUsed THEN
Response.Write("<option value=""" & lLoop & """>" & lLoop & "</option>" & vbCR)
END IF
NEXT

-------- code not tested --------

 

[CamaroLT]

I do appreciate the code, but I think I'm going to use the additional table. Two reasons;

First, if I decide that I want to incorporate a new set of rules, such as, I don't want to list the IPs that I've specifically reserved for DHCP, I can come up with new logic and replace the table with a view. The view could work out what range to list and not list.

Second, at least for me, the single SQL statement gives better indication as to what the code is actually going to be doing. I would think that its less expensive for the DB to pull the appropriate records than have VBScript deal with it.

I know that I might stepping all over the place when it comes to where the business logic is occurring.

-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=
NEVER send the boss to do a techs job

 

[SimonEireconsulting]

horses for courses....

Using the table is a good idea if you want to exclude other IPs that are not currently assigned.

However, I disagree with your second point. It is always best to read from / write to the database using a stored procedure, for security reasons.

You could name the stored procedure sGetAvailableIPs and then when you look at your code you will know exactly what it does.