hello,
I have a numeric paramter that I give it's value in the url like that

product.php?category_id=1

sometimes I need to display all the categories how to do that without making another page , is there a way that I can pass a wildcard character to the select syntax so I get all records ?

------------------------------------
sql :

SELECT *
FROM products
WHERE (category_id = colname)

-------------------------------------------
I use $_GET['category_id'] for colname parameter

I use dreamweaver 8.02

thanks

It all depends on how you want it to be parsed i.e. i did 1 last week for personal use where is check if parameter is empty, if it is then my query is transformed into a select all.
<pre id=code><font face=courier size=2 id=code>
$SQL = "SELECT * from products";
if (empty($urlparam) || $urlparam == ""
{
$SQL.=WHERE (category_id = $urlparam);
}
</font id=code></pre id=code>

but how to do that using dreamweaver is there a way to enter a wildcard in query dialog in dreamweaver ?

thanks

You're using an ID unless am not understanding your question then WHY would you need a wildcard??? You quoted <BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>I have a numeric paramter that I give it's value in the url like that <hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote> if this is a key field then no need for a wildcard. But like i said if you want it to select all the data in you database if the parameter is not present or is present but empty then try to use the code below.
<pre id=code><font face=courier size=2 id=code>
$SQL = "SELECT * from products";
if (empty($urlparam) || $urlparam == ""
{
$SQL.="WHERE (category_id = $urlparam)";
}
</font id=code></pre id=code>

ok but if there is more than one parameter
for example category_id, subcat_id, product_name

I need to search for all products that contains the word "shoes" without the use of first two parameter

now please tell me what is the sql for this situation for both using categories id without product name or using product name only on the same page ?

thanks

Aaah, now i understand. In mysql there's something called boolean search. It allow you to search in * fieldname with * parameter.

sorry for not making myself clear but I put this * in the url

products.php?category_id=5&subcat_id=*

and I get nothing

wildcard in mysql is this %

ok I still get nothing

products.php?category_id=5&subcat_id=%


in dreamweaver 8 there is a place for variable default value when you make a new query in advanced dialog so I placed % I also tried *
but I can not get all the result

I want to know when dreamweaver will use the default value of a variable so I can get all the records

thanks

Ok in which case just send your code through i it will be fixed.

thanks

<?php
if (!function_exists("GetSQLValueString")) {
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;
}
}

$maxRows_rs_products = 5;
$pageNum_rs_products = 0;
if (isset($_GET['pageNum_rs_products'])) {
  $pageNum_rs_products = $_GET['pageNum_rs_products'];
}
$startRow_rs_products = $pageNum_rs_products * $maxRows_rs_products;

$colname_rs_products = "-1";
if (isset($_GET['category_id'])) {
  $colname_rs_products = (get_magic_quotes_gpc()) ? $_GET['category_id'] : addslashes($_GET['category_id']);
}
$colname2_rs_products = "*"; //<-------- here is the wildcard
if (isset($_GET['subcat_id'])) {
  $colname2_rs_products = (get_magic_quotes_gpc()) ? $_GET['subcat_id'] : addslashes($_GET['subcat_id']);
}
mysql_select_db($database_mainsqldb, $mainsqldb);
$query_rs_products = sprintf("SELECT * FROM products WHERE (category_id = %s) and (subcat_id = %s) ORDER BY prod_name ASC", GetSQLValueString($colname_rs_products, "int"),GetSQLValueString($colname2_rs_products, "int"));
$query_limit_rs_products = sprintf("%s LIMIT %d, %d", $query_rs_products, $startRow_rs_products, $maxRows_rs_products);
$rs_products = mysql_query($query_limit_rs_products, $mainsqldb) or die(mysql_error());
$row_rs_products = mysql_fetch_assoc($rs_products);

if (isset($_GET['totalRows_rs_products'])) {
  $totalRows_rs_products = $_GET['totalRows_rs_products'];
} else {
  $all_rs_products = mysql_query($query_rs_products);
  $totalRows_rs_products = mysql_num_rows($all_rs_products);
}
$totalPages_rs_products = ceil($totalRows_rs_products/$maxRows_rs_products)-1;

function DoFormatCurrency($theObject,$NumDigitsAfterDecimal,$DecimalSeparator,$GroupDigits,$CurrencySymbol) { 
	$currencyFormat=$CurrencySymbol.number_format($theObject,$NumDigitsAfterDecimal,$DecimalSeparator,$GroupDigits);
	return ($currencyFormat);
}
?>

1 thing before i modify anything how many parameters will u use as search criterias in this query?

this code contains two parameter
category_id
subcat_id

somtimes I need to supply only a ctegory_id

thanks

Mod. Try it out.
<pre id=code><font face=courier size=2 id=code>
&lt;?php
if (!function_exists("GetSQLValueString") {
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;
}
}

$maxRows_rs_products = 5;
$pageNum_rs_products = 0;

if (isset($_GET['pageNum_rs_products'])) {
$pageNum_rs_products = $_GET['pageNum_rs_products'];
}
$startRow_rs_products = $pageNum_rs_products * $maxRows_rs_products;

//$colname_rs_products = "-1"; &lt;-- What is this?????
if (isset($_GET['category_id']) || isset($_GET['subcat_id'])) {
$where = "where ";
}
$colname_rs_products = (isset($_GET['category_id']) ? "category_id = ".$_GET['category_id'] : "";

$colname_rs_products.= (isset($_GET['subcat_id']) ? $and ." subcat_id =".$_GET['subcat_id'] : "";

mysql_select_db($database_mainsqldb, $mainsqldb);
$query_rs_products = "SELECT * FROM products $where $colname_rs_products ORDER BY prod_name ASC";
$query_limit_rs_products = sprintf("%s LIMIT %d, %d", $query_rs_products, $startRow_rs_products, $maxRows_rs_products);
$rs_products = mysql_query($query_limit_rs_products, $mainsqldb) or die(mysql_error());
$row_rs_products = mysql_fetch_assoc($rs_products);

if (isset($_GET['totalRows_rs_products'])) {
$totalRows_rs_products = $_GET['totalRows_rs_products'];
} else {
$all_rs_products = mysql_query($query_rs_products);
$totalRows_rs_products = mysql_num_rows($all_rs_products);
}
$totalPages_rs_products = ceil($totalRows_rs_products/$maxRows_rs_products)-1;

function DoFormatCurrency($theObject,$NumDigitsAfterDecimal,$DecimalSeparator,$GroupDigits,$CurrencySymbol) {
$currencyFormat=$CurrencySymbol.number_format($theObject,$NumDigitsAfterDecimal,$DecimalSeparator,$GroupDigits);
return ($currencyFormat);
}
?&gt;
</font id=code></pre id=code>

Edited by - roders22 on 31 May 2006 16:28:48

thanks alot

so there is no way in dreamweaver to only supply a wildcard
and I get all the records I have to modify the code by hand

(I have a new topic about sitemappath I need your advice about it)

ok thanks again

U'r welcome. You will always have to specify that if a wildcare is present then use it. But then it comes to the same thing as i did in your code.

Hi robby -

Im hoping you will see this post..... I am trying to do the exact same thing as this other post. That is I have one page being used for two different functions. Its for an Alaska tour company. One, is a user can select their destination (ie. Juneau Alaska) and then a populated drop down will display only the categories that are available in Juneau (such as helicopter tours, whale watching, etc). The two drop downs unsed for that selection process are then used to populate two url parameter with an "id" for the specified category and "location" parameter that passes their chosen location. however, also on that page is a list of all categories of tours in all of alaska. If a user clicks one of those links (say whale watching) they are taken to the same redirect detail page. Accept now instead of know both their tour category and their location all we know is their chosen tour category.

So , as you can see. In scenario one it is easy to filter records based on their choices, but in scenario two no records are retrieved because no "location" parameter has been set by which to filter. I tried to implement what you have done here to my code but I get errors that appear to be associated with too many nested double quotes. I get a "unexpected ';' on line 63 error. If I replace one of the double quotes with a single quote I get new T-STRING errors.

ANy ideas how I might accomplish this?

Thanks so much

Adam
<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
Mod. Try it out.
<pre id=code><font face=courier size=2 id=code>
&lt;?php
if (!function_exists("GetSQLValueString") {
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;
}
}

$maxRows_rs_products = 5;
$pageNum_rs_products = 0;

if (isset($_GET['pageNum_rs_products'])) {
$pageNum_rs_products = $_GET['pageNum_rs_products'];
}
$startRow_rs_products = $pageNum_rs_products * $maxRows_rs_products;

//$colname_rs_products = "-1"; &lt;-- What is this?????
if (isset($_GET['category_id']) || isset($_GET['subcat_id'])) {
$where = "where ";
}
$colname_rs_products = (isset($_GET['category_id']) ? "category_id = ".$_GET['category_id'] : "";

$colname_rs_products.= (isset($_GET['subcat_id']) ? $and ." subcat_id =".$_GET['subcat_id'] : "";

mysql_select_db($database_mainsqldb, $mainsqldb);
$query_rs_products = "SELECT * FROM products $where $colname_rs_products ORDER BY prod_name ASC";
$query_limit_rs_products = sprintf("%s LIMIT %d, %d", $query_rs_products, $startRow_rs_products, $maxRows_rs_products);
$rs_products = mysql_query($query_limit_rs_products, $mainsqldb) or die(mysql_error());
$row_rs_products = mysql_fetch_assoc($rs_products);

if (isset($_GET['totalRows_rs_products'])) {
$totalRows_rs_products = $_GET['totalRows_rs_products'];
} else {
$all_rs_products = mysql_query($query_rs_products);
$totalRows_rs_products = mysql_num_rows($all_rs_products);
}
$totalPages_rs_products = ceil($totalRows_rs_products/$maxRows_rs_products)-1;

function DoFormatCurrency($theObject,$NumDigitsAfterDecimal,$DecimalSeparator,$GroupDigits,$CurrencySymbol) {
$currencyFormat=$CurrencySymbol.number_format($theObject,$NumDigitsAfterDecimal,$DecimalSeparator,$GroupDigits);
return ($currencyFormat);
}
?&gt;
</font id=code></pre id=code>

Edited by - roders22 on 31 May 2006 16:28:48
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

Adam Stockland

so if i understood well you have a page where you're not sending the same parameters to your query right??
In 1 there's a location_id and a category_id and in the other page its just a category_id. Right??

Also it says unexpected ';' on line xx
1. Check that you've closed all your quotes properly.

If the above is ok and isn't causing the problem then try to use some kind of statement to check run your query with the parameters depending on which id you're sending to it.
For e.g.

<pre id=code><font face=courier size=2 id=code>
if(!empty($_GET['location_id']) && !empty($_GET['category_id']))
{
$query = "select * from mytable where category_id = ".$_GET['category_id']." and location_id = ".$_GET['location_id'];
}elseif(empty($_GET['location_id'] && !empty($_GET['category_id'])){
$query = "select * from mytable where category_id = ".$_GET['category_id'];
}else{
$query = "select * from mytable";
}
</font id=code></pre id=code>

Of course you don't have to write it like that but its just an idea on how you can make it work.

Another easy way ...(well its easy for me, because it saves time lol) pass an extra parameter for e.g.
lets say in post 1 you pass in your url something like that
<pre id=code><font face=courier size=2 id=code>www.myurl.com?action=form&category_id=2&location_id=3</font id=code></pre id=code>
this says that i've submitted this from my form and that i want to read both category_id and location_id
<pre id=code><font face=courier size=2 id=code>www.myurl.com?action=link&category_id=2&location_id=3</font id=code></pre id=code>
this says that i've clicked on a link and i only want to read only 1 param category_id
<pre id=code><font face=courier size=2 id=code>
if($_GET['action']=='form']
{
$query="select * from mytable where category_id = 2 and location_id = 3";
}elseif($_GET['action']=='link']
{
$query="select * from mytable where category_id = 2";
}
</font id=code></pre id=code>
I've just minimized everything by just stating which of the 2 parameters i want to read depending on another parameter in this situation the parameter being "action" but its basically the same as the first 1.

I hope i didn't confused the whole thing.


Edited by - roders22 on 10 Dec 2008 18:53:18

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
so if i understood well you have a page where you're not sending the same parameters to your query right??
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>
Yes, What I would like to do is be able to use one page to process three different scenarios. One, we know form variable A, OR two we might know form variable B but NOT form variable A, OR three we might know both. As of now I have three different pages.....one to handle each scenario. If there is a way to pass a wild card via the url parameter I could have one page process all three different requests by just telling the query that by default if a selection is not made (to limit the number of records) then return all records. As it is now if the query isnt given both criteria to search with it returns zero results.

I hope that help you understand what I am trying to do. It sure seems like if the user doesnt make a selection on one of the form elements you could just pass % in the url parameter to see all records....but, as you know, that doesnt work..

Thanks

PS
Ill see if I can post some code....

Adam Stockland

send some code but try to see if my previous modified post help you to work it out.

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
send some code but try to see if my previous modified post help you to work it out.
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>
Thanks for the help. Ill play with it a bit more....

Adam Stockland