Comma inside MySQL query

Issue

$sql_query_posts = "SELECT * FROM `posts`";

$sql_form_permission = mysql_query("SELECT * FROM `integrations` WHERE `rank_id`='$user_level' AND `mode`='form_per'");
    if ( mysql_num_rows($sql_form_permissions) > 0 ) {

Now the part I’m struggling with:

   $sql_query_posts .= " IN (";
    while ( $row_form_permissions = mysql_fetch_assoc($sql_form_permissions) ) { 
    $sql_query_posts .= $row_form_permissions['form_id'] . ",";
    }
    $sql_query_posts .= ")";
    }

I want the output to be something like:

SELECT * FROM `posts` IN (4, 3, 2)

But the code above gives an output like:

SELECT * FROM `posts` IN (4, 3, 2,)

So I have to get rid of the comma at the end. How can I make sure the script disables the comma at the last row.

Solution

Use $sql_query_posts = substr($sql_query_posts, 0, -1); like this:

$sql_query_posts .= " IN (";
while ( $row_form_permissions = mysql_fetch_assoc($sql_form_permissions) ) { 
    $sql_query_posts .= $row_form_permissions['form_id'] . ",";
}
$sql_query_posts = substr($sql_query_posts, 0, -1);
$sql_query_posts .= ")";

Just to address the case when you might end up with no records fetched (an empty array), it would maybe be wiser to use implode(), like this (I always use it like that):

$ins = array();
while ( $row_form_permissions = mysql_fetch_assoc($sql_form_permissions) ) {
    $ins[] = $row_form_permissions['form_id'];
}
$sql_query_posts .= sprintf(' IN (%s)', implode(',', $ins));

Answered By – Mladen B.

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published