returning & sorting all data

[ztron]

I'm new to php and still learning and reading. I need some help and suggestions about something I want to do. I don't necessarily need code, just wondering on how to go about doing this.

I want to connect to a database and select certain fields and then return that data sorted in descending order. I would like to just return every single record and have it sort in descending order by what the value of a particular field is. Eventually I would just like to have it report just the 10 highest records after they are sorted.

Does this sound possible and can I have it fetch and return the data just by clicking a link to the php file, or will I have to setup a form and a submit button? I really don't want to compare the data to anything, I just want to pull everything out of the database and list certain fields sorted according to the value of a certain field.

Do I create a loop to have it keep printing the values for each record (there are about 1300). Also, if someone could explain the sort function to me that would be helpful. The field I am going to be sorting in descending order has a numerical value too.

ANy help is appreciated. Just want to learn more.

[LordShryku]

Well, if what you really want it the top tem records, sorted by that field in descending order, you can save yourself a lot of work and do it all in the SQL by using the ORDER BY and LIMIT clauses.

This ispossible to do without a form. If your query is always going to stay the same, then you could just link to this.

[ztron]

ok, so how does the order by and limit clause work? will it still go through all 1300 records and then just pick the top 10 and order them in descending order if i limit it to 10 or will it just pick the first 10 records in the database and then order those in descending order?

this stuff seems so easy to do, but the database i am working with is not normalized at all and has a ton of empty fields, a lot of them that i have no idea what they are for. it's an old fox pro database. it's a small company that i'm working for, but they are getting bigger and the program they are using, market power, can't pull a lot of the data that they want. i would really rather just completely re-design the database in MySQL, but at this point it really isn't an option. they are looking at buying some other program in the future, but for now i am pretty much just making queries for them to run on the web.

[bretticus]

Fox Pro huh? Well so much for LIMIT. I have NO experience with fox pro, however, a very common SQL parameter is TOP. If fox pro follows that standard, you could try:

"SELECT TOP 10 * FROM table ORDER BY column DESC"

[bretticus]

On second thought, it may not be possible to even connect the the FOx Pro database. I know that it used to cost $$$ to get the drivers neccessary to connect to a Microsoft SQL server via PHP on a Linux machine. That may be diff if your PHP install is running on windows. In that case I suggest you go to the asp forum and save yourself some $$$.

[ztron]

i've been able to connect to the database fine by using a function that a previous employee created. he told me that the database was foxpro and not normalized at all. it's pretty confusing for me to try to find the right data i want to pull. for instance, i wanted to pull a distributors name, address, and phone by entering their url. their is a table called distributor main that has all of this information and a field called dm_url. i thought that was where their url would be stored, but the field is empty for every single distributor. anyway, here is the function that is used to connect to the database:

<?

function db_connect()
{
   $result = mysql_connect("localhost", "user", "pass"); 
   if (!$result)
      return false;
   if (!mysql_select_db("candle_auth"))
      return false;

   return $result;
}

function db_result_to_array($result)
{
   $res_array = array();

   for ($count=0; @odbc_fetch_into($result,$row); $count++)
     $res_array[$count] = $row;

   return $res_array;
}

?>

[bretticus]

What?? mysql_connect gets you connected to a fox pro database?? I would never thought that possible. Try sending the query: "SHOW VARIABLES" and if you get a result. You may be dealing with MySQL afterall.

Now, is your PHP install on Windows or Linux?

Oh, By the way, when you same the database isn't normalized, does that mean each record, may or may not have a primary or combination key?

[ztron]

the other guy i work with that did most of the current php coding said that it was an old fox pro database, but i know sql queries work fine with it. for all i know it could be MySql. whatever it is they are using windows 2000 server. i'll try some of the recommended things on monday.

[bretticus]

If it's windows 2000 server, code it in asp. Unless you're updating his old stuff. I just don't see the point of using PHP when you have a Windows server and a Windows database.