SELECT a subset of records in correct order 1

[ChrisRChamberlain]

Hi all

It's Friday and am suffering from brain failure.

The following code selects records 180 to 200 from the query but in descending order.

All that's required is to select the records in ascending order within the SELECT... code block

[COLOR=blue]lnRows = 20
lnOffset = 200

SELECT TOP lnRows company;
[tab]FROM C:\tables\contacts;
[tab]WHERE company IN;
[tab](SELECT TOP lnOffset company FROM C:\tables\contacts ORDER BY company ASC);
[tab]ORDER BY company DESC;
[tab]INTO CURSOR temp[/color][COLOR=green] && Correct records in reverse order[/color]

Presumably another WHERE... etc?

TIA

FAQ184-2483 - answering getting answered.​

Chris
PDFcommander.com
PDFcommander.co.uk

 

[alvechurchdata]

Simplest solution would be a second bit of SQL:
[TT]select * from temp into cursor temp2 order by company[/TT]

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[ChrisRChamberlain]

Geoff

Thanks for your reply

The reason for wanting there to be only one SELECT-SQL statement is that the code is to be used by a PHP script to pull records, n at a time from a VFP table, into a webpage.

FAQ184-2483 - answering getting answered.​

Chris
PDFcommander.com
PDFcommander.co.uk

 

[Mike Lewis]

Chris,

Good question. As Geoff says, it's easy to do if you don't mind having a second query. But it's a toughie if you want to do it all in one.

One possibility might be to use a projection (assuming you are using VFP 9.0). Something like:

SELECT TOP 10 Field1, Field1 FROM (Select TOP 10 * FROM MyTable ORDER BY X DESC) ORDER BY X

I haven't tried it, and I don't know if the syntax is even supported, but it might be worth a bash.

BTW, I'll bet Tamar knows the answer.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[bborissov]

Mike, this is a valid query in VFP9, with one correction. You must ALIAS derived table:

SELECT TOP 10 Field1, Field1;
       FROM (Select TOP 10 * FROM MyTable ORDER BY X DESC) [COLOR=blue][b]Tbl1[/b][/color];
       ORDER BY X

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Olaf Doschke]

Hi Chris,

as you want eg the last 20 records of the first 200, but in correct order, why not have one level more?

Select t2.* From;
  (SELECT TOP lnRows t1.company;
   FROM ;
     (SELECT TOP lnOffset company;
      FROM curcontacts
      ORDER BY company ASC;
     ) as t1;
   ORDER BY company DESC;
   ) as t2;
 Order By company ASC;
 INTO CURSOR temp

In fact you want record 181 to 200 as "page", don't you? It might be best performance to have the table in physical order of companys first, then select the range of recnos.

Why not have a stored proc that creates such a temp table and go from there?

Bye, Olaf.

 

[ChrisRChamberlain]

Olaf

Thanks for your reply - star for you!

I hear what you are saying about the physical order - unfortunately the tables could be queried on any field.

FAQ184-2483 - answering getting answered.​

Chris
PDFcommander.com
PDFcommander.co.uk

 

[ChrisRChamberlain]

Revisiting this thread as PHP cannot parse the SELECT-SQL code if the code contains a subquery.

Olaf's suggestion unfortunately has two.

The following is PHP code and shows how you can successfully mix in PHP variables beginning with '$'.

$Start = 100;
$End = 200;
$Query = "SELECT company, street, town FROM contacts ".
[tab]where RECNO() > $Start AND RECNO() < $End ".
[tab]AND [t] $ company ".
[tab]AND [d] $ street ".
[tab]ORDER BY town DESC";

So the question becomes how to construct a SELECT-SQL query in VFP without a subquery that allows you select n rows starting at record x?

FAQ184-2483 - answering getting answered.​

Chris
PDFcommander.com
PDFcommander.co.uk

 

[Mike Lewis]

Chris,

Just to go down to the ground floor for a moment ...

Is your aim to run a query from a PHP script that will query a VFP database?

