Melbourne2991 Melbourne2991 - 6 days ago 6
MySQL Question

mySQLi query producing null values although query works outside of function

This one's driving me round the bend, and just can't work it out. The script works fine up until when I try to echo the final mysqli query (zen_id). I have tested the query outside of the if and while statements and it works fine. The query before it also has no troubles retrieving

$model_array['model']
and echoes it flawlessly. I don't get any errors, simply nothing is outputted. A vardump of
$id_array['zen_system_id']
gives me null, as does
$id_array
,
$result_2
also produces a large number of null values in this format:
object(mysqli_result)#5 (5) { ["current_field"]=> int(0) ["field_count"]=> int(1) ["lengths"]=> NULL ["num_rows"]=> int(0) ["type"]=> int(0) }
I think i'm probably misssing something quite obvious here I'm just lost at the moment, possibly something to do with the while function or something??

//Connect to Database
$mysqli = mysqli_connect("localhost", "login", "user", "database");
//Check Connection
if ($mysqli->connect_errno) {
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

$result_1 = mysqli_query($mysqli,"SELECT `product_id` FROM oc_product");


while ($rows_1 = mysqli_fetch_array($result_1))
{

$product_id = $rows_1['product_id'];

$result = mysqli_query($mysqli,"SELECT `product_id` FROM `oc_product_option_value` WHERE `product_id`=$product_id");

$rows = mysqli_fetch_array($result);

//echo $product_id . " " . $rows['product_id'] . "</br>";

if($rows['product_id'] == null)
{

$result = mysqli_query($mysqli,"SELECT `model` FROM `oc_product` WHERE `product_id`=$product_id");
$model_array = mysqli_fetch_array($result);
$model = $model_array['model'] . "</br>";

//echo $model;

$result_2 = mysqli_query($mysqli,"SELECT `zen_system_id` FROM `oc_wholesale_link` WHERE `model_id`='" . $model . "'");
$id_array = mysqli_fetch_array($result_2);
echo $zen_id = $id_array['zen_system_id'];
}

}

Answer

try to encapsulate data which you are passing to sencond query, also, to check if query would return result you can just make an if condition to the fetch itself

$result = mysqli_query($mysqli,"SELECT `product_id` FROM `oc_product_option_value` WHERE `product_id`= '".$product_id."'");
if($rows = mysqli_fetch_array($result))
{
    $result = mysqli_query($mysqli,"SELECT `model` FROM `oc_product` WHERE `product_id`=  '".$product_id."'");
    $model_array = mysqli_fetch_array($result);
    $model = $model_array['model'];

    //echo $model;

    $result_2 = mysqli_query($mysqli,"SELECT `zen_system_id` FROM `oc_wholesale_link` WHERE `model_id`='" . $model . "'");
    $id_array = mysqli_fetch_array($result_2);
    echo $zen_id = $id_array['zen_system_id'];
}
else
{
    echo 'Product Not Found';
}

I've changed this line $model = $model_array['model'] . "</br>"; i just supposed <br> was there for debuggin purpose.

UPDATE

Since you only need to get products id wich doesn't exist in oc_product_option_value you can change the query to this

$result = mysqli_query($mysqli,"SELECT `product_id` FROM `oc_product_option_value` WHERE `product_id`= '".$product_id."'");
if($rows = mysqli_fetch_array($result))
{
    $result = mysqli_query($mysqli,"SELECT `model` FROM `oc_product` WHERE `product_id`=  '".$product_id."'");
    $model_array = mysqli_fetch_array($result);
    $model = $model_array['model'];

    //echo $model;

    $result_2 = mysqli_query($mysqli,"SELECT `zen_system_id` FROM `oc_wholesale_link` WHERE `model_id`='" . $model . "'");
    $id_array = mysqli_fetch_array($result_2);
    echo $zen_id = $id_array['zen_system_id'];
}
else
{
    echo $product_id . '<br>';
}
Comments