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. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which one is best for you. — John Conde
– John Conde, Commented Apr 10, 2017 at 11:42
Your script is at risk of SQL Injection Attack Have a look at what happened to Little Bobby Tables Even if you are escaping inputs, its not safe! Use prepared parameterized statements. — John Conde
– John Conde, Commented Apr 10, 2017 at 11:42
collection of where condition should not be enclosed by single quotes '".$filtr_zamestnanci_ID."' — JYoThI
– JYoThI, Commented Apr 10, 2017 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
– RiggsFolly, Commented Apr 10, 2017 at 11:47

Christopher Smit · Accepted Answer · 2017-04-10 12:00:01Z

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 = "";
}
?>

JYoThI · Accepted Answer · 2017-04-10 11:56:02Z

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"

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related