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
select * from database WHERE %s = %s
Moderator: General Moderators
- 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
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:
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)
Re: select * from database WHERE %s = %s
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:
which displays:
vid=set and mid=aberrations
SELECT * FROM minis WHERE 'set' = 'aberrations'
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")); ?>vid=set and mid=aberrations
SELECT * FROM minis WHERE 'set' = 'aberrations'
Re: select * from database WHERE %s = %s
Woohoo. It seems to be working now. This is the final SQL statement:
It all came down to the quotes around the first %s.
Thanks for your help!
Code: Select all
$query_mini = sprintf("SELECT * FROM minis WHERE `%s` = %s", GetSQLFieldNameString($vid), GetSQLValueString($mid, "text"));Thanks for your help!
- 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
You needed to add a ], not remove a [.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 [
(#10850)