MySQL passing variable to dynamic query

Question

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

Not an answer to your question, but your script is vulnerable by SQL injection - you need to fix that — Pekka
– Pekka, Commented Oct 13, 2010 at 10:29
And, aren't you already filtering the query? I'm not sure I understand what you want to do — Pekka
– Pekka, Commented Oct 13, 2010 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
– Dizzy Bryan High, Commented Oct 13, 2010 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
– Dizzy Bryan High, Commented Oct 13, 2010 at 10:57

Wil Moore III · Accepted Answer · 2010-10-13 23:42:38Z

1

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

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
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
That all being said, you should really evaluate if you really need a stored procedure.

answered Oct 13, 2010 at 23:42

Wil Moore III

7,2503 gold badges38 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dizzy Bryan High Over a year ago

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.

Collectives™ on Stack Overflow

MySQL passing variable to dynamic query

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related