Thanks Johan,
You're partly right in your interpretation, but not in total :-)
Later on in time I'll lift a tip.
I need some time to process your answer and do my homework.
Nevertheless I've got already something that works, but maybe isn't the
most efficient.
Nevertheless a good start, thanks.
BR, Hans.
Johan De Meersman schreef op 11-12-14 om 23:44:
----- Original Message -----
From: "Wm Mussatto" <mussa...@csz.com>
Subject: Re: How to retrieve next record?
Related what is the form of the prmary key. If its numeric something like
$sDBQuery1 = "SELECT * FROM kentekenlogtest WHERE kenteken <
'$sActueelkenteken' limit 1"
might work.
No, kenteken is dutch for license plate. If so, not numeric, although
greater/less comparisons do work on strings, too. My guess, from the sample
queries, would be that this is processing for some form of automated number
plate recognition system :-)
Now, Hans, besides pointing you in the right direction, I'm going to be whining
a bit about some pet peeves of mine. I'm waiting for the start of a midnight
intervention, anyway :-p
That query, as pointed out already, is only asking for a single kenteken. I'll
stick to the dutch column names for clarity for other readers, btw - although
one of the aforementioned pet peeves is nonenglish variable names. Makes code
an absolute bitch to maintain for someone who doesn't speak that language.
That's from experience; I've had to debug crap in french and spanish, among
other languages.
Your code (or, more precisely, the DB driver) is only going to make those
records available to your program that you have explicitly asked for, so that
query will only ever make the one record available. You will need to build a
query that returns all the records you want to access, or, alternatively, make
repeated queries. The former is more efficient by far; the latter is useful if
the next set depends on what you find in the previous set.
Another pet peeve: don't use select *. Explicitly select the columns you're
looking for. It a) saves network bandwith; b) guards against later table
structure changes; c) potentially allows the use of covering indexes and d)
reduces the server memory footprint required for sorting etc.
Once you built the correct query, you'll need to have a cursor to loop through it.
Your DB driver will probably refer to it as a resultset or a similar denomination.
The typical buildup for a database connection (bar advanced abstraction layers) is
db_connect (returns a database handle); dbh->execute(sql) (returns a resultset
handle); loop using rs->fetch_next (probably returns an array or hash with the
data). See your language's db class documentation for the gritty details there. You
may also find a fetch_all or similar which returns you the entire resultset in a
single call. Can be useful, but remember that that means allocating memory clientside
for the entire dataset in one go, instead of reusing the same variables row for row.
A further pet peeve: don't just dump variables into your sql string, use bind variables.
The "easy" method opens you up for little Bobby Tables. Google that, if you're
unfamiliar with it. Then weep in despair :-p
The idea of bind variables is fairly simple: you stick placeholders in your sql
string where you would otherwise use string interpolation; then tell the
statement handle the variables that should go in there. The database is
actually aware of this method, so there is no chance that the variables might
get interpreted as part of the SQL - it KNOWS they're variables, not keywords.
Additionally, if you're going to be executing the same statement repeatedly,
use prepared statements instead of regular executes. On MySQL the benefit is
marginal (but still noticeable), on other databases it might be considerable -
sometimes orders of magnitude faster. Oracle, for instance, has an execution
plan cache; so if you use prepared statements, it can skip the whole parse -
analyze - pick plan bit and skip straight to the next execution round with the
new values you provided. On fast statements (like primary key lookups) that can
sometimes save 80% and more of the roundtrip time.
The abovementioned where-clause with limit is probably also going to work; but
then you'll need to re-query time after time; and limit does not always work
quite intuitively - although in this simple case, it does. If you *must*
re-query time after time, do a speed comparison with and without prepared
statements; otherwise do go for the fetch_next loop.
Now, you've got documentation to read, I believe. Off you go :-)
/johan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
