Android Question A mysql query question.

[tufanv]

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

 

[Peter Simpson]

MySQL??? a
You maybe looking for something like this.

SELECT* FROM `table` WHERE `ids` IN (214, 245, 224)

Enjoy...

 

[tufanv]

MySQL??? a
You maybe looking for something like this.

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.

 

[Mahares]

I need to extract them from the string

Split the string using Regex And use its elements in the SQL statement As such:

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))"

 

[tigrot]

Dim strQuery As String ="SELECT * FROM Mytable WHERE id IN (strArray(0), strArray(1), strArray(2))"

Better to code like this:

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) & "')"

 

[tigrot]

use ' in SQL construction if the ID is a string or without if a number

 

[Mahares]

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.

 

[tufanv]

How about using Explode ? I think this works :

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

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


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

 

[tigrot]

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.

 

[OliverA]

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

    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

 

[Mahares]

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:

strQuery  ="SELECT * FROM MyTable WHERE id IN (" & strArray(0) & "," &  strArray(1) & "," & strArray(2) & ")"

 

[tigrot]

How about using Explode ? I think this works :

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

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


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

This is not B4X. It's PHP.

 

[OliverA]

I am sending a comma seperated id string to my php like this : 214,235,224

This is not B4X. It's PHP.

He's talking about PHP.

 

[tufanv]

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 )

 

[OliverA]

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.

 

[OliverA]

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

$strQuery = "SELECT * FROM Mytable WHERE id IN (" . implode(",", $all) . ")";