LinuxQuestions.org

LinuxQuestions.org (/questions/)
-   Programming (http://www.linuxquestions.org/questions/programming-9/)
-   -   I'm trying to get PHP to filter results from a MySQL query!!! (http://www.linuxquestions.org/questions/programming-9/im-trying-to-get-php-to-filter-results-from-a-mysql-query-507901/)

Tom "Techno" Earl 12-06-2006 10:28 AM

I'm trying to get PHP to filter results from a MySQL query!!!
 
Hey everybody!

I am trying to build a MySQL database of Fans for a local band, so that I can mail them out a newsletter. I am trying to get it so that I can have a dropdown menu with all the locations loaded into it, then it will open a table with all the people in that area.

I have managed to get it so that it does that so far, however, it is showing an option for EVERY result, rather than filtering out the ones that are the same as each other, is there any way that I can get it to see if there is more than 1 result and then just display that as one option??

If anyone knows what I mean, I would be very pleased if they could tell me the answer!

Thanks

Here is the code I am using at the mo:

This is the main page, which contains the form
PHP Code:

<?php
 
// include the headers for jakeandelwood.co.uk
 
include 'jakeandelwood.html';
?>
<?php
 
// This is a small program designed to query a MySQL database
 // to draw up email addresses based on location, it uses a simple
 // way of querying the database (included in email.php), this 
 // script should run off any database, as long as you have a little
 // knowledge of PHP and MySQL and are able to change the appropriate 
 // fields in the script and on your MySQL database
?> 
<?php
 
// Check to see if the post variable is present, if not load the form
 
if ($_POST) {
 include 
'email.php';
} else { 
?>
<h1>Database look up</h1>
<b>Please use the drop down box below to find email addresses that belong to people in different areas!</b><br><br>
 <center>
 <form action="index.php" method="post">
  <select name="county">
<?php
}
?>
<?php
   
// Open a mysql connection
   
include 'includes/mysql.php';
   
$select1 "SELECT town_city FROM locations";
   
$result1 mysqli_query($link$select1);
// loop through to bring up all the database locations
 
while ($row mysqli_fetch_array($result1)) {
    
$town_city $row['town_city'];
    echo 
"<option>".$town_city."</option>";
}
?>

This is the table page with the results:
PHP Code:

<?php
 
// include the details of the mysql database
 
include 'includes/mysql.php';
 
$input $_POST['county'];
?>
<!--Set up a table-->
<table border="1" cellpadding="5">
 <tr>
  <th>Database ID</th>
  <th>Name</th>
  <th>E-mail address</th>
  <th>Town/City</th>
  <th>County</th>
  <th>Status</th>
 </tr>
<?php
// build and execute the query
$select "SELECT id, name, email, town_city, county, status FROM locations WHERE county=\"$input\"";
$result mysqli_query($link$select);

//loop through the results
while ($row mysqli_fetch_array($result)) {
//get each element and put it in a variable
$id $row['id'];
$name $row['name'];
$email $row['email'];
$town_city $row['town_city'];
$county $row['county'];
$status $row['status'];

//Print out the results
echo <<<END
 <TR>
  <TD>$id</TD>
  <TD>$name</TD>
  <TD><A HREF="mailto:$email">$email</A></TD>
  <TD>$town_city</TD>
  <TD>$county</TD>
  <TD>$status</TD>
 </TR>
END;
}
?>
<!--End the Table-->
</TABLE> 
<?php
 mysqli_close
($link); 
?>

This is the connection data:
PHP Code:

<?php
// Open connection with the MySQL database
$link mysqli_connect('localhost','USERNAME','PASSWORD','DATABASE');
?>

Thanks

vargadanis 12-06-2006 10:54 AM

ehh buddy... I am afraid it is not 100% clear for me.

So there is a table like that:

Code:

id | name | location
----------------------
 1 | Jack | Hamburg
 2 | Jess | New York
 3 | Steve| Hamburg

And you want to select to location but each one only once? Eg: Hamburg to be displayed once.. Right?
You can do that by adding the DISTINCT to the selection:
Code:

SELECT DISTINCT(location) FROM table
After that you should make a selection like that:
PHP Code:

$res SELECT FROM table WHERE `location` = '".$_GET['form_location']."'

I hope it will help... Ask if you need further help.
And chk this site out:
http://dev.mysql.com/doc/refman/5.0/en/select.html

Wells 12-06-2006 11:01 AM

Quote:

I have managed to get it so that it does that so far, however, it is showing an option for EVERY result, rather than filtering out the ones that are the same as each other, is there any way that I can get it to see if there is more than 1 result and then just display that as one option??
So are you saying that your database has multiple entries of the same people, and you just want to show them once if they appear more than once in the query?

You probably need to use either DISTINCT or a GROUP BY to get what you are looking for.

Tom "Techno" Earl 12-07-2006 03:08 AM

Hey,

Thanks for the replies,

I'm sorry about the unclearness of my post! I didn't quite know how to put it into words!

But, thanks, you've both got what I meant, and I have tried vargadanis' post, and that works perfectly, had some teething problems at first, but then I realised I'd missed a ' out of the code somewhere a bit further up :rolleyes:

Anywayz, got it working now, it's brilliant, I'll post where it is when it's finished, so others can see what I meant.

Thanks

Tom "Techno" Earl

emakundi 07-18-2011 06:49 AM

Thanx
 
Thanx man ive been looking for this for days now.
had to register just 2 reply dis. lol


All times are GMT -5. The time now is 01:36 PM.