Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    New Coder
    Join Date
    Apr 2011
    Posts
    11
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Issue with mysql_query

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

  • #2
    Supreme Master coder! _Aerospace_Eng_'s Avatar
    Join Date
    Dec 2004
    Location
    In a place far, far away...
    Posts
    19,291
    Thanks
    2
    Thanked 1,043 Times in 1,019 Posts
    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().
    ||||If you are getting paid to do a job, don't ask for help on it!||||

  • #3
    Banned
    Join Date
    Feb 2011
    Posts
    2,699
    Thanks
    13
    Thanked 395 Times in 395 Posts
    Quote Originally Posted by vjoho View Post
    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);
    ?>

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,028
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    You can do it all in one query:
    Code:
    ( 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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    New Coder
    Join Date
    Apr 2011
    Posts
    11
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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();
    ?>

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,028
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    New Coder
    Join Date
    Apr 2011
    Posts
    11
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,028
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    DEBUG!
    Code:
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #9
    Banned
    Join Date
    Feb 2011
    Posts
    2,699
    Thanks
    13
    Thanked 395 Times in 395 Posts
    Quote Originally Posted by vjoho View Post
    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.


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •