filtering by text field instead of int field

[bigbird3156]

Hi I am trying to view records on my data base according to a text based field rather than a numerical one...

I cant get it to work... at this stage it simply shows all records from the database, not just the ones that were passed to it...

Here is the code that passes the value to the page... it seems to work if the url is anything to go by...

<a href="des_cat.php?designer=<?php echo $row_view_catalogue["designer"]; ?>">

and here is the code from the actual display page...

$colname_view_des_cat = "-1";
if (isset($_GET['designer'])) {
  $colname_view_des_cat = $_GET['designer'];
}
mysql_select_db($database_CA_database, $CA_database);
$query_view_des_cat = sprintf("SELECT rec_num, prod_num, prod_name, designer FROM products WHERE designer = %d, GetSQLValueString($colname_view_des_cat, "text"));
$view_des_cat = mysql_query($query_view_des_cat, $CA_database) or die(mysql_error());
$row_view_des_cat = mysql_fetch_assoc($view_des_cat);
$totalRows_view_des_cat = mysql_num_rows($view_des_cat);
?>

<?php do { ?>
  <table width="100%" border="0">
    <tr>
      <td width="18%"><?php echo $row_view_des_cat['rec_num']; ?></td>
      <td width="19%"><?php echo $row_view_des_cat['prod_num']; ?></td>
      <td width="39%"><?php echo $row_view_des_cat['prod_name']; ?></td>
      <td width="24%"><?php echo $row_view_des_cat['designer']; ?></td>
    </tr>
  </table>
  <?php } while ($row_view_des_cat = mysql_fetch_assoc($view_des_cat)); ?>
</body>
</html>
<?php
mysql_free_result($view_des_cat);
?>

I tried changing the $colname_view_des_cat = "-1"; line to text instead of -1 because I think I heard that was needed somewhere but it did not seem to make any difference...

can someone give me some leads...

thanks heaps

The Bird from Down Under- Bigbird 3156
Programmer?? - I thought the option was pretender not programmer!!

 

[vacunita]

GetSQLValueString is a Dreamweaver custom PHP function, and I'm not sure what it does exactly.

But if you want to pass a test string, why are you telling it its an integer?

$query_view_des_cat = sprintf("SELECT rec_num, prod_num, prod_name, designer FROM products WHERE designer = [red]%d[/red]", GetSQLValueString($colname_view_des_cat, "text"));

The part in red (%d) tells the sprintf function to expect a number. If you give it a text string, it equates to 0.

change the %d to %s so it expects a string.

You should also surround your text string in your query with single quotes.
So:

$query_view_des_cat = sprintf("SELECT rec_num, prod_num, prod_name, designer FROM products WHERE designer = [COLOR=blue]'[/color][COLOR=white red]%s[/color][COLOR=blue]'[/color], GetSQLValueString($colname_view_des_cat, "text"));

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[bigbird3156]

Thanks for that...

however after making this change I get the following error...

Parse error: syntax error, unexpected T_STRING in /home/creativ3/public_html/csstest/des_cat.php on line 39

if I leave the " in after the '%s' (ie '%s'") I get this 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 'Rosalie Quinlan Designs''' at line 1

any ideas

The Bird from Down Under- Bigbird 3156
Programmer?? - I thought the option was pretender not programmer!!

 

[vacunita]

You are missing a closing double quote there. I did not correct it since I didn't know if it was a typo or not.

but you need a " before the comma.

$query_view_des_cat = sprintf("SELECT rec_num, prod_num, prod_name, designer FROM products WHERE designer = '%s'[COLOR=white red]"[/color], GetSQLValueString($colname_view_des_cat, "text"));

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[bigbird3156]

That was what I was saying, when I have the double quote there I get the second 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 'Rosalie Quinlan Designs''' at line 1

as you can see the error seems to include the double quote next to the single quote at the end of 'Rosalie Quinlan Designs'''

The Bird from Down Under- Bigbird 3156
Programmer?? - I thought the option was pretender not programmer!!

 

[jpadie]

Getsqlvaluestring enquotes automatically.

But it is flawed as a function. I posted a fix for it in this forum a month or so ago.

 

[bigbird3156]

with or without the " I get an error though (the errors listed 4 posts up)... the first error is without the " and refers to the line in question the second error is with the "...

btw japadie, do you know which thread the fix was listed under I have looked around a bit and could not find it, but I will go back again after posting this

The Bird from Down Under- Bigbird 3156
Programmer?? - I thought the option was pretender not programmer!!

 

[vacunita]

Ohh o.k sorry, since I did not know, what the Dreamweaver function did, I did not anticipate the fact that it already enquotes the value.

Just remove the single quotes around the %s and you should be fine.

Sorry about that.


----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[bigbird3156]

... and no quotes brings me back to the original problem of not displaying any records at all...

That is why in my original post I had %d instead of %s as %d displays ALL records and %s displays none...

stuff I have looked at since shows me that it should be %s... but I don't know what else has gone wrong...

Can anyone see what else could be wrong in the code to suggest why no records are being displayed?

The Bird from Down Under- Bigbird 3156

 

[bigbird3156]

Never mind - It seems to be working now... I am not sure why, because I have simply put it back to a state that it was originally in but that did not workk...???

sorry to take up your time with this one

The Bird from Down Under- Bigbird 3156