Jump to content

Archived

This topic is now archived and is closed to further replies.

puritystandsout

MySQL arrays

Recommended Posts

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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']



Share this post


Link to post
Share on other sites
[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]

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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...

Share this post


Link to post
Share on other sites
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...

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.