2

Is there anything wrong with this SQL code? I got it from a tutorial, but it's returning the following error message

Database query failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 1' at line 1

function get_subject_by_id($subject_id) {
    global $connection;
    $query = "SELECT * ";
    $query .= "FROM subjects ";
    $query .= "WHERE id=" . $subject_id ." ";
    $query .= "LIMIT 1";
    $result_set = mysql_query($query, $connection);
    confirm_query($result_set);
    // if no rows are returned, fetch array will return false
    if ($subject = mysql_fetch_array($result_set)){
    return $subject;
    } else {
    return NULL;

    }
    }
Improve this question
0

5 Answers 5

Reset to default
7

Best to echo the query and see what it looks like.

Probably $subject_id contains no value or an invalid value. If $subject_id is a string, you should escape it (using mysql_real_escape_string) and put it inside quotes in the query.

[Edit]

You know you can put enters in strings too, right? 

// More readable
$query = "
  SELECT *
  FROM subjects
  WHERE id = $subject_id
  LIMIT 1";
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Can you please tell me how I would do that? (i.e. echo the query) I am a beginner...
put the line echo $query; below the line $query .= "LIMIT 1";. :) It will output the query.
This is the error message it gave me when i did echo $query. SELECT * FROM subjects WHERE id= LIMIT 1Database query failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 1' at line 1
Sorry, I meant inside strings. That way you can write the query nicely outlined in a single string without having all those query .= " in your code. I think it looks more readable. No real difference in functionality though.
Yes, as you can see in the output of your query, there is no id. There should be an id to check for behind id=, but since the variable is empty, the query continues with 'LIMIT' which makes it invalid. Make sure you pass the right id to your function.
2 
$query .= "where id=" . $page_id . " ";

Needs to be put within single quotes. Replace the above statement by

$query .= "where id='" . $page_id . "' ";

Improve this answer

Comments

1

Frankly, it's impossible to say what is exactly wrong with this code, not knowing what values are substituted in the query in place of variables.

Apart from that, the code in question may be subject to SQL injection attacks.

If I may put together other suggestions that will make sure no error is ever generated with this code:

function get_subject_by_id($subject_id) {
    global $connection;
    $query = "SELECT * ";
    $query .= "FROM subjects ";
    $query .= "WHERE id='" . mysql_real_escape_string($subject_id) ."' ";
                       // note the quotes and escaping wrapper
    $query .= "LIMIT 1";
    $result_set = mysql_query($query, $connection);
    confirm_query($result_set);
    // if no rows are returned, fetch array will return false
    if ($subject = mysql_fetch_array($result_set)) {
        return $subject;
    } else {
        return NULL;
    }
}

Additionally, using global variables is a bad practice nowadays, so I suppose the example you're using is quite outdated.

Improve this answer

4 Comments

without knowing if variables were causing a problem, how did you know it wouldn't generate an error. Can you please explain how your changes fixed the problem. I am not familiar with this function mysql_real_escape_string
The error message states that syntax is wrong next to "LIMIT" part of the statement, which is exactly where the variable substitution occurs. The variable, being substituted, can cause syntax errors, for obvious reasons. Putting it in quotes makes sure that empty value still generates valid SQL statement, whereas mysql_real_escape_string makes sure that all symbols that might break the intended syntax, including single quote, which definitely would break it, are escaped with a slash. Read about it on php.net, the info is there.
Thank you for your help. As a complete beginner doing a beginner's tutorial, there's no way I would have been able to isolate that problem and figure it out from php.net... php is teaching me how stupid I am!
You're learning, and that is a smart thing to do :) Good luck!
0

Try to use mysql_real_escape_string()

Improve this answer

Comments

0 
function get_subject_by_id($subject_id) {
        global $connection;
        $query = "SELECT * ";
        $query .= "FROM subjects ";
        $query .= "WHERE id='" . $subject_id ."' "; //You need single quotes
        $query .= "LIMIT 1";
        $result_set = mysql_query($query, $connection);
        confirm_query($result_set);
        // REMEMBER:
        // if no rows are returned, fetch_array will return false
        if ($subject = mysql_fetch_array($result_set)) {
            return $subject;
        } else {
            return NULL;
        }
    }

$query .= "WHERE id='" . $subject_id ."' "; //work
$query .= "WHERE id=" . $subject_id ." "; //not work
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.