If so, have you considered writing the query as a stored procedure within the VFP DBC, and calling that from a SELECT statement in PHP? I've never done SQL from PHP, but my guess is that it would work.

Or, how about doing the query as a local view in VFP, and accessing that from PHP?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[ChrisRChamberlain]

Mike

The query would be constructed from data gathered from PHP form(s) and, as you can see from the example, it's possible with the exception of nested subqueries.

Using stored procedures would give you a finite list of options which is not what is being sought.

The server is a Windows server, there are no VFP runtime files on the server, and the client would be any web browser.

FAQ184-2483 - answering getting answered.​

Chris
PDFcommander.com
PDFcommander.co.uk

 

[Mike Lewis]

Chris,

Re gathering data from forms to construct the query ... I was assuming you could write a stored procedure that accepts parameters. The PHP script would pass the data from the forms as parameters. So that would solve the "finite list of options" issue.

Re the lack of run time files ... You would only need the VFP OLE DB driver to be installed on the server - nothing would need to be installed on the client. However, I accept that installing the driver could be an issue.

As I said, I've no direct experience in this area (and you have), so I might well be talking rubbish. I just thought I'd raise the possibility.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Olaf Doschke]

Hi Chris, you seem to be using VFPODBC then, that has the VFP6 limitations.

But you can work with OLEDB Provider in PHP:

$conn = new COM("ADODB.Connection") or die("Cannot start ADO");
$conn->Open("Provider=vfpoledb.1;Data Source=E:\data\some.dbc;Collating Sequence=machine");
$query = "Select * from contacts";
$result = $conn->Execute($query);

Bye, Olaf.

 

[Olaf Doschke]

not to forget closing a connection down again:

$conn->Close(); 
$conn = NULL;

Bye, Olaf.

 

[ChrisRChamberlain]

Mike

Installing the VFP OLE DB driver on the server should not be an issue. Providing the query with the subquery(s) work, then for me that would be the shortest route.

Olaf

I was unaware that the VFP OLE DB would resolve the issue of subqueries in PHP, so thank you for that. What I can't yet resolve is how you format the result.

With ODBC it would be

while(odbc_fetch_row($result))
{
// code
// code
}

etc

FAQ184-2483 - answering getting answered.​

Chris
PDFcommander.com
PDFcommander.co.uk

 

[Mike Lewis]

Chris,

Providing the query with the subquery(s) work, then for me that would be the shortest route.

Well, given that the OLE DB provider acutally runs the query in VFP, using VFP syntax, it should have no difficulty in doing subqueries.

In fact, there should be no need to write a stored procedure or to use local views, as per my earlier suggestion. If your PHP can create a string containing the query, it should be able to pass this to the OLE DB. (How it gets back the results is another issue.)

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Olaf Doschke]

Hi Chris,

Code for fetching result rows:

if ($result->recordcount == 0)
    {
    echo "No contacts found\n";
    }
  else
    {
    while (!$result->EOF) 
      {
       echo $result->Fields("contactname")->value."\n"          
       $result->MoveNext();
      } 
    }

In general, as you use the ADO COM classes this way, a reference to that will help you find out what else you can do. $result is in fact an ADODB.Recordset, all the methods properties and events of that class apply.

Bye, Olaf.

 

[ChrisRChamberlain]

Mike, Olaf

Thanks for your replies.

This project's towards the top of the 'todo' list and will be revisited shortly

FAQ184-2483 - answering getting answered.​

Chris
PDFcommander.com
PDFcommander.co.uk

 

[ChrisRChamberlain]

With help from a PHP guru it's been possible to achieve what was required using a PHP script and ODBC as opposed to OLE DB.

Examples can be found at

http://www.pdfcommander.com/test7.php

http://www.pdfcommander.com/test8.php

FAQ184-2483 - answering getting answered.​

Chris
PDFcommander.com
PDFcommander.co.uk

 

[Olaf Doschke]

I see,

so I assume the full data set (or some filtered portion like in test7) is queried and the partioning into pages is done with PHP, right? Using session variables perhaps, to hold a recordset in memory.

