Simple Database Query

xray55 · 10-27-2019, 09:29 PM

ok thx

whats the exact command to run the test?

xray55 · 10-27-2019, 10:49 PM

ok thank you

what is the exact command to run

scasey · 10-27-2019, 11:28 PM

Quote:

Originally Posted by xray55

ok thank you

what is the exact command to run

See #12

scasey · 10-28-2019, 02:33 AM

Quote:

Originally Posted by xray55

hey scasey

thank u..

I tried it but it ignore it with no change

Code:

mysql> update streams set stream_source = replace(stream_source, 'live/', '');
Query OK, 0 rows affected (0.02 sec)
Rows matched: 1251  Changed: 0  Warnings: 0

Just noticed that your OP says “about 235” rows, but this query “matched” 1251 rows — because there is no WHERE clause.
1. You should probably add a WHERE clause so the attempt is only made on rows that you want to change.
2. Are you sure that the first attempt didn’t work?

xray55 · 10-28-2019, 03:31 PM

hey scasey,

This what Ive tried

Code:

update streams set stream_source = replace(stream_source, 'live\/', '');

Query OK, 0 rows affected (0.01 sec)
Rows matched: 1302 Changed: 0 Warnings: 0

Code:

update streams set stream_source = replace(stream_source, 'live/', '');

Query OK, 0 rows affected (0.02 sec)
Rows matched: 1302 Changed: 0 Warnings: 0

Code:

SELECT replace(stream_source, 'live/', '') FROM stream where stream_source like '%live%';

ERROR 1146 (42S02): Table 'xtream_iptvpro.stream' doesn't exist
update streams set stream_source = replace(stream_source, "live/", '');
Query OK, 0 rows affected (0.02 sec)
Rows matched: 1302 Changed: 0 Warnings: 0

scasey · 10-28-2019, 05:46 PM

Quote:

Originally Posted by xray55

hey scasey,

This what Ive tried

Code:

update streams set stream_source = replace(stream_source, 'live\/', '');

Query OK, 0 rows affected (0.01 sec)
Rows matched: 1302 Changed: 0 Warnings: 0

Code:

update streams set stream_source = replace(stream_source, 'live/', '');

Query OK, 0 rows affected (0.02 sec)
Rows matched: 1302 Changed: 0 Warnings: 0

Code:

SELECT replace(stream_source, 'live/', '') FROM stream where stream_source like '%live%';

ERROR 1146 (42S02): Table 'xtream_iptvpro.stream' doesn't exist

Well, that last one doesn't have the right table name in: stream instead of streams Did you correct and re-run with the right table name?

Also, when doing a test like that, I like to output both the before and after pictures, like:

Code:

SELECT stream_source, replace(stream_source, 'live/', '') FROM streams where stream_source like '%live%';

In the attempts without WHERE clauses -- the first two -- the "Rows matched" counts should equal the number of rows in the table. What does

Code:

select count(*) from streams;

return? And

Code:

select count(*) from streams where stream_source like '%live%';

Also

Code:

select stream_source from streams where stream_source like '%live%';

I'm trying to get you to look at the data!
I think one of your trys worked and there are no longer any rows with "live/" in.

And the "Rows matched count" keeps changing. Why?

xray55 · 10-29-2019, 02:26 AM

Hi

The output was thousands of lines like this

| ["http:\/\/domain:9595\/live\/username\/pass\/2030.m3u8"]

and no I didnt previously successfully replaced, I can view the links in the GUI server and see they are still /live/

like I said I easily replace live and end up with double //

I was hoping I can learn to deal with slashes once and for all.

DM me if u wish to skype. I can let u try yourself and see what works

scasey · 10-29-2019, 02:58 AM

Quote:

Originally Posted by xray55

Hi

The output was thousands of lines like this

| ["http:\/\/domain:9595\/live\/username\/pass\/2030.m3u8"]

and no I didnt previously successfully replaced, I can view the links in the GUI server and see they are still /live/

like I said I easily replace live and end up with double //

I was hoping I can learn to deal with slashes once and for all.

DM me if u wish to skype. I can let u try yourself and see what works

It's not about slashes! (sorry to yell, but I'm trying to get you to understand)

The data contains escaped slashes...you're trying to match live/ and the string in the database is live\/
so...to test:

Code:

SELECT stream_source, replace(stream_source, 'live\/', '') FROM streams where stream_source like '%live%';

that should yield "thousands of lines" That look like:

Code:

["http:\/\/domain:9595\/live\/username\/pass\/2030.m3u8"]  ["http:\/\/domain:9595\/username\/pass\/2030.m3u8"]

can you figure out the update from that?

PS I thought you'd already removed the .m3u8..?

