Page 1 of 1

MySQL query in a function

Posted: Fri May 21, 2010 8:39 am
by IGGt
Hi Guys,

I'm trying to put together a function to replace a MySQL Stored procedure. But I keep getting 'parse errors'.

The code I have so far is:

Code: Select all

function BasicSquares($a)
{
	While($a<26) {
	                     $query1 = mysql_query("SELECT colour FROM db1.colours ORDER BY RAND() LIMIT 1");
	                     $colour = mysql_fetch_array($query1);

	                     $query2 = mysql_query( "Update db1.Squares set colour =".$colour['colour']."WHERE id =".$a." )";
	                     $a=$a+1;
	                     }
}

BasicSquares("1");
but it doesn't work, can anyone see what I have missed (or even if this would work at all).

Basically the idea is it selects a random colour from the colours table, and then updates the squares table at ID "1". It then does the same again for ID "2" and so on until it has filled all the way up to ID "25". And then it stops.

Re: MySQL query in a function

Posted: Fri May 21, 2010 8:44 am
by Eran
Check out this part:

Code: Select all

mysql_query( "Update db1.Squares set colour =".$colour['colour']."WHERE id =".$a." )";
You are putting the closing bracket of the mysql_query() function inside a string - you're not actually closing it. should be:

Code: Select all

... "WHERE id =" . $a);

Re: MySQL query in a function

Posted: Fri May 21, 2010 9:09 am
by IGGt
cheers for that. It almost works now. If I echo out the colour I can see it is indeed picking a random colour 25 times. But it isn't updating the table, apparently the MySQL syntax is wrong.

I have tried putting it straight into MySQL (substituting where necessary) and can' t see anything wrong. Any Ideas?

Code: Select all

function BasicSquares($a)
{
	While($a<26) {
	$query1 = mysql_query("SELECT colour FROM db1.colours ORDER BY RAND() LIMIT 1");
	$colour = mysql_fetch_array($query1);

	$query2 = mysql_query( "update db1.squares set colour =".$colour['colour']."WHERE id =".$a) or die( mysql_error() );
	//echo $colour['colour']." ";
	$a=$a+1;
	}
}
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 '' at line 1"

MySQL =
set @colour = (SELECT colour FROM db1.colours ORDER BY RAND() LIMIT 1);
update db1.squares set colour = @colour WHERE id = 1;

Re: MySQL query in a function

Posted: Fri May 21, 2010 9:16 am
by Eran
There are a few problems here -
1. You don't check to see if the color fetch query actually returning anything. So $colour['colour'] (are you british by the way?) could in fact be empty.
2. You don't quote the color, so the update query would fail. Strings need to be quoted in the query, either with single or double quotes.
3. The WHERE condition needs at least one space from the color name. As it is now, they are concatenated into one string.
4. There might not be in fact a row with the identifier $a for some of the values. Row identifiers are not necessarily sequential if some deletes took place.

Re: MySQL query in a function

Posted: Fri May 21, 2010 9:33 am
by IGGt
success!!!

so obvious once it gets pointed out, it now works great. cheers for your help.

(and yes, I am British. Was it my strange use of the letter 'U' in colour that gave me away)!!