Mysql convert to mysqli query problem

PHP
1

I’m rebuilding my website. All the Mysql have to be converted to mysqli.
But I stumple on 2 problems.

  • When I use fetch_assoc and num_row in 1 query, only 1 of the two will work.
  • I also have a hard time finding the problem in my query, in mysql it works, but in mysqli not.

Original code

  $productsql = "
        Select
            producten.idproduct,
            producten.productlink,
            prod_categorie.idcategorieen,
            prod_omschrijving.producttitel,
            producten.prijs,
            categorieen.catnaam
        FROM
            categorieen, producten
        INNER JOIN
            prod_omschrijving 
        ON
            producten.idproduct=prod_omschrijving.idproduct
        INNER JOIN
            prod_categorie
        ON
            producten.idproduct=prod_categorie.idproduct
        WHERE
            prod_categorie.idcategorieen=categorieen.idcategorieen
        AND
            prod_omschrijving.taal='".$lang."'
        AND
            producten.idproduct = '".$bestelregelcheck['productid']."'
    ";
        $productqry = mysql_query($productsql, $dbwebwinkel);    
        $product = mysql_fetch_assoc($productqry);

new code

$productqry = "
		Select
			producten.idproduct,
			producten.productlink,
			prod_categorie.idcategorieen,
			prod_omschrijving.producttitel,
			producten.prijs
		FROM
			producten
		INNER JOIN
			prod_omschrijving 
		ON
			producten.idproduct=prod_omschrijving.idproduct
		INNER JOIN
			prod_categorie
		ON
			producten.idproduct=prod_categorie.idproduct
		WHERE
			
			prod_omschrijving.taal='".$lang."'
		AND
			producten.idproduct = '".$bestelregelcheck['productid']."'
	";
	
	$productstmt = $connectionwebshop->prepare($productqry);				
	$productstmt->execute();
	$productstmt->bind_result($productid, $productlink, $idcategorieen, $producttitel, $prijs);
	$productstmt->fetch();
	$productstmt->close();
2

I don’t see you use any of these two?

3

Yes as @donboe said i don’t see you use fetch_assoc or num_row in your query.
But here is the code what i think is right for your problem

$productqry = "
Select
producten.idproduct,
producten.productlink,
prod_categorie.idcategorieen,
prod_omschrijving.producttitel,
producten.prijs
FROM
producten
INNER JOIN
prod_omschrijving
ON
producten.idproduct=prod_omschrijving.idproduct
INNER JOIN
prod_categorie
ON
producten.idproduct=prod_categorie.idproduct
WHERE
prod_omschrijving.taal=(?)
AND
producten.idproduct = (?)";

$productstmt = $connectionwebshop->prepare($productqry);
$productstmt->bind_param(“ss”, $lang,$bestelregelcheck[‘productid’]);
$productstmt->execute();
$productstmt->bind_result($productid, $productlink, $idcategorieen, $producttitel, $prijs);
$productstmt->fetch(productstmt);
$productstmt->close($connectionwebshop);

4

I have one question. When changing, why not skip mysqli all together and start using PDO

5

Thank you very much for all the replies.
I needed to make a choice between mysqli and pdo. With mysqli I did not had to make that many changes, it looked easyer and I could find more on the internet about this. So thats the reason I went for mysqli.
For now I changed 60% of my websites, so still a lot of work.

I made a mistake with copying, it was late, my apoligisch for this. The question is about 2 queries not one
The first query does not work because of this part:

FROM categorieen, producten

form the original:

$productsql = "
        Select
            producten.idproduct,
            producten.productlink,
            prod_categorie.idcategorieen,
            prod_omschrijving.producttitel,
            producten.prijs,
            categorieen.catnaam
        FROM
            categorieen, producten
        INNER JOIN
            prod_omschrijving 
        ON
            producten.idproduct=prod_omschrijving.idproduct
        INNER JOIN
            prod_categorie
        ON
            producten.idproduct=prod_categorie.idproduct
        WHERE
            prod_categorie.idcategorieen=categorieen.idcategorieen
        AND
            prod_omschrijving.taal='".$lang."'
        AND
            producten.idproduct = '".$bestelregelcheck['productid']."'
    ";
        $productqry = mysql_query($productsql, $dbwebwinkel);    
        $product = mysql_fetch_assoc($productqry);

This what I have so far, you can see i removed the categorieenpart, because iI can’t get it working, but I still need it.

$productqry = "
		Select
			producten.idproduct,
			producten.productlink,
			prod_categorie.idcategorieen,
			prod_omschrijving.producttitel,
			producten.prijs
		FROM
			producten
		INNER JOIN
			prod_omschrijving 
		ON
			producten.idproduct=prod_omschrijving.idproduct
		INNER JOIN
			prod_categorie
		ON
			producten.idproduct=prod_categorie.idproduct
		WHERE
			prod_omschrijving.taal='".$lang."'
		AND
			producten.idproduct = '".$bestelregelcheck['productid']."'
	";
	$productstmt = $connectionwebshop->prepare($productqry);				
	$productstmt->execute();
	$productstmt->bind_result($productid, $productlink, $idcategorieen, $producttitel, $prijs);
	$productstmt->fetch();
	$productstmt->close();

The second problem is with this query

$bestelregelcheckqry ="
            SELECT
                productid,
                aantal
            FROM
                bestelregel
            WHERE
                bestelnummer     =     ?
            AND
                aantal            !=    '0'
            ";
        $regelstmt = $connectionwebshop->prepare($bestelregelcheckqry);                
        $regelstmt->error;
        $regelstmt->bind_param('i', $ordernr);
        $regelstmt->execute();
        $regelstmt->store_result();
        $checkregelrows = $regelstmt->num_rows;
        $result = $regelstmt->get_result();
            
        if($checkregelrows >= 1)
        {
        $inhoudwinkelwagen    = $winkelwagenkopjes;
        while($bestelregelcheck = $result->fetch_assoc())

I’m not allowed to use the nuw_rows and the fetch_assoc.
If I remove the fetch assoc part, $checkrows will be giving a number higher then 1, when the fetch_assoc is still in the script the $checkrows is 0.
When the part shown below is present, the fetch_assoc is not working, it just gives nothing in return.
When I remove this part, the fetch_assoc is working fime
part

        $regelstmt->store_result();
        $checkregelrows = $regelstmt->num_rows;
6

Is there any reason you’re using store_result() rather than just executing the query then fetching results?

$regelstmt->execute();
$result = $regelstmt->get_result();
$rows = $result->num_rows;
if ($rows > 0) {
  while ($row = $result->fetch_assoc()) {
    .. do some things
    }
  }

(I am learning with PDO, so I might just not understand the question.)

1 Like
7

thanks this solveds the second part of my question.