xray55 · 10-29-2019, 03:18 AM

Code:

mysql> SELECT stream_source, replace(stream_source, 'live/', '') FROM streams where stream_source like '%live%';

302 rows in set (0.00 sec)
The output was tons of lines like this
["http:\/\/domain:9595\/live\/username\/pass\/2030.m3u8"]

Code:

mysql> select count(*) from streams;

+----------+
| count(*) |
+----------+
| 1317 |
+----------+
1 row in set (0.00 sec)

Code:

mysql> select stream_source from streams where stream_source like '%live%';

["http:\/\/domain:9595\/live\/username\/pass\/2030.m3u8"]

Based on tests and output, my guess for the correct Query

replace(stream_source, '\/live\', '')

or

replace(stream_source, 'live\/', '')

scasey · 10-29-2019, 03:46 AM

Quote:

Originally Posted by xray55

Code:

mysql> SELECT stream_source, replace(stream_source, 'live/', '') FROM streams where stream_source like '%live%';

302 rows in set (0.00 sec)
The output was tons of lines like this
["http:\/\/domain:9595\/live\/username\/pass\/2030.m3u8"]

Code:

mysql> select count(*) from streams;

+----------+
| count(*) |
+----------+
| 1317 |
+----------+
1 row in set (0.00 sec)

Code:

mysql> select stream_source from streams where stream_source like '%live%';

["http:\/\/domain:9595\/live\/username\/pass\/2030.m3u8"]

Based on tests and output, my guess for the correct Query

replace(stream_source, '\/live\', '')

Please use code tags when posting output.
Did you test that? Use the SELECT column, replace(...) syntax. Don’t forget the WHERE clause.
You only want to act on the 302 rows that have “live” in them.

I think you want

Code:

replace(stream_source, ‘live\/, ‘’)

Do you see the difference?

Your guess would remove the escape character from the following slash. I don’t know if that would matter downstream. Maybe. Maybe not.

Keep us posted...

AnanthaP · 10-29-2019, 05:34 AM

mySql also has a locate function.

What do

Quote:

select locate('live',stream_source)

And

Quote:

Select left(stream_source,locate('live')-1)

And

Quote:

Select concat(left(stream_source,locate('live')-1), right(stream_source,length(stream_source)-left(stream_source,locate('live')-1)-6)

give

scasey · 10-29-2019, 05:51 AM

^^ Interesting, but not really relevant to the issue at hand, IMO
The OP is working to modify data in the database.

xray55 · 10-29-2019, 06:48 PM

scasey

I cant get it

Code:

update streams set stream_source = replace(stream_source, 'live\/', '');

Code:

update streams set stream_source = replace(stream_source, '\/live\/', '\/');

Same Output

Code:

mysql> update streams set stream_source = replace(stream_source, '\/live\/', '\/');
Query OK, 0 rows affected (0.02 sec)
Rows matched: 1317  Changed: 0  Warnings: 0

scasey · 10-29-2019, 07:27 PM

Quote:

Originally Posted by xray55

Code:

mysql> SELECT stream_source, replace(stream_source, 'live/', '') FROM streams where stream_source like '%live%';

302 rows in set (0.00 sec)
The output was tons of lines like this
["http:\/\/domain:9595\/live\/username\/pass\/2030.m3u8"]

The output of this should have been something like

Code:

["http:\/\/domain:9595\/live\/username\/pass\/2030.m3u8"] ["http:\/\/domain:9595\/live\/username\/pass\/2030.m3u8"]

assuming that the brackets ([]) and quotes are in the data. Are they?
That query should have returned two columns, not just one. Of course it didn't work because the value to match wasn't correct.
Again, you need to show us exactly what the query is returning. We don't need to see all 302 rows, but please copy and past a few lines. It's OK to obfuscate the domain name, but everything else should be as it's printed.

Please do the tests first. By that, I mean the SELECT column,replace(column,'original','replacement'). Please use the WHERE clause.
Showing us the result of the update attempts does not provide any helpful information. It just shows that it didn't work.
The SELECT column, replace(...) WHERE ... will show what's actually happening.

So, the corrected statement to try is

Code:

SELECT stream_source, replace(stream_source, 'live\/', '') FROM streams where stream_source like '%live%';

That should display 302 rows that look like

Code:

["http:\/\/domain:9595\/live\/username\/pass\/2030.m3u8"] ["http:\/\/domain:9595\/username\/pass\/2030.m3u8"]

Please show us two or three of those rows.

This syntax worked for me. We just have to figure out why it's not working for you.

scasey · 10-29-2019, 07:29 PM

Oh!
The data you're showing us now still has the .m3u8, in your OP you posted that the replace of the .m3u8 had worked.
Why is it still there?