SitePoint Sponsor

User Tag List

Results 1 to 20 of 20
  1. #1
    SitePoint Wizard billy_111's Avatar
    Join Date
    Jul 2009
    Posts
    1,683
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Why does this SELECT not work?

    Hey,

    I dont know why this select does not work..

    PHP Code:
                include("conn.php");
                
    $query "SELECT * FROM tbl_cat_selection WHERE cat_id = '".$_GET['ID']."'";
            
                
    $result =  mysql_query ($query);
                
    $row mysql_fetch_array($result,MYSQL_ASSOC);
                
    $numrows mysql_num_rows($result);

                if(
    $numrows == 0)
                {
                echo 
    "<h4 style='margin-left:15px'>Sorry, there no products in this category!</h4>";
                }
                else
                {
                    while (
    $row mysql_fetch_array($result)) 
                     {
                        echo 
    $row[main_image];
                                    }
                } 
    When i take the while away it works, but i need to while loop for it to loop through multiple records..

    Can someone help?

    Regards
    Billy

  2. #2
    SitePoint Enthusiast
    Join Date
    Apr 2009
    Location
    Greenville, South Carolina, United States
    Posts
    50
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Are you setting the $_GET['ID'] via a URL Request? If not, there is your problem
    Phase 8 Facebook Applications and Web Development

  3. #3
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I dont know why
    Well, as a matter of fact, it is only you, who can get to know.
    use mysql_error() function to see if something wrong with this query.
    also check if there are any data in the database.

    also instead of $_GET['ID'] you should use
    PHP Code:
    intval($_GET['ID']) 
    to follow SQL syntax rules and avoid malicious SQL injection

  4. #4
    SitePoint Wizard billy_111's Avatar
    Join Date
    Jul 2009
    Posts
    1,683
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes i am setting it through the URL..

    This is the URL:-

    http://www.fatzk.com/mens.php?ID=1

    Now, if you look in my select, i have said is $numrows is 0 then show a message. I get this message if there are no returns. But on the page where i expect something the message does not appear. So this means the code knows that it should output something..

    You can test it yourself via the URL..

    Any ideas?

  5. #5
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    add these lines at the top of your code
    PHP Code:
    ini_set('display_errors',1);
    error_reporting(E_ALL); 

  6. #6
    SitePoint Member ProjetSEO's Avatar
    Join Date
    Nov 2008
    Location
    Montreal (Quebec)
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello,

    Code:
    $query = "SELECT * FROM tbl_cat_selection WHERE cat_id = '".$_GET['ID']."'";
    Try to avoid to get the variable directly in the select statement. Instead use

    Code:
    $myid = $_GET['ID'];
    (to protect your database you will need to validate your variable here)
    $query = "SELECT * FROM tbl_cat_selection WHERE cat_id = \"$myid\";
    or

    Code:
    $myid = $_POST['ID'];
    (to protect your database you will need to validate your variable here)
    $query = "SELECT * FROM tbl_cat_selection WHERE cat_id = \"$myid\";

    And you will want to change the "*" with all the tables you need. You must avoid using "*". It will be faster and more secure.

    Hope this help

    Sebas
    http:// projetseo . com

  7. #7
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Might be a stupid question, but are you sure there is a column called main_image in that table? And if there is, does it contain any data?
    You might also want to debug your code adding some echo and print_r commands here and there to see what parts of the script are actually executed, and what value variables have at that time.

  8. #8
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ProjetSEO, your code will produce a parse error and does nothing to help.

  9. #9
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Hi projet, welcome to SP
    Quote Originally Posted by ProjetSEO View Post
    Try to avoid to get the variable directly in the select statement. Instead use

    Code:
    $myid = $_GET['ID'];
    (to protect your database you will need to validate your variable here)
    $query = "SELECT * FROM tbl_cat_selection WHERE cat_id = \"$myid\";
    If you're not doing any validation, storing the $_GET or $_POST variable in another variable has no advantage at all. And why do you put the value in double quotes? Use single quotes, or non at all if the value is numeric. Which makes me think: OP, is cat_id numeric? Then don't use quotes.

    And you will want to change the "*" with all the tables you need.
    They're called columns, not tables, but you're correct about not using the '*'

  10. #10
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    quotes are ok. mysql will cast string to integer.
    Why all this (wrong) guessing if we can have exact error message?

  11. #11
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by Shrapnel_N5 View Post
    quotes are ok. mysql will cast string to integer.
    Why all this wrong guessing if we can have exact error message?
    I'm not saying the quotes are causing the problem, I know MySQL will handle it. But quoting numeric values is not correct.

  12. #12
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Everything is correct. No problem with quotes. As well as with *. It is "pseudo knowledge" that doesn't help a bit with the real problems.

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by billy_111 View Post
    I dont know why this select does not work..
    test it outside of php, using an actual value for the id

    i'll bet you a coffee and doughnout that it ~does~ work
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Me thinks "undefined index main_image" or only a single row.

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Shrapnel_N5 View Post
    No problem with quotes.
    not in mysql, no

    but it is a very bad habit to get into, because this "skill" (lazily and haphazardly quoting stuff) will ~not~ work in other databases
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    not in mysql, no

    but it is a very bad habit to get into, because this "skill" (lazily and haphazardly quoting stuff) will ~not~ work in other databases
    Yes exactly.
    In order to make abstraction so that it works for all db layers.

    we can use some utility function. For example:

    PHP Code:
    function quote_smart($value){
        
    $value stripslashes($value);
        if (!
    is_numeric($value)){
            
    $value "'" mysql_real_escape_string($value) . "'";
        }
        return 
    $value;
    }

    //usage
    $sql "SELECT * FROM table WHERE field=".quote_smart($value); 
    In the meanwhile i found one (must say better) code :
    PHP Code:
    <?php
    function quote_smart($values$quotes true) {
        if (
    is_array($values)) {
            foreach (
    $values as $key => $value) {
                
    $values[$key] = quote_smart($value$quotes);
            }
        }
        else if (
    $values === null) {
            
    $values 'NULL';
        }
        else if (
    is_bool($values)) {
            
    $values $values 0;
        }
        else if (!
    is_numeric($values)) {
            
    $values mysql_real_escape_string($values);
            if (
    $quotes) {
                
    $values '"' $values '"';
            }
        }
        return 
    $values;
    }
    ?>
    isn't this a better way to go?

  17. #17
    SitePoint Wizard
    Join Date
    Nov 2005
    Posts
    1,191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
     $row mysql_fetch_array($result,MYSQL_ASSOC);
    // ... snip
    while ($row mysql_fetch_array($result)) 
    I don't use mysql functions, but won't that mean you won't get the first row in the while? If only one then that's probably why it doesn't work?

  18. #18
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ahahaha silly me. such a silly mistake I missed

  19. #19
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Shrapnel_N5 View Post
    ahahaha silly me. such a silly mistake I missed
    What did you realize BTW?

  20. #20
    SitePoint Wizard billy_111's Avatar
    Join Date
    Jul 2009
    Posts
    1,683
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks guys,

    Your suggestions led me to the answer..

    Regards



Bookmarks

Posting Permissions

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