How to use a variable in PHP that store in SQL query in database

Question

I stored my SQL query in database and at this query i used PHP variable; then when i fetch the data from MySQL the variable which used in query does not work and query die! what should i do?

Exp: I stored this query on database:

"SELECT * FROM mytable WHERE id='$id' OR name='$name'"

and then when fetch this query the variables does not work in code like this:

mysqli_query($conn,$sql)

or

mysqli_query($conn,"$sql")

It is unclear what you actually do in your code. What do you really mean by "I stored this query on database"? Is it really the _query_ you stored? Please post a working code example demonstrating the issue, not just single lines. Add that information to the question itself, do _not_ post it into a comment to the question. Thanks. — arkascha, Oct 08 '18 at 06:31
What does "_does not work_" mean? You get a white page? You get the wrong results? What errors do you get? — brombeer, Oct 08 '18 at 06:32
check database connection with correct username and password — sam, Oct 08 '18 at 07:04
Does the *die message* include the actual error that has occurred and can you show us what it is? — Nigel Ren, Oct 08 '18 at 07:07
in other words, when i fetch the variable in SQL query from database, the variable fetch like a character string and does not initialize by compiler — amirhosein, Oct 08 '18 at 08:00

score 0 · Answer 1 · answered Oct 08 '18 at 06:32

0

using (.) concetinate operator in php and more detail see

"SELECT * FROM mytable WHERE id='".$id."' OR name = '".$name."'"

answered Oct 08 '18 at 06:32

Jignesh Joisar

13,720
5
57
57

i did it but when i fetch the data the compiler does not understand the variables and echo the ' ".$id." ' like a string and does not initialize it. – amirhosein Oct 08 '18 at 07:59

score 0 · Answer 2 · answered Oct 08 '18 at 06:32

mysqli_query - Performs a query on the database 
mysqli_fetch_array - Fetch a result row as a numeric array and as an associative array

try following code to fetch data

$sql= mysqli_query($coni, "SELECT * FROM mytable WHERE id='$id' OR name='$name'");
$info = mysqli_fetch_array($sql)

score 0 · Answer 3 · answered Oct 08 '18 at 06:47

You have to use mysqli_fetch_array() after executing mysqli_query(). The following code will print all rows matching your query.

$result = mysqli_query($conn, $sql);
while($row=mysqli_fetch_array($result)){
    print_r($row);
}

If that does not work. Be sure that you have the correct link to the database

$conn= mysqli_connect("127.0.0.1", "my_user", "my_password", "my_db");

More info about mysqli_connect can be seen in the documentation.

But it is more safe to use the PDO(PHP Database Object), beacuse it gives a better layer of security with prepared statements.

There is a tutorial about PDOs. Look at it for more details. For even more details refer the PDO documentaion.

score 0 · Accepted Answer · answered Oct 08 '18 at 07:14

One of the problems of storing a statement as you have in the database is that it assumes that there are variables called $id and $name in scope at the time you execute the statement. If instead you used prepared statements and place holders, you can bind the values you need at the time you run the statement.

So your statement would be...

"SELECT * FROM mytable WHERE id=? OR name=?"

You would then run it using

$stmt = mysqli_prepare($conn,$sql);
mysqli_stmt_bind_param($stmt, "ss", $id1, $searchNname);
mysqli_stmt_execute($stmt);
// Process data as you need to

(Still not convinced that holding these statements in the database is a good idea, but that is up to you.)

How to use a variable in PHP that store in SQL query in database

4 Answers4