Why does this SELECT not work?

[billy_111]

Hey,

I dont know why this select does not work..

            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

[aalicki]

Are you setting the $_GET['ID'] via a URL Request? If not, there is your problem

[Shrapnel_N5]

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

intval($_GET['ID']) 

to follow SQL syntax rules and avoid malicious SQL injection

[billy_111]

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?

[Shrapnel_N5]

add these lines at the top of your code

ini_set('display_errors',1);
error_reporting(E_ALL); 

[ProjetSEO]

Hello,

$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

$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

$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

[guido2004]

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.

[Shrapnel_N5]

ProjetSEO, your code will produce a parse error and does nothing to help.

[guido2004]

Hi projet, welcome to SP

Try to avoid to get the variable directly in the select statement. Instead use

$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 '*'

[Shrapnel_N5]

quotes are ok. mysql will cast string to integer.
Why all this (wrong) guessing if we can have exact error message?

[guido2004]

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.

[Shrapnel_N5]

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.

[r937]

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

[crmalibu]

Me thinks "undefined index main_image" or only a single row.

[r937]

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

[PHPycho]

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:

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
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 ? 1 : 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?

[hash]

 $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?

[Shrapnel_N5]

ahahaha silly me. such a silly mistake I missed

[PHPycho]

ahahaha silly me. such a silly mistake I missed

What did you realize BTW?

[billy_111]

Thanks guys,

Your suggestions led me to the answer..

Regards