Issue with mysql_query

[vjoho]

I'm currently designing my first e-commerce site and I've run into a mySQL problem. I have a page titled Apparel.php that is supposed to show the most recent item uploaded from each of it's subpages. In other word Apparel is the $category and it has 4 $subcategories (Dresses.php, Skirts.php, Coats.php and Shorts.php). I was able to get the most recent item uploaded of just 1 of the $subcategories, and I was able to get the most recent 4 items uploaded into that $category but not the most recent from each $subcategory. Here's both of the codings that I tried with no success:

Tried this first but only the last mysql_query showed:
$sql = mysql_query("SELECT * FROM products WHERE category='Apparel' AND subcategory='Dresses' ORDER BY date_added DESC LIMIT 1");
$sql = mysql_query("SELECT * FROM products WHERE category='Apparel' AND subcategory='Skirts' ORDER BY date_added DESC LIMIT 1");
$sql = mysql_query("SELECT * FROM products WHERE category='Apparel' AND subcategory='Coats' ORDER BY date_added DESC LIMIT 1");
$sql = mysql_query("SELECT * FROM products WHERE category='Apparel' AND subcategory='Shorts' ORDER BY date_added DESC LIMIT 1");

Then tried this but as I assumed it shows the 4 most recent Apparel uploads but not the most recent from each of Apparel's subcategories:
$sql = mysql_query("SELECT * FROM products WHERE category='Apparel' ORDER BY date_added DESC LIMIT 4");

[_Aerospace_Eng_]

You can't keep calling them $sql. Each query returns a result so you need to use unique variable names for each query and then you can fetch the results using mysql_fetch_assoc() or mysql_fetch_array().

[bullant]

Originally Posted by vjoho

Tried this first but only the last mysql_query showed:

As _A_E_ points out, in your code each run of your query is overwriting the previous result set and so you will only see the results from the last query run.

One way to run multiple sequential queries is something like this.

PHP Code:

<?php

$queryA = array(
    "SELECT * FROM products WHERE category='Apparel' AND subcategory='Dresses' ORDER BY date_added DESC LIMIT 1",
    "SELECT * FROM products WHERE category='Apparel' AND subcategory='Skirts' ORDER BY date_added DESC LIMIT 1",
    "SELECT * FROM products WHERE category='Apparel' AND subcategory='Coats' ORDER BY date_added DESC LIMIT 1",
    "SELECT * FROM products WHERE category='Apparel' AND subcategory='Shorts' ORDER BY date_added DESC LIMIT 1"
);
foreach ($queryA as $query) {
    $rs = @mysql_query($query, $conn) or die('Could not run query');
    while ($row = mysql_fetch_assoc($rs)) {
        //do something
    }
}
mysql_free_result($rs);
?>

[Old Pedant]

You can do it all in one query:

( SELECT * FROM products WHERE category='Apparel' AND subcategory='Dresses' ORDER BY date_added DESC LIMIT 1 )
UNION
( SELECT * FROM products WHERE category='Apparel' AND subcategory='Skirts' ORDER BY date_added DESC LIMIT 1 )
UNION
( SELECT * FROM products WHERE category='Apparel' AND subcategory='Coats' ORDER BY date_added DESC LIMIT 1 )
UNION
( SELECT * FROM products WHERE category='Apparel' AND subcategory='Shorts' ORDER BY date_added DESC LIMIT 1 )

You can't use LIMIT in an individual SELECT within a UNION unless you put the entire SELECT in parentheses, as shown.

[vjoho]

Hi Everyone,
Thanks for your help. This is what I ended up doing and it's working but I'm just wondering if since the coding is kinda long if it will slow down the site from loading. I'm actually using 4 queries but only posted 2 to keep it shorter. Do you guys recommend a better way to code it. When I tried the coding you all used it wasn't working. Maybe I was misplacing it. Not sure. Here's my new code:

<?php
// Connect to the MySQL database
include "storescripts/connect_to_mysql.php";

