SQL syntax error only when executing a query in PHP

Question

I believe to have a problem with PHP. I am shown the following error:

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 'SELECT @n := @n + 1 as count , interests.id , entity.id , entity.name ' at line 2

If I execute the the following query in MySql, it does so successfully and returns the desired results. However, this does not occur when I execute the same query with PHP.

 SET @n = 0 ;
    SELECT @n := @n + 1   as count
      , interests.id
      , entity.id
      , entity.name
      , entity.entity_type
      , entity.city_country_province
      , entity.street_address
      , entity.user_id
      , entity.descript
      , entity.icon
      , city_country_province_type.city
      , city_country_province_type.province
      , city_country_province_type.country
      , entity.linkout
      , entity.map_lng
      , entity.map_lat              
    FROM interests INNER JOIN interest_entity ON interests.id = interest_entity.interet_id
    INNER JOIN entity ON interest_entity.entity_id = entity.id
    INNER JOIN city_country_province_type ON entity.city_country_province = city_country_province_type.id             
    WHERE ((interests.id)=9) 
    GROUP BY interests.id
      , entity.id
      , entity.name
      , entity.entity_type
      , entity.city_country_province
      , entity.street_address
      , entity.user_id
      , entity.descript
      , entity.icon
      , city_country_province_type.city
      , city_country_province_type.province
      , city_country_province_type.country
      , entity.linkout
      , entity.map_lng
      , entity.map_lat   
      HAVING count >= 10 AND count <= 20 order by entity.name desc

NOTE OF INTEREST: The web server i am using does not support MySqli, therefore I am using mysql_query() functions. I suspect that this could be part of the problem. However, I execute it with MySql WAMP for localhost testing and receive the same error.

Please add the PHP, this is only SQL. The web server i am using does not support MySqli, so why tag mysqli? Without the PHP this question can't be answered. — chris85
– chris85, Commented Jan 21, 2017 at 18:27
If your LIVE server only allows mysql_ you are going to have to pay for a real server — RiggsFolly
– RiggsFolly, Commented Jan 21, 2017 at 18:33

Bill Karwin · Accepted Answer · 2017-01-21 20:20:05Z

1

You can't run multiple queries in one API call.

Simple solution: use multiple API calls. One to SET @n=0, then a separate API call to execute the SELECT query. As long as you use the same connection to MySQL, the value of @n stays alive in the session.

The mysqli extension does support mysqli_multi_query() but I still recommend doing one query at a time.

answered Jan 21, 2017 at 20:20

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

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

Collectives™ on Stack Overflow

SQL syntax error only when executing a query in PHP

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related