mySQL Queries in WordPress

2 Views

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

Read More
// 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";
}

$cat in the function attribute is the catalogue number of the item I am querying and the output is “There are 2 bears”.

I put this in WordPress and the webpage hangs. Then through Google searching, I 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";
}

Now the output is “There are bears” (missing the $stock value). 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?

Post Views: 2

Related posts

Leave a Reply

2 comments

  1. Try:

    function QueryStock($cat) {
    $query = "SELECT * FROM stock WHERE catno = " . $cat;
    global $wpdb;
    $row = $wpdb->get_row($query);
        $catno = $row->catno; //Column name in table
        $itemname = $row->itemname; //Column name in table
        $price = $row->price; //Column name in table
        $stock = $row->stock; //Column name in table

    echo "There are " . $stock . " bears";
}

    Also, I would advise against using “SELECT *”. Try to query only for the columns used. This is better for performance.

    Edit: saw a typo. You had “$catno == $row…” in your code. You should only use one “=”

  2. Eureka!!!,

    It’s often something missed, and it was in the MySQL query.

    The query should be SELECT * FROM stock WHERE catno = '$cat' making $query = "SELECT * FROM stock WHERE catno = '$cat'";

    I have taken on board the “SELECT *” issue @eckes and @rickonline pointed out, went for separate queries and used $wpdb->get_var().

    Working coding is..

    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";
}