Databases
StackOverflow2013
Postsdbo
POWhy is my MySQL Query returning only 7 of 28 records?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhy is my MySQL Query returning only 7 of 28 records?
    primarykey
    data
    text
    <p>I'm working on the datafeed script for my Affiliate program and really thought I was "doin' it" but when I run the script it creates the CSV file with only 7 of 28 products. Prior to testing I made all the products meet the conditions of the query so they all should be included.I even removed the stock level and price conditions. Still only 7 of 28 gets added to the csv file 5 from one category and 2 from the other. There are only 5 products in the first category and all of those are returned. But only 2 of the 23 in the second category are returned.</p> <p><strong>The Goal</strong> is to get the specific data for all products from the categories in the query and dump them into a csv file.</p> <p>This is my first time trying to write a more complex sql query (for a beginner anyway).</p> <p>Using print_r I found the problem is my query. I thought I had this working before. The difference between when it was working and now is that I included grabbing the categoryid and catparentid. But even if I remove these additions, I still get the same results. Now I'm thinking that I might have thought it was working before on my larger database of products but perhaps it actually wasn't returning all of the products but because there were so many I thought they were.</p> <p>The full script is pretty long but I created a new script below just to see if the query is even grabbing all of the records... it's not. :(</p> <p>If I run a query to check for unassociated products (i.e. ones that have no category associations), all products are associated with a category. </p> <p>All of the products have image &amp; brand associations so I don't think that's the issue either.</p> <p>Even if I remove the Where clause altogether, only the 7 products are grabbed.</p> <p>I also ran the query directly within the database and got the same thing:</p> <pre><code>Showing rows 0 - 6 ( 7 total, Query took 0.0029 sec) SELECT isc_products.productid, isc_products.prodcode, isc_products.prodname, isc_products.produrl, isc_products.prodcalculatedprice, isc_products.proddesc, isc_products.prodcurrentinv, isc_products.upc, isc_categories.categoryid, isc_categories.catparentid, isc_categories.catname, isc_product_images.imagefilestd, isc_product_images.imagefilethumb, isc_brands.brandname FROM isc_products INNER JOIN isc_categories ON isc_products.prodcatids = isc_categories.categoryid INNER JOIN isc_product_images ON isc_products.productid = isc_product_images.imageprodid INNER JOIN isc_brands ON isc_products.prodbrandid = isc_brands.brandid WHERE isc_categories.categoryid =10 OR isc_categories.categoryid =12 </code></pre> <p>Could there be any special characters in the data that would cause this issue?</p> <p>Or am I using the inner joins incorrectly? </p> <pre><code>&lt;pre&gt; &lt;?php // Make a MySQL Connection mysql_connect("localhost", "myusername", "mypassword") or die(mysql_error()); mysql_select_db("mydatabase") or die(mysql_error()); $result = mysql_query("SELECT isc_products.productid, isc_products.prodcode, isc_products.prodname, isc_products.produrl, isc_products.prodcalculatedprice, isc_products.proddesc, isc_products.prodcurrentinv, isc_products.upc, isc_categories.categoryid, isc_categories.catparentid, isc_categories.catname, isc_product_images.imagefilestd, isc_product_images.imagefilethumb, isc_brands.brandname FROM isc_products INNER JOIN isc_categories ON isc_products.prodcatids = isc_categories.categoryid INNER JOIN isc_product_images ON isc_products.productid = isc_product_images.imageprodid INNER JOIN isc_brands ON isc_products.prodbrandid = isc_brands.brandid WHERE isc_categories.categoryid = 17 OR isc_categories.categoryid = 15 OR isc_categories.categoryid = 16 OR isc_categories.categoryid = 12 OR isc_categories.categoryid = 13 OR isc_categories.categoryid = 14 OR isc_categories.categoryid = 11 OR isc_categories.categoryid = 10 AND isc_products.prodcurrentinv &gt; 1 AND isc_products.prodcalculatedprice &gt; 1.00 ORDER BY isc_categories.catname ") or die(mysql_error()); while($row = mysql_fetch_array( $result)){ print_r ($row); } // end of file echo '&lt;br/&gt;****** Script successfully run! ******'; ?&gt; &lt;/pre&gt; </code></pre> <p>Any thoughts on this would be appreciated. <em>Dislcaimer</em> if your first thought is "Why didn't you..." It's because I didn't or don't know about it. :) One day, my friend... one day I will. hahahaha </p> <p>Thank you!</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USTsanders
    1. USTsanders
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POWhy is my MySQL Query returning only 7 of 28 records?
      1. This table or related slice is empty.
      1. VTUpMod
    1. CONeed to see your data or at least the schema.
      singulars
      1. POWhy is my MySQL Query returning only 7 of 28 records?
      1. USShiplu Mokaddim
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload