Android Question A mysql query question.

tufanv

Expert
Licensed User
Longtime User
Hello,

I am sending a comma seperated id string to my php like this : 214,235,224 and in mysql table I have ids from 1 to 1000. I want to get the rows with ids 214,245 and 224 . ( not with where id =214 or id=235 etc..) because the ids wil be random every time. How can I use there where query for this. mysql need to find those rows with the ids like:

select * from table where id "is one of the ids in the string sepereated by comma" (214,235,224)

Thanks
 

tufanv

Expert
Licensed User
Longtime User
MySQL??? a
You maybe looking for something like this.
B4X:
SELECT* FROM `table` WHERE `ids` IN (214, 245, 224)

Enjoy...
yes but 214,245 amd 224 are random I need to extract them from the string I have sent via my app seperated by comma , those values would be 12,23,45 and next time another random values.
 
Upvote 0

Mahares

Expert
Licensed User
Longtime User
I need to extract them from the string
Split the string using Regex And use its elements in the SQL statement As such:
B4X:
Dim str As String="24,178,498"
    Dim strArray() As String=Regex.Split(",",str)  
    Dim strQuery As String ="SELECT * FROM Mytable WHERE id IN (strArray(0), strArray(1), strArray(2))"
 
Upvote 0

tigrot

Well-Known Member
Licensed User
Longtime User
Dim strQuery As String ="SELECT * FROM Mytable WHERE id IN (strArray(0), strArray(1), strArray(2))"
Better to code like this:
B4X:
Dim str As String="24,178,498"
    Dim strArray() As String=Regex.Split(",",str)
    Dim strQuery As String ="SELECT * FROM Mytable WHERE id IN ('" & strArray(0) & "','" &  strArray(1) & "','" & strArray(2) & "')"
 
Upvote 0

Mahares

Expert
Licensed User
Longtime User
use ' in SQL construction if the ID is a string or without if a number
He told us in his question that the ID is anywhere between 1 and 1000. Therefore, they are all numbers. No need to complicate it with the single quotes.
 
Upvote 0

tufanv

Expert
Licensed User
Longtime User
How about using Explode ? I think this works :

B4X:
    $semboller= clean($_REQUEST["semboller"]);

    $symbols = explode(",", $semboller);
    $all = array();


    foreach ($symbols as $symbol) {   .....
 
Upvote 0

tigrot

Well-Known Member
Licensed User
Longtime User
He told us in his question that the ID is anywhere between 1 and 1000. Therefore, they are all numbers. No need to complicate it with the single quotes.
Yes, but if he uses your code the three values won't be extracted and inserted in SQL text and text will result =
"SELECT * FROM Mytable WHERE id IN (strArray(0), strArray(1), strArray(2))" as is.
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
How about using Explode ?
On top of that I would not trust the input at all and either use is_numeric or preg_match to ensure that each item is a number (not some nasty SQL injection trick). With preg_match, you can get very specific

B4X:
    foreach ($symbols as $symbol) {
       if (!preg_match('/^[1-9][0-9]{0,3}$/', $symbol)) {
          // Error or ignore
       }
       if ($symbol > 1000) { // I'm assuming PHP will convert the text to a number before doing the compare.
         // The above preg_match matches 1..1999
         // Out of range, error or ignore
       }
       ...
    }

Edit: Source: https://stackoverflow.com/questions/7649752/php-is-numeric-or-preg-match-0-9-validation
 
Upvote 0

Mahares

Expert
Licensed User
Longtime User
Dim strQuery As String ="SELECT * FROM Mytable WHERE id IN ('" & strArray(0) & "','" & strArray(1) & "','" & strArray(2) & "')"
Now that I had time to test, you do not need the single quotes since the column is numbers between 1 and 1000 although you can use them. The simpler syntax without the cumbersome single quotes is:
B4X:
strQuery  ="SELECT * FROM MyTable WHERE id IN (" & strArray(0) & "," &  strArray(1) & "," & strArray(2) & ")"
 
Upvote 0

tufanv

Expert
Licensed User
Longtime User
Hi guys thanks very much for suggestions. First i used explode successfuly later I tried oliver’s code but thr array size changes always , is it adaptable to that scenario ? ( where the size is vaeiable )
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
What's your complete PHP code to create the query string? Yes one should be able to handle array size changes. Let us start with what you have.
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
Let's say you store all valid values in the $all array you have defined in your code snipped above, then you could do a
B4X:
$strQuery = "SELECT * FROM Mytable WHERE id IN (" . implode(",", $all) . ")";
 
Upvote 0
Top