Login or Register to Ask a Question and Join Our Community


UNIX for Beginners Questions & Answers

Variable definition inside mysql query

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums UNIX for Beginners Questions & Answers Variable definition inside mysql query
# 8  
Old 05-09-2019
The bold video_id IS the column value from the table.


Do you REALLY want the string constant '.655.' (with two enclosing dots), or the integer video ID?

What is the output of your above select, and what if '.655.' is replaced by video_id?
This User Gave Thanks to RudiC For This Post:
baris35
# 9  
Old 05-09-2019
No sure whether or not it will make a difference, but maybe you can give a try enclosing column name in back tick like `column_name`
# 10  
Old 05-09-2019
I think I've found the root cause.
When I was about to post this thread, I supposed that the code was not grabbing video_id from related line as I received error. Now I see I have to change the lasting part of the code :
Code:
....WHERE url like '%" $1 "%.mkv';"}

like
Code:
....WHERE url like '" $1 "%.mkv';"}

or:
Code:
....WHERE url like '" $1 ".mkv';"}

Let's say: if $1 is It, the code finds many matching lines in '%It%.mkv'
If the value in $1 is This.is.how.we.do.it.mkv, it returns with error as there are double entries.

I will post the result later on as my database needs to be repaired.


Thank you
Boris
# 11  
Old 05-09-2019
Voila!
Changed the last part with exact match like shown below and no need to create a second query to grab video_id:
.....WHERE url = 'http://localhost/test/" $1 ".mkv';"}

What I still do not know is how it grabs video_id as value in SELECT field.
I did not tell it to read video_id where $1 matches.
I supposed that I should have created a second query to grab it which was the main purpose of the thread.

Thank You All
Boris
# 12  
Old 05-09-2019
Quote:
Originally Posted by baris35
...
.....WHERE url = 'http://localhost/test/" $1 ".mkv';"}
...
I was about to ask why you were using the LIKE condition...



Quote:
I did not tell it to read video_id where $1 matches.
No? What is the result of these selects:

Code:
SELECT  video_id FROM video_series_files WHERE url = 'Mr.Smith.Goes.to.Washington.mkv';
SELECT  video_id, 'sub', 'custom' FROM video_series_files WHERE url = 'Mr.Smith.Goes.to.Washington.mkv';
 SELECT '.105.', video_id, 'sub', 'custom', 'http://localhost/test/Mr.Smith.Goes.to.Washington.en.srt', 'a:1:{i:0;s:2:"en";}', '', '1', '1' FROM video_series_files WHERE url = 'Mr.Smith.Goes.to.Washington.mkv';

# 13  
Old 05-09-2019
Hello Rudic,
Without testing, I can say that it will not return any result because there should be http://% like this:
url = 'http://%Mr.Smith.Goes.to.Washington%.mkv';
Maybe I am wrong..

If the last occupied row id is 100, I run it like this:
barisprep
Code:
CNT=100
{print  "INSERT INTO video_series_files (id, video_id, file_type, protocol, url, languages, \
quality, accessed ,status )"
        " SELECT '." ++CNT ".', video_id, 'sub', 'custom', \
'http://localhost/test/" $1 "." $2 "." $3 "', 'a:1:{i:0;s:2:\"" $2 "\";}', '', '1', '1' \
FROM video_series_files WHERE url = 'http://localhost/" $1 ".mkv';"}

Then I run:
Code:
awk -vCNT=$counter -fbarisprep 2 > barisprep.sql

Next:
Code:
grep -A0 "INSERT" barisprep.sql > 1.sql
sed -i '/--/d' 1.sql
cat 1.sql |  awk '/101/{count++; { sub("101",count+100, $0)}; }; {print }' > barisprep.sql
mysql -uroot -D mydatabase -pmypasswd -sN  < barisprep.sql

Thank you
Boris
Last edited by baris35; 05-09-2019 at 05:41 PM.. Reason: extra info added
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

Environment variable definition

I'm a bit confused about the term ‘environment variables'. Within your shell you can set two types of variables: 1. Shell variable - affecting functionality within your shell 2. User defined variable When using the ‘export' command on a variable you make sure it's being inherited by new sub... (2 Replies)
Discussion started by: niels
2 Replies

2. UNIX for Advanced & Expert Users

Variable definition

Hi all, I'm bit new to the advanced bash shell scripting. When I'm looking at some of the existing code in my organization, got confused with a few variable definings. For ex: var1={1:-30} var2="abc def ghi" var3={xyz:-$var2} In above, 1st and last lines are confusing me.... (4 Replies)
Discussion started by: raghu.iv85
4 Replies

3. UNIX for Dummies Questions & Answers

What is inside the definition of Unix?

Is FreeBSD and OpenBSD considered Unix? What O.S does Most of the forum members use? How popular are Licensed Unix operating systems for home users? Additionally I thought Linux was a Minux fork and BSD was a Unix fork. Thanks in ... (7 Replies)
Discussion started by: theKbStockpiler
7 Replies

4. Shell Programming and Scripting

Using a shell script variable in a mysql query using 'LIKE'

Hello. I am writing a simple script that reads a text file and removes records from a mysql database. The items in the text file are of the format: firstname.middle.lastXXX, where XXX is a 3 digit number. The table has an email field that will match the firstname.middle.last. So, I thought I... (1 Reply)
Discussion started by: bricoleur
1 Replies

5. Shell Programming and Scripting

bash assign mysql query single field to variable

I'm running a bash script query and assigning the output to a variable like this: exists=`mysql -u $USER_NAME --password=$PASSWORD -D "somedb" \ -e "SELECT * FROM somedb.sometable WHERE field1 ='$a' \ AND field2 ='$b' LIMIT 0 , 30";` which returns something like: echo... (2 Replies)
Discussion started by: unclecameron
2 Replies

6. Shell Programming and Scripting

How to define a variable with variable definition is stored in a variable?

Hi all, I have a variable say var1 (output from somewhere, which I can't change)which store something like this: echo $var1 name=fred age=25 address="123 abc" password=pass1234 how can I make the variable $name, $age, $address and $password contain the info? I mean do this in a... (1 Reply)
Discussion started by: freddy1228
1 Replies

7. Shell Programming and Scripting

variable inside variable inside loop headache

Hi Gurus I have a file called /tmp/CMDB which looks like this serial: 0623AN1208 hostname: server1 model: x4100 assetID: 1234 I am writing a for loop that will go through this file line by line creating a variable of itself. Using the first iteration of the loop (i.e. the first line) as... (6 Replies)
Discussion started by: hcclnoodles
6 Replies

8. Shell Programming and Scripting

Passing a variable from shell script to mysql query?

I heard this was possible but from my research I haven't been able to figure it out yet. Seems it should be simple enough. Basically from a high level view I'm trying to accomplish... . $X='grep foo blah.log' then 'mysql command SELECT foo FROM bar WHERE ' . $X or something like that. ... (2 Replies)
Discussion started by: kero
2 Replies

9. Shell Programming and Scripting

What wrong with the variable definition

i am using the below script and trying to move files in that directory in that pattern to archive. But it doesn;t seem to take the metacharacters. Please sugggest. Code Debug output: (1 Reply)
Discussion started by: dsravan
1 Replies
Login or Register to Ask a Question