select * from database WHERE %s = %s

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
omnigate
Forum Newbie
Posts: 4
Joined: Sun May 25, 2008 10:59 am

select * from database WHERE %s = %s

Post 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
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

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

Post 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; 
	} 
(#10850)
omnigate
Forum Newbie
Posts: 4
Joined: Sun May 25, 2008 10:59 am

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

Post 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'
omnigate
Forum Newbie
Posts: 4
Joined: Sun May 25, 2008 10:59 am

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

Post 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!
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

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

Post 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.
(#10850)
Post Reply