how to use php variable generated by loop in the mysql query

Question

I need little help. Please how can I get the PHP value "$filtr_zamestnanci_ID" to the sql_query. Code here :

<?php  
if (isset($_POST["filtr_zamestnanci_ID"])) {
    for ($a = 0; $a < count($_POST["filtr_zamestnanci_ID"]); $a++) {
        $filtr_zamestnanci_ID .="AND companies_text_records_user_ID = '".$_POST["filtr_zamestnanci_ID"][$a]."'&nbsp;";
    }
}else {
    $filtr_zamestnanci_ID = "";
}

echo "filtr_zamestnanci_ID :".$filtr_zamestnanci_ID;

mysql_query("SET CHARACTER SET utf8"); 
$sql_1 =
    mysql_query("SELECT * FROM companies_text_records
        LEFT JOIN companies ON companies_text_records_company_ID = company_ID
        LEFT JOIN login_users ON user_id = companies_text_records_user_ID
        WHERE companies_text_records_relative_to = '0'
        '".$filtr_zamestnanci_ID."'
        ORDER BY companies_text_records_ID DESC");
?>

If I pass it without loop everything is OK. But output from loop don´t work at all. Maybe something in the formatting of "$filtr_zamestnanci_ID" ?

FYI, [you shouldn't use `mysql_*` functions in new code](http://stackoverflow.com/questions/12859942/). They are no longer maintained [and are officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). See the [red box](http://php.net/manual/en/function.mysql-connect.php)? Learn about [*prepared statements*](https://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://php.net/manual/en/mysqlinfo.api.choosing.php) will help you decide which one is best for you. — John Conde, Apr 10 '17 at 11:42
Your script is at risk of [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Have a look at what happened to [Little Bobby Tables](http://bobby-tables.com/) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). — John Conde, Apr 10 '17 at 11:42
collection of where condition should not be enclosed by single quotes '".$filtr_zamestnanci_ID."' — JYoThI, Apr 10 '17 at 11:45
echo the SQL that you generate, you will probably see no spaces between the end of one line and the `AND` so try ` $filtr_zamestnanci_ID .=" AND ....` and loose the ` ` from the end — RiggsFolly, Apr 10 '17 at 11:47

Christopher Smit · Answer 1 · 2017-04-10T12:00:01.813

Try the following. Will also sort out your SQL Injection issues:

<?php
    $conn = new PDO("mysql:host=$hostname;dbname=$db_name;charset=utf8mb4", $db_username, $db_password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

if (isset($_POST["filtr_zamestnanci_ID"])) {
    for ($a = 0; $a < count($_POST["filtr_zamestnanci_ID"]); $a++) {
        $filtr_zamestnanci_ID = $_POST["filtr_zamestnanci_ID"][$a];

        $stmt = $conn->prepare("SELECT * FROM companies_text_records
                            LEFT JOIN companies ON companies_text_records_company_ID = company_ID
                            LEFT JOIN login_users ON user_id = companies_text_records_user_ID
                        WHERE companies_text_records_relative_to = '0'
                        AND companies_text_records_user_ID = :company_text_records_user_id
                        ORDER BY companies_text_records_ID DESC");

        if ($stmt->execute(array(':company_text_records_user_id' => $filtr_zamestnanci_ID))) {
            while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
                $someField = $row['columnFromDatabase'];
            }
            echo 'success';
        }
    }
}else {
    $filtr_zamestnanci_ID = "";
}
?>

score 0 · Answer 2 · answered Apr 10 '17 at 11:56

Warning mysql_query, mysql_fetch_array,mysql_connect etc.. extensions were deprecated in PHP 5.5.0, and it was removed in PHP 7.0.0. Instead, the MySQLi or PDO_MySQL extension should be used.

1) Give space before AND

$filtr_zamestnanci_ID .=" AND companies_text_records_user_ID = '".$_POST["filtr_zamestnanci_ID"][$a]."'";

2) remove single quotes enclosed for additionally added where clause '".$filtr_zamestnanci_ID."'

"SELEC‌T * FROM companies_text_records LEFT JOIN companies ON companies_text_records_company_ID = company_ID LEFT JOIN login_users ON user_id = companies_text_records_user_ID WHERE companies_text_records_relative_to = '0' ".$filtr_zamestnanci_ID." ORDER BY companies_text_records_ID DESC"

how to use php variable generated by loop in the mysql query

2 Answers2