cannot get results from all dropdowns

bufhal

Hello;
I am rather stuck on this and I hope someone can take a look and see what is wrong.
I have two files 1.) index.php which contains 3 dropdown menus Agency, City and Date-and 2.) results.php which prints results to screen in a popup. So far, I am getting only one result for City. Can someone take a look and see why I cannot get results for the other two dropdowns?
I appreciate your feedback:p
Here is index.php:
$data = mysql_query("SELECT date, agency, city FROM agencies ORDER BY date, agency, city ASC;
");
if (!$data)
Die(mysql_error()); // display MySQL error message on error
$agencies = Array();

while($row = mysql_fetch_array($data)) { // assign results into arrays

$dates[] = $row["date"];

$agencies[] = $row["agency"];
$cities[] = $row["city"];
}

$dates = Array_Unique($dates); // remove duplicate values
$agencies = Array_Unique($agencies);
$cities = Array_Unique($cities);

Sort($dates); // sort arrays
Sort($agencies);
Sort($cities);

$date_out = "<select name='date' onchange=\"window.open('results.php?action=date&value='+this.value, 'agencyWin', 'location=yes,left=20,top=20');\">";
$agency_out = "<select name='agency' onchange=\"window.open('results.php?action=agency&value='+this.value, 'agencyWin', 'location=yes,left=20,top=20');\">";
$city_out = "<select name='city' onchange=\"window.open('results.php?action=city&value='+this.value, 'agencyWin', 'location=yes,left=20,top=20');\">";

$date_out .= "<option>-- select date ---</option>";
$agency_out .= "<option>-- select agency ---</option>";
$city_out .= "<option>-- select city ---</option>";
//forEach ($dates as $value)
//$date_out .= "<option value='$value'>$value</option>";

forEach ($dates as $value)
$date_out .= "<option value='$value'>".date('m/d/Y',strtotime($value))."</option>";

forEach ($agencies as $value)
$agency_out .= "<option value='$value'>$value</option>";

forEach ($cities as $value)
$city_out .= "<option value='$value'>$value</option>";

$date_out .= "</select>\n";
$agency_out .= "</select>\n";
$city_out .= "</select>\n";

echo $date_out."";
echo $agency_out."";
echo $city_out."<BR>";

Here is results.php
<?php
if (!IsSet($action) || !IsSet($value)) // check if both vars are set
Die("Both vars must be set");
if (Trim($value) == "") // check if value is non-blank
Die("Value can't be left blank");
if ($action != "date" && $action != "agency" && $action != "city")
Die("Unknown action requested");
// Connection to the db server and select active db
$SQLlink = @mysql_connect("sql13.com", "bufhal2", "password"); //creates a connection
if (!$SQLlink)
Die("Couldn't connect to the db server."); // display error message on error
if (!mysql_select_db("bufhal2_wnyaic", $SQLlink))
Die("Couldn't access database."); // display error message on error
// escape data from user
if (ini_get('magic_quotes_gpc')) { // unescaping data if needed
$value = StripSlashes($value);
}
$value = mysql_escape_string($value); // escaping data for MySQL db

$data = mysql_query("SELECT * FROM agencies WHERE $action = '$value'"); // perform a query

if (!$data)
Die(mysql_error()); // display MySQL error message on error
$agencies = Array();
while($row = mysql_fetch_array($data)) {
$agencies[] = $row;
}

$output = "<table border=1>\n";
if (mysql_num_rows($data) == 0) { // in the case of no results found - display alert message
$output .= "<tr><td colspan=4>No results found</td></tr>";

} else {
$output .= "<tr>";
$output .= "<td align=\"center\"><b>Date</b></td>";
$output .= "<td align=\"center\"><b>Agency</b></td>";
$output .= "<td align=\"center\"><b>City</b></td>";
$output .= "<td align=\"center\"><b>Time</b></td>";
$output .= "<td align=\"center\"><b>Day</b></td>";
$output .= "<td align=\"center\"><b>Location</b></td>";
$output .= "<td align=\"center\"><b>Building or Room</b></td>";
$output .= "<td align=\"center\"><b>Street</b></td>";
$output .= "<td align=\"center\"><b>Zip</b></td>";
$output .= "<td align=\"center\"><b>Phone</b></td>";
$output .= "<td align=\"center\"><b>Contact</b></td>";
$output .= "</tr>";
forEach ($agencies as $agency) { // display row for each result (eg. you can have more agencies in one town)

$output .= "<tr>"; 
$output .= "<td align=\"center\">".$agency["date"]."</td>"; 
$output .= "<td align=\"center\">".$agency["agency"]."</td>"; 
$output .= "<td align=\"center\">".$agency["city"]."</td>"; 
$output .= "<td align=\"center\">".$agency["time"]."</td>"; 
$output .= "<td align=\"center\">".$agency["day"]."</td>"; 
$output .= "<td align=\"center\">".$agency["location"]."</td>"; 
$output .= "<td align=\"center\">".$agency["building_room"]."</td>"; 
$output .= "<td align=\"center\">".$agency["street"]."</td>"; 
$output .= "<td align=\"center\">".$agency["zip"]."</td>"; 
$output .= "<td align=\"center\">".$agency["phone"]."</td>"; 
$output .= "<td align=\"center\">".$agency["contact"]."</td>";

$output .= "</tr>\n";
}
}

$output .= "</table>\n";

echo $output;

?>

[

nemonoman

Well the code looks pretty clean, and it looks like you've worked on it a bit...

some troubleshooting ideas?

Maybe your labels don't match up?: Try troubleshooting this
$dates[] = $row["date"];
$agencies[] = $row["agency"];
$cities[] = $row["city"];

by changing to this:
$dates[] = $row[0];
$agencies[] = $row[1];
$cities[] =$row[2];
print_r($row); // I added this line for good measure

What is the content of these arrays before your start working them over??

print_r($dates);
print_r($agencies);
print_r($cities);

And for kicks after this:

$dates = Array_Unique($dates); // remove duplicate values
$agencies = Array_Unique($agencies);
$cities = Array_Unique($cities);

Sort($dates); // sort arrays
Sort($agencies);
Sort($cities);

//Let's see the content now you've messed around with it:
print_r($dates);
print_r($agencies);
print_r($cities);

I'll tell you what I think is the case...I think your data doesn't contain the values you believe that it contains...in other words, that your programming is OK, the data is screwy.

The troubleshooting steps above will help to prove what's wrong.

BuzzLY

when naming your selects, use square brackets. PHP will interpret those on the next page as arrays. For example:

$date_out = "<select name='date[]' onchange=\"window.open('results.php?action=date&value='+this.value, 'agencyWin', 'location=yes,left=20,top=20');\">";

Notice the PHP section, and the pretty colors? Please use [php] and [/php] tags around your code to make it more readable.

nemonoman

Maybe I've misread your problem? Are you saying that you can't get your arrays to display in selects? or that the selects won't properly execute code and popup the right window?

If the latter, it's your javascript code, which isn't effective.
Instead this.value it wants to be more like:
this.options[this.options.selectedIndex ].value

bufhal

thanks for the replys...
My dropdown for City is displaying all records. The other two dropdowns-Agency and Date, come back with "no results found" although there is data in the MySQL database.

nemonoman

I'm confused. Let's see if I have it straight. Stop me when I get it wrong.

1, You create three dropdowns. city, date, agency.
2. Those dropdowns populate OK.
3. You include javascript code to open a new window when a dropdown item is selected.
4. The javascript opens a new window OK
5. Built into the javascript open window code is a url: results.php.
6. Built into the javascript open window code is a query string: action=city|agency|date & value=value of the selelcted item
7. The generated URL is OK. It says results.php?action=city&value=Buffalo or some such
8. When action = city, the results.php code functions as expected:
9. An appropriate SQL query is generated by the PHP
10. The database returns expected values
11. When action = agency || date, the appropriate SQL is generated
12. In these other 2 cases, however, data returned is not the expected values: "not rows found".

Is that right?

Can you verify please steps
7 by copying out a URL generated for agency or date

AND
11 by echoing the generated SQL query

Thank you.

bufhal

Your accessment is correct-The pop up always works. Perhaps this will help. Here is the URL of the website:
http://www.immunizewny.org/index5.php
I have been working on this for so long I think I may be missing something.
Perhaps you can see waht is happening between the code and URL.
Thank you😉

bufhal

I know this is sloppy, but here is the test data in the MySQL database so you can see what is being drawn:

0001 Akron Blue Cross 11/18/2003 Tues 2-4 pm Akron Newstead Senior Citizen pharmacy 5691 Cummings Rd 14001 542-2775 Joanne Roach no

0002 Amherst Blue Cross 11/11/2003 Tues 2-4 pm Wegmans 82 pharmacy 675 Alberta Drive 14226 630-8665 Linda Scharf yes

0003 Amherst Blue Cross 12/2/2003 Tues 2-4 pm Wegmans 82 pharmacy 675 Alberta Drive 14226 630-8665 Linda Scharf no

0004 Amherst Blue Cross 10/19/2003 Sun 2-4 pm Boulevard Mall pharmacy 730 Alberta Drive 14226 630-8665 Linda Scharf yes

0005 Amherst Blue Cross 10/20/2003 Mon 2-4 pm Boulevard Mall pharmacy 730 Alberta Drive 14226 630-8665 Linda Scharf yes

0006 Amherst Blue Cross 10/21/2003 Tues 2-4 pm Boulevard Mall pharmacy 730 Alberta Drive 14226 630-8665 Linda Scharf yes

0007 Amherst Blue Cross 10/22/2003 Wed 2-4 pm Boulevard Mall pharmacy 730 Alberta Drive 14226 630-8665 Linda Scharf yes

0008 Blasdale Visiting Nurses Ass 12/4/2004 thurs 5-9 pm Jones Center back room 453 Goundry Street 14120 692-0036 Gary Zakes no

0009 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
0010 Getzville Univera 12/15/2004 thurs 9-12 Rite Aid back of store 123 Gics 14222 8888888 Paul Gemmer yes

BuzzLY

First thing:

Change this:
$data = mysql_query("SELECT * FROM agencies WHERE $action = '$value'");

...to this:
$sql = "SELECT * FROM agencies WHERE $action = '$value'";
$data = mysql_query($sql);

Then, echo out $sql to the screen so you can see what it is. Copy that SQL statement exactly as you see it and paste it into MySQL and see what it returns. If it's not working, check to make sure that capitalization is correct.

nemonoman

I get 'no results found' for all dropdown values -- city agency AND date -- except 1 or 2 blank values that you've got in the date dropdown.

Accordingly, I think we can isolate the problem to the query on the results page.

Follow buzzly's instructions and print out the results of your various queries.

bufhal

I manually inserted a few rows of data via phpmyadmin and the dropdowns seem to be working-except the Agency- Blue Cross.
not to jump subjects-but I think the code may be ok-just a matter of me uploading the data in a better format

When I upload a correctly formatted text file into the database-and phpmyadmin appears to display it in the correct format, the querys do not work..??
However, with the three rows of data I manually inserted, only the Agency, Blue Cross will not come out when queried.
0001 Batavia Agency 1/05/2005 sat 12-5 pm Rite Aid Phar 123 Street 14225 7166952255 Gary Jones
0002 Pittsburg Blue Cross 1/25/2005 Sun 9-10 am Bruns Building back room 12 5544 Green Street 12555 7169995555 Gary Green
0004 Tonawanda Green Agency 2/22/2005 Thurs 12-1 am back of store room 12 78954 Miama Street 14555 6952222 Joe Blow

The only question is why does Blue Cross not come out in the query?
I appreciate all of your help nemonoman and BuzzLY

I can repost in another part of the Forum for this last subject of uploading text files to phpmyadmin