Sortin multiple arrays

Hi.

I’m using a foreach loop to display results from two MySQL tables that are being combined into one list. I’m using an array for each column in one of the tables, and I want to sort the combined list by one value. Obviously, if I sort only the array that holds the value I want to sort by, the other arrays will be out of sequence. I’m looking for a way to either sort all of the arrays by one.

Thanks.

What kind of data, what is the query itself?

You can write a join against the tables. Then, using the order by clause and get the results directly from the database in the order you are wanting.

Thanks for the response. I managed to figure it out. I used array_multisort.

You should still consider moving this to the query itself. Databases are very optimized for this type of work, and have caching for query results, lookups, etc.

I agree with JimL 100% … That is exactly what the MySQL query system was designed for!

Okay, this is the code I’m using.
[php]if($web==‘timeline’){
$sql=“SELECT * FROM people ORDER BY id”;
$res=mysql_query($sql);
$am=mysql_num_rows($res);
if($am>0){
while ($entry = mysql_fetch_object ($res)) {
if($entry->birth!=null){
$ev++;
$id=$entry->id;
$evnt[$ev]=‘Birth of ‘.$entry->name;
$nam[$id]=$entry->name;
//calculate age based on father
$fid=$entry->father;
$fage[$id]=$entry->birth;
if($fid!=null){
$brth[$id]=$brth[$fid]+$fage[$id];
$e_whn[$ev]=$nam[$fid].’ was ‘.$fage[$id].’ years old’;
}else{
$fth=0;
$brth[$id]=$fage[$id];
$e_whn[$ev]=’’;
}

$e_date[$ev]=$brth[$id];
$e_dref[$ev]=$entry->bref;
if($entry->bbev==true){$e_type[$ev]=0;}else{$e_type[$ev]=4;}

}
if($entry->death!=null){
$ev++;
$evnt[$ev]='Death of '.$entry->name;
$e_whn[$ev]='Aged '.$entry->death;
$e_date[$ev]=$entry->death+$brth[$id];
$e_dref[$ev]=$entry->dref;
if($entry->dbev==true){$e_type[$ev]=1;}else{$e_type[$ev]=5;}
}
}
}

$sql=“SELECT * FROM events ORDER BY id”;
$res=mysql_query($sql);
$am=mysql_num_rows($res);
if($am>0){
while ($entry = mysql_fetch_object ($res)) {
$ev++;
$evnt[$ev]=$entry->event;
$rel=$entry->relative;
if($rel!=null){
$e_date[$ev]=$entry->date+$brth[$rel];
$e_whn[$ev]=$nam[$rel].’ was ‘.$entry->date.’ years old’;
}else{
$e_date[$ev]=$entry->date;
$e_whn[$ev]=’’;
}
$e_dref[$ev]=$entry->dref;
$e_type[$ev]=2;
if($end!=null){
$ev++;
$evnt[$ev]=‘End of ‘.$entry->event;
$e_date[$ev]=$entry->end;
$e_dref[$ev]=$entry->eref;
$e_type[$ev]=3;
$e_whn[$ev]=’’;
}
}
}
array_multisort($e_date,$e_type,$e_whn,$evnt,$e_dref);[/php]

First of all, don’t use mySQL_ functions. mysqli_ at a minimum PDO if you are feeling more technological.

What are the relationships between people and events?

Why not, and how do you use mysqli?

What are the relationships between people and events?

They’re both being plotted on a timeline.

Not sure what timeline you are using, but, MySQL has been deprecated and is not in the latest version of
PHP. If it is your own personal server you are using, then it does not matter really. But, hosting companies
will drop support for MySQL in the near future. Some already have dropped it.

Moving up to MySQLi is very simple if you use “procedural MySQL” now. (Which you do.)

You need to change the connection string to place a handle to it in a variable. Then, change all the function
calls to MySQL to the improved MySQLi versions. Most just need the handle placed in the function. So, like
$res=mysql_query($sql); would become $res=mysqli_query($conn, $sql);

Not complicated… After changing over to MySQLi, you can learn about prepared statements which is very
handy as it protects your database from a lot of hacker tricks… Hope that helps…

I’ll look into it. Thanks.

Regarding sorting the arrays using MySQL (or MySQLi), how would I do that?

That depends on their relationship. “on a timeline” doesn’t tell me anything. How are they related? Do they share something in common?

The timeline shows events in people’s lives such as their birth, death, starting school, etc. In the table of people, it has the person’s name, the id number of their father, the date of their birth, and the date of their birth. In the events table, it has other events in their life.

So, if there’s an entry on the people table like this, ‘id=2, name=“Adam”, father=1, birth=25, bref=“1901 Census”, death=75, dref=“Death Certificate”’, it would indicate that the father was the person with the id 1 and they were 25 when Adam was born, the information was found in the 1901 Census, and Adam died when he was 75 and that information was taken from his death certificate.

If there was an entry in the events table like this, ‘id=1, event=“Graduated from Harvard”, date=24, relative=2, dref=“Photograph”’, it would indicate that Adam (relative refers to the id of the person the event relates to) graduated from Harvard when he was 24, and this information is based on a photograph of the graduation that has something like ‘Adam aged 24’ written on the back.

The code calculates the date based on the ages given and plots them on the timeline of the family history.

I’m trying to describe how it works as best I can. Does anyone have any suggestions? The method I’m using works, but if there’s a way I can do it in the MySQL or MySQLi query, I’m willing to try it.

Thanks.

Well, it appears to be just fine as it is. You end up with the data you need.
You might be able to do it in SQL, but, I do not think it would speed it up a lot.
LOL, if it works, don’t fix it…

BUT, as we have mentioned, MySQL is deprecated and is not even in the latest version of PHP.
Your code will break if the server is updated to the latest PHP. The change to MySQLi is simple looking at
the code you posted. There are only a few changes needed. I listed them earlier in this thread. Very easy
to do. The “Improved” version doesn’t seem much faster from what I see. But, the old version is gone soon!

Thanks for the reply. I’m looking into MySQLi. I have most of my MySQL queries in the same document, so hopefully it won’t take too long to make changes.

Thanks.

Sponsor our Newsletter | Privacy Policy | Terms of Service