php using variable in loop

Question

Another questions related to the use of variables in loops. I have two mysql databases with the following columns:

country_db (that's the name of the db) country_name country_cit_rate country_level

company_db (that's the name of the db) company_name company_country company_level

I want to make a number of calculations for each company (defined by "company_name") and these calculations should run from the highest level ("company_level") to the company_level with the number 1.

Therefore I created the following loop ($level_max is previously defined as the highest company_level in the company_db):

do 
{
echo "The number is: $level_max <br>";

$company_variables_array = mysqli_query($con,"SELECT company_name, company_country FROM company_db WHERE company_level=$level_max");

while($company_variables_fetch = mysqli_fetch_array($company_variables_array))
    {
        $company_name = $company_variables_fetch['company_name'];
        $company_country = $company_variables_fetch['company_country'];

        echo $company_name;
        echo $company_country;

    }

$level_max--;
}
while ($level_max>0);

By using the echo statements I can see that the code works exactly as I want it.

However, I've been struggling two days with the next step. For each company IN THE LOOP I would like to obtain the related value in the column "country_cit_rate" of the "country_db". Since $company_country has been defined within the loop I expected this to be an easy step but I get the following error message: "Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in XXX on line XXX"

I used to following code:

do 
{
echo "The number is: $level_max <br>";

$company_variables_array = mysqli_query($con,"SELECT company_name, company_country FROM company_db WHERE company_level=$level_max");

while($company_variables_fetch = mysqli_fetch_array($company_variables_array))
    {
        $company_name = $company_variables_fetch['company_name'];
        $company_country = $company_variables_fetch['company_country'];

echo $company_name;
echo $company_country;

        $company_country_cit_rate_array = mysqli_query($con,"SELECT country_cit_rate FROM country_db WHERE country_name=$company_country");
        $company_country_cit_rate_fetch = mysqli_fetch_array($company_country_cit_rate_array);
        $company_country_cit_rate = $company_country_cit_rate_fetch['country_cit_rate'];
        echo $company_country_cit_rate . "%";
    }

$level_max--;
}
while ($level_max>0);

What am I doing wrong?

The reason may very well be because of this `WHERE country_name=$company_country");` that stands to be a string, therefore you need to quote the variable `WHERE country_name='$company_country'");` the boolean error explains it. Had it been an `int`, then it would have worked, but I highly doubt that it is. — Funk Forty Niner, Oct 22 '14 at 23:33
Edit this line to include the following: `$company_country_cit_rate_array = mysqli_query($con,"SELECT country_cit_rate FROM country_db WHERE country_name=$company_country") or die(mysqli_error($con));` and post the results. My guess is the the query is failing. — Matt Clark, Oct 22 '14 at 23:34
Great, thanks! It was exactly what Fred -ii- suggested, thanks all :-))) — dathiezer, Oct 22 '14 at 23:36
You're welcome. I'll make it an answer and you can accept it and we can close the question and mark it as solved. @dathiezer Here's how => **http://meta.stackexchange.com/a/5235/** — Funk Forty Niner, Oct 22 '14 at 23:37

score 2 · Accepted Answer · edited May 23 '17 at 12:20

Comment to answer:

The reason may very well be because of this WHERE country_name=$company_country"); which stands to be a string, therefore you need to quote the variable
WHERE country_name='$company_country'");

The boolean error explains it.

Had it been an int, then it would have worked, but I highly doubt that it is.

Additionally, I would like to point out that, your present code is open to SQL injection.
Use prepared statements, or PDO with prepared statements, they are much safer.

You should add or die(mysqli_error($con)) to mysqli_query() to help troubleshoot.

score 0 · Answer 2 · answered Oct 22 '14 at 23:31

mysqli_query is returning false because there was an error.

You can inspect the error by calling mysqli_error($con) after mysqli_query;

As an aside you should not be building literal query strings with variables like that. I'm not going into the "use PDO" dogma rant, but if you're using mysqli you should at least use prepared statements and bound parameters http://us1.php.net/manual/en/mysqli-stmt.bind-param.php

php using variable in loop

2 Answers2