+- UserSpice (https://userspice.com/forums)
+-- Forum: Miscellaneous (https://userspice.com/forums/forumdisplay.php?fid=28)
+--- Forum: Modifications and Hackery (https://userspice.com/forums/forumdisplay.php?fid=29)
+--- Thread: How To: SELECT 1 or 2 rows depending on a DISTINCT single row (/showthread.php?tid=1456)
How To: SELECT 1 or 2 rows depending on a DISTINCT single row - Malatesa - 04-27-2019
Hello everyone.
I currently have a database called wall. If contains a record of a text post, OR a picture post, OR a picture and a text post. So there should be 1 of 3 outputs from my SELECT. But right now, my SELECT statement only returns one row at a time, which is fine if someone only posts a text post, or if someone only posts a picture post, but in the case that they post a picture AND text at the same time (the same td) then I need those 2 rows returned so I can process them TOGETHER.
My current SELECT statement: SELECT rowId, td, filename, userId, deleted FROM wall ORDER BY td DESC
The output from my current SELECT statement:
rowId td filename userId deleted
107 1556377561 ../users/wall/2019/04/1556377561.1218.txt 11 0
106 1556377561 ../users/wall/2019/04/1556377561.1218.png 11 0
105 1556377556 ../users/wall/2019/04/1556377556.0624.txt 11 0
104 1556377556 ../users/wall/2019/04/1556377556.0624.png 11 0
103 1556377530 ../users/wall/2019/04/1556377530.5411.txt 11 0
102 1556377522 ../users/wall/2019/04/1556377522.3709.txt 11 0
101 1556377393 ../users/wall/2019/04/1556377393.0412.txt 11 0
99 1556377380 ../users/wall/2019/04/1556377380.8744.png 11 0
100 1556377380 ../users/wall/2019/04/1556377380.8744.txt 11 0
98 1556375783 ../users/wall/2019/04/1556375783.454.txt 11 0
97 1556375751 ../users/wall/2019/04/1556375751.5567.txt 11 0
96 1556375751 ../users/wall/2019/04/1556375751.5567.png 11 0
95 1556345197 ../users/wall/2019/04/1556345197.4695.txt 1 0
94 1556345197 ../users/wall/2019/04/1556345197.4695.png 1 0
93 1556345018 ../users/wall/2019/04/1556345018.6357.txt 1 0
92 1556345018 ../users/wall/2019/04/1556345018.6357.png 1 0
91 1556345000 ../users/wall/2019/04/1556345000.6300.png 1 0
As you can see, the output returns a row containing either a row with a record of a text post, or a row containing a record of a picture post. This is good IF people only post a text post OR a picture post, but if they post a text AND a picture post, my current SELECT statement won't know because it only returns EITHER a text post OR a picture post, but it doesn't yet return the possibility of a text AND a picture post.
Output possibility 1) a single row that is a text post row
rowId td filename userId deleted
103 1556377530 ../users/wall/2019/04/1556377530.5411.txt 11 0
(I will display this as a text only post)
Output possibility 2) a single row that is a picture post row
rowId td filename userId deleted
91 1556345000 ../users/wall/2019/04/1556345000.6300.png 1 0
(I will display this as a picture posted without a text message associated with the picture)
Output possibility 3) 2 rows that contains both the text row and the picture row as a set
rowId td filename userId deleted
107 1556377561 ../users/wall/2019/04/1556377561.1218.txt 11 0
106 1556377561 ../users/wall/2019/04/1556377561.1218.png 11 0
(I will show this as a picture that goes with some associated text)
So I need a SELECT statement that returns either 1 row with a text post if there is not an associated picture post with that same td (time in seconds), or 1 row with a picture post if there is not an associated text post with that same td (time in seconds), or 2 rows with a text post AND a picture post with the same td (time in seconds). Then I can show EITHER the text post if there was no picture posted with the text, or a picture post if there was no text posted with it, or the text AND picture because they were posted at the same time as a set.
Any suggestions welcome.
RE: How To: SELECT 1 or 2 rows depending on a DISTINCT single row - Brandin - 04-28-2019
Hi there,
There's not enough details on how you plan on getting this data. If you need to retrieve these single or double rows, you need to have a where clause - what is it? Where filename = ?
Thank you,
Brandin.
RE: How To: SELECT 1 or 2 rows depending on a DISTINCT single row - Malatesa - 04-29-2019
(04-28-2019, 01:54 PM)Brandin Wrote: Hi there,
There's not enough details on how you plan on getting this data. If you need to retrieve these single or double rows, you need to have a where clause - what is it? Where filename = ?
Thank you,
Brandin.
I got it. It's starting to come together. https://witches.community/users/wall.php
RE: How To: SELECT 1 or 2 rows depending on a DISTINCT single row - Brandin - 04-29-2019
Awesome, glad to see, thank you for letting me know.
RE: How To: SELECT 1 or 2 rows depending on a DISTINCT single row - astropos - 04-30-2019
I'm doing something similar right now. Hope I've read and understood your question properly
I have the main table 'items' where I create one record for each post. My input form is rather like yours in that users can enter text or upload an image, or both.
Before you store the item in the db, you can determine which of the input fields have been filled (is it text or image or both) and then assign an 'item_type' code before INSERTing. This way you have only one row per 'item'. Then when you come to read the table for display, you can use program logic (switch, or multiple if's) to decide what to do based on the 'item_code' rather than multiple queries. Subesquent comments and comment replies would go in another two seperate tables.
proto:
receive $_POST
use some logic to determine what it is
[eg] if text field is not empty the item code is 't'
[eg] if image field is not empty the item code is 'i'
else item code is 'z'
INSERT INTO items (textfield,imagefield,code)
---
SELECT * FROM items [WHERE] ORDER BY date DESC
if(item_code = 't') echo '<p>xyz</p>'
if(item_code = 'i') echo '<img src=xyz />'
if(item_code = 'z') echo '<p>xyx</p><img src=xyz />'
else echo 'this record is malformed'
The above is just a rough example and not definitive(!) Does it make any sense?
https://cw3.cloudwidgets.co.uk/ (src avail if you'd like)
RE: How To: SELECT 1 or 2 rows depending on a DISTINCT single row - Malatesa - 04-30-2019
(04-30-2019, 09:06 AM)astropos Wrote: I'm doing something similar right now. Hope I've read and understood your question properly
I have the main table 'items' where I create one record for each post. My input form is rather like yours in that users can enter text or upload an image, or both.
Before you store the item in the db, you can determine which of the input fields have been filled (is it text or image or both) and then assign an 'item_type' code before INSERTing. This way you have only one row per 'item'. Then when you come to read the table for display, you can use program logic (switch, or multiple if's) to decide what to do based on the 'item_code' rather than multiple queries. Subesquent comments and comment replies would go in another two seperate tables.
proto:
receive $_POST
use some logic to determine what it is
[eg] if text field is not empty the item code is 't'
[eg] if image field is not empty the item code is 'i'
else item code is 'z'
INSERT INTO items (textfield,imagefield,code)
---
SELECT * FROM items [WHERE] ORDER BY date DESC
if(item_code = 't') echo '<p>xyz</p>'
if(item_code = 'i') echo '<img src=xyz />'
if(item_code = 'z') echo '<p>xyx</p><img src=xyz />'
else echo 'this record is malformed'
The above is just a rough example and not definitive(!) Does it make any sense?
https://cw3.cloudwidgets.co.uk/ (src avail if you'd like)
Very cool! Looks pretty slick!
I've got a working example of my "wall" at https://witches.community/users/wall.php but the code is terrible lol (but it works!)