Jump to content


Photo

Queries on Arrays


  • Please log in to reply
2 replies to this topic

#1 onlyican

onlyican
  • Members
  • PipPipPip
  • Advanced Member
  • 921 posts
  • LocationHants - UK

Posted 03 September 2006 - 12:46 PM

Hey

I am building a Advanced Search form for my site.
One of the opetions is to get them to search for members from x country
Now all the countries are stored in the database to show on the registration form
Therefor
Countries should not have mulitple variations
(Example UK, United Kingdom, England....)

But the ORigininal Database holds ALL countries. And I dont have members from every country (yet)
So What I want to do is run a query against the members, to get there country,
Simple
SELECT * FROM members GROUP BY country

But also on this search form, I want to do simular things, for other fields

I do not wants to have 6 queries running
So I was thinking of running one query, Selecting everything

Then store the result in an array

Is there a way, I can GROUP before putting in the array
or run a query against the array to select unique entries

I was thinking of doing something like

<?php
$query = "SELECT * FROM members";
$result = mysql_query($query);
$countries = array();
$local = array();

while($row = mysql_fetch_assoc($result)){
if($row["country"] != $country_check){
$countries[] = $row["country"];
}
$country_check = $row["country"];

if($row["local"] != $local_check){
$local[] = $row["local"];
}
$local_check = $row["local"];
}
?>

Now I know that would work
But is there a better way to do it

NOTE: There are more than 2 things I will run, this is just a mini example


EDIT//:
Sorry the above wont work cos they are not in order
So
Spain
USA
UK
USA
will show like that
Tell me the problem, I will try tell you the solution

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 03 September 2006 - 02:09 PM

If all you want is list of countries with members

SELECT DISTINCT country FROM members ORDER BY country
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 onlyican

onlyican
  • Members
  • PipPipPip
  • Advanced Member
  • 921 posts
  • LocationHants - UK

Posted 03 September 2006 - 03:37 PM

But the thing is I want to select other things as well
Instead of running 10 queries

The way I am currently doing it is

<?php
$query1 = "SELECT * FROM members WHERE pro_status = 'a'";
$result1 = mysql_query($query1);

while($row = mysql_fetch_assoc($result1)){
if(!in_array($row1["country"], $countries)){
$countries[] = $row1["country"];
}

if(!in_array($row1["local"], $locals)){
$locals[] = $row1["local"];
}

}

sort($countries)
sort($locals);

//The form which will display these

?>

Is that the best way, or can anyone recommend a better way


Tell me the problem, I will try tell you the solution




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users