what's wrong with this join query?

[co.ador]

 

I am having a warning which indicates there is a not valid mysql result, I think the problems lay down at the WHERE clause, but I am not sure.

 

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /storeprueba/sidebar.php on line 21

 

$categoryurl = $_GET['categoryurl'];
$sql= mysql_query("SELECT * FROM products, categories WHERE products.category = '$categoryurl' DESC LIMIT 6");
$productCount = mysql_num_rows($sql); // line 21
if ($productCount>0 )
     {
     while($row = mysql_fetch_array($sql))
	          {
	            $id= $row["id"];
				$product_name= $row["product_name"];
				$price = $row["price"];
				$category = $row["category"];
				$subcategory = $row["subcategory"];
				$location = $row["location"];
				$date_added = strftime("%b %d, %Y", strtotime($row["date_added"]));

 

 

thanks.

[KevinM1]

Are you certain that $_GET['categoryurl'] exists?

[co.ador]

yes from index.php I send this values in the url as below

 

index.php

 
<?php 
$sql2= mysql_query("SELECT * FROM categories ORDER BY category DESC ");
$categoryCount = mysql_num_rows($sql2); 
         if ($categoryCount>0 )
              {
       while($row = mysql_fetch_array($sql2))
           {
	        $idc= $row["id"];
	        $category= $row["category"];
                           $dynamiccategory .=
                                 '<p>
                                 <a href="storeprueba/sidebar.php?categoryurl='. $category .'">' . $category . '</a>
                                 </p>'; 
}
}
            ?>

 

then when click there is a url in sidebar.php like

 

storeprueba/sidebar.php?categoryurl=Phones

 

 

in sidebar.php I am doing as fallows

 

<?php 
$categoryurl = $_GET['categoryurl'];
$sql= mysql_query("SELECT * FROM products WHERE products.category = '$categoryurl' DESC LIMIT 6");
$productCount = mysql_num_rows($sql); // line 21 where the error is.  ERROR line
if ($productCount>0 )
     {
     while($row = mysql_fetch_array($sql))
	          {
	            $id= $row["id"];
				$product_name= $row["product_name"];
				$price = $row["price"];
				$category = $row["category"];
				$subcategory = $row["subcategory"];
				$location = $row["location"];
				$date_added = strftime("%b %d, %Y", strtotime($row["date_added"]));
                   $dynamicList .= '

               <p >' . $product_name . ' </p>
                       <p >  $' . $price . '</p>' 
?>

<div>  
<p>
<?php echo $dynamicList; ?><br />
</p>
</div>

 

 

Then with that it throws a warning error code on line 21.

 

Wondering what is wrong here.

[Simmo]

echo the sql to see what it says.

 

$sql= mysql_query("SELECT * FROM products, categories WHERE products.category = '$categoryurl' DESC LIMIT 6");

 

echo $sql;

 

I can't see where you have joined either, try,

SELECT * FROM products, categories WHERE  products.categoryID = categories.categoryID  And products.category = '$categoryurl' DESC LIMIT 6");

 

I'm guessing the field names, but you get the idea.  This is assuming categories has a PK of categoryID

[co.ador]

i echoed the sql and nothing came out.

 

In the suggestion you had simo about categories id and products id, they are not the same in the database even though I think I might try to instead to join the table at the category field maybe create a numeric id in the products table so it matchs the id table in the categories instead of joining it at the category. Sometimes numbers works better than character when joining tables.

[co.ador]

did by the id,

 

I created a category_id field to match the id of the products table and it displays the same warning.

 

I am also echoing the $sql and it is not displaying any error message or hint.

 

<?php
$sql= mysql_query("SELECT * FROM products, categories WHERE categories.category_id = " . $id . "   DESC LIMIT 6");
echo $sql; ?>

[betterphp]

if you add

 

echo mysql_error();

 

after the failing query, it will tell you whats wrong with it )

[KevinM1]

You're not joining anything here.  Your statement is saying:

 

SELECT all categories from products and categories WHERE categories.category_id = something

 

Where's the part that references the products table?  You need something in your WHERE clause that ties one of the columns of products to one of the columns of categories.

[Simmo]

Example

 

Table called Product with fields ; productID categoryID, productName, productCost....ect (productID is a Primary Key)

Table called Category with fields; categoryID, categoryName

 

$sql = "SELECT * From Product, Category Where Product.categoryID = Category.categoryID AND Category.categoryName = '".$_GET['urlVar']."' ";

echo $sql;

$sqlresult = mysql_query($sql);

while ($row = mysql_fetch_array($sqlresult)){

 

///pull out the rows

 

 

}

 

That shouldn't be too far off

[co.ador]

The previous error was revealed by the function of mysql_error, and it was a unknown categories.category_id, instead I was making a mistake and it was products.category_id = " . $id . "". The error is not displaying, but nevertheless the fields i want to display are all from the products table, I only using category here to join the table but the display in the code below is coming all from the products table. all the fields like Product_name, price, category, subcategory etc... But somethiung is wrong here becuase the code some how is not displaying the products even when I know the products.category_id is in deed matching the $id variable. I think i have to instead of select * I have to specify the fields and display it in the dynamicList variable below the while loop specifying whether the field is coming from the products or categories table. Now How can I specify this in t he code so the query doesn't get confused to what display? Because even though the query is ok now, still it goes to the Else {" We have no products listed in our store yet " }

 

thank you guys, @Nightslyr the references to the products table is below but since the query is joining two tables I think it gets confused to what fields will display in the dynamicList variable

$sql= mysql_query("SELECT * FROM products, categories WHERE products.category_id = " . $id . "");
echo mysql_error();
$productCount = mysql_num_rows($sql); 
if ($productCount>0 )
     {
     while($row = mysql_fetch_array($sql))
	          {
	            $id= $row["id"];
				$product_name= $row["product_name"];
				$price = $row["price"];
				$category = $row["category"];
				$subcategory = $row["subcategory"];
				$location = $row["location"];
				$date_added = strftime("%b %d, %Y", strtotime($row["date_added"]));
                   $dynamicList .= '

<div class="dynamicList">

<div class="alone">
<div class="dynamicListF"><a href="product.php?id=' . $id . '"><strong>' . $product_name . '</strong></a></div>
<a href="product.php?id=' . $id . '">
<img style="border:#666 1px solid;" src="inventory_images/' . $id . '.jpg" alt="' . $product_name . '                   "align="left" width="178" height="230" border="1" /></a><br />
     
<div class="Divinfo">
     <div >
       <p >Price:</p>
       <p >  $' . $price . '</p> 
     </div>
  <div class="clear"></div>
     <div >
       <p >Category:</p>
       <p> ' . $category . '</p> 
     </div>
     <div>
 <div class="clear"></div>
        <p >Brand:</p>
        <p> ' . $subcategory. '</p> 
     </div>

   <div class="clear"></div>
  <div class="city">
        <p> ' . $location. '</p>  
      </div>

     </div>
  </div>  


</div>


</div>

}} else {
$dynamicList = "We have no products listed in our store yet";
}

[co.ador]

Example

 

Table called Product with fields ; productID categoryID, productName, productCost....ect (productID is a Primary Key)

Table called Category with fields; categoryID, categoryName

 

$sql = "SELECT * From Product, Category Where Product.categoryID = Category.categoryID AND Category.categoryName = '".$_GET['urlVar']."' ";

echo $sql;

$sqlresult = mysql_query($sql);

while ($row = mysql_fetch_array($sqlresult)){

 

///pull out the rows

 

 

}

 

That shouldn't be too far off

 

good point how can I pull out the products in the while loop or how can I specify in the loop from what table the fields are coming from. Becuase as you know there is an id field in each of the tables..

[co.ador]

Should I use mysql_fetch_assoc function instead of mysql_fetch_array ? since there are two different tables.

[KevinM1]

Why do you need to join them if everything you want is in products?  Wouldn't:

 

SELECT * FROM products WHERE category_id = $id

 

Suffice?

 

You join tables when you need to display or manipulate data from both of them.  If you're simply spitting out the data of one based on it's foreign key, just supply a value for that foreign key.

[Simmo]

A join is needed because he wants to pull out fields in each table, price, product name, category.

 

You can just use the field name in your $rows[''] and using category ID shouldn't be a problem because its going to be the same value in both tables

[KevinM1]

A join is needed because he wants to pull out fields in each table, price, product name, category.

 

If that's the case, he has bigger problems than just a failing query.

 

@co.ador: http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

[co.ador]

You are right,

 

I was joining but in deed I won't use any data from category. Category only has two fields id and category. Now I do need to match the id in the category with the category_id field in the products table but I having that from the url coming from index.php. You are right we only need products table..

 

SELECT * FROM products WHERE category_id = $idc

 

I put $idc because in another query above this one there is an $id variable and some how it is interfering in the Global scope. So I decided to rename it.

 

No I am taking your advide to only use that query above which I think it is great. Before I read your comment I was using

 

$sql= mysql_query("SELECT * FROM products, categories WHERE products.category_id = " . $idc . "");

 

And it worked, but to fully understand the one I have right now working, I wondering what would happen if in category there were a field called product_name as in the table products with this query "SELECT * FROM products, categories WHERE products.category_id = " . $idc . " ?

 

I would like to know to fully understand the differences in between your query and this one.

 

 

[Simmo]

As long as he uses the tables I gave as an example then they will be normalised. Though, I did forget to say to make sure the categoryID in the Category table is a Primary Key.  And to make things easire make sure the Primary Key in both tables have auto increment.

[KevinM1]

When you write an SQL query that tries to pull data from two or more tables, the WHERE clause will apply to ALL of them.  So, you're really asking:

 

SELECT * FROM products WHERE products.category_id = some id // <-- good query

AND

SELECT * FROM categories WHERE products.category_id = some id // <-- bad query, because products.category_id doesn't exist in the categories table

 

When joining tables, your WHERE clauses are very important.  They're what actually decides the returning data set.  Since you want to join some results from one table with another, you need to specify the link between them in the WHERE clause.

 

So, something like:

 

SELECT * FROM products, categories WHERE products.category_id = categories.id AND products.name = 'Fork'

 

Will work because of the AND.  The first comparison ties the tables together (you're joining them on the category id).  The second one narrows the result set down to just forks.

 

I hope this makes some sense.

[co.ador]

 

 

SELECT * FROM products, categories WHERE products.category_id = categories.id AND products.name = 'Fork'

 

 

Will work because of the AND.  The first comparison ties the tables together (you're joining them on the category id).  The second one narrows the result set down to just forks.

 

I hope this makes some sense.

 

That will make it more specific And products.product_name = 'Fork' but can it be join at product.category = categories.category instead of the id and product.category_id? what is better vs each other?

 

so far it is working charms joining it at the category_id...

[co.ador]

in the categories and products I have a category field in both of them, but in one block I just want to refer to the category field in the categories table how can I achieve that in the html with php?

 

<?php 
$dynamicList = "";
$sql= mysql_query("SELECT * FROM products, categories WHERE products.category_id = " . $idc . "");
echo mysql_error();
$productCount = mysql_num_rows($sql); 
if ($productCount>0 )
     {
     while($row = mysql_fetch_array($sql))
	          {
	            $id= $row["id"];
				$product_name= $row["product_name"];
				$price = $row["price"];
				$category = $row["category"];
				$subcategory = $row["subcategory"];
				$location = $row["location"];
				$date_added = strftime("%b %d, %Y", strtotime($row["date_added"]));
                   $dynamicList .= '

<div >
       <p >Category:</p>
       <p> ' . $category . '</p> 
     </div>
     <div>
 <div class="clear"></div>
        <p >Sub:</p>
        <p> ' . $subcategory. '</p> 
     </div>
';
}} else {
$dynamicList = "We have no products listed in our store yet";
}
?>

 

Over here at the div block there is a call for $category variable but thinking about that we have category fields in both tables how can I refer to the categories table category field in here.

 

 <div >
       <p >Category:</p>
       <p> ' . $category . '</p> 
     </div>
     <div>

 

I think i should change the while loop and specify some how the that the call for the category field as in here

$category = $row["category"];

 

make references to the categories table because as it is setup right now it will refer to either or field category field found in both tables.

 

Hopefully the query is fine, Well I am wondering if I will have to specify the fields at the query at the SELECT clause as categories.category and products.category.

 

help on that.

[KevinM1]

I'm not entirely sure what you want to display.  Can you list your tables, and highlight which columns you want from each?

[co.ador]

Table structure for table `products`

--

 

CREATE TABLE IF NOT EXISTS `products` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `product_name` varchar(225) NOT NULL,

  `price` varchar(16) NOT NULL,

  `details` text NOT NULL,

  `category` varchar(255) NOT NULL,

`subcategory` varchar(225) NOT NULL,

  `date_added` date NOT NULL,

  `location` varchar(255) NOT NULL,

  `category_id` int(11) NOT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `product_name` (`product_name`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=19 ;

 

 

 

 

Table structure for table `categories`

--

 

CREATE TABLE IF NOT EXISTS `categories` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `category` varchar(25) CHARACTER SET utf8 NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=17 ;

 

 

The columns in bold are the ones I want to display.

 

This is the structure of the tables Products and Categories. Both of them has the category field. In the query above I want to refer to the categories field to display it instead of the product categories table.

 

[KevinM1]

Okay, before you go much further, you need to strengthen your db design.  Your tables aren't normalized, which is causing confusion and will make it progressively harder to insert and retrieve data.

 

Here's what I'd do for tables:

 

Products:

id

name

price

details

location

date_added

category_id

 

Categories:

id

parent_id

name

 

Then, you could do something like:

 

"SELECT * FROM products, categories WHERE categories.id = $idc AND products.category_id = categories.id"

 

This will give you everything except the parent category name.  You can get that from an additional query:

 

"SELECT name FROM categories WHERE parent_id = {$row['parent_id']}"

 

There may be a better query which could do all of that in one statement.  SQL isn't one of my strengths, so you may want to ask in our MySQL sub-forum.  Still, you need to normalize your tables, else you'll keep getting into these situations where you need to contort your queries in order to get some simple data sets.  Read: http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

[co.ador]

what's the role of the parent_id here? what are you trying to compare with in the products table?

[KevinM1]

what's the role of the parent_id here? what are you trying to compare with in the products table?

 

You have categories and sub-categories, right?  Logically, a sub-category is a category.  parent_id stores the id of that particular category's parent, if it has one.

 

id                                name                                  parent_id

1                                  Apparel                              0

2                                  Men's                                  1

3                                  Women's                            1

 

In this example, you have Apparel as a top-level category.  It has two sub-categories, Men's and Women's.  Apparel's parent_id column contains 0 as that's not a valid id in this table (remember, categories.id auto-increments and starts at 1).  That tells us it's a top-level category.

 

None of this has anything to do with a product per se.  Products enter the mix when you use its foreign key.

 

So, let's say you want to look at a product in Men's Apparel.  A t-shirt or something.  Your first query, when you process the incoming category id, would be:

 

"SELECT * FROM products, categories WHERE categories.id = 2 AND products.category_id = categories.id"

 

That would get you everything except the parent category (in this case, Apparel) info.  So, you'd need to pass the parent_id value obtained from the previous query into a new query:

 

"SELECT name FROM categories WHERE parent_id = 1"

 

Remember, the 1 comes from the result of the previous query.

 

Like I said, there may be a way to get it all in one query.  But this is the general process I'd use.  It allows you to nest categories, and keeps product data from being tangled with category data, which is what you want.