sqlquery on works in builders

[jallison]

Greetings all,

I have two tables in a mysql database:

• category - a unique list of all major catalog items with a number and description, like:

  cat_num/description
  001, Tables
  002, Chairs
  003, Couches
  etc.

• catalog - all the items in stock, their full catalog number, order to display in, and description, like:

  cat_num/order/model
  003.010, 0, Sectionals
  003.011, 1, 2-Piece Sectional
  003.012, 2, 3-Piece Sectional
  003.013, 3, 4-Piece Sectional
  003.020, 0, Love Seats
  003.030, 0, Overstuffed
  etc.

A page is called from a navigational menu using the catalog's catalog number, such as: xxx.php?003.010

At the top of each page is a header with the category description and item name, such as: Couches - Sectionals

This requires a query that joins the two tables on the category catalog number and the left 3 characters of the catalog catalog number. Then I want to get the name of the first matching item in the catalog to display. So I use this query:

   $sqlquery = "SELECT category.cat_num, category.description, catalog.model, catalog.order ".
               "FROM catalog ".
               "RIGHT JOIN category ON left(catalog.cat_num,3) = category.cat_num ".
               "WHERE ((category.cat_num='003') AND (catalog.order=1))";

The problem is that this query works in MS Access, it works in Navicat, it works in DBManager, but it does not work in real life!

Does anyone have any suggestions to try? TIA!!

John

[jallison]

This has really been driving me crazy!!

This is what I am down to:

$sqlquery = "SELECT category.description FROM category WHERE (category.cat_num = '003')";
$result = mysql_query($sqlquery);
if ($result)
{
   echo "good news";
   mysql_free_result($result);
}
else
{
   echo "bad news";
}

Just a simple table lookup. Works in all of the programs except on the actual website. I'm always getting "bad news".

Anybody??

John

[swr]

Instead of:
echo "bad news";
try:
echo mysql_error();

That will give you more info on why the SQL failed.

The usual way to do it is in the same breath as mysql_query:
$result = mysql_query(...) or die(mysql_error());

[jallison]

swr,

I usually use mysql_error(); but when I don't suspect sql errors I never bother...I usually echo comments every other line or so to trace the sequence of events.

In this case I have to admit that something that simple made all the difference... No database selected. I've been incrementally updating about 50 php pages so all the code is commented the same, looks the same, and is the same order. Somehow, on this one file, I left out the line of code which opened the database.

So...Thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you!

John