Mysql syntax error in select statement

Question

I have a table main_tag with structure as

1   id int(100) 
2   name varchar(100)   
3   description varchar(1000)
4   added_on timestamp

and have function of php that is as follows

function all_data_of_main_tag_table(){

        include_once 'db.php';
        $conn = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
        $json = array();
        $nameQuery ='SELECT * FROM `main_tag` WHERE 1';
        echo '<br>'.$nameQuery.'<br>';
        $rsnameQuery = $conn->query($nameQuery);      
        if($rsnameQuery === false){
            echo 'hi'.'<br>';
            trigger_error('Wrong SQL: '.$nameQuery.' Error: '.$conn->error, E_USER_ERROR);
        }
        else{
           $rows_returned = $rsnameQuery->num_rows;
        }
        while($row = $rsnameQuery->fetch_assoc()){
            $json =$row;
        }
        $conn->close();
        return $json;
    }

On running this function it is giving error:

{
  "description": null,
  "name": null,
  "id": null
}
MySQL error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1

Where's the "wrong SQL" bit gone? Perhaps this error message refers to a different query — Strawberry
– Strawberry, Commented Dec 26, 2016 at 6:54
Are you sure that's the query that gets the error? The error message in your code begins with Wrong SQL:, but your message says MySQL error:. It also doesn't print $nameQuery like your trigger_error() call does. — Barmar
– Barmar, Commented Dec 26, 2016 at 6:59
Also, if you want to push onto an array, you should use $json[] = $row;. You're overwriting the variable each time, so you only get the last row. — Barmar
– Barmar, Commented Dec 26, 2016 at 7:00

smartnet · Accepted Answer · 2016-12-26 07:06:53Z

2

In your query, you didn't specify where to look

Where 1

It does not specify any column

// it should be something like this 
Where id = 1

So your query be like this

$nameQuery ='SELECT * FROM `main_tag` WHERE id = 1

answered Dec 26, 2016 at 7:06

smartnet

874 bronze badges

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

Comments

smarttechy · Accepted Answer · 2016-12-26 07:24:24Z

0

my bad my query was ok the error was of other query. however

while ($row = mysqli_fetch_assoc($rsnameQuery)) {
   $json[] = $row;
}

this was to be written instead of

while($row = $rsnameQuery->fetch_assoc()){
     $json =$row;
}

This was not returning the data.

edited Dec 26, 2016 at 7:24

answered Dec 26, 2016 at 7:15

smarttechy

9021 gold badge9 silver badges23 bronze badges

Collectives™ on Stack Overflow

Mysql syntax error in select statement

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related