SELECT mysql_query Function does not work

[lpruen]

Hi,

 

I have spent some time on this and can't get to the bottom of it. I want to re-use my SQL query so decided to put it in a fucntion. The query works fine if i run it in the function "registered_table" which eventually displays the query in a table, but when I put the query in a seperate function I recieve a SQL syntax error.

 

function registered_table is used to display my data.

function search_field is the query

 

If I echo the query in the search_field funtion I can see the SQL Resource but when I return $dbResult to registered_table I recieve the SQL syntax 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 'LIKE '%%' ORDER BY date DESC LIMIT ,' at line 1"

 

From this I understand that the SQL Resource is not returned but rather the query dbResult which is then executed but I am unsure why it doesn't work. I have also tried using set values in the query rather than my variables eg "SELECT * FROM registered_members WHERE name LIKE luke ORDER BY date DESC LIMIT 0, 5" but I recieve the "Unknown column 'luke' in 'where clause'" but again this works if it is in the registered_table function.

 

The basic outline of the code is below, I removed other parts to make it easier to identify the problem area. Any help on this will be greatly appreciated.

 

Thanks Luke

 

function registered_table() {

$conn = db_connect();

$recordperPage = 5;

if (isset($_GET['page'])) {

				$page = $_GET['page'];
				}
				else {
					$page=1;
				}

$startFrom = ($page-1) * $recordperPage;



if (isset($_POST['searchField'])){
							        
                                                                        $searchCat = addslashes($_POST['searchCat']);
								$searchField = addslashes($_POST['searchField']);	

								search_field($startFrom, $recordperPage, $searchCat, $searchField);

								echo search_field($dbResult);

								break;

								}

//Script IF statement and Script continues

 

 

 

function search_field($startFrom, $recordperPage, $searchCat, $searchField) { 					
							    
								$conn = db_connect();				

								$query_dbResult = "SELECT * FROM registered_members WHERE $searchCat LIKE 

'%$searchField%' ORDER BY date DESC LIMIT $startFrom, $recordperPage";
								$dbResult = mysql_query($query_dbResult, $conn) or die(mysql_error());

								db_connect_error($dbResult);

								//echo $dbResult;
								//break;

								return $dbResult;

}

 

 

[Adam]

Have you tried echoing out the values for $startFrom, $recordperPage, $searchCat and $searchField to double check they contain what you expect?

 

...right syntax to use near 'LIKE '%%' ORDER BY...

 

This would suggest the $searchField variable does not contain a value... "% %".

[lpruen]

Hi MrAdam,

 

I added the follwoing code which gave me this output

 

startFrom = 0

recordperPage = 5

searchCat = name

searchField = luke

dbResult = Resource id #9

startFrom =

recordperPage =

searchCat =

searchField =

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 'LIKE '%%' ORDER BY date DESC LIMIT ,' at line 1

 

from this code

 

function search_field($startFrom, $recordperPage, $searchCat, $searchField) { 					
							    
								$conn = db_connect();	

								echo "startFrom = $startFrom <br />"; 

								echo "recordperPage = $recordperPage <br />"; 

								echo "searchCat = $searchCat <br />"; 

								echo "searchField = $searchField <br />";

								$query_dbResult = "SELECT * FROM registered_members WHERE $searchCat LIKE '%$searchField%' ORDER BY date DESC LIMIT $startFrom, $recordperPage";
								$dbResult = mysql_query($query_dbResult, $conn) or die(mysql_error());

								db_connect_error($dbResult);

								echo "dbResult = $dbResult <br />";
								//break;

								return $dbResult;

}

 

So it has the values orginaly in the function search_field but when it attempts to execute the query again under function registered_table there are no values. I would of expected it to kept the values but obviousy not. How would I pass all of these back?

 

Thanks,

 

Luke

 

[cags]

If your function is defined like so...

 

function search_field($startFrom, $recordperPage, $searchCat, $searchField) {
  // other code here
}

 

It is expecting you to pass those 4 variables in when you call the function like so...

 

$results = search_field($startFrom, $recordperPage, $searchCat, $searchField);

[lpruen]

Thats it. Thanks cags  . Bit of a newb but that makes complete sense, thanks again!

[PFMaBiSmAd]

Edit: Repeats some of what cags posted above...

 

The problem is not necessarily in your function definition, but in how you are calling it -

 search_field($startFrom, $recordperPage, $searchCat, $searchField);
                                    
echo search_field($dbResult);

 

That code calls search_field() twice.

 

The first time you provide all four parameters and guess what, that call is probably successful and does not produce an error. However, the funciton returns the value from the mysql_query(), but the call to the function does not assign that value to anything, so it won't be available for any purpose.

 

The second call to search_field() only contains one of the required parameters AND that parameter both does not exist (because the first call to the function did not assign the returned value to anything) and that value is not of the type expected for the $startFrom parameter. Even if you had assigned what the first call to the function returned you could not call your function a second time because you cannot take the result of one mysql_query, which is a result resource when the query works, and put that directly into another query. You must fetch something from a result resource for it to be usable.