MySQL queries in Wordpress

Question

I have a MySQL database table called stock which holds a stock record on items for sale. When querying the database using PHP I have no issue, but when I try to do it through WordPress it doesn't work.

my PHP is as follows

// Query the stock level
function QueryStock($cat) {
    $query = "SELECT * FROM stock";
    $result = mysql_query($query);

    if (!$result) die ("Database access failed: " . mysql_error());
    $rows = mysql_num_rows($result);
    for ($j = 0 ; $j < $rows ; ++$j)
    {
        $row = mysql_fetch_row($result);
        if ($row[0] == $cat) {
            $catno=$row[0];
            $supplier=$row[1];
            $itemname=$row[2];
            $category=$row[3];
            $price=$row[4];
            $stock=$row[5];
        }
    }
    mysql_close($db_server);

    echo "There are " . $stock . " bears, the price of each bear is &pound;" . $price;
}

$cat in the function attribute is the catalogue number string of the item I am querying and the output is "There are 2 bears, the price of each bear is £4.99".

I put this in WordPress and the webpage hangs. Through Google searching, I then found out about the $wpdb object being used by WordPress at designmodo.com. So with some code condensing too, I changed it to...

function QueryStock($cat) {
    $query = "SELECT * FROM stock WHERE catno = " . $cat;
    global $wpdb;
    $row = $wpdb->get_row($query, ARRAY_N);
        $catno == $row[0];
        $itemname = $row[2];
        $price = $row[4];
        $stock = $row[5];

    echo "There are " . $stock . " bears, the price of each bear is £" . $price;
}

Now the output is "There are bears, the price of each bear is £" (missing the $stock and $price values).

As a workaround, I could use the following

function QueryStock($cat) {
    $query1 = "SELECT catno FROM stock WHERE catno = '$cat'";
    $query2 = "SELECT itemname FROM stock WHERE catno = '$cat'";
    $query3 = "SELECT price FROM stock WHERE catno = '$cat'";
    $query4 = "SELECT stock FROM stock WHERE catno = '$cat'";

    global $wpdb;

    $catno = $wpdb->get_var($query1);
    $itemname = $wpdb->get_var($query2);
    $price = $wpdb->get_var($query3);
    $stock = $wpdb->get_var($query4);

    echo "There are " . $stock . " bears, the price of each bear is £" . $price;
}

and this works, but it doesn't solve my problem of dealing with an array output from a MySQL query.

I have been doing loads of Google searching to get where I am and I am having problems understanding it. Where am I going wrong?

Answer 1

I found the errors thanks to the help from @Milo

@Milo's query on catno pointed me to my error with my first query involving catno, and I also noticed a double equals in $catno == $row[0]; when it should be $catno = $row[0];

Final coding is

function QueryStock($cat) {
    $query = "SELECT * FROM stock WHERE catno = '$cat'";
    global $wpdb;
    $row = $wpdb->get_row($query, ARRAY_N);
        $catno = $row[0];
        $itemname = $row[2];
        $price = $row[4];
        $stock = $row[5];

    echo "There are " . $stock . " bears, the price of each bear is £" . $price;
}