Page 1 of 1

select * from database WHERE %s = %s

Posted: Mon Sep 06, 2010 7:12 pm
by omnigate
I'm trying to create a master / detail page using some preset search links on the master page to pull from a mySQL database for the detail page.

ie. Marbles, Red.

So, if someone clicks on the Marbles link on the master page I would like the SQL statement to be:
Select * from database WHERE 'type' = marbles.

Or if someone clicks on the Red link, I would like the SQL statement to be:
Select * from database WHERE 'color' = red.

The trick is I'm only using a single detail page. So I would like to use variables for the WHERE statement.

I've tried many different combinations and looked thru the PHP manual and on the web but cannot seem to figure this out.

This is my current statement:

Code:
$query_mini = sprintf("SELECT * FROM database WHERE %s = %s", GetSQLValueString($vid, "text"), GetSQLValueString($mid, "text"));

I'm trying to connect to the page via page.php?vid=type&mid=marble
Or trying to connect to the page via page.php?vid=color&mid=red

I've verified the table names and field names match exactly.

I've setup an echo statement to verify the GET Statements are pulling the URL parameters correctly.

Please help.

Thanks,

Steve

Re: select * from database WHERE %s = %s

Posted: Mon Sep 06, 2010 10:42 pm
by Christopher
It should be:

Select * from database WHERE color = 'red';

So:
$query_mini = sprintf("SELECT * FROM database WHERE %s = '%s'", GetSQLFieldNameString($vid), GetSQLValueString($mid, "text"));

Not sure what the function is, but I found this code on the Adobe site:

Code: Select all

	function GetSQLFieldNameString($theFieldName)  {   
		return preg_replace('/[^0-9A-Za-z\_\.]/', '', $theFieldName); 
	}

	function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")  {   
		$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;   
		$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);   
		switch ($theType) {     
			case "text":       
				$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";       
				break;      
			case "long":     
			case "int":       
				$theValue = ($theValue != "") ? intval($theValue) : "NULL";       
				break;     
			case "double":       
				$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";       
				break;     
			case "date":        
				$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";        
				break;     
			case "defined":       
				$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;       
				break;  
		}   
		return $theValue; 
	} 

Re: select * from database WHERE %s = %s

Posted: Tue Sep 07, 2010 11:48 am
by omnigate
I already had the function GetSQLValueString specified, so added the function GetSQLFieldNameString for the $vid piece. But the preg_replace line chirped at me until I removed the [

However I'm still getting no matches (The screen populates with the frame and css coding, no errors and no data).

I was getting the following SQL error until I switched the single quote to the first %s instead of the second %s

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 'set = ''aberrations''' at line 1

It echo's the correct information for the variables - $vid = set (field) and $mid = aberrations (value), not sure why it won't display any matches. I have a second page setup with the same information without the variables and it displays the 2 matches in the table correctly.

Here are my echos:

Code: Select all

echo "vid=".$vid." and mid=".$mid; ?>
echo sprintf("SELECT * FROM minis WHERE '%s' = %s", GetSQLFieldNameString($vid), GetSQLValueString($mid, "text")); ?>
which displays:
vid=set and mid=aberrations
SELECT * FROM minis WHERE 'set' = 'aberrations'

Re: select * from database WHERE %s = %s

Posted: Tue Sep 07, 2010 1:11 pm
by omnigate
Woohoo. It seems to be working now. This is the final SQL statement:

Code: Select all

$query_mini = sprintf("SELECT * FROM minis WHERE `%s` = %s", GetSQLFieldNameString($vid), GetSQLValueString($mid, "text"));
It all came down to the quotes around the first %s.

Thanks for your help!

Re: select * from database WHERE %s = %s

Posted: Tue Sep 07, 2010 6:27 pm
by Christopher
omnigate wrote:I already had the function GetSQLValueString specified, so added the function GetSQLFieldNameString for the $vid piece. But the preg_replace line chirped at me until I removed the [
You needed to add a ], not remove a [. ;) I fixed the code above.