MySQL arrays

[puritystandsout]

Dear anyone who can help,

I am doing SELECT queries on a mysql db to retrieve three different columns as arrays.

I then need to join these arrays, remove any duplicate entries and sort them alphabetically.

Theoretically, this is possible.  However for me, it all falls down because I don't get all the data I would expect our of the mysql db.

Has anyone had this problem?  Could anyone help?

Many Thanks!

Regards and Jesus Christ's blessings,

Chris Cundill

[GingerRobot]

So you want all the data from one field in one array, all the data from another in another array?

[code]
<?php
$sql = mysql_query("SELECT * FROM `yourtable`");
while($row = mysql_fetch_assoc($sql))
{
$field1= $row[field1];
$field2= $row[field2];
$field3= $row[field3];
$array1[] = $field1;
$array2[] = $field2;
$array3[] = $field3;
}
$array1 = array_unique($array1);
$array2 = array_unique($array2);
$array3 = array_unique($array3);
?>
[/code]

That should do it.

[puritystandsout]

Thanks for the reply...

Let me make it a bit clearer.

I have data in three columns in a mysql table and I need to put them all into one array. Then once they are in the one array I need to run  a array_unique on them to filter out any duplicates and then sort them alphabetically...

Could you help.

[OOP]

hmmm...i don't know if I got your question right!!!

you can specify the name of the required three columns in your select statement and then fetch the data. The three columns will be in one array.

let us say
[code]$query = "select single c1,c2,c3 from `mytable`";
$result = mysql_query($query);
if($result){
$data = mysql_fetch_array($result);
}[/code]

now the array $data will have the three columns like this
$data['c1'],$data['c2'], and $data['c3']

[zq29]

[code]<?php
$array = array();
$result = mysql_query("SELECT `col1`,`col2`,`col3` FROM `table`") or die(mysql_error());
while($row = mysql_fetch_assoc($result)) {
    $array[] = $row['col1'];
    $array[] = $row['col2'];
    $array[] = $row['col3'];
}
$array = array_unique($array);
sort($array);
?>[/code]

[kenrbnsn]

If you take the original proposed solution and use one array instead of three, that should give you your solution. If you had posted the code that was giving you problems, we could help you find your bug. But, right now we're posting with no knowledge of your database design. Please post your code.

Ken

[puritystandsout]

Okay point taken.

Here is the code I've been trying...

[code]
$query = mysql_query("SELECT DISTINCT skill1 FROM members ") or die (mysql_error());
$skill1_array = mysql_fetch_assoc($query);


$query = mysql_query("SELECT DISTINCT skill2 FROM members ") or die (mysql_error());
$skill2_array = mysql_fetch_assoc($query);


$query = mysql_query("SELECT DISTINCT skill3 FROM members ") or die (mysql_error());
$skill3_array = mysql_fetch_assoc($query);



$skill_arrays_merged = array_merge($skill1_array, $skill2_array, $skill3_array);
$skill_array = array_unique($skill_arrays_merged);
sort($skill_array);


foreach($skill_array as $skill) {
if ($skill !== "") {
echo "<option value" . $skill . ">" . $skill . "</option>";
}
}
[/code]

Basically what does wrong is that I only get three items in the $skill_array when there should be five.

Hope you can help.

Thanks everyone for your contributions...

[zq29]

Correct me if I have the wrong end of the stick here, but you're only querying for three 'items' instead of five. skill1, skill2 and skill3 from the members table...

[puritystandsout]

I think you are all assuming that I retrieving just one record/row from the database.  I am not.  There are multiple rows and there could be duplicate entries in any of the three columns I am searching on.

I need to get all data from these three columns for all the records/rows, put them into an array and filter out duplicates.

Thanks for your continued help.

[puritystandsout]

Could anybody help?  ???

[move]please[/move]

[kenrbnsn]

Reply #4 is essentially the solution to your problem.

Here it is again, this time with your columns:
[code]<?php
$array = array();
$query = "SELECT skill1,skill2,skill3 FROM members";
$result = mysql_query($q) or die("Problem with the query: $q<br>" . mysql_error());
while($row = mysql_fetch_assoc($result)) {
    $array[] = $row['skill1'];
    $array[] = $row['skill2'];
    $array[] = $row['skill3'];
}
$array = array_unique($array);
sort($array);
echo '<pre>' . print_r($array,true) . '</pre>';
?>[/code]

Ken

[puritystandsout]

Hey, you're right! It worked!  Thank you one and all - you've really helped me get past a frustrating moment.