SQL not working correctly with user-made variable

Question

I'm having trouble with my MySQL results, I have a table named "posts" and one column is named "category" which is an integer value. I created a variable to get the category number from the browser depending on which category link a user clicks on.

If the category number is "1" it should display a test post(which is does). But if the category number is anything other than "1" which is the category id, it still shows the same test post when it should say "No results found" since it is querying for a different category id number.

I'm guessing it has something to do with the SQL syntax cause I've done added single-quotes around the variable name, and it error'd out so I removed them. I'm only posting the necessary code below cause I know it has something to do with that variable in the SQL:

// Variable to hold number
$category = is_numeric($_GET["c"]);

// Query
$query = "SELECT * FROM posts WHERE category = $category";

NOTE: Now i'm having a different issue. I need to make it so when there are results the while statement goes through and displays them, but if there are no results, display another message. The issue is it wont display the no results message when there are no results. Here is my code:

while($results = mysqli_fetch_assoc($query))
{
    echo '$results["title"] <br>';
}

if(count($results) == 0 || count($results) == null) {echo 'No results';}

I've tried placing the if statement in the while and outside like so, but neither way works. I have to have the while statement cause I know i'll have more than one result so I can't use an if statement. The while statement needs an else!

Martin · Accepted Answer · 2013-06-03 21:54:21Z

3

You're turning the category into a boolean value.

You actually want this:

$category = intval($_GET['c']);

answered Jun 3, 2013 at 21:54

Martin

6,7054 gold badges28 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

MarioP · Accepted Answer · 2013-06-03 22:02:08Z

1

is_numeric returns true/false, so your query is "SELECT * FROM posts WHERE category = true"

Try this:

if(is_numeric($_GET["c"])) {
  $category = $_GET["c"];
} else {
  // exit the script or set a default value
}
// ...

edited Jun 3, 2013 at 22:02

answered Jun 3, 2013 at 21:55

MarioP

3,8321 gold badge25 silver badges32 bronze badges

1 Comment

Martin Over a year ago

else throw a notice and stumble into undefined behaviour?

anon · Accepted Answer · 2013-06-03 21:53:28Z

0

Your variable inside your query need additional ' apostrophes like this

$query = "SELECT * FROM posts WHERE category = '$category'";

One more thing, if you are trying to specifically detect numbers in PHP use the ctype function

if (ctype_alpha($id){
     //do something
}

answered Jun 3, 2013 at 21:53

anon

1 Comment

Martin Over a year ago

That's only necessary for strings. Regarding the edit, the function he had originally (is_numeric) was appropriate here

rinchik · Accepted Answer · 2013-06-03 21:58:00Z

-1

cast it to int!

$category = (int) $_GET["c"];

and then

if (!empty($category))
{
// do your stuffff
}

answered Jun 3, 2013 at 21:58

rinchik

2,6708 gold badges33 silver badges47 bronze badges

Collectives™ on Stack Overflow

SQL not working correctly with user-made variable

4 Answers 4

Comments

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related