Page 1 of 1
drop-down driven by myslq data
Posted: Wed May 19, 2004 1:06 am
by jameso
I have a <select> box with options that I've pulled from my MySQL database, but I want to query it so it DOESN'T contain what's in a certain table.
In other words I have 2 tables with user ID values that I want to cross-check against each other, and if one is not in the other I want to make an option tag out of it.
Any ideas? I can supply what code I have if needed, but it's more of the logic that's totally confusing me.
Thanks people!
Posted: Wed May 19, 2004 2:52 am
by dave420
I did something similar to this just the other week. The problem with generating a list from a mysql query, is a fair bit of data can pass between the server and the client. As this has to be accomplished without updating the page, a method has to be devised to allow PHP to transfer the data "in the background", so to speak.
I did this by inserting a <script> tag in my document, with an ID. I had an onkeydown event on a text box which, when containing over X characters, would update the "src" of the script tag to a PHP file that spits out special javascript functions to update a <div> which is positioned below the textbox, providing the look-n-feel(tm) of, say, Autocomplete on IE and in Windows. The <div> contains a table, which holds each of the records returned by the MySQL query. The table is constructed using JavaScript, which is quite trivial. You can make each row clickable, to insert a value into the textbox when clicked.
I made it to search a contact database. It works rather well, even considering the amount of data we were searching on.
What's exactly the problem?
Posted: Wed May 19, 2004 2:59 am
by Lord Sauron
I don't understand what the problem exactly is, but perhaps you mean something like this:
Code: Select all
<?php
$result = mysql_query("SELECT t1.userName, t1.userID FROM Table1 t1, Table2 t2 WHERE t1.userID = t2.userID;", "Cannot compare userIDs", mySQLConnection);
$row = mysql_fetch_row($result);
IF (empty($row) {
print("No results found\n");
}
ELSE {
print("<SELECT CLASS="selectBox" NAME='compareUsers[]' SIZE="3">\n");
WHILE (!empty($row)) {
print("<OPTION VALUE='$userID' SELECTED>".$userName."</OPTION>\n");
$row = mysql_fetch_row($result);
} // Close while-loop
}
print("</SELECT>\n");
?>
Re: What's exactly the problem?
Posted: Tue Jun 08, 2004 12:41 am
by mitchikoy
Lord Sauron wrote:I don't understand what the problem exactly is, but perhaps you mean something like this:
Code: Select all
<?php
$result = mysql_query("SELECT t1.userName, t1.userID FROM Table1 t1, Table2 t2 WHERE t1.userID = t2.userID;", "Cannot compare userIDs", mySQLConnection);
$row = mysql_fetch_row($result);
IF (empty($row) {
print("No results found\n");
}
ELSE {
print("<SELECT CLASS="selectBox" NAME='compareUsers[]' SIZE="3">\n");
WHILE (!empty($row)) {
print("<OPTION VALUE='$userID' SELECTED>".$userName."</OPTION>\n");
$row = mysql_fetch_row($result);
} // Close while-loop
}
print("</SELECT>\n");
?>
Uhm, I suppose the opposite effect of your query is what he means.
He is looking for the results that is NOT in the other table
use this query instead
Code: Select all
select tbl1.* from table1 left join table2 on table1.tblID = table2.tblID where table2.tblID = NULL.
This will query all those in table1 that is NOT in table2