MySQL passing variable to dynamic query

Question

hi everyone,

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

Answer 1

Hi,

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.