[FIXED] How to fetch 3 columns from the same row in the result?

Issue

I'm trying to fix a code in an osCommerce store which is giving the following error on PHP 5.4:

mysql_result(): supplied argument is not a valid MySQL result resource

This is the code:

$products = $cart->get_products();
    for ($i=0, $n=sizeof($products); $i<$n; $i++) {
    $id_produto = (INT)$products[$i]['id'];
    $sql = tep_db_query("SELECT p.manufacturers_id,m.manufacturers_cep,m.manufacturers_name FROM products p
    LEFT JOIN manufacturers m ON m.manufacturers_id = p.manufacturers_id
    WHERE p.products_id = '$id_produto'")OR DIE(mysql_error());
    $id_fabricante = mysql_result($sql,'0','manufacturers_id');
    $cep_fabricante = mysql_result($sql,'0','manufacturers_cep');
    $nome_fabricante = mysql_result($sql,'0','manufacturers_name');

    $id_fabricantes[$id_fabricante]['peso'] += $products[$i]['quantity']*$products[$i]['weight'];
    $id_fabricantes[$id_fabricante]['cep'] = $cep_fabricante;
    $id_fabricantes[$id_fabricante]['nome'] = $nome_fabricante;

    }

I tried to change it and there are no more errors, but it's still not working. Is this the correct way to do it?

$products = $cart->get_products();
for ($i=0, $n=sizeof($products); $i<$n; $i++) {
$id_produto = (INT)$products[$i]['id'];
$sql = tep_db_query("SELECT p.manufacturers_id,m.manufacturers_cep,m.manufacturers_name FROM products p
LEFT JOIN manufacturers m ON m.manufacturers_id = p.manufacturers_id
WHERE p.products_id = '$id_produto'")OR DIE(mysql_error());

$row = mysqli_fetch_assoc($sql);
$id_fabricante = $row['manufacturers_id'];

$row = mysqli_fetch_assoc($sql);
$cep_fabricante = $row['manufacturers_cep'];

$row = mysqli_fetch_assoc($sql);
$nome_fabricante = $row['manufacturers_name'];
    
$id_fabricantes[$id_fabricante]['peso'] += $products[$i]['quantity']*$products[$i]['weight'];
$id_fabricantes[$id_fabricante]['cep'] = $cep_fabricante;
$id_fabricantes[$id_fabricante]['nome'] = $nome_fabricante;

}

Solution

No, this is not correct. If you check the manual, you will see that the second parameter is the row to fetch in your result set. In your original example, you are fetching data only from the first row - 0 - and nothing else.

In your mysqli code, you fetch a new row before every assignment so the data will be a mix of the values of different fields from different rows.

The correct way would be something like:

// fetch the first row in the result set
$row = mysqli_fetch_assoc($sql);

$id_fabricante = $row['manufacturers_id'];
$cep_fabricante = $row['manufacturers_cep'];
$nome_fabricante = $row['manufacturers_name'];

Apart from that you would need to add error handling to make sure there is a row.

You should also try to avoid running sql queries in a loop. You could probably get all rows in 1 query using for example mysql's IN clause and then you could loop over that result set.

Answered By - jeroen

Answer Checked By - Candace Johnson (PHPFixing Volunteer)