drop-down driven by myslq data

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
jameso
Forum Newbie
Posts: 7
Joined: Wed Mar 17, 2004 3:31 pm

drop-down driven by myslq data

Post 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!
dave420
Forum Contributor
Posts: 106
Joined: Tue Feb 17, 2004 8:03 am

Post 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.
User avatar
Lord Sauron
Forum Commoner
Posts: 85
Joined: Tue Apr 20, 2004 5:53 am
Location: Tilburg, NL

What's exactly the problem?

Post 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");

?>
User avatar
mitchikoy
Forum Newbie
Posts: 3
Joined: Tue Aug 12, 2003 8:35 pm
Location: Phil
Contact:

Re: What's exactly the problem?

Post 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
Post Reply