Welcome to WebmasterWorld Guest from 54.91.71.108

Forum Moderators: phranque

Message Too Old, No Replies

MySQL Query Not Working

MySQL with Do While Lopp not working

     
10:33 pm on Sep 29, 2004 (gmt 0)

Junior Member

10+ Year Member

joined:Aug 14, 2002
posts:153
votes: 0


Hi. I hope this is the correct spot to post this topic. I wasn't able to figure out where a general discussion would be regarding MySQL or databases for that matter. Anyways here's my problem.

I'm trying to write a query that does an insert statement multiple times until the while loop returns false. I tried executing the below code but MySQL server returns the following error. Oh also I'm running MySQL 3.23.37. I tried to check and see if the problem was due to the version I have but was unable to locate anything on MySQL's web site. For all I know maybe I'm going about this query the wrong way, never done a dowhile loop before in SQL. Any ideas to solve the problem would be greatly appreciated.

ERROR 1064: You have an error in your SQL syntax near 'PROCEDURE dowhile()
ERROR 1064: You have an error in your SQL syntax near 'WHILE v1 <= 41675 DO INSERT INTO PostalCodeReps(Postal_Code_Info_ID, Company' at line 1

ERROR 1064: You have an error in your SQL syntax near 'END WHILE' at line 1
ERROR 1064: You have an error in your SQL syntax near 'END' at line 1


CREATE PROCEDURE dowhile()
BEGIN
DECLARE v1 INT DEFAULT 41190;

WHILE v1 <= 41675 DO
INSERT INTO PostalCodeReps(Postal_Code_Info_ID, Company_ID_Unitary, Company_ID_Applied) VALUES(v1, 1332,1332)
SET v1 = v1 + 1;
END WHILE;
END

10:41 pm on Sept 29, 2004 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Apr 18, 2003
posts:827
votes: 0


It looks like you'll have to wait, upgrade to a real database, or get creative with your procedure paramaters, as MySQL doesn't yet support references to tables in procedures/functions. See the Note and examples at the bottom of:

[dev.mysql.com...]

10:50 pm on Sept 29, 2004 (gmt 0)

Senior Member from MT 

WebmasterWorld Senior Member 10+ Year Member

joined:Apr 1, 2003
posts:1843
votes: 0


MySQL has only strated adding Stored Procedures, and in a limited format. If you rely on stored procedures use soemthing like Oracle. MySQL is designed to be lean and mean.

SN

1:46 pm on Sept 30, 2004 (gmt 0)

Junior Member

10+ Year Member

joined:Aug 14, 2002
posts:153
votes: 0


Okay that is fine. Is there a way to do a while loop then without using a procedure?
1:56 pm on Sept 30, 2004 (gmt 0)

Administrator

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:July 31, 2003
posts:12548
votes: 2


A server-side scripting language such as PHP would work well here. Are you trying to do this through a web server-side script, or from a command line?
2:48 pm on Sept 30, 2004 (gmt 0)

Junior Member

10+ Year Member

joined:Aug 14, 2002
posts:153
votes: 0


I'm trying to achieve this via MySQL Control Center which is basiclly the same as doing it command line. Looks like I might have to do something up quick in PHP. So nothing else I can do then in MySQL it self then?
3:21 pm on Sept 30, 2004 (gmt 0)

Administrator

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:July 31, 2003
posts:12548
votes: 2


To the best of my knowledge I would have to say no, not without a stored procedure. You would have to script something. Easy in PHP...

<?php 
// Connection:
$db_server = 'localhost';
$db_user = 'userid';
$db_pwd = 'password';
$db_name = 'test';
$db_link = mysql_connect($db_server, $db_user, $db_pwd)
or exit('Could not connect (' . mysql_errno() . '): '
. mysql_error()); # Test mode only
$db = mysql_select_db($db_name, $db_link)
or exit('Could not select database (' . mysql_errno() . '): '
. mysql_error()); # Test mode only
// Process:
for ($v1 = 41190; $v1 <= 41675; $v1++) {
mysql_query("INSERT INTO PostalCodeReps
(Postal_Code_Info_ID, Company_ID_Unitary, Company_ID_Applied)
VALUES($v1, 1332, 1332)");
}

?>
4:12 pm on Sept 30, 2004 (gmt 0)

Junior Member

10+ Year Member

joined:Aug 14, 2002
posts:153
votes: 0


Okay thanks. I've already started on doing a PHP solution. Thanks everyone for your help.
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members