0

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 &pound;" . $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 &pound;" . $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?

  • 2
    what is the datatype of catno? your first query treats it as an integer, while your second set of queries is enclosing it in single quotes as if it were a string. also, always enable debugging while you are developing, so you can see error and warning messages which will help you debug your code. – Milo Feb 16 '15 at 23:28
0

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 &pound;" . $price;
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.