Sorting MySQL results with php

ayeronnie

I need to sort results from a number of mysql tables, and not sure, the right way to do it. I will be selecting from 4 tables, and a seperate select for each, each into a seperate array, then combine the arrays.

Now I need to sort the combined array by a given field. One thing though, all 4 tables will have this field, but not all 4 tables have the same number of fields. Will this mess up my sorting?

What would be the best way to sort the array?

I know about union in mysql, but I cant see how it will work for me, I wish it would. For one, the tables do not have the same fields. Plus I will be accessing these tables from other domains, so I will need different db connects. If this is possible, speak up. I've tried with no luck.

Thanks in advance.

ayeronnie

OhLordy

Left join in the sql?
Otherwise I think it's going to need some manual fiddling with the data. What is the structure of the four tables and some example data please
Rob

ayeronnie

The four tables are, with fields:

current:
site_id
name
email
site_name
link
link_back
catagory
description
date_of_page
date_added
site_type

mine:
site_name
link
catagory
description
date_added
site_type

sponsor:
site_name
link
catagory
description
mouseover
date_added
site_type

toplist:
site_name
link
catagory
description
date_added
site-type

Of course this is a link script. The part I am stuck on is, building the pages(I write to a file which will be an include). I need to have a seperate select for each table, then sort those rows by site_name. I need seperate selects because I will be using 3 of these tables from other domains, so I will need seperate db connections. I thought then I would combine the 4 selects into one array. Which would be a multi-dim array right?

Not sure exactly how to sort the final array, and how to sort by site_name. And I am wondering, since the tables are not all the same, the fields not all the same, if this will mess up sorting?
I guess arrays are not my thing yet..🙂

Any thoughts would be greatly appreciated.
Thanks

ronnie

OhLordy

<?
$all_tables=array();
for($i=0;$i<mysql_num_rows($result);$i++) {
  $row=mysql_fetch_array($result);
  $array[$i]['site_name']=$row['site_name'];
  $array[$i]['email']=$row['email'];
  etc...
}

//second table
for($i=0;$i<mysql_num_rows($result);$i++) {
  $row=mysql_fetch_array($result);
  for($a=0;$a<count($array);$a++) {
    if($row['site_name']==$array[$a]['site_name']) {
      $array[$a]['link']=$row['link'];
    }
  }
}
?>

Not sur if I quite understand what you want. Does that look like the sort of thing?
Post Back
Rob

tsinka

Hi,

what about fetching the data already sorted ? is there a table that contains all site names ?
Do all tables contain all site names ? If so you could do something like

SELECT ... FROM .... ORDER BY site_name

That would get all data already sorted by site_name. I think mysql supports odering by varchar or text fields.

If there's one table that contains all possible site names I would take that one as reference to built the array.

You could then walk through the fetched array and merge it with the other three result arrays.

ayeronnie

I should have been more specific. The main problem is I have to select from 4 different tables, and the fields do not match for all 4, close not different. And I am not sure if this will cause a problem with selecting. I would love to be ble to use mysql for the select and to sort the records. But the only possible solution is mysql UNION, and I can find much on it, or many code examples to figure out how to use it my situation. Plus it does not say if the fields being different, will cause problems with UNION.

The 4 tables are all different, all different types of links or sets of links. No duplicates. I did it this way because I would be preforming regular tasks on different link sets.

But also, I should have mentioned, though the tables have different fields, for this operation, I will only need certian fields, the same fields from eavch table, they would be:

site_name
link
description
mouseover (only from sponsor table)

I was just wondering if I narrowed down my selects. Right now I select * . I was thinking, then the fields would be the same on select, except for the mouseover one on the sponsor table. Maybe I should drop the mouseover field, hate to, but if it solves the problem.

Sorry to make this so long, just wanted to explain all. Thanks again for the help. any suggestions?
Thanks.

ronnie

OhLordy

Fields not being the same will not be a problem when using the left join Mysql will just return all the values leaving null values for where that column doesn't apply.
Pick a table (probably current)

select .... from current, left join mine on current.site_name=mine.site_name left join sponsor on current.site_name=sponsor.site_name left join toplist on current.site_name=toplist.site_name

