DaniWeb Forum Index > Web Development > PHP

Pagination Without Limit in Query

Please support our PHP advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

« First

•

Join Date: Sep 2007

Posts: 1,542

Reputation: cwarn23 has a spectacular aura about

Solved Threads: 137

cwarn23

Offline

Posting Virtuoso

#21

30 Days Ago

•

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.

Try not to bump 10 year old threads as it can be really annoying.
http://syntax.cwarn23.net/
Smilies: ^_* +_+ v_v -_- *~*`
My favourite PC. - Oopy Doopy Do 2U2!

•

Join Date: Sep 2009

Posts: 557

Reputation: network18 is an unknown quantity at this point

Solved Threads: 64

network18

Offline

Posting Pro

#22

30 Days Ago

•

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

"The discipline of writing something down is the first step towards making it happen."

follow me on twitter

•

Join Date: Sep 2009

Posts: 557

Reputation: network18 is an unknown quantity at this point

Solved Threads: 64

network18

Offline

Posting Pro

-1

#23

30 Days Ago

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.

"The discipline of writing something down is the first step towards making it happen."

follow me on twitter

•

Join Date: Jun 2009

Posts: 121

Reputation: sam023 is an unknown quantity at this point

Solved Threads: 2

sam023

Offline

Junior Poster

-1

#24

29 Days Ago

 Help with Code Tags 
 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; 29 Days Ago at 4:55 am.

•

Join Date: Jun 2009

Posts: 121

Reputation: sam023 is an unknown quantity at this point

Solved Threads: 2

sam023

Offline

Junior Poster

#25

29 Days Ago

•

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

Help with Code Tags

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..!!

•

Join Date: Sep 2009

Posts: 557

Reputation: network18 is an unknown quantity at this point

Solved Threads: 64

network18

Offline

Posting Pro

#26

16 Days Ago

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

"The discipline of writing something down is the first step towards making it happen."

follow me on twitter

•

Join Date: Jun 2009

Posts: 121

Reputation: sam023 is an unknown quantity at this point

Solved Threads: 2

sam023

Offline

Junior Poster

#27

15 Days Ago

•

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

 Help with Code Tags 
 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..!!!