Retrieving FIRST occurrence of a string in a column (SQLITE PHP)
(1) By PongoZ on 2020-03-28 20:24:17 [link] [source]
Hi I'm new to SQLITE and PHP and I'm trying to build a simple database.
How can I echo all the variables of a table that are under a specific column heading ("ColumnX")?
Next step, I would need to find the index of the PREVIOUS, the NEXT, the FIRST and the LAST occurrence of a specific unique string ("Hello") in the column.
Cheers!
(2) By Simon Slavin (slavin) on 2020-03-28 21:15:58 in reply to 1 [link] [source]
For your first question:
I think you want something like
SELECT ColumnX FROM MyTable
For your second question:
The rows of SQL tables don't have any order. There's no first row or last row, just a bag full of rows. If you don't specify an ORDER BY clause you could get your rows in any order the software finds convenient. And if you do the same SELECT twice you might get the rows in two different orders.
From your questions I think you need to read a book or a web site which teaches you SQL. You might find this useful:
(4.1) By PongoZ on 2020-03-29 06:33:07 edited from 4.0 in reply to 2 [source]
Thank you Simon. I did not realize that SQL tables' rows have not an order per se. Very important point to understand! I have therefore added a sequential incremental number to identify the order of the entries as they are added to the database. Let me clarify with an example what I would like to achieve. MyTable: progressive_no ColumnX 1 1 2 2 3 3 4 4 5 2 6 2 7 5 8 2 Let's take string "2" from ColumnX. As you can see it appears in progressive_no 2, 5, 6 and 8. Let's assume now that I have currently selected the row with progressive_no 5 (string "2" from ColumnX). Now I need to retrieve at which progressive_no I find the: - previous occurrence of string "2" (if a previous entry is present) - next occurrence of string "2" (if another entry is present) - first occurrence of string "2" - last occurrence of string "2" Cheers!
(7) By luuk on 2020-03-29 07:40:49 in reply to 4.1 [link] [source]
CREATE TABLE MyTable (progressive_no int, ColumnX int); INSERT INTO MyTable(progressive_no, ColumnX) VALUES (1,1), (2,2), (3,3), (4,4), (5,2), (6,2), (7,5), (8,2); SELECT t1.ColumnX , t1.progressive_no, MAX(t2.progressive_no) as Previous, MIN(t3.progressive_no) as Next, MIN(t4.progressive_no) as First, MAX(t5.progressive_no) as Last FROM MyTable t1 LEFT JOIN ( SELECT progressive_no, ColumnX FROM MyTable t2 ORDER BY t2.progressive_no DESC ) t2 ON t2.ColumnX=t1.ColumnX and t2.progressive_no <t1.progressive_no LEFT JOIN ( SELECT progressive_no, ColumnX FROM MyTable t3 ORDER BY t3.progressive_no ASC ) t3 ON t3.ColumnX=t1.ColumnX and t3.progressive_no >t1.progressive_no LEFT JOIN ( SELECT progressive_no, ColumnX FROM MyTable t4 ORDER BY t4.progressive_no ASC ) t4 ON t4.ColumnX=t1.ColumnX LEFT JOIN ( SELECT progressive_no, ColumnX FROM MyTable t5 ORDER BY t5.progressive_no DESC ) t5 ON t5.ColumnX=t1.ColumnX WHERE t1.progressive_no=5 GROUP BY t1.progressive_no,t1.Columnx; output: ColumnX progressive_no Previous Next First Last ---------- -------------- ---------- ---------- ---------- ---------- 2 5 2 6 2 8
(9) By Keith Medcalf (kmedcalf) on 2020-03-29 09:16:07 in reply to 7 [link] [source]
The following returns the same results and is about twice as fast even with only 8 records in MyTable ... and it should always take about the same time no matter how many records are in the table.
CREATE TABLE MyTable
(
progressive_no integer primary key,
ColumnX text
);
create index idx on MyTable(ColumnX);
INSERT INTO MyTable (ColumnX) VALUES
(1),
(2),
(3),
(4),
(2),
(2),
(5),
(2);
select * from MyTable;
progressive_no ColumnX
-------------- ----------
1 1
2 2
3 3
4 4
5 2
6 2
7 5
8 2
SELECT t1.ColumnX,
t1.progressive_no,
(
select progressive_no
from MyTable
where ColumnX == t1.ColumnX
and progressive_no < t1.progressive_no
order by progressive_no desc
limit 1
) as previous,
(
select progressive_no
from MyTable
where ColumnX == t1.ColumnX
and progressive_no > t1.progressive_no
order by progressive_no
limit 1
) as next,
(
select progressive_no
from MyTable
where ColumnX == t1.ColumnX
order by progressive_no
limit 1
) as first,
(
select progressive_no
from MyTable
where ColumnX == t1.ColumnX
order by progressive_no desc
limit 1
) as last
from MyTable as t1
where progressive_no == 5;
ColumnX progressive_no previous next first last
---------- -------------- ---------- ---------- ---------- ----------
2 5 2 6 2 8
(8) By Keith Medcalf (kmedcalf) on 2020-03-29 08:00:27 in reply to 4.1 [link] [source]
Records in a table do have a "record number" that reflects the order in which the records were added called the "rowid". You can read about it here https://sqlite.org/rowidtable.html.
If your "progressive_no"
is simply a number that is one bigger than the biggest number that exists so far then you can declare your MyTable as:
create table MyTable
(
progressive_no integer primary key,
ColumnX text not null
);
create index MyTableIndex on MyTable (ColumnX);
whenever you do an insert like:
insert into MyTable (ColumnX) values (?);
then the new record will automatically be assigned a progressive_no
of coalesce(max(progressive_no)+1, 1)
. This is a 64-bit integer so when the next record (records after # 9223372036854775807) then a "free number" will randomly be chosen if one exists for the new record number. See https://sqlite.org/autoinc.html
(3) By Keith Medcalf (kmedcalf) on 2020-03-28 23:33:38 in reply to 1 [link] [source]
Please define your use of the term "index"
Please define your use of the phrase "of a specific unique string in a column"
By "index" do you mean the rowid?
By first/last/previous/next no you mean that, for example, when looking at the specific row containing 'it is it partially it is wonky' the instance of 'it' WITHIN the data?
Please provide an EXAMPLE of what you are trying to achieve since your problem definition is somewhat ambiguous. If possible please state all conditions and constraints on your problem space.
(5) By PongoZ on 2020-03-29 05:58:25 in reply to 3 [link] [source]
Hi Keith,
Please have a look at my reply above as it clarifies my question. Thanks
(6) By Keith Medcalf (kmedcalf) on 2020-03-29 07:34:35 in reply to 5 [link] [source]
- previous occurrence of string "2" (if a previous entry is present)
select progressive_no
from MyTable
where ColumnX == :string
and progressive_no < :lastno
order by progressive_no desc
limit 1;
- next occurrence of string "2" (if another entry is present)
select progressive_no
from MyTable
where ColumnX == :string
and progressive_no > :lastno
order by progressive_no
limit 1;
- first occurrence of string "2"
select progressive_no
from MyTable
where ColumnX == :string
order by progressive_no
limit 1;
- last occurrence of string "2"
select progressive_no
from MyTable
where ColumnX == :string
order by progressive_no desc
limit 1;
Where :string is the string value you are looking for ('2' in your example) and :lastno is the progressive_no where you are looking from (5 in your example).
You will want a unique index on (ColumnX, progressive_no).
(10) By PongoZ on 2020-04-01 08:05:05 in reply to 6 [link] [source]
Awesome. It works perfectly. Many thanks!