Bye, Olaf.

 

[ChrisRChamberlain]

Olaf

FYI

<?php

//connect to the database
$connectionstring = odbc_connect("vfpdatabase", "", "");

//find out how many records are in the table
$selectQuery = "SELECT company, street, town ,county, postcode ".
	"WHERE LEFT(UPPER(postcode),2) = [GL] FROM contacts ORDER BY company ASC";

$countQuery = "SELECT count(*) FROM contacts WHERE LEFT(UPPER(postcode),2) = [GL]";

//	Show SQL string
echo "<table>";
echo "<tr><td><font face='Tahoma,Geneva' size=2>This SELECT-SQL query against a VFP database using ODBC is ' $selectQuery'</font></td></tr>";
echo "<tr><td><br></td></tr>";
echo "</table>";

//	Get number of records
$result = odbc_exec($connectionstring, $countQuery);
odbc_fetch_row($result,0);
$numRecords = odbc_result($result, 1);

if ($numRecords === 0) {die ("you have no records that match the query criteria");}

$result = null;

//params
$rowsPerPage = 100;
$page = isset($_GET['page']) ? $_GET['page'] : 1;
$offset = ($page-1) * $rowsPerPage;
$numPages = ceil($numRecords/$rowsPerPage);
//end params

//execute query
$queryexe = odbc_do($connectionstring, $selectQuery);
echo <<<EOL
<style>
 .currentPageLink{color:red;}
 .pageLink{color:blue;}
</style>
EOL;

$pagelinks = "";
for ($i=1; $i<=$numPages; $i++){
    $style = ($i===$page) ? "currentPageLink" : "pageLink";
    $pagelinks .= <<<EOL
    <a href="{$_SERVER['PHP_SELF']}?page=$i" class="$linkstyle">$i</a>
EOL;
}

//	Display table header
echo "<table align='center' border ='1' bordercolor='white' bgcolor='#cdf9fc'>";
print ("<tr>");
print ("<td><b><font face='Tahoma,Geneva' size=2>Company</font></b></td>");
print ("<td><b><font face='Tahoma,Geneva' size=2>Street</font></b></td>");
print ("<td><b><font face='Tahoma,Geneva' size=2>Town</font></b></td>");
print ("<td><b><font face='Tahoma,Geneva' size=2>County</font></b></td>");
print ("<td><b><font face='Tahoma,Geneva' size=2>Postcode</font></b></td>");
print ("</tr>");

//	Query database
for ($cnt=0; $cnt<$rowsPerPage; $cnt++){
    if ($cnt ==0) {
        odbc_fetch_row($queryexe,$offset);
    } else {
        odbc_fetch_row($queryexe);
    }
    $company = odbc_result($queryexe, 1);
    $street = odbc_result($queryexe, 2);
    $town = odbc_result($queryexe, 3);
    $county = odbc_result($queryexe, 4);
    $postcode = odbc_result($queryexe, 5);
    //	format result
    print ("<tr>");
    print ("<td><font face='Tahoma,Geneva' size=2>$company</font></td>");
    print ("<td><font face='Tahoma,Geneva' size=2>$street</font></td>");
    print ("<td><font face='Tahoma,Geneva' size=2>$town</font></td>");
    print ("<td><font face='Tahoma,Geneva' size=2>$county</font></td>");
    print ("<td><font face='Tahoma,Geneva' size=2>$postcode</font></td>");
    print ("</tr>");
}
echo "</table>";

//	Display links, etc
echo "<table>";
print ("<tr><td><br></td></tr>");
print ("<td><font face='Tahoma,Geneva' size=2>$numRecords records returned displaying $rowsPerPage rows per page</font></td>");
print ("<tr><td><br></td></tr>");
echo "<tr> <td colspan=\"5\"><font face='Tahoma,Geneva' size=2>Results page: $pagelinks</font></td></tr>";
echo "</table>";

//disconnect from database
odbc_close($connectionstring);

?>

FAQ184-2483 - answering getting answered.​

Chris
PDFcommander.com
PDFcommander.co.uk