Ad:

PHP Discussion Thread
Unsolved
Views: 1998

You are currently viewing page 3 of this multi-page discussion thread; Jump to the first page

Nov 23rd, 2009

Re: Pagination Without Limit in Query

Click to Expand / Collapse Quote originally posted by sam023 ...

Now it throughing this error..

Unknown column 'id_tariffplan' in 'where clause'

That is a basic mysql error which means mysql has been told to search for a column named id_tariffplan however no such column exists. A common problem for this is mis-spelling the column and not having the column inserted in the first place.

cwarn23

Sponsor

Featured Poster

Reputation Points: 410

Solved Threads: 258

Occupation: Genius

Offline

3,004 posts
since Sep 2007

Permalink

Nov 23rd, 2009

Re: Pagination Without Limit in Query

Click to Expand / Collapse Quote originally posted by sam023 ...

Now it throughing this error..

Unknown column 'id_tariffplan' in 'where clause'

are you passing that variable at the time of calling.
Post it here, how you calling the procedure from php

network18

Reputation Points: 29

Solved Threads: 76

Practically a Master Poster

Offline

616 posts
since Sep 2009

Permalink

Nov 23rd, 2009

-1

Re: Pagination Without Limit in Query

id_tariffplan is the variable to hold the first parameter value passed to the stored procedure.
So its not a column name.Either the error is "unknown column idtariffplan" , (where idtariffplan is the column name) and id_tariffplan will just assign its value to it in the where clause.

network18

Reputation Points: 29

Solved Threads: 76

Practically a Master Poster

Offline

616 posts
since Sep 2009

Permalink

Nov 24th, 2009

-1

Re: Pagination Without Limit in Query

mysql Syntax (Toggle Plain Text)
DROP PROCEDURE IF EXISTS `dd`.`view_ratecard`$$
 
CREATE DEFINER=`root`@`localhost` PROCEDURE `view_ratecard`(id_tariffplan INT(11),page_size INT(2),row_start INT(2),country_code CHAR(30))
BEGIN
SET @lim = CONCAT(' LIMIT ', row_start, ',', page_size);
 
if(country_code="") THEN
	SET @q = "select dialprefix,destination,rateinitial,initblock,billingblock,id_trunk,grace,idtariffplan,id from
	cc_ratecard where idtariffplan=id_tariffplan and rate_version='0' ORDER by destination asc";
ELSE
	SET @q = "select dialprefix,destination,rateinitial,initblock,billingblock,id_trunk,grace,idtariffplan,id from
	cc_ratecard where idtariffplan=id_tariffplan and rate_version='0' and dialprefix like concat(country_code,'%') ORDER by destination asc";
 
END if ;
 
SET @q = CONCAT(@q, @lim);
PREPARE st FROM @q;
EXECUTE st;
DEALLOCATE PREPARE st;
    END$$
DELIMITER ;
END if ;
DROP PROCEDURE IF EXISTS `dd`.`view_ratecard`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `view_ratecard`(id_tariffplan int(11),page_size int(2),row_start int(2),country_code char(30))
BEGIN
SET @lim = CONCAT(' LIMIT ', row_start, ',', page_size);

if(country_code="") then
	SET @q = "select dialprefix,destination,rateinitial,initblock,billingblock,id_trunk,grace,idtariffplan,id from
	cc_ratecard where idtariffplan=id_tariffplan and rate_version='0' ORDER by destination asc";
else
	SET @q = "select dialprefix,destination,rateinitial,initblock,billingblock,id_trunk,grace,idtariffplan,id from
	cc_ratecard where idtariffplan=id_tariffplan and rate_version='0' and dialprefix like concat(country_code,'%') ORDER by destination asc";

end if ;

SET @q = CONCAT(@q, @lim);
PREPARE st FROM @q;
EXECUTE st;
DEALLOCATE PREPARE st;
    END$$
DELIMITER ;
end if ;

This is your Store procedure..

i m just calling in database by call function

call view_ratecard_php('1','10','0','')

and it throughing the error

Last edited by sam023; Nov 24th, 2009 at 4:55 am.

sam023

Reputation Points: 11

Solved Threads: 6

Junior Poster

Offline

164 posts
since Jun 2009

Permalink

Nov 24th, 2009

Re: Pagination Without Limit in Query

Click to Expand / Collapse Quote originally posted by network18 ...

id_tariffplan is the variable to hold the first parameter value passed to the stored procedure.
So its not a column name.Either the error is "unknown column idtariffplan" , (where idtariffplan is the column name) and id_tariffplan will just assign its value to it in the where clause.

Exactly...

id_tariffplan is variable and idtraiffplan is column name..!!! but still i m getting this error

PHP Syntax (Toggle Plain Text)

 Unknown column 'id_tariffplan' in 'where clause'

i think it is because here you are writing query in string format..!! by string format i mean query in double quotes""... then how can u pass variable in it..?

i think for passing variable in string we need '?'

i have already a SP for limit..! but i just to try your SP..!!

sam023

Reputation Points: 11

Solved Threads: 6

Junior Poster

Offline

164 posts
since Jun 2009

Permalink

Dec 7th, 2009

Re: Pagination Without Limit in Query

No.MySQL throws this error , when we try to specify a column name not present in the table structure.
So make sure the table structure is fine

network18

Reputation Points: 29

Solved Threads: 76

Practically a Master Poster

Offline

616 posts
since Sep 2009

Permalink

Dec 8th, 2009

Re: Pagination Without Limit in Query

Click to Expand / Collapse Quote originally posted by network18 ...

No.MySQL throws this error , when we try to specify a column name not present in the table structure.
So make sure the table structure is fine

mysql Syntax (Toggle Plain Text)
SELECT dialprefix,destination,rateinitial,initblock,billingblock,id_trunk,grace,idtariffplan,id FROM
 
cc_ratecard WHERE idtariffplan=id_tariffplan
select dialprefix,destination,rateinitial,initblock,billingblock,id_trunk,grace,idtariffplan,id from

cc_ratecard where idtariffplan=id_tariffplan

Thats what i m saying.. Mysql taking id_tariffplan as column not as variable.. and there is no column name as id_tariffplan...thats why it is throwing that error..!! may be because it in string format..!!!

even if u run above query out of SP(directly).. it will throw same error..!!!

sam023

Reputation Points: 11

Solved Threads: 6

Junior Poster

Offline

164 posts
since Jun 2009

Permalink

Dec 9th, 2009

Re: Pagination Without Limit in Query

i hope now getting me..!

sam023

Reputation Points: 11

Solved Threads: 6

Junior Poster

Offline

164 posts
since Jun 2009

Previous
1
2
Page 3

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in PHP Forum Timeline: send html form as mail attachment in php

Next Thread in PHP Forum Timeline: stop playing 4 10 sec while buffer mp3 file?

DaniWeb Message

Cancel Changes

User Name
Password