However I think the main reason you are having trouble finding the right way of pulling data from you database is that it is badly designed. There is quite a lot of un normalised data there. It may seem like a logical way to structure a database but when you think about it in terms of how relational databases are supposed to work it doesn't make a lot of sense.

All the links should be in one table with an atribute "type" which would denote whether they are mine, current, sponsor or toplist. and then you should have sub tables for the other info.

Good Luck
Rob

Weedpacket

It is a lot better to get your data schema right, of course; but if you don't have such control, and you're stuck with what you have, and you want to get all these different arrays together into one array sorted on site_name...

Start with an empty array. I'm going to call it $sites[]. A more appropriate and descriptive name should be used, but $sites is good enough for pedagogy.

So the line is $sites=array().
Make the four separate queries.
For each query, read through the entire result set, using fetch_array() (or fetch_assoc(), but not fetch_row()) and storing the returned array as an element of $sites.

$sites[] = mysql_fetch_array(....);

Once you're done with the four queries, it's time to sort. Example (2) of the [man]usort[/man] page will tell you everything you need.

Of course, as OhLordy has intimated, you've now got the pleasant task of using this array (which is going to be fairly messy, with different elements having different formats). At the very least, restrict your queries to returning only the fields you actually need. There's no point in copying 180kB of data from the database to PHP if you can ask for and get only the 30kB you actually want. "Select *" is evil. Avoid it.

ayeronnie

You might be very right, maybe my DB is poorly designed. I'm learning as I go, and like Olordy has mentioned, though I thought it was the best way, maybe it is the worst. And if it is bad design, please point it out..🙂

My thoughts, why I designed it this way (or badly designed it), and please any one, point out my mistakes and miss-thinking, I really would appreciate it.

I designed it this way because:

1). Seperate tables - because some of my task's, on a regular basis, would only be to a certian set of links. Like checking current links for link back or if they are down, ect. Smaller table, less load, less time. Current links would be the ONLY ones I would have to check link back and such.

2). Seperate tables, because a number of "links" would be shared with other domains, like mine (my sites), sponsors, toplist, but current links would be different for each domain. I thought why have the same tables for each domain, when i could just share one? Not to mention, putting the same info in for eah domain.

3). Seperate tables - because there could be a good 10,000 or more links in the db. Would there be a big difference going through a table with 10,000 links compared to 6,000?

4). Seperate tables - I read about lean db/tables. For example, making fields only as big as they need be, length. If your input will be only 2 charactors in length, and you specify the field to be 10, you've got alot of wasted space, adding to the size of the db, un-needed. Only one of my tables, sponsor has the extra field "mouseover", so all other links would have a empty value in that field, hence alot of wasted space, un-needed space in the db. Just my thinking.

I guess I dont know if it's less load, faster to go through a large table with mysql, and letting mysql do most of the work, then looking to do the queries with php that I am looking at with seperate tables? Also on the other hand, these links will not be in production, I build include pages from the data. So maybe i anwsered my own question.

I am here to learn, so please fire away, I'd be happy to hear if I am thinking wrong..🙂 Sorry to make these posts so long, just want to make sure every one knows what I am doing, to point out flaws, ect.

ronnie

BuzzLY

1). Easily fixed with a query like SELECT ... FROM sites WHERE site_type = 'current'

2). Again, easily fixed with the WHERE clause in your SELECT query.

3). MySQL can handle a million records with it's eyes closed -- especially if the data is organized well.

4). Not sure about that one, but I doubt it's much concern.

My suggestion is to read an article on database normalization. It's a good start.

ayeronnie

Thanks for the input Buzz. A friend, like you pointed out, that MySQL can handle alot more than I thought without trouble.

I was trying to take "load" off the DB, when I did'nt realize it could handle it, like you said, with it's eye's closed. Good thing I learned this, I would have put more load on php, and probably caused more problems.

Since MySQL will have not problem with the small amount of data I will be putting in, I have changed the db putting most everything on one table, and making query life on a newbie, much easier..🙂

I happened upon that article you pointed out, even printed it, just need to get it in to my head. Thanks.

Any one have tips or experience in organizing data well? I've used only the fields I really need, made them no longer than needed and much is varchar, so hopefully I am off to a descent start.

Thanks again Buzz.

ronnie