0

sorry if this is a basic question! up till now i have been creating mysql queries in php and passing across variables within the sql call like this:

$myparam = $_GET["id_tbl"];

mysql_select_db($database_camerich, $camerich);
$query_rs_table = sprintf("SELECT * FROM table_tbl WHERE idimg_tbl = ".$myparam." ORDER BY order_tbl ";
$rs_table = mysql_query($query_rs_table, $camerich) or die(mysql_error());
$row_rs_table = mysql_fetch_assoc($rs_table);
$totalRows_rs_table = mysql_num_rows($rs_table);

I've managed to create this on the server as table_view (without the filter) which i am able to retrive ok.

mysql_select_db($database_camerich, $camerich);
    $query_rs_table = sprintf("SELECT * FROM table_view";
    $rs_table = mysql_query($query_rs_table, $camerich) or die(mysql_error());
    $row_rs_table = mysql_fetch_assoc($rs_table);
    $totalRows_rs_table = mysql_num_rows($rs_table);

I can filter this:

$query_rs_table = sprintf("SELECT * FROM table_view WHERE idimg_tbl = ".$myparam."";

i would like to know how to set up a filter on the query dynamically on the server rather than filtering the results in php after the the query is returned.

i hope that makes sense.

thanks

Dizzy Bryan High
  • 2,015
  • 9
  • 38
  • 61
  • 2
    Not an answer to your question, but your script is vulnerable by [SQL injection](http://stackoverflow.com/questions/568995/best-way-to-defend-against-mysql-injection-and-cross-site-scripting) - you need to fix that – Pekka Oct 13 '10 at 10:29
  • And, aren't you already filtering the query? I'm not sure I understand what you want to do – Pekka Oct 13 '10 at 10:30
  • Thanks for that pecca, this isnt the actual code i'm using, was just to give an example, i can filter the query with php but i want the sql server to filter the data before its sent back to the clent. but i dont know how to pass the values to mysql so it knows what to filter by – Dizzy Bryan High Oct 13 '10 at 10:44
  • or maybe i need to use a stored procedure to filter the data on the mySQL server? but still i dont know how to pass values to a stored procedure from php... – Dizzy Bryan High Oct 13 '10 at 10:57
  • Are you looking for 'Prepared Statement' functionality? – Phill Pafford Oct 13 '10 at 13:33
  • am not sure what a prepared statement is, basically i want to get data from the server and have the data filtered before the request is sent back to the client, so i dont have to filter it in php, i have not done this before so dont know the technical term for this! – Dizzy Bryan High Oct 13 '10 at 17:18

1 Answers1

1

Here are a few suggestions that I hope you will try in order:

  1. Switch to using PDO for your database access -- PDO is a more modern database access interface and it is object oriented. It also supports prepared statements. - http://www.php.net/manual/en/book.pdo.php
  2. Switch your view to a stored procedure. MySQL Views do not take parameters. Views also have other limitations as shown here: http://dev.mysql.com/doc/refman/5.1/en/create-view.html. Stored procedures accept parameters and can be called via PDO: http://php.net/manual/en/pdo.prepared-statements.php
  3. That all being said, you should really evaluate if you really need a stored procedure.
Wil Moore III
  • 6,968
  • 3
  • 36
  • 49
  • thanks for that wilmoore, i will read about pdo, at the moment i am just trying to learn about stored procedures, and how they work, so i have another tool in my box as it were! I am guessing that stored procedures are probably best for updating inserting records, rather than obtaining filterd data, as calling a view and then filtering it via php is no big difficulty. – Dizzy Bryan High Oct 14 '10 at 09:08