CODEWITHSUNDEEP
phpmysqlpdo
Ozzy
Ozzy

Reputation: 8312

Weird row-count return for PHP/MySQL PDO

I've checked this in phpMyAdmin, and it returned 1 result:

    SELECT id, name
      FROM tbl_product_category
INNER JOIN tbl_product_cat_basket AS basket
        ON id = basket.cat_id
     WHERE basket.product_id = 2
     LIMIT 0,1

I then checked this in phpMyAdmin, and it returned COUNT = 1:

    SELECT COUNT(*)
      FROM tbl_product_category
INNER JOIN tbl_product_cat_basket AS basket
        ON id = basket.cat_id
     WHERE basket.product_id = 2
     LIMIT 0,1

Now I don't understand why this causes $rows to return empty string or null:

function getProductCategory($product_id) {
    $sql = "SELECT id, name FROM tbl_product_category
        INNER JOIN tbl_product_cat_basket AS basket
                ON id = basket.cat_id
             WHERE basket.product_id = ?
             LIMIT 0,1;";
    $data = array((int)$product_id);
    $rows = dbRowsCount($sql, $data);
logErrors("getProductCategory(".$product_id.") returned rows: ".$rows);
    if ($rows == 1) {
        dbQuery($sql, $data);
        return dbFetch();
    } else {
        return null;
    }
}

The logErrors method gives me this output:

getProductCategory(1) returned rows:
getProductCategory(2) returned rows:
getProductCategory(3) returned rows:
getProductCategory(4) returned rows:
getProductCategory(5) returned rows:

and so I'm returning null from the method.

My dbRowsCount(...) method converts the SQL to do a SELECT COUNT(*), then dbQuery(...) executes the real query. This is strange because the dbRowsCount(...) so far has always returned the correct rows for my other queries.

Here it is anyway:

function dbRowsCount($sql, $data) {
    global $db, $query;
    $regex = '/^SELECT\s+(?:ALL\s+|DISTINCT\s+)?(?:.*?)\s+FROM\s+(.*)$/i';
    if (preg_match($regex, $sql, $output) > 0) {
        $query = $db->prepare("SELECT COUNT(*) FROM {$output[1]}");
        $query->setFetchMode(PDO::FETCH_NUM);
        if ($data != null)
            $query->execute($data);
        else
            $query->execute();
        if (!$query) {
            echo "Oops! There was an error: PDOStatement returned false.";
            exit;
        }
        return $query->fetchColumn();
    }
    return false;
}

So what's gone wrong?

Edit

Here is some output from my program: [Original Query] [Query after regex]

SELECT id, name FROM tbl_product_category INNER JOIN tbl_product_cat_basket AS basket ON id = basket.cat_id WHERE basket.product_id = ? LIMIT 0,1;
SELECT COUNT(*) FROM tbl_product_category

getProductCategory(1) returned rows: 7
SELECT id, name FROM tbl_product_category INNER JOIN tbl_product_cat_basket AS basket ON id = basket.cat_id WHERE basket.product_id = ? LIMIT 0,1;
SELECT COUNT(*) FROM tbl_product_category

getProductCategory(2) returned rows: 7
SELECT id, name FROM tbl_product_category INNER JOIN tbl_product_cat_basket AS basket ON id = basket.cat_id WHERE basket.product_id = ? LIMIT 0,1;
SELECT COUNT(*) FROM tbl_product_category

getProductCategory(3) returned rows: 7
SELECT id, name FROM tbl_product_category INNER JOIN tbl_product_cat_basket AS basket ON id = basket.cat_id WHERE basket.product_id = ? LIMIT 0,1;
SELECT COUNT(*) FROM tbl_product_category

getProductCategory(4) returned rows: 7
SELECT id, name FROM tbl_product_category INNER JOIN tbl_product_cat_basket AS basket ON id = basket.cat_id WHERE basket.product_id = ? LIMIT 0,1;
SELECT COUNT(*) FROM tbl_product_category

getProductCategory(5) returned rows: 7
SELECT id, name FROM tbl_product_category INNER JOIN tbl_product_cat_basket AS basket ON id = basket.cat_id WHERE basket.product_id = ? LIMIT 0,1;
SELECT COUNT(*) FROM tbl_product_category

getProductCategory(6) returned rows: 7
SELECT id, name FROM tbl_product_category INNER JOIN tbl_product_cat_basket AS basket ON id = basket.cat_id WHERE basket.product_id = ? LIMIT 0,1;
SELECT COUNT(*) FROM tbl_product_category

getProductCategory(7) returned rows: 7
SELECT id, name FROM tbl_product_category INNER JOIN tbl_product_cat_basket AS basket ON id = basket.cat_id WHERE basket.product_id = ? LIMIT 0,1;
SELECT COUNT(*) FROM tbl_product_category

getProductCategory(8) returned rows: 7
SELECT id, name FROM tbl_product_category INNER JOIN tbl_product_cat_basket AS basket ON id = basket.cat_id WHERE basket.product_id = ? LIMIT 0,1;
SELECT COUNT(*) FROM tbl_product_category

getProductCategory(9) returned rows: 7
SELECT * FROM tbl_store_admin WHERE php_sesskey=?;
SELECT COUNT(*) FROM tbl_store_admin WHERE php_sesskey=?;

SELECT * FROM tbl_menu_cat_basket WHERE menu_id=?;
SELECT COUNT(*) FROM tbl_menu_cat_basket WHERE menu_id=?;

SELECT * FROM tbl_menu_cat_basket WHERE menu_id=?;
SELECT COUNT(*) FROM tbl_menu_cat_basket WHERE menu_id=?;

SELECT * FROM tbl_menu_cat_basket WHERE menu_id=?;
SELECT COUNT(*) FROM tbl_menu_cat_basket WHERE menu_id=?;

SELECT * FROM tbl_menu_cat_basket WHERE menu_id=?;
SELECT COUNT(*) FROM tbl_menu_cat_basket WHERE menu_id=?;

SELECT * FROM tbl_menu_cat_basket WHERE menu_id=?;
SELECT COUNT(*) FROM tbl_menu_cat_basket WHERE menu_id=?;

SELECT * FROM tbl_menu_cat_basket WHERE menu_id=?;
SELECT COUNT(*) FROM tbl_menu_cat_basket WHERE menu_id=?;

SELECT * FROM tbl_menu_cat_basket WHERE menu_id=?;
SELECT COUNT(*) FROM tbl_menu_cat_basket WHERE menu_id=?;

SELECT * FROM tbl_product ORDER BY last_update ASC;
SELECT COUNT(*) FROM tbl_product ORDER BY last_update ASC;

Upvotes: 1

Views: 377

Answers (2)

Kep
Kep

Reputation: 5857

Your RegeExp doesn't match, it's missing the dot-all flag (/is in total).

This causes . to also match newlines in your string.

Upvotes: 2

jeroen
jeroen

Reputation: 91744

It seems your regex is not returning the correct result. As you are only looking for the first occurrence of FROM and return everything after that, why don't you just use stristr?

$output = stristr($sql, 'FROM');    // $output includes 'FROM'
if ($output !== false)
{
    $query = $db->prepare("SELECT COUNT(*) {$output}");
    // etc.

Upvotes: 1

Related Questions