CODEWITHSUNDEEP
phpmysql
Kael Coonnelly
Kael Coonnelly

Reputation: 1

SQL syntax error only when executing a query in PHP

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.

Upvotes: 0

Views: 89

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562260

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.

Upvotes: 1

Related Questions