// Run a select query to get latest item from each subcategory
$dynamicList = "";
$query1 = mysql_query("SELECT * FROM products WHERE category='Apparel' AND subcategory='Dresses' ORDER BY date_added DESC LIMIT 1");
$query2 = mysql_query("SELECT * FROM products WHERE category='Apparel' AND subcategory='Skirts' ORDER BY date_added DESC LIMIT 1");
$productCount = mysql_num_rows($query1); // count the output amount
if ($productCount > 0) {
while($row = mysql_fetch_array($query1)){
$id = $row["id"];
$product_name = $row["product_name"];
$subcategory = $row["subcategory"];
$dynamicList .= '
<ul>
<li><a href="Apparel/' . $subcategory . '.php"><img src="inventory_images/' . $id . '_1.jpg" alt="' . $product_name . '" width="140" height="210" border="0" /></a>
<center><h3>' . $subcategory . '</h3></center>
</li>
</ul>';
}
} else {
$dynamicList = "We have no products listed in our store yet";
}
$productCount = mysql_num_rows($query2); // count the output amount
if ($productCount > 0) {
while($row = mysql_fetch_array($query2)){
$id = $row["id"];
$product_name = $row["product_name"];
$subcategory = $row["subcategory"];
$dynamicList .= '
<ul>
<li><a href="Apparel/' . $subcategory . '.php"><img src="inventory_images/' . $id . '_1.jpg" alt="' . $product_name . '" width="140" height="210" border="0" /></a>
<center><h3>' . $subcategory . '</h3></center>
</li>
</ul>';
}
} else {
$dynamicList = "We have no products listed in our store yet";
}
mysql_close();
?>

[Old Pedant]

What was wrong with adapting Bullant's answer? Or using a UNION as I suggested???

Your code is clumsy and long, as you have to repeat the same stuff over and over.

Also, if you wanted all the results in a single <ul>...</ul> block, your code won't do it.

In addition, your code will *MISTAKENLY* give a "We have no products listed in our store yet" when, for example, you *DO* have Dresses but no Skirts.

[vjoho]

After Bullant's message I tried this but got the message "could not run query". I know I did something wrong but I don't know what.

<?php
// Connect to the MySQL database
include "storescripts/connect_to_mysql.php";
// Run a select query to get latest item from each subcategory
$dynamicList = "";
$queryA = array(
"SELECT * FROM products WHERE category='Apparel' AND subcategory='Dresses' ORDER BY date_added DESC LIMIT 1",
"SELECT * FROM products WHERE category='Apparel' AND subcategory='Skirts' ORDER BY date_added DESC LIMIT 1",
"SELECT * FROM products WHERE category='Apparel' AND subcategory='Coats' ORDER BY date_added DESC LIMIT 1",
"SELECT * FROM products WHERE category='Apparel' AND subcategory='Shorts' ORDER BY date_added DESC LIMIT 1"
);
foreach ($queryA as $query) {
$rs = @mysql_query($query, $conn) or die('Could not run query');
while ($row = mysql_fetch_assoc($rs)) {
$id = $row["id"];
$product_name = $row["product_name"];
$subcategory = $row["subcategory"];
$dynamicList .= '
<ul>
<li><a href="Apparel/' . $subcategory . '.php"><img src="inventory_images/' . $id . '_1.jpg" alt="' . $product_name . '" width="140" height="210" border="0" /></a>
<center><h3>' . $subcategory . '</h3></center>
</li>
</ul>';
}
}
mysql_free_result($rs);
?>

Then I tried yours and got a Parse error: syntax error, unexpected T_STRING. I know I sound really stupid right now but I'm doing this site for a friend and before this I had only created HTML/CSS based sites, so this is all very new and confusing for me

[Old Pedant]

DEBUG!

foreach ($queryA as $query) {
    echo "DEBUG: " . $query . "<hr>";
    $rs = @mysql_query($query, $conn) or die('Could not run query');
    ...

I don't use PHP, but that's the very first step in any language.

As for the error with my code: I gave you pure MySQL code, *NOT* PHP. I expected you to convert it into PHP.

Parse error: syntax error, unexpected T_STRING.

That's a PHP error that has nothing to do with the query, per se. Show your PHP code based on my query.

[bullant]

Originally Posted by vjoho

After Bullant's message I tried this but got the message "could not run query". I know I did something wrong but I don't know what.

That means you have a problem in this line:

$rs = @mysql_query($query, $conn) or die('Could not run query');

$conn is what I used for demo purposes. If you are storing your database connection in a different variable then you need to substitute $conn with your database connection.

Also, for debugging purposes remove the @ symbol which suppresses system errors being output to the browser.

I assume you have manually tested your sql queries in an sql window of some sort to make sure they return the expected results.