cannot get 12 fields back, only 3

[bufhal]

Working on a web page with three dropdowns: Agency, Date and City. When a user clicks on one of the choices from the dropdown, a record(s) with all 12 fields are supposed to be returned in a pop up. I only have Agency City and Date being returned. The twelve fields I want returned are listed in the results.php file for output below. Both files are below--can someone please take a look and show me how to remedy this?
Thank you in advance..Bufhal

index.php

<?php
// Connection to the db server and select active db
$SQLlink = @mysql_connect("t.com", "wny", "947"); //creates a connection

if (!$SQLlink)
Die("Couldn't connect to the db server."); // display error message on error

if (!mysql_select_db("org", $SQLlink))
Die("Couldn't access database."); // display error message on error

// perform query
$data = mysql_query("SELECT date, agency, city FROM agencies");

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 ($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."
";

?>

and results.php

<?PHP
$data = mysql_query("select * FROM agencies");


if (!$data)
Die(mysql_error()); // display MySQL error message on error

$output = "<table border=1>\n"; // I can format table here

if (mysql_num_rows($data) != 0) { // in the case of results
while($agency = mysql_fetch_array($data)) {
$output .= "<tr>";
$output .= "<td>".$agency["date"]."</td>";
$output .= "<td>".$agency["agency"]."</td>";
$output .= "<td>".$agency["city"]."</td>";
$output .= "<td>".$agency["day"]."</td>";
$output .= "<td>".$agency["time"]."</td>";
$output .= "<td>".$agency["location"]."</td>";
$output .= "<td>".$agency["building_room"]."</td>";
$output .= "<td>".$agency["street"]."</td>";
$output .= "<td>".$agency["zip"]."</td>";
$output .= "<td>".$agency["phone"]."</td>";
$output .= "<td>".$agency["contact"]."</td></tr>\n";
echo($output);
$output = ""; // clear buffer
}
}else{ // in case of NO results
echo("<tr><td colspan=11>No results</td></tr>");
}
echo "</table>"; // end of table

?>
//
Close window (javascript:window.close();)

[bMind]

Not too clear about what you are trying to do, I am assuming you are missing some code in result.php?

Are you sure your session is passed on to the result page correctly?

[bufhal]

I know what I want to do, not sure how to do it. When I click on the dropdown on the webpage now and click an option (e.g. city name Buffalo) it returns only three fields in a record(city, date, agency) that has 12 fields. I need ALL fields to be returned in the pop up. The 12 are listed in the results.php file for output.
Not sure where to change the code to return all fields--I thought select * from agencies would do it but it is not.
Thanks

[bMind]

One of the things you can do is to select one field and see if it returns any results..for example




SELECT zip FROM agencies



If there is no result return, it's telling me that there is no data in this field.

If there is result, you may try to type out all the fields' name instead of using *

That's how i usually trouble shoot the problem.

if possible, please show the whole code for result.php and we can take a look.

[bufhal]

I tried to select all the fields and that did not work-still returned the three. I have 4 records of 12 fields of data in the MySQL database. I will keep playing with it. Here is another version for results.php
Thank for you help..

<?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(".com", "wny", "immunize"); //creates a connection

if (!$SQLlink)
Die("Couldn't connect to the db server."); // display error message on error

if (!mysql_select_db("immunizewny", $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 date, agency, city 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=3>No results found</td></tr>";

} else {
forEach ($agencies as $agency) { // display row for each result (eg. you can have more agencies in one town)
$output .= "<tr>";
$output .= "<td>".$agency["date"]."</td>";
$output .= "<td>".$agency["agency"]."</td>";
$output .= "<td>".$agency["city"]."</td>";



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

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

echo $output;

?>

Close window (javascript:window.close();)
</body>
</html>

[bufhal]

Regarding your suggestion of running a query on one field-I am using phpmyadmin and can run queries directly and receive the results (like select zip from agencies).

[HardCoded]

Uhhm...in the second example you are only trying to print 3 fields...

[bMind]

Hi,
I quickly looked at your result page and it does not make sense to me. I will ignore the top part and below is my UNTESTED version that you may try...




$data = @mysql_query("SELECT * FROM agencies WHERE action = $value");
if (!data){echo("

Error: " . mysql_error() ."</p>"); exit();}
while ( $agency = mysql_fetch_array($data) )
{
$num_rows = mysql_num_rows($agency);//count records
if ($num_rows == 0)
{
echo "No result here";
}
else
{
$output = "<table border=1>\n";
$output .= "<tr>";
$output .= "<td>".$agency["date"]."</td>";
$output .= "<td>".$agency["agency"]."</td>";
$output .= "<td>".$agency["city"]."</td>";
$output .= "<td>".$agency["day"]."</td>";
$output .= "<td>".$agency["time"]."</td>";
$output .= "<td>".$agency["location"]."</td>";
$output .= "<td>".$agency["building_room"]."</td>";
$output .= "<td>".$agency["street"]."</td>";
$output .= "<td>".$agency["zip"]."</td>";
$output .= "<td>".$agency["phone"]."</td>";
$output .= "<td>".$agency["contact"]."</td></tr>\n";
$output .= "</table>";

echo "$output";
}

}


I am trying to understand what are you trying to do. The drop down list you have would allow you to display 3 different set of results..

if action is date, the database would display...
if action is agency, the database would display..
if action is city, the database would display...

As far as I know, they all would display the same result because you are pulling the same information from the database. For example, the database query above says..

SELECT * FROM agencies WHERE action = $value

Which means

SELECT EVERYTHING FROM AGENCIES WHERE ACTION EQUAL DATE

SELECT EVERYTHING FROM AGENCIES WHERE ACTION EQUAL AGENCY

SELECT EVERYTHING FROM AGENCIES WHERE ACTION EQUAL CITY

Give it a try and let me know how it works out.

thanks

[bufhal]

Tested quickly and got these errors:

Notice: Use of undefined constant data - assumed 'data' in E:\www\immunizewny_org\results.php on line 19

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in E:\www\immunizewny_org\results.php on line 20

[bMind]

Ok, my bad..

I was going to fast and didn't realize i missed '$' sign...

here you go, instead of..


if (!data){echo("

Error: " . mysql_error() ."</p>"); exit();}

try...


if (!$data) error_message (sql_error());

The reason why we added $ there is because data is a variable and not constant, that's why you see the error message - Use of undefined constant data .

Hope that helps..