printf or similar inside an SQL query?

I saw a similar question but no answers yet.

I have the following SQL query:

$sql = mysql_query("SELECT * FROM patient_db_0004 WHERE MATCH ( Name, id_number ) AGAINST ('$term' IN BOOLEAN MODE);");

where patient_db_0004 is I want to print an auto increment index like "0001,0002,0003,0004" inside m开发者_Python百科y SQL query.

printf("%04s",$i);

To print the values inside the query.

On MySQL you can use the string functions. For your example, you might want to try LPAD

SELECT LPAD(`id`, 4, '0') FROM patient_db_0004

You could search for similar functions for your database engine.

Sample output:

mysql> SELECT LPAD(`id` , 4, '0') FROM `patient_db_0004` LIMIT 10;
+---------------------+
| LPAD(`id` , 4, '0') |
+---------------------+
| 0001                |
| 0002                |
| 0003                |
| 0004                |
| 0005                |
| 0006                |
| 0007                |
| 0008                |
| 0009                |
| 0010                |
+---------------------+
10 rows in set (0.00 sec)

(I've got an auto_increment field called id in the above example)

Edit: based on your comment, it seems Vincent's answer is what you are looking for:

<?php
$term = 'foobar';
for($i=1; $i<5; $i++)
{
    echo(sprintf("SELECT * FROM patient_db_%04d WHERE MATCH (Name, id_number) AGAINST ('$term' IN BOOLEAN MODE);\n", $i));
}
?>

Gave me:

SELECT * FROM patient_db_0001 WHERE MATCH (Name, id_number) AGAINST ('foobar' IN BOOLEAN MODE);
SELECT * FROM patient_db_0002 WHERE MATCH (Name, id_number) AGAINST ('foobar' IN BOOLEAN MODE);
SELECT * FROM patient_db_0003 WHERE MATCH (Name, id_number) AGAINST ('foobar' IN BOOLEAN MODE);
SELECT * FROM patient_db_0004 WHERE MATCH (Name, id_number) AGAINST ('foobar' IN BOOLEAN MODE);

If I understand correctly, you are trying to run these queries. So what you might consider doing here is:

<?php
// ...
for($i=1; $i<5; $i++)
{
    $query = sprintf("SELECT * FROM patient_db_%04d WHERE MATCH (Name, id_number) AGAINST ('$term' IN BOOLEAN MODE);\n", $i);
    $sql = mysql_query();
}
?>

You want to use sprintf instead of printf (which prints the value to the string) :

$query = sprintf("SELECT foo FROM bar WHERE baz = '%s'", "hi");
$sql = mysql_query($query);

However, keep in mind that this does not prevent you from SQL injections. You still have to use mysql_real_escape_string on user